PostgreSQL 数据库权限、角色总结

目录

• 概述
• 1、权限
  • （1）权限的初始状态
  • （2）grant 和 revoke
  • （3）权限类型
  • （4）适用对象的类型
  • （5）默认情况
• 2、角色
  • （1）数据库角色
  • （2）角色属性
    • 角色属性的运用
  • （3）角色成员
  • （4）drop 角色
  • （5）预定义角色
  • （6）函数安全
• 3、grant 和 revoke 的使用
  • （1）grant
  • （2）revoke
  • （3）ALTER DEFAULT PRIVILEGES
• 4、权限管理的表格与视图
  • （1）查看用户
  • （2）查看角色
  • （3）查看某用户的表权限
  • （4）查看某用户在某表的列上的权限
  • （5）查看某用户的 usage 权限
  • （6）查看某用户在存储过程函数的执行权限
  • （7）查看当前用户能够访问的数据类型
  • （8）查看用户自定义类型上授予的USAGE权限
  • （9）查看角色之间的成员关系
  • （10）查看新建对象的默认权限
• 5、案例：创建只读账号
  • （1）登录
  • （2）创建用户
  • （3）设置访问数据库的权限
  • （4）设置访问 schema 的权限
  • （5）授予 SELECT 权限
  • （6）设置新建对象的默认权限
  • （7）（可选）设置搜索路径：

概述

学习如何配置、管理权限，至少需要掌握几个方面的内容：

• 数据库对象的权限的初始状态，以及权限类型、数据库对象的类型的对应关系。
• 角色属性、角色成员的配置，预定义角色，create/alter/drop role 的使用。
• grant 和 revoke 的使用。
• 常用于权限管理的表格与视图。

1、权限

参考资料

（1）权限的初始状态

对象的所有者（或超级用户）可以对该对象执行任何操作。

ALTER TABLE table_name OWNER TO new_owner;

"owner to" 一个新的所有者。

（2）grant 和 revoke

使用 grant 和 revoke 进行授权和撤权。

grant 权限类型 on 适用对象 to 用户（组）;
revoke 权限类型 on 适用对象 from 用户（组）;

在 SQL 标准中，用户和角色为不同的概念；在 PostgreSQL 中，用户和角色统一为一种实体，即用户和角色是“相同的”，用户是具有连接登录权限的角色。

（3）权限类型

权限类型包括：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE、SET、ALTER SYSTEM。

详情如下：

SELECT

• 允许从表、视图、具体化视图或其他类似表的对象的任何列或特定列执行 SELECT 操作。
• 还允许使用 COPY TO。
• UPDATE、DELETE 或 MERGE 也需要此权限。
• 对于 SEQUENCE，此权限还允许使用 currval 函数。
• 对于 LARGE OBJECT，此权限允许读取对象。

INSERT

• 允许将新行 INSERT 到表、视图等中。可以在特定列上授予，在这种情况下，只能在 INSERT 命令中使用这些列（其他列将获得默认值）。
• 还允许使用 COPY FROM。

UPDATE

• 允许对表、视图等的任何列或特定列执行 UPDATE。
• 对于 SEQUENCE，此权限允许使用 nextval 和 setval 函数。
• 对于 LARGE OBJECT，此权限允许写入或截断对象。

DELETE
允许从表、视图等中 DELETE 行。

TRUNCATE
允许对表进行 TRUNCATE。

REFERENCES
允许创建引用表的外键约束或表的特定列。

TRIGGER
允许在表、视图等上创建触发器。

CREATE

• 对于数据库，允许在数据库中创建新的 SCHEMA 和发布，并允许在数据库中安装 Trusted Extensions。
• 对于 SCHEMA，允许在 SCHEMA 中创建新对象。要重命名现有对象，您必须拥有该对象，并拥有此权限。
• 对于表空间，允许在表空间内创建表、索引和临时文件，并允许创建将表空间作为其默认表空间的数据库。

请注意，撤销此权限，不会更改现有对象的存在或位置（即不会删除对象）。

CONNECT
允许被授权者连接到数据库。在连接启动时检查此权限（除了检查 pg_hba.conf 施加的任何限制外）。

TEMPORARY
允许在使用数据库时创建临时表。

EXECUTE
允许调用函数或过程，包括使用在函数上实现的任何运算符。这是唯一适用于函数和过程的权限类型。

USAGE

• 对于过程语言（LANGUAGE），允许使用该语言创建函数。这是唯一适用于过程语言的权限类型。
• 对于 SCHEMA，允许访问 SCHEMA 中包含的对象（假设还满足对象自己的权限要求）。从本质上讲，这允许被授权者在 schema 中 “查找” 对象。如果没有此权限，仍然可以查看对象名称，例如，通过查询 system catalog。此外，撤销此权限后，现有会话可能具有以前执行过此 “查找” 的语句，因此这不是阻止对象访问的完全安全方法。
• 对于SEQUENCE，允许使用 currval 和 nextval 函数。
• 对于 DOMAIN 和 TYPE，允许在创建表、函数和其他架构对象时使用 DOMAIN 和 TYPE。此权限并不控制该类型的所有 “用法”，例如，查询中出现的 type 的值。它仅阻止创建依赖于 TYPE 的对象。此权限的主要目的是控制哪些用户可以创建对 TYPE 的依赖关系，这可能会阻止所有者以后更改类型。
• 对于 FOREIGN DATA WRAPPER，允许使用外部数据包装器创建新服务器。
• 对于 FOREIGN SERVER，允许使用服务器创建外部表。被授权者还可以创建、更改或删除与该服务器关联的用户映射。

SET
允许在当前会话中将服务器配置参数设置为新值。

ALTER SYSTEM
允许使用 ALTER SYSTEM 命令将服务器配置参数配置为新值。

---

另，需要特别注意的是，角色属性可以影响角色的权限，这些属性包括：是否为超级用户（SUPERUSER/NOSUPERUSER）、是否可以创建新角色（CREATEROLE/NOCREATEROLE）、是否可以启动流复制（REPLICATION/NOREPLICATION）、是否可以绕过行级安全性（BYPASSRLS/NOBYPASSRLS）、角色连接最大数量（CONNECTION LIMIT）。但这一般是在创建角色时，通过角色属性进行设置。

（4）适用对象的类型

权限及其适用的对象的类型：

权限类型	缩写	适用对象的类型
SELECT	r (“read”)	LARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table column
INSERT	a (“append”)	TABLE, table column
UPDATE	w (“write”)	LARGE OBJECT, SEQUENCE, TABLE, table column
DELETE	d	TABLE
TRUNCATE	D	TABLE
REFERENCES	x	TABLE, table column
TRIGGER	t	TABLE
CREATE	C	DATABASE, SCHEMA, TABLESPACE
CONNECT	c	DATABASE
TEMPORARY	T	DATABASE
EXECUTE	X	FUNCTION, PROCEDURE
USAGE	U	DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE
SET	s	PARAMETER
ALTER SYSTEM	A	PARAMETER

对象及其可以授予的权限：

对象的类型	权限	默认公开权限	psql Command
DATABASE	CTc	Tc	\l
DOMAIN	U	U	\dD+
FUNCTION or PROCEDURE	X	X	\df+
FOREIGN DATA WRAPPER	U	none	\dew+
FOREIGN SERVER	U	none	\des+
LANGUAGE	U	U	\dL+
LARGE OBJECT	rw	none	\dl+
PARAMETER	sA	none	\dconfig+
SCHEMA	UC	none	\dn+
SEQUENCE	rwU	none	\dp
TABLE (and table-like objects)	arwdDxt	none	\dp
Table column	arwx	none	\dp
TABLESPACE	C	none	\db+
TYPE	U	U	\dT+

（5）默认情况

默认情况下，PostgreSQL 在创建对象时，不会将某些类型的对象的权限授予 PUBLIC：FOREIGN DATA WRAPPER、FOREIGN SERVER、LARGE OBJECT、PARAMETER、SCHEMA、SEQUENCE、TABLE 、Table column 及 TABLESPACE。

默认情况下，PostgreSQL 在创建对象时，会将某些类型的对象的权限授予 PUBLIC：DATABASE 【CONNECT 和 TEMPORARY（创建临时表）权限】，FUNCTION or PROCEDURE【EXECUTE 权限】，DOMAIN、LANGUAGE、TYPE【USAGE 权限】。当然，对象所有者可以 REVOKE 这些默认权限，并显式授予权限。为了获得最大的安全性，请在创建对象的同一事务中发出 REVOKE；然后，其他用户就不能使用该对象。

此外，还可以使用 ALTER DEFAULT PRIVILEGES 命令覆盖这些默认权限设置。

2、角色

参考资料

（1）数据库角色

数据库角色在数据库集群安装中是全局的（而不是按单个数据库）。

