从零开始学PostgreSQL (四)： 数据库角色

概述

在 PostgreSQL 中，角色（Roles）是用来管理数据库访问权限的一种机制。一个角色可以被认为是一个用户或一组用户。角色可以拥有数据库对象（如表、视图、函数等）并控制其他角色对这些对象的访问权限。角色的主要属性包括：

• 登录能力：角色是否可以作为用户来登录数据库。
• 创建数据库：角色是否可以创建新的数据库。
• 创建角色：角色是否可以创建新的角色。
• 继承权限：角色是否可以继承其成员的权限。
• 永久连接：角色是否在连接建立时始终有效。
• 绕过行级安全性：角色是否可以绕过行级安全策略直接访问数据。
• 绕过表级安全性：角色是否可以绕过表级安全策略直接访问数据。
• 超级用户：角色是否具有超级用户权限。

预定义角色：

PostgreSQL 提供了一些预定义的角色，例如：

• public：所有用户都是 public 角色的成员。这是最低级别的权限。
• pg_read_all_data：可以读取所有数据库的数据。
• pg_write_all_data：可以写入所有数据库的数据。
• pg_read_server_files：可以读取服务器上的文件。
• pg_stat_scan_tables：可以收集表的统计信息。
• pg_signal_backend：可以发送信号到后端进程。
• pg_execute_server_program：可以执行服务器上的程序。

角色成员资格：

角色可以是另一个角色的成员，这允许角色继承其父角色的权限。例如，创建一个新的角色并将其添加到 pg_read_all_data 角色中，那么新角色将自动获得读取所有数据的能力。

删除角色：

删除角色通常涉及使用 DROP ROLE 命令。在删除角色之前，应该注意以下几点：

• 删除具有超级用户权限的角色可能会导致无法登录数据库。
• 如果角色拥有数据库对象，需要先删除这些对象或转移所有权。
• 角色可能是其他角色的成员或成员资格的基础，因此需要处理好依赖关系。

数据库角色与属性

从概念上讲，数据库角色与操作系统用户完全分开。在实践中，保持通信可能很方便，但这不是必需的。数据库角色在整个数据库群集安装中是全局的（而不是每个单独的数据库）。

数据库角色可以具有多个属性，定义其权限并与客户端认证系统交互。

1. 登录特权

• 只有具有此属性的角色可以用作数据库连接的初始角色名。拥有登录特权的角色可以被视为“数据库用户”。
• 创建具有登录特权的角色：CREATE USER name;

（CREATE USER 和 CREATE ROLE name LOGIN 是等效的，不同之处在于前者默认包括 LOGIN 特权，后者不包括。）

2.超级用户状态

• 数据库超级用户可以绕过所有权限检查，但不能绕过登录权限检查。这是一个危险的特权，应谨慎使用，最好大部分工作以非超级用户的角色进行。
• 创建新的数据库超级用户：

CREATE ROLE name SUPERUSER;

3.数据库创建

• 除了超级用户外，必须显式授予角色创建数据库的权限。
• 创建具有创建数据库特权的角色：

CREATE ROLE name CREATEDB;

4.角色创建

• 除了超级用户外，必须显式授予角色创建其他角色的权限。
• 创建具有创建角色特权的角色：

CREATE ROLE name CREATEROLE;

5.复制初始化

• 除了超级用户外，必须显式授予角色启动流复制的权限。
• 创建用于流复制的登录角色：

CREATE ROLE name REPLICATION LOGIN;

6.密码

• 如果客户端认证方法要求用户在连接到数据库时提供密码，则密码才有意义。
• 在角色创建时指定密码：

CREATE ROLE name PASSWORD 'string';

7.权限继承

• 默认情况下，角色会继承其成员角色的权限。要创建不继承权限的角色，请使用 NOINHERIT。
• 对于单个授权，可以使用 WITH INHERIT TRUE 或 WITH INHERIT FALSE 覆盖继承。
• 创建不继承权限的角色：

CREATE ROLE name NOINHERIT;

8.绕过行级安全

• 除了超级用户外，必须显式授予角色绕过所有行级安全（RLS）策略的权限。
• 创建具有绕过行级安全权限的超级用户角色：

CREATE ROLE name BYPASSRLS;

9.连接限制

