用户权限

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

• 角色:将权限赋予角色,角色内有多个用户

• 用户:登录的真实账号

• 权限:进入目标数据库设置权限并赋予角色

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

角色

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

创建

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数据库的权限