新初始化的系统始终包含一个预定义的角色。此角色始终是“超级用户”，通常，此角色将命名为 postgres。

与数据库服务器的每个连接，都是使用某个特定角色的名称建立的，在该连接中，该角色确定初始访问权限。

特殊的 “角色” 名称 PUBLIC 可用于向系统上的每个角色授予权限。它自动地、隐式地包含了数据库中的每个用户。

任何特定角色都将具有：直接授予它的权限、授予它当前所属的组角色的权限，以及授予 PUBLIC 的权限。

（2）角色属性

LOGIN/NOLOGIN：

LOGIN：具有此属性的角色可以用于数据库连接，即可以视为登录用户。
NOLOGIN：没有此属性的角色不能用于数据库连接，默认值。

SUPERUSER/NOSUPERUSER：

SUPERUSER：超级用户拥有数据库的所有权限，包括创建数据库、创建角色、修改系统配置等。
NOSUPERUSER：非超级用户，其权限受到一定限制，默认值。

CREATEDB/NOCREATEDB：

CREATEDB：具有此属性的角色可以创建数据库。
NOCREATEDB：没有此属性的角色不能创建数据库，默认值。

CREATEROLE/NOCREATEROLE：

CREATEROLE：具有此属性的角色可以创建、修改和删除其他角色，以及给其他角色赋予或撤销权限。应将具有“创建角色”权限的用户，视为超级用户。
NOCREATEROLE：没有此属性的角色不能执行上述操作，默认值。

INHERIT/NOINHERIT：

INHERIT：具有此属性的角色会自动继承其所属组角色的权限，默认值。需要注意的是，角色属性 对应的权限并不被继承，例如，CREATEDB、CREATEROLE；即通过 create role 和 alter role 设置的角色属性，不被继承。但在 PostgreSQL 的早期版本中，会继承所有权限。被继承的，是诸如对表等对象的操作权限。另，需要注意的是，在 SQL 标准中，只有角色有继承属性，并且继承所有权限，而用户没有所谓的继承。
NOINHERIT：没有此属性的角色不会继承其所属组角色的权限，除非显式地执行SET ROLE命令来临时“成为”组角色。

REPLICATION/NOREPLICATION：

REPLICATION：具有此属性的角色可以启动流复制。
NOREPLICATION：没有此属性的角色不能启动流复制，默认值。

BYPASSRLS/NOBYPASSRLS：

BYPASSRLS：具有此属性的角色可以绕过行级安全性（Row-Level Security, RLS）检查【另，超级用用和所有者始终绕过 RLS 检查】。
NOBYPASSRLS：没有此属性的角色不能绕过RLS检查，默认值。

CONNECTION LIMIT：

此属性用于限制角色可以同时建立的连接数，但超级用户不受限。

PASSWORD：

用于设置角色的密码。如果设置了密码，则角色在连接数据库时需要提供该密码。

VALID UNTIL：

此属性用于设置角色的密码有效期。它仅定义密码的过期时间，而不是角色本身的过期时间。特别是，使用非基于密码的身份验证方法登录时，不会强制执行过期时间。

---

角色属性的运用

--CREATE：
CREATE ROLE name [ [ WITH ] option [ ... ] ];

/*
option 可以是一个或多个角色属性、参数：
	  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 
其中，SYSID 仅用于向后兼容，将被忽略。
*/

--ALTER：
ALTER ROLE role_specification [ WITH ] option [ ... ];

/*
option 可以是一个或多个角色属性：
	  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'
*/

注意事项：

• 使用 ALTER ROLE 更改角色的属性，
  使用 DROP ROLE 删除角色。
  CREATE ROLE 指定的所有属性都可以由 ALTER ROLE 命令进行修改。

  • ALTER ROLE 语句是 PostgreSQL 扩展，在SQL标准中，并没有直接名为 ALTER ROLE 的子句。很多现代的数据库管理系统提供了相同的功能。

• 首选使用 GRANT 和 REVOKE 进行用户组的成员管理。
  在 CREATE ROLE 时，可以立即指定 新建角色 属于哪个组或其包含哪些现有角色。

  • IN ROLE 或 IN GROUP：指定 新建角色 属于哪个组；
  • ROLE 或 USER：指定 新建角色 包含哪些现有角色；
  • ADMIN：同 ROLE【指定 新建角色 包含哪些现有角色】，但同时使 列出的现有角色具有以下权限：添加成员到该组。

（3）角色成员

将用户分组，以简化权限管理。

• 作为组的角色一般不具有 LOGIN 属性，但可以根据需要，设置该属性。