• 连接限制指定角色可以建立的并发连接数。-1（默认值）表示没有限制。
• 在角色创建时指定连接限制：

CREATE ROLE name CONNECTION LIMIT 'integer';

10.删除操作

删除用户或删除角色：

DROP ROLE name;

11.查询角色操作

查询现有角色集;
查询哪些能够登录的人：
SELECT rolname FROM pg_roles;
SELECT rolname FROM pg_roles WHERE rolcanlogin;

预定义角色

1、PostgreSQL 提供了一组预定义的角色，这些角色提供对某些常用特权功能和信息的访问;

2、向用户授予对这些角色的访问权限;

3、预定义角色;

GRANT pg_signal_backend TO admin_user;

角色成员资格

在 PostgreSQL 中，角色成员资格的管理是通过创建角色并使用 GRANT 和 REVOKE 命令来实现的。以下是关于角色成员资格的总结：

1.创建角色：使用 CREATE ROLE 命令可以创建角色，例如：

CREATE ROLE role_name;

设置组角色：在 PostgreSQL 中，可以创建一个角色来代表一个组。通常，组角色不具备 LOGIN 特权，但可以根据需要设置。

CREATE ROLE group_role;

添加和删除成员：使用 GRANT 命令将角色添加为组角色的成员，使用 REVOKE 命令从组角色中撤销成员身份。

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

授予组角色的成员资格：组角色和非组角色之间没有本质区别，因此可以向其他组角色授予成员身份。

GRANT group_role1 TO group_role2;

角色的权限继承：

• 继承：角色默认继承其成员角色的权限。可以使用 WITH INHERIT TRUE 或 WITH INHERIT FALSE 覆盖默认的继承行为。

GRANT parent_role TO child_role WITH INHERIT TRUE;

• 设置 ROLE：成员角色可以使用 SET ROLE 命令暂时切换到组角色，以获取组角色的特权。

SET ROLE group_role;

特权和继承的影响：

• 成员角色通过继承获得直接或间接授予的特权。
• WITH INHERIT FALSE 不会继承权限，而 SET ROLE 可以临时启用组角色的权限。

删除角色

在 PostgreSQL 中，删除角色涉及到转移或删除角色拥有的数据库对象及其权限。以下是删除角色的总结：

1.转移对象所有权：使用 ALTER TABLE 或 ALTER OWNED BY 命令可以将角色拥有的数据库对象的所有权转移给其他角色。例如：

ALTER TABLE table_name OWNER TO new_owner_role;

2.REASSIGN OWNED 命令：使用 REASSIGN OWNED 命令可以一次性将要删除角色拥有的所有对象的所有权重新分配给另一个角色。这对于在多个数据库中转移所有权特别有用。

REASSIGN OWNED BY doomed_role TO successor_role;

3.DROP OWNED 命令：使用 DROP OWNED 命令可以删除角色拥有的所有对象。它还会撤销授予目标角色的任何不属于目标角色的对象的权限。

DROP OWNED BY doomed_role;

4.删除角色：最终，使用 DROP ROLE 命令删除角色本身。在删除角色之前，确保所有对象的所有权已经正确转移或删除，并且不再需要该角色。

DROP ROLE doomed_role;

5.多数据库的考虑：如果要删除的角色在多个数据库中都拥有对象，需要在每个数据库中运行 REASSIGN OWNED 和 DROP OWNED 命令。

6.注意事项：

如果存在依赖关系或未转移的对象，DROP OWNED 命令会发出警告消息，指示需要先处理这些对象。

删除角色前，建议手动审查和处理异常情况，以确保不会意外删除重要的数据库对象或权限。

注意事项

• 在修改角色属性或删除角色时要谨慎，以免影响数据库的正常运行或造成安全漏洞。
• 确保角色的权限最小化，只授予必要的权限，遵循最小特权原则。
• 使用预定义角色可以简化权限管理，但应理解每个角色的含义和权限。
• 在创建和管理角色时，考虑到角色成员资格的传递性，确保权限分配符合设计意图。

总结

PostgreSQL 的角色系统提供了强大的权限管理工具，通过定义角色和其属性，可以精确控制数据库访问权限。预定义角色简化了常见权限的管理，而角色成员资格和删除角色的处理则需要细致规划，以保证数据库的安全性和可用性。正确的角色管理是维护数据库安全和性能的关键因素之一。