[postgresql]用户管理

前言

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