• 将作为组的角色视为一组权限，授予不同的角色，或从角色上撤销：

GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;

• 组角色和非组角色之间实际上没有任何区别。
  授权不允许形成循环。
  不允许向 PUBLIC 授予角色的成员资格，即不要向 public 组添加成员。它自动包含数据库中的每个用户。

---

成员角色通过两种方式使用组角色的权限：

• 成员角色都可以显式地执行 SET ROLE 来临时“成为”组角色。在此状态下，数据库会话可以访问组角色而不是原始登录角色的权限，并且创建的任何数据库对象都被视为由组角色而不是成员角色拥有。
• 其次，具有 INHERIT 属性的成员角色会自动使用其所属角色的权限，包括继承的任何权限。

举例说明：

--创建具有INHERIT属性的角色joe:
CREATE ROLE joe LOGIN INHERIT;

--创建具有NOINHERIT属性的角色admin:
CREATE ROLE admin NOINHERIT;

--创建具有NOINHERIT属性的角色wheel:
CREATE ROLE wheel NOINHERIT;

--将joe作为成员，添加进admin组：
GRANT admin TO joe;

--将admin作为成员，添加进wheel组：
GRANT wheel TO admin;

一个 session 以 joe 身份连接登录数据库，则立即具有授予 joe 的权限 和 授予 admin 的权限 。因为 joe 具有属性 INHERIT，能继承其所在组 admin 的任何权限。

即便 joe 也是 wheel 的成员（间接地），但该 session 并不具有授予 wheel 的权限。因为 admin 具有属性 NOINHERIT，并未继承任何来自 wheel 的权限。

SET ROLE admin;

执行 SET ROLE admin;之后，数据库将该 session 视为以 admin 连接，则仅具有授予 admin 的权限。

SET ROLE wheel;

执行 SET ROLE wheel;之后，数据库将该 session 视为以 wheel 连接，则仅具有授予 wheel 的权限。

要想恢复到原始状态，执行以下任一语句即可：

SET ROLE joe;
SET ROLE NONE;
RESET ROLE;

注意事项一：

在 SQL 标准中，角色与用户有明显的区别，角色自动继承其所属组的权限，而用户不会。

在 PostgreSQL 要想达到同样效果，可以通过：

• 将角色的属性设置为 INHERIT，使角色像 SQL标准中的角色一样，能自动继承其所属组的权限；
• 将角色的属性设置为 NOINHERIT，使角色像 SQL标准中的用户一样，不能自动继承其所属组的权限。

注意事项二：

诸如 LOGIN, SUPERUSER, CREATEDB 和 CREATEROLE 等这些角色属性，视为不能被继承的权限。在上述的例子中，假定已经给 admin 设置了属性 CREATEDB 和 CREATEROLE ，以 joe 身份连接登录数据库的 session 并不能立即具有创建数据库和创建角色的权限，需要执行 SET ROLE admin;之后才可以。

（4）drop 角色

删除一个角色，首先必须删除干净所有者是该角色的对象，或将这些对象改为其他角色所有；并且必须撤销授予该角色的任何权限。

可以使用 ALTER 命令来修改对象的所有者，例如，ALTER TABLE table_name OWNER TO new_owner;

也可以使用 REASSIGN OWNED 命令，将对象的所有权重新分配给其他角色。由于 REASSIGN OWNED 无法跨库执行，因此必须在每个数据库中运行它。如果是跨数据库共享对象，第一个 REASSIGN OWNED 命令执行时，就更改了它们的所有权，例如数据库或表空间。

使用 DROP OWNED 命令，删除剩余的对象。同样，此命令无法跨库执行，因此必须在每个数据库中运行它。此外，DROP OWNED 不会删除整个数据库或表空间，因此，如果角色拥有任何尚未转让给新所有者的数据库或表空间，则必须手动执行删除数据库或表空间。

DROP OWNED 还负责删除相应的权限。由于 REASSIGN OWNED 不涉及此类对象，因此通常需要同时运行 REASSIGN OWNED 和 DROP OWNED（按此顺序）以完全删除要删除的角色的依赖项。

简而言之，删除已用于拥有对象的角色的最通用方法是：

REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
-- 每个数据库中，重复上述命令
DROP ROLE doomed_role;

如果没有处理干净，就尝试 DROP ROLE，它将发出消息，指出哪些对象需要重新分配或删除。

（5）预定义角色

PostgreSQL 提供了一组预定义的角色。

角色	被允许的访问
pg_read_all_data	读取所有数据（表、视图、序列）：具有 SELECT 权限，对所有 schema 具有 USAGE 权限；即使没有显式声明拥有这些权限。此角色没有设置角色属性 BYPASSRLS。如果正在使用 RLS，则管理员可能希望在此角色被授予的角色上设置 BYPASSRLS。
pg_write_all_data	写入所有数据（表、视图、序列）：具有 INSERT、UPDATE 和 DELETE 权限以及对所有 schema 具有 USAGE 权限；即使没有显式声明拥有这些权限。此角色没有设置角色属性 BYPASSRLS。如果正在使用 RLS，则管理员可能希望在此角色被授予的角色上设置 BYPASSRLS。
pg_read_all_settings	读取所有配置变量，甚至包括通常仅对超级用户可见的变量。
pg_read_all_stats	读取所有 pg_stat_* 视图并使用各种与统计相关的扩展，甚至是通常仅对超级用户可见的扩展。
pg_stat_scan_tables	执行监控功能，这些功能可能会持续很长时间，并且可能对表执行 ACCESS SHARE 锁。
pg_monitor	读取各种监控视图/执行各种监控功能。此角色是 pg_read_all_settings、pg_read_all_stats 和 pg_stat_scan_tables 的成员。
pg_database_owner	没有。成员由当前数据库所有者组成。
pg_signal_backend	向另一个后端发出信号以取消查询或终止其会话。
pg_read_server_files	使用 COPY 或其他文件访问功能时，允许读取文件【即那些在服务器上数据库可以访问的文件】。
pg_write_server_files	使用 COPY 或其他文件访问功能时，允许写入文件【即那些在服务器上数据库可以访问的文件】。
pg_execute_server_program	使用 COPY 或调用函数（函数将执行服务端程序）时，允许在数据库服务器上执行程序【以运行数据库的用户的身份来执行程序】。
pg_checkpoint	允许执行 CHECKPOINT 命令。

pg_monitor、pg_read_all_settings、pg_read_all_stats 和 pg_stat_scan_tables 角色旨在允许管理员轻松配置角色以监视数据库服务器。它们授予一组通用权限，允许角色读取通常仅限于超级用户的各种有用的配置设置、统计信息和其他系统信息。

pg_database_owner 角色具有一个隐式的、依赖于情境的成员，即当前数据库的所有者。与任何角色一样，它可以拥有对象或接收访问权限的授予。因此，一旦 pg_database_owner 在模板数据库中拥有权限，从该模板实例化的数据库的所有者都将行使这些权限。pg_database_owner 不能是任何角色的成员，并且不能具有非隐式成员。最初，此角色拥有（模板数据库） public 模式（schema），因此每个（从该模板实例化的）数据库所有者都管理 public 模式的本地使用。

pg_signal_backend 角色旨在允许管理员启用受信任但非超级用户的角色，以向其他后端发送信号。目前，此角色允许发送信号以取消另一个后端上的查询或终止其会话。但是，被授予此角色的用户无法向超级用户拥有的后端发送信号。

pg_read_server_files、pg_write_server_files 和 pg_execute_server_program 角色旨在允许管理员拥有受信任但非超级用户的角色，这些角色能够以运行数据库的用户身份访问文件，并在数据库服务器上运行程序。由于这些角色能够访问服务器文件系统上的任何文件，因此它们在直接访问文件时会绕过所有数据库级权限检查，并且可用于获得超级用户级访问权限，因此在向用户授予这些角色时应非常小心。

预定义角色的使用例子：

GRANT pg_signal_backend TO admin_user;

（6）函数安全

• PostgreSQL 仅允许超级用户创建以不可信语言编写的函数。
  函数在后端服务器进程内运行，具有数据库服务器守护程序的操作系统权限。如果用于该函数的编程语言允许未经检查的内存访问，则可以更改服务器的内部数据结构。因此，此类函数可以绕过任何系统访问控制。允许进行此类访问的函数语言，被视为“不可信”语言。

• 严格控制谁可以定义对象。
  函数、触发器和行级安全策略允许用户将代码插入到后端服务器中，其他用户可能会无意中执行这些代码。因此，最强大的保护措施是严格控制谁可以定义对象。

• 仅对受信任所有者拥有的对象进行查询。
  从 search_path 中删除相关 schema——这些 schema 允许不受信任的用户创建对象。

3、grant 和 revoke 的使用

参考资料1 参考资料2

（1）grant

