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）适用对象的类型

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

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

（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_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）grant

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

grant A on B to C [ WITH GRANT OPTION ] [ GRANTED BY role_specification ];
GRANT D TO E [ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ] [ GRANTED BY role_specification ]

注：

• 方括号 [] 代表可选内容；
• 如果指定了 GRANTED BY，则指定的授权者必须是当前用户。此子句当前以此形式存在，仅用于 SQL 兼容性。
  即 role_specification 可以是： [ GROUP ] role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER