【[译]简述PostgreSQL角色和权限】

PostgreSQL有一个细粒度的系统来管理用户角色和权限。这有助于管理员决定谁可以访问某些数据，以及他们可以对这些数据做什么。它是关于管理权限的，您可以在其中创建不同的角色，并且角色可以是其他角色的成员。

在这篇博客中，我们将深入探讨PostgreSQL用户角色和权限，包括数据库、模式和其他对象级别的权限，遵循FAQ格式。

用户、角色和组之间的区别是什么？

在PostgreSQL中，用户、组和角色本质上是相同的，但用户在默认情况下增加了登录功能。命令CREATE USER和CREATE GROUP与CREATE ROLE相同。

注意：使用CREATE ROLE命令时，默认情况下创建的角色没有登录权限。在我们的博客中，我们将互换使用角色和用户这两个术语。

在PostgreSQL中，每个到数据库服务器的连接都链接到一个特定的角色。最初，连接通过身份验证模块（pg_hba.conf），然后这个角色决定在该连接期间执行的命令的初始访问权限。

角色属性

角色具有各种属性，这些属性定义了他们在数据库系统中可以执行的操作。这些属性很重要，因为它们决定了角色可以采取的行动的范围。以下是一些关键属性及其含义：

属性	描述
LOGIN	此属性使角色能够进行身份验证并连接到数据库群集。没有此属性的角色无法建立连接，从而限制了他们对数据库的访问。
PASSWORD	使角色能够使用密码进行身份验证，以安全访问数据库。所有PostgreSQL用户都必须有用于身份验证的密码。
SUPERUSER	在数据库集群中提供最高级别的权限。超级用户可以做任何事情，例如访问所有数据库、执行任何操作和管理其他角色。在PostgreSQL中，这个初始超级用户帐户默认称为postgres，它通常与运行PostgreSQL集群进程的系统用户名相匹配。
CREATEDB	允许角色在PostgreSQL集群中创建新数据库。
CREATEROLE	允许角色在数据库系统中创建新角色并对其进行管理。
INHERIT	此属性决定角色是否可以从其他角色继承特权。激活后，角色将获得其所属角色的特权。如果指定了NOINHERIT，则角色必须使用SET命令来访问父角色的权限。

如何创建和分配角色？

CREATE ROLE readonly; => 此角色无法登录数据库内部
CREATE ROLE testuser LOGIN ENCRYPTED PASSWORD ‘SECRET’; => 此角色可以登录和访问集群内的数据库。
GRANT readonly to testuser; => 分配给具有的只读角色的所有权限都将由testuser继承

什么是PUBLIC角色？

PostgreSQL中的PUBLIC角色是一个特殊的角色，它为系统中的每个角色授予特权。它就像一个默认组，指示所有角色，甚至是后来创建的角色。它确保所有角色都有权访问某些特权。默认情况下，所有其他角色都会自动授予PUBLIC成员资格，并继承其特权。

在PostgreSQL 15之前，每个用户都对公共模式拥有完全的权限（创建/使用），所有用户共享该权限。然而，从PostgreSQL 15开始，用户不再能够在公共模式中创建任何对象。此更改适用于代表所有用户的公共角色。现在的标准做法是撤销公共模式的CREATE特权。

PostgreSQL提供了哪些数据库级别的权限？

PostgreSQL在数据库级别提供各种特权，包括：

特权	描述
CONNECT	允许用户连接到特定的数据库。
CREATE	授予在数据库中创建新对象的能力。
TEMPORARY	允许创建临时表。

默认情况下，CONNECT权限被授予PUBLIC角色（所有用户）。但是，不可能直接从单个用户撤销此权限。相反，您需要从PUBLIC角色中撤销它，然后将它单独授予其他需要它的用户。

REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;

现在使用testuser连接postgres数据库

psql -U testuser -d postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  permission denied for database "postgres"
DETAIL:  User does not have CONNECT privilege.

如何分配schema级别的权限？

PostgreSQL数据库可以由一个或多个模式组成，其中包含表和其他对象，如视图、数据类型、函数、过程和运算符。不同的模式可以具有相同名称的对象，而不会发生冲突。例如，schema1和schema2都可以包含名为table1的表。与数据库不同，模式不是严格分开的。用户可以访问他们所连接的数据库中任何模式中的对象，前提是他们拥有必要的权限。

默认情况下，用户不能访问他们不拥有的模式中的对象。若要允许访问，schema的所有者必须授予该schema的USAGE权限。例如：

GRANT USAGE ON SCHEMA schema1 TO testuser;

用户还可以被允许在其他人的schema中创建对象。要允许这样做，必须授予对schema的CREATE权限。例如