GRANT 命令有两个基本变体：一个用于角色的权限管理，另一个用于角色的成员管理。

角色的权限管理：

GRANT A ON B TO C [ WITH GRANT OPTION ] [ GRANTED BY role_specification ];

• 方括号 [] 代表可选内容；
• GRANTED BY：指定的授权者必须是当前用户。此子句当前以此形式存在，仅用于 SQL 兼容性；
  即 role_specification 可以是： [ GROUP ] role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER。
• WITH GRANT OPTION：权限的接收者可以将 授予给他的权限授予其他角色。如果没有 WITH GRANT OPTION，则接收者无法将权限授予他人。
• A：一个或多个权限类型，B：权限类型的适用对象，C：一个或多个角色；
  A 也可以是ALL [PRIVILEGES]，不同的适用对象，ALL 代表的权限也不同，参考上面表格：《对象及其可以授予的权限》；
  如果 A 是 TEMPORARY，可以简写为 TEMP；
  当适用对象是表的时候，A 可以是表的一个或多个列名；
  当适用对象是表的时候，B 可以是 ALL TABLES IN SCHEMA schema_name [, ...]；
  当适用对象是 SEQUENCE 的时候，B 可以是 ALL SEQUENCES IN SCHEMA schema_name [, ...]；
  当适用对象是 FUNCTION | PROCEDURE | ROUTINE 的时候，B 可以是 ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...]；

角色的成员管理：

GRANT D TO E [ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ] [ GRANTED BY role_specification ]

• D：一个或多个组角色；E：一个或多个成员角色；
• 更改现有成员，没有指定 ADMIN | INHERIT | SET 时，保留它们的原有值 ；
• 关键字 OPTION 被视为 TRUE 的同义词；
• ADMIN 选项：允许 成员角色 E 向其他人授予 组角色 D 的成员资格；若未指定，则此选项默认为 FALSE；
• INHERIT 选项：控制新成员资格的继承状态；如果设置为 TRUE，则会导致新成员从授予的角色继承。如果设置为 FALSE，则新成员不会继承。如果在创建新角色成员身份时未指定，则默认为新成员的 inheritance 属性。
• SET 选项：如果设置为 TRUE，则允许成员使用 SET ROLE 命令更改为授予的角色。如果一个角色是另一个角色的间接成员，则仅当存在一个授权链（每个授权链都具有 SET TRUE）时，它才能使用 SET ROLE 更改为该角色。此选项默认为 TRUE。
• GRANTED BY：指定由谁进行了授权操作，即指定授权人。授权人必须具有 ADMIN OPTION，除非是超级用户。如果授权人不是超级管理员，并且被撤销了 ADMIN OPTION，则也需要撤销相应的授权。

注意事项：

• 当对象的非所有者尝试对该对象执行 GRANT 权限时，如果对该对象没有任何权限，则该命令将彻底失败。只要某些权限可用，grant 命令就会继续，但它只会授予用户具有 grant option 的那些权限。如果用户不具有 grant option，将收到警告信息。
• Grant 和 revoke 命令能被对象的所有者执行。
  组角色拥有的对象，其成员也可以对该对象执行 grant 和 revoke 命令，同时授权者将被记录为组角色。通过多个角色成员资格路径间接持有所需的权限，则无法指定哪个包含 role 将被记录为已完成授权。在这种情况下，最佳做法是使用 SET ROLE 成为您想要执行 GRANT 的特定角色。
  具有 grant option 的角色，也可以对该对象执行 grant 和 revoke 命令。

（2）revoke

角色的权限管理：

REVOKE [ GRANT OPTION FOR ] A ON B FROM C [ GRANTED BY role_specification ] [ CASCADE | RESTRICT ];

• 如果指定了 GRANT OPTION FOR，则仅撤销权限的 grant 选项，而不撤销权限本身。否则，privilege 和 grant 选项都将被撤销。
• 如果存在对表的权限，同时也存在对列的权限，此时，如果撤销列的授权，不影响对表的所有列的访问；如果撤销对表的权限，将丧失对表的所有列的权限。

角色的成员管理：

REVOKE [ ADMIN OPTION FOR ] D ON B FROM E [ GRANTED BY role_specification ] [ CASCADE | RESTRICT ];

• 如果指定了 ADMINOPTION FOR，则仅撤销权限的 admin 选项，而不撤销成员资格。否则，成员资格 和 admin 选项都将被撤销。

注意事项：

