用户权限

用户和权限涉及以下术语:

角色:将权限赋予角色,角色内有多个用户
用户:登录的真实账号
权限:进入目标数据库设置权限并赋予角色

通常先创建角色,进入目标数据库分配合适的权限给角色再创建用户,将用户关联到角色

角色

角色是权限的集合，通常将权限授予角色，然后再把用户加入到角色。

创建

CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be
      SUPERUSER | NOSUPERUSER --超级用户,默认NOSUPERUSER
    | CREATEDB | NOCREATEDB --创建数据库,默认NOCREATEDB
    | CREATEROLE | NOCREATEROLE --创建角色,默认NOCREATEROLE
    | INHERIT | NOINHERIT 继承权限，可以把除superuser权限继承给其他用户/角色，默认NOINHERIT。
    | LOGIN | NOLOGIN --是否允许登录，具有 LOGIN 属性的角色也可以被看作一个 用户,默认NOLOGIN
    | REPLICATION | NOREPLICATION --启动复制,默认NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS --安全策略RLS权限，默认nobypassrls。
    | CONNECTION LIMIT connlimit --限制用户并发数，默认-1，不限制。正常连接会受限制，后台连接和prepared事务不受限制。
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL --设置密码，密码仅用于有login属性的用户，不使用密码身份验证，则可以省略此选项。加密方法由配置参数 password_encryption 确定，密码始终以加密方式存储在系统目录中。
    | VALID UNTIL 'timestamp' --密码过期时间,不设置则永不失效。
    | IN ROLE role_name [, ...] --新角色将立即添加为新成员
    | IN GROUP role_name [, ...] --新角色将立即添加为新成员
    | ROLE role_name [, ...] --ROLE子句列出一个或多个现有角色，这些角色自动添加为新角色的成员。
    | ADMIN role_name [, ...] --与ROLE类似，但命名角色将添加到新角色WITH ADMIN OPTION，使他们有权将此角色的成员资格授予其他人
    | USER role_name [, ...] --同上
    | SYSID uid --忽略

修改

ALTER ROLE 语句修改它的属性,语法同create

查看角色

SELECT rolname FROM pg_roles;

用户

语法

CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

创建

CREATE USER usera1 WITH PASSWORD 'pwd' IN ROLE role_rw_a; --创建用户并添加到角色

CREATE USER readb WITH PASSWORD 'pwd' ;
grant role_rw_a to readb; --添加用户到角色

删除

REVOKE role_rw_a FROM usera1;--从角色中移除用户

drop user reada;--删除用户.如果此用户有对象存在，则不允许删除，需要先把对象转移给其它用户
REASSIGN OWNED BY usera1 TO unvdb;--将reada的对象转移给unvdb
--DROP OWNED BY usera1; --谨慎使用。删除用户usera1同时删除他拥有的对象

赋权

语法如下：

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 { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN 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 ]

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 | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
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 ]

其中role_specification可以是：

[ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USER

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

权限和使用说明表：

权限名称	使用说明
SELECT	对于表和视图来说，表示允许查询，对于序列来说，表示允许使用 currval 函数。
INSERT	表示允许向特定的表中插入数据，拥有此权限，也表示允许使用 COPY FROM语句向表中插入数据。
UPDATE	对于表来说，表示允许更新表中数据。想要使用 SELECT…FOR UPDATE or SHARE 语句，也需要该权限。
DELETE	允许删除表中数据。
TRUNCATE	表示允许在表中执行 TRUNCATE 操作。
REFERENCES	创建外键约束时，需要参照列和被参照列，都拥有该权限。该权限可以授予表中所有列，或者其中一个列。
TRIGGER	允许在表中创建触发器。
CREATE	对于数据库来说，表示创建 SCHEMA 权限；对于模式来说，表示创建数据库对象权限；对于表空间来说，表示允许将表、索引创建到此表空间，或者移动到此表空间。
CONNECT	表示用户连接到指定数据库
TEMPORARY	表示允许使用指定数据库，使用临时表。
EXECUTE	允许调用函数或过程，包括使用在函数之上实现的任何运算符。这是适用于函数和过程的唯一权限类型。
USAGE	对于程序语言，允许使用语言来创建该语言的函数, 这是适用于过程语言的唯一权限类型。对于模式，允许访问模式中包含的对象（假设对象自己的权限要求也已得到满足）。对于序列, 允许使用currval 和 nextval 函数。对于类型和域，允许在创建表、函数和其他模式对象时使用类型或域。（注意，此权限不控制类型的全部 “usage” ，例如查询中出现的类型的值。它仅防止创建依赖于类型的对象。此权限的主要目的是控制哪些用户可以对类型创建依赖项，这可能会防止所有者以后更改类型。）对于外部数据包装器，允许使用外部数据包装器创建新服务器。对于外部服务器，允许使用服务器创建外部表。受让者还可以创建、更改或删除与该服务器关联的自己的用户映射。
ALL PRIVILEGES	表示授予所有的权限。

最佳实践

比如有以下需求: 有数据库 db_a,db_b; 用户 admin 是超级管理员,拥有所有权限；用户 readonly 是只读，允许读取所有数据库；用户 usera1 拥有 db_a 的所有权限,用户 reada 拥有 db_a 的只读权限; 用户 userb1 拥有 db_b 的所有权限,用户 readb 拥有 db_b 的只读权限;

创建角色

CREATE ROLE admin superuser PASSWORD 'admin' login; --创建超级管理员角色并设置密码
CREATE ROLE readonly PASSWORD 'readpwd' LOGIN;-- 创建所有库的只读角色

CREATE ROLE role_rw_a LOGIN; --创建读写角色
CREATE ROLE role_rw_b LOGIN;


CREATE ROLE role_r_a LOGIN; --创建只读角色
CREATE ROLE role_r_b LOGIN;

-- DROP ROLE role_rw_a;--删除角色

进入db_a数据库给角色分配权限

\c db_a; --进入目标数据库并配置权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly,role_r_a,role_rw_a;  --修改默认权限为可读，否则赋权后新增加的表需要重新赋权
GRANT CONNECT ON DATABASE db_a  TO readonly,role_r_a,role_rw_a; --允许只读用户连接数据库
GRANT USAGE ON SCHEMA public TO readonly,role_r_a,role_rw_a; --允许只读用户使用public模式

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly,role_r_a;--分配select权限给readonly,role_r_a
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO role_rw_a;--分配所有权限给role_rw_a

-- REVOKE  SELECT  ON ALL TABLES in SCHEMA public from readonly; --回收select权限从readonly角色中

进入db_b数据库给角色分配权限

\c db_b; --进入目标数据库并配置权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly,role_r_b,role_rw_b;  --修改默认权限为可读，否则赋权后新增加的表需要重新赋权
GRANT CONNECT ON DATABASE db_a  TO readonly,role_r_b,role_rw_b; --允许只读用户连接数据库
GRANT USAGE ON SCHEMA public TO readonly,role_r_b,role_rw_b; --允许只读用户使用public模式

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly,role_r_b;--分配select权限给readonly,role_r_b
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO role_rw_b;--分配所有权限给role_rw_b

-- REVOKE  SELECT  ON ALL TABLES in SCHEMA public from readonly; --回收select权限从readonly角色中

管理用户角色

CREATE USER usera1 WITH PASSWORD 'pwd' IN ROLE role_rw_a; --创建用户并添加到角色
CREATE USER reada WITH PASSWORD 'pwd' IN ROLE role_r_a;

CREATE USER userb1 WITH PASSWORD 'pwd' IN ROLE role_rw_b;
CREATE USER readb WITH PASSWORD 'pwd' IN ROLE role_r_b;

--grant role_rw_b to userb1; --添加用户到角色
REVOKE role_rw_a FROM usera1;--从角色中移除用户

drop user reada;--删除用户.如果此用户有对象存在，则不允许删除，需要先把对象转移给其它用户
REASSIGN OWNED BY usera1 TO unvdb;--将reada的对象转移给unvdb
--DROP OWNED BY usera1; --谨慎使用。删除用户usera1同时删除他拥有的对象

查看角色权限

\c db_a;--进入数据库
SELECT * from information_schema.table_privileges WHERE grantee = 'reada'; --查看reada角色对db_a数据库的权限