基于PostgreSQL 权限解读

–回收schema的public权限
–revoke all on schema public from public; –正常情况使用这个
revoke all on schema public,tiger,tiger_data,topology from public; –使用postgis扩展使用这个
–从core数据库收回public的所有权限
revoke all on database core from public;
revoke all on database postgres from public;
–重新将schema授予数据库所有者
grant all on schema public,tiger,tiger_data,topology to 数据库所有者;

create schema history;
revoke all on schema history from public;
–重新将schema history授予数据库所有者
grant all on schema history to 数据库所有者;

psql -h localhost -p 5432 -U postgres -d core

–创建一个名称sel的用户
create user sel with nosuperuser nocreatedb nocreaterole noinherit login noreplication nobypassrls password ‘123’;
–允许连接至数据库core
grant connect on database core to sel;

–查看角色使用\duS+
\duS+
–或
\duS+ sel

–查看用户在schema的权限
\dp

–查看数据库的用户
\l+

r — SELECT (“读”)
w — UPDATE (“写”)
a — INSERT (“追加”)
d — DELETE
D — TRUNCATE
x — REFERENCES
t — TRIGGER
X — EXECUTE
U — USAGE
C — CREATE
c — CONNECT
T — TEMPORARY
arwdDxt — ALL PRIVILEGES （对于表，对其他对象会变化）
* — 用于前述特权的授权选项

psql -h localhost -p 5432 -U sel -d core

drop table aaa;
create table aaa(
objectid integer
);
select * from features;

ERROR: table “aaa” does not exist
ERROR: no schema has been selected to create in
ERROR: relation “features” does not exist

psql -h localhost -p 5432 -U postgres -d core

grant usage on schema public to sel;
grant select on all tables in schema public to sel;

psql -h localhost -p 5432 -U sel -d core

select * from features;
delete from features;

psql -h localhost -p 5432 -U postgres -d core

–回收分配的权限并删除用户
revoke select on all tables in schema public from sel;
revoke usage on schema public from sel;
revoke connect on database core from sel;
drop role sel;

psql -U username -d database_name -h host -W

su username
psql

createuser username

CREATE ROLE rolename;

CREATE USER username;

CREATE ROLE role_name WITH optional_permissions;

CREATE ROLE username WITH LOGIN;

ALTER ROLE username WITH attribute_options;

ALTER ROLE username WITH NOLOGIN;

GRANT permission_type ON table_name TO role_name;

GRANT UPDATE ON demo TO demo_role; –赋予demo_role demo表的update权限
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to demo_role; –赋予demo_role所有表的SELECT权限

GRANT ALL ON demo TO demo_role; –赋给用户所有权限
GRANT SELECT ON demo TO PUBLIC; –将SELECT权限赋给所有用户

REVOKE permission_type ON table_name FROM user_name;

CREATE ROLE temporary_users;
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;

SET ROLE role_name; –切换到role_name用户
RESET ROLE; –切换回最初的role

ALTER ROLE test_user INHERIT;

DROP ROLE role_name;
DROP ROLE IF EXISTS role_name;