GRANT CREATE ON SCHEMA schema1 TO abc;

要在单个命令中同时提供这两种权限，请运行以下操作：

GRANT ALL ON SCHEMA schema1 TO testuser;

如何分配表级权限？

PostgreSQL在表级别提供了各种DDL和DML权限：

• SELECT：允许从表中提取数据。
• INSERT：允许向表中添加新行。
• UPDATE：修改表中的现有行。
• DELETE：授予从表中删除行的功能。
• REFERENCES：允许创建外键约束。
• TRIGGER：允许在表上创建触发器。
• TRUNCATE：允许在表上使用TRUNCATE。

授予INSERT和UPDATE权限：

Grant insert,update on table tablename to testuser;

授予单个表上的所有权限：

Grant all on table tablename to testuser;

授予schema中所有表的所有权限：

Grant all on all tables in schema schemaname to testuser;

ALTER DEFAULT PRIVILEGE命令的用途是什么？

ALTER DEFAULT PRIVILEGES命令允许管理员为schema中特定角色创建的对象定义默认访问级别。这对于新创建的对象需要一致访问控制的情况非常有用。默认情况下，只有所有者（创建对象的用户）才能访问新创建的对象，除非授予明确的权限。例如

ALTER DEFAULT PRIVILEGES FOR ROLE developer IN SCHEMA test GRANT SELECT, INSERT ON TABLES TO reporting;

上面的命令意味着，每当developer角色在测试schema内创建任何新表时，SELECT和INSERT权限都将自动授予reporting角色。

注意：重要的是要理解GRANT权限对每个对象都是独立的。对数据库授予权限不会自动将这些权限扩展到数据库中的schema。同样，对schema授予权限也不会自动对该schema中的表授予权限。

访问表时，权限检查按特定顺序进行：

1.您对该schema有USAGE权限吗？

• 如果否：访问被拒绝。
• 如果是：继续下一步。

2.你是否也有适当的发言权？

• 如果否：访问被拒绝。
• 如果是：继续检查列权限。

这意味着，如果您可以访问一个schema中的所有表，但没有该schema的USAGE权限，PostgreSQL将抛出拒绝权限错误。

PostgreSQL中预定义的角色是什么？

预定义的角色是指一个内置的角色，旨在授予访问常用信息的权限。这简化了管理员的任务，使他们能够轻松地向其他人提供访问权限，而无需为相同的授权执行多个SQL查询。这些角色概述如下：

角色	描述
pg_read_all_data	读取所有数据（表、视图、序列），就好像对这些对象拥有SELECT权限，对所有架构拥有USAGE权限一样，即使没有明确的权限。此角色没有设置角色属性BYPASSRLS。如果正在使用RLS，管理员可能希望在授予此角色的角色上设置BYPASSRLS。
pg_write_all_data	写入所有数据（表、视图、序列），就好像对这些对象具有INSERT、UPDATE和DELETE权限，对所有架构具有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_use_reserved_connections	允许使用通过reserved_connections保留的连接插槽。
pg_create_subscription	允许对数据库具有create权限的用户发出create subscription。

PostgreSQL 14引入了两个新角色，从应用程序开发人员的角度提供了显著的好处：

pg_read_all_data
pg_write_all_data

例如，当向应用程序用户提供只读权限时，通常的方法包括为每个模式执行多个命令：

GRANT SELECT on all tables in schema1 to application_user;
GRANT SELECT on all tables in schema2 to application_user;
ALTER DEFAULT PRIVILEGES in schema1 GRANT select on tables to application_user;
ALTER DEFAULT PRIVILEGES in schema2 GRANT select on tables to application_user;

问题是，当处理大量的模式和编写用户时，这个过程会变得重复。

对于像pg_read_all_data这样的预定义角色，我们只需要运行一个命令：

GRANT pg_read_all_data to application_user;

最佳实践

建议在PostgreSQL中设置细粒度访问控制的方法如下：

1.为每个应用程序或服务创建单独的用户。这有助于轻松识别任何连接问题，并有助于通过审核日志跟踪访问。

2.根据应用程序访问要求定义具有特定权限的多个角色。相应地将这些角色分配给用户，因为这有助于简化管理并简化授予或撤销权限的过程。

3.仅向用户和角色授予其各自任务所需的权限。

4.要小心超级用户权限，因为他们拥有对数据库的不受限制的访问权限，如果被泄露，会带来重大的安全风险。

5.为数据库用户实施强大的密码策略，以防止未经授权的访问。定期轮换密码。

6.利用SSL/TLS协议对客户端和数据库服务器之间的通信进行加密。

文章原文：https://stormatics.tech/blogs/postgres-roles-and-privileges-simplified
作者：Muhammad Ali
译：阎书利