Pg权限管理

授予权限的语法：

GRANT some_privileges ON object_type object_name TO role_name | user_name;

回收权限的语法：

REVOKE some_privileges ON object_type object_name TO role_name | user_name;

其中，授予权限的具体内容包括

-- 序列的授权
GRANT { { USAGE | SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

-- 表的授权
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] ) [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

-- 模式的授权
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

-- 库的授权
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

-- 域的授权
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

-- FOREIGN的授权
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

-- 函数|存储过程的授权
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...] | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

-- 语言的授权
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

-- 将角色授予给用户：
GRANT role_name [, ...] TO user_name [, ...] [ WITH ADMIN OPTION ]

上述 role_specification 可以是：
[ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER

样例

（1）创建用户

CREATE USER user_smy;

（2）创建角色

CREATE ROLE role_smy;

（3）允许角色连接数据库

GRANT CONNECT ON DATABASE db_smy TO role_smy;

（4）允许角色使用模式

GRANT USAGE ON SCHEMA schema_smy TO role_smy;

（5）对于已有的表，赋予角色select（或update，delete，insert等）权限

-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO role1;

GRANT SELECT ON ALL TABLES IN SCHEMA schema_smy TO role_smy;

（6）对于新增的表，赋予角色默认权限，后续不需再次赋权

-- ALTER DEFAULT PRIVILEGES IN SCHEMA schema_smy GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role1;

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_smy GRANT SELECT ON TABLES TO role_smy;

（7）角色赋予用户

GRANT role_smy TO user_smy;