• 用户只能撤销该用户直接授予的权限。例如，如果用户 A 已将具有 grant 选项的权限授予用户 B，而用户 B 又将其授予用户 C，则用户 A 无法直接从 C 撤销该权限。相反，用户 A 可以撤销用户 B 的 grant 选项，并使用 CASCADE 选项，以便依次撤销用户 C 的权限。再举一个例子，如果 A 和 B 都向 C 授予了相同的权限，则 A 可以撤销自己的授权，但不能撤销 B 的授权，因此 C 实际上仍将拥有该权限。

（3）ALTER DEFAULT PRIVILEGES

用于定义默认访问权限，允许您设置将应用于将来创建的对象的权限。

ALTER DEFAULT PRIVILEGES [ FOR { ROLE | USER } target_role [, ...] ] [ IN SCHEMA schema_name [, ...] ] abbreviated_grant_or_revoke

• 它不会影响分配给现有对象的权限。

• 目前仅支持更改 schema、table（包括视图和外部表）、sequence、function 和 type（包括 domain）的默认权限。

  • 对于此命令，function 包括 aggregates 和 procedures。因此，无法单独为函数和过程设置默认权限。
    在此命令中，单词 FUNCTIONS 和 ROUTINES 是等效的（ROUTINES 是以后首选的函数和过程的标准术语）。

• 默认权限可以全局设置（即针对当前数据库中创建的所有对象），也可以仅针对在指定 schema 中创建的对象来设置。
  二者的关系如下：

  • schema 的默认权限是全局授予的：默认情况下（即没有执行过任何 ALTER DEFAULT PRIVILEGES 命令），或者之前执行过未指定 schema 的 ALTER DEFAULT PRIVILEGES 命令。
  • 每个 schema 指定的默认权限将添加到特定对象类型的全局默认权限中。这意味着，无法通过Per-schema REVOKE 来撤销 schema 的全局默认权限。Per-schema REVOKE 仅用于撤回先前 Per-schema GRANT 的效果。

• 只能更改 由您自己或您所在组的组角色 将来创建的对象的默认权限。

创建对象时，需要注意的是，任何对象类型的默认权限，通常向对象所有者授予所有可授予的权限，并且也可能向 PUBLIC 授予一些权限。默认情况

4、权限管理的表格与视图

（1）查看用户

SELECT * FROM pg_user u ORDER BY u.usename;

（2）查看角色

SELECT * FROM pg_roles r ORDER BY r.rolname;

SELECT * FROM  pg_roles WHERE rolname='postgres';

SELECT * FROM pg_authid;

（3）查看某用户的表权限

SELECT * FROM information_schema.table_privileges WHERE grantee = 'your_user_name';

（4）查看某用户在某表的列上的权限

SELECT * FROM information_schema.column_privileges WHERE grantee='your_user_name'; 

（5）查看某用户的 usage 权限

SELECT * FROM information_schema.usage_privileges WHERE grantee='your_user_name';

（6）查看某用户在存储过程函数的执行权限

SELECT * FROM information_schema.routine_privileges WHERE grantee='your_user_name'; 

（7）查看当前用户能够访问的数据类型

SELECT * FROM information_schema.data_type_privileges; 

（8）查看用户自定义类型上授予的USAGE权限

SELECT * FROM information_schema.udt_privileges WHERE grantee='your_user_name';

（9）查看角色之间的成员关系

--角色之间的成员关系
SELECT
	t2.rolname as "group_name",
	t3.rolname as "member_name",
	t4.rolname as "grantor_name",
	t1.admin_option
FROM pg_auth_members t1 
	LEFT JOIN pg_authid t2 ON t2.oid = t1.roleid
	LEFT JOIN pg_authid t3 ON t3.oid = t1.member
	LEFT JOIN pg_authid t4 ON t4.oid = t1.grantor;

--查看特定角色的成员	
SELECT 
	rolname as "member_name"  
FROM pg_roles   
WHERE oid IN 
(
	SELECT member   
	FROM pg_auth_members   
	WHERE roleid = (SELECT oid FROM pg_roles WHERE rolname = 'pg_read_all_settings')
);

（10）查看新建对象的默认权限

PostgreSQL 的全局默认权限并不直接通过一个单独的表或视图来展示，而是隐含在对象创建时的行为中。不过，你可以通过 pg_default_acl 表来查看和管理要为新创建对象分配的初始权限。这些权限可以被视为一种全局默认权限，因为它们适用于所有没有明确指定其他权限的新对象。对于特定 schema 的默认权限，你可以使用与上面类似的查询，但加上一个 WHERE 子句来限制结果只显示该 schema 的条目。

SELECT   
    pg_catalog.pg_get_userbyid(d.defaclrole) AS "Granter",  
    n.nspname AS "Schema",  
    CASE d.defaclobjtype   
        WHEN 'r' THEN 'table'  
        WHEN 'S' THEN 'sequence'  
        WHEN 'f' THEN 'function'  
        WHEN 'T' THEN 'type'  
    END AS "Type",  
    pg_catalog.array_to_string(d.defaclacl, E', ') AS "Access privileges"  
FROM   
    pg_catalog.pg_default_acl d  
LEFT JOIN   
    pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace  
WHERE   
    n.nspname = 'abc' --指定 schema
ORDER BY   
    1, 2, 3;

5、案例：创建只读账号

在 PostgreSQL 中，创建一个只读权限的用户，通常意味着，你需要授予该用户对特定数据库中的表或视图有SELECT权限，但不授予其他如 INSERT、UPDATE、DELETE 等修改数据的权限。这可以通过 GRANT 语句来实现。但请注意，你可能还需要考虑对数据库和模式（schema）的权限，以及用户如何连接到数据库。

（1）登录

首先，你需要以超级用户或具有足够权限的用户身份登录到PostgreSQL数据库。

（2）创建用户

使用 CREATE ROLE 或 CREATE USER（CREATE USER 是 CREATE ROLE 的一个带有登录权限的别名）语句来创建新用户。

CREATE USER readonly_user WITH PASSWORD 'your_secure_password';

注意：从 PostgreSQL 10 开始，建议使用 CREATE ROLE 和 ALTER ROLE ... LOGIN 来创建用户，因为 CREATE USER 只是 CREATE ROLE 的一个快捷方式。

（3）设置访问数据库的权限

默认情况下，新创建的用户没有权限访问任何数据库。你需要允许用户连接到数据库。

GRANT CONNECT ON DATABASE your_database_name TO readonly_user;

（4）设置访问 schema 的权限

如果数据库中有多个模式，并且你只想让用户访问其中一个或几个模式，你可能还需要授予用户对这些 schema 的USAGE权限。

GRANT USAGE ON SCHEMA public TO readonly_user;

如果要对多个 schema 或所有 schema 授予权限，你可能需要为每个 schema 执行 GRANT 语句。可以动态生成这些语句：

SELECT 'GRANT USAGE ON SCHEMA ' || schemaname || ' TO readonly_user;' as "GrantStatement"
FROM 
(
	SELECT DISTINCT(table_schema) AS schemaname
	FROM information_schema.tables 
	WHERE table_schema NOT IN ('pg_catalog', 'information_schema')  
	ORDER BY table_schema
) t;

（5）授予 SELECT 权限

最后，你需要为只读用户授予对特定表的 SELECT 权限。

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

如果要对多个表或所有表授予权限，你可能需要为每个表执行 GRANT 语句。可以动态生成这些语句：

SELECT 'GRANT SELECT ON ' || table_schema || '.' || table_name || ' TO readonly_user;'  
FROM information_schema.tables  
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')  
  AND table_type = 'BASE TABLE'
ORDER BY table_schema;

（6）设置新建对象的默认权限

注意：上面的命令仅对已经存在的表授予权限。对于将来创建的表，你需要使用 ALTER DEFAULT PRIVILEGES 来设置默认权限。

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

这条命令会影响 public 模式中之后创建的所有表，自动授予 readonly_user 对这些新表的 SELECT 权限。

如果要对多个 schema 或所有 schema 设置新建对象的默认权限，你可能需要为每个 schema 执行 GRANT 语句。可以动态生成这些语句：

SELECT 'ALTER DEFAULT PRIVILEGES IN SCHEMA ' || schemaname || ' GRANT SELECT ON TABLES TO readonly_user;'
FROM 
(
	SELECT  DISTINCT(table_schema) AS schemaname
	FROM information_schema.tables 
	WHERE table_schema NOT IN ('pg_catalog', 'information_schema')  
	ORDER BY table_schema
) t;

（7）（可选）设置搜索路径：

如果你希望用户默认只访问特定的模式，可以设置其搜索路径。（6）函数安全

ALTER ROLE readonly_user SET search_path TO public;

完成以上步骤后，readonly_user 用户应该能够连接到数据库，并查询public模式（或你指定的其他模式）中的表，但不能修改这些表中的数据。

请根据你的具体需求调整这些步骤。