前言
PostgreSQL使用角色的概念管理数据库访问权限。角色是一系列相关权限的集合。为了管理方便，通常把一系列相关的数据库权限赋给一个角色，如果哪个用户需要这些权限，就把角色赋给相应的用户。可以简单理解为用户组和用户的关系。在PostgreSQL中，角色与用户是没有区别的，一个用户也是角色。
用户和角色在整个数据库实例中是全局的，在同一个实例中的不同数据库中，看到的用户都是相同的。
安装初始化的时候有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。一般都叫“postgres”。
创建用户和角色
-- 创建角色
CREATE ROLE name [ [WITH] option [...] ]
-- 创建用户
CREATE USER name [ [WITH] option [...] ]
注意：创建用户方式创建出来的用户默认有 LOGIN 权限，而创建角色创建出来的用户没有 LOGIN 权限。
| option内容 | 说明 |
|---|---|
SUPERUSER |NOSUPERUSER |
创建出来的用户是否为超级用户 |
CREATEDB |NOCREATEDB |
指定创建出来的用户是否有创建数据库的权限 |
CREATEROLE |NOCREATEROLE |
指定创建出来的用户是否有创建其他角色权限 |
CREATEUSER |NOCREATEUSER |
指定创建出来的用户是否有创建其它用户的权限 |
INHERIT | NOINHERIT |
创建的用户拥有某个或几个角色的权限 |
LOGIN | NOLOGIN |
创建出来的用户是否有连接数据库的权限 |
CONNECTION LIMIT connlimit |
用户可以使用的并发连接的数量，默认为 "-1"，表示没有限制 |
[ENCRYPTED | UNENCRYPTED] PASSWORD 'password' |
存储的用户口令是否加密 |
VALID UNTIL 'timestamp' |
密码失效时间，不指定的话永久有效 |
IN ROLE role_name [...] |
指定用户成为哪些角色的成员 |
IN GROUP role_name [...] |
等同于IN ROLE rome_name，不过已过时 |
ROLE role_name [...] |
role_name 将成为这个新建的角色的成员 |
ADMIN role_name [...] |
role_name 将有这个新建角色 WITH ADMIN OPTION权限 |
USER role_name |
与ROLE相同，不过已过时 |
SYSID uid |
用于SQL兼容，实际没什么用 |
管理权限
用户的权限分为两类，一类是创建用户时指定的权限，这些权限可使用ALTER ROLE命令来修改。另一类权限由 GRANT | REVOKE 命令管理
ALTER ROLE
一般管理如下几种权限：
- 超级用户的权限
- 创建数据库的权限
- 是否允许登录的权限
语法：
-- option的含义和创建语句相同
ALTER ROLE name [ [ WITH ] option [...] ]
GRANT与REVOKE
一般管理如下几种权限：
- 创建SCHEMA
- 库中创建临时表的权限
- 连接某个数据库的权限
- 在模式中创建数据库对象的权限，比如创建表、视图、函数等
- 表中执行
SELECT、UPDATE、INSERT、DELETE等操作的权限 - 对序列进行查询、使用、更新的权限
- 白哦中创建触发器的权限
- 把表、索引等建到指定表空间的权限
语法：
-- 授予/撤销用户某个角色的权限
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
REVOKE [ ADMIN OPTION FOR ] role_name [, ...] FROM role_name [, ...] [ CASCADE | RESTRICT ]
-- 数据库逻辑结构对象的操作权限简写语法格式
GRANT some_privileges ON database_object_type object_name TO role_name;
REVOKE some_privileges ON database_object_type object_name FROM role_name;
-- GRANT语法
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] ) [,...] | ALL [ PRIVILEGES ] ( column [, ...] ) } ON [ TABLE ] table_name [, ...] TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE database_name [, ...] TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN DATA WRAPPER fdw_name [, ...] TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN SERVER server_name [, ...] TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...] | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE lang_name [, ...] TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } ON LARGE OBJECT loid [, ...] TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schema_name [, ...] TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespace_name [, ...] TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
-- REVOKE语法
REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] ) [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) } ON [ TABLE ] table_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ] { { USAGE | SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ] { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } ON DATABASE database_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON DOMAIN domain_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN DATA WRAPPER fdw_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN SERVER server_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...] | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE lang_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } ON LARGE OBJECT loid [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ] { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] } ON SCHEMA schema_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ] { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespace_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON TYPE type_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
示例：
-- 把查询table1表的权限授予所有用户
GRANT select on TABLE table1 to public;
-- 为用户zhangsan授予testdb1库所有权限
GRANT ALL PRIVILEGES ON DATABASE testdb1 TO zhangsan;
查看用户
在 psql 中输入\du或\dg
查看用户权限
-- 查看指定用户的系统权限
select * from pg_roles where rolename="zhangsan";
-- 查看指定用户的表权限
select * from information_schema.table_privileges where grantee="zhangsan";
-- 查看用户的USAGE权限
select * from information_schema.usage_privileges where grantee='zhangsan';
删除用户
drop role zhangsan;
示例
create role zhangsan with login;
grant all on database testdb to zhangsan;
-- 删除用户需要先取消授权
revoke all on database testdb from zhangsan;
drop role zhangsan;
参考
- 唐成 - 《PostgreSQL修炼之道：从小工到专家（第2版）》
- 官方文档 - Privileges