Postgresql 中文操作指南

31.9. Security #

用于复制连接的角色必须具有 REPLICATION 属性（或为超级用户）。如果角色缺少 SUPERUSER 和 BYPASSRLS，发布者行安全策略可以执行。如果角色不信任所有表所有者，请在连接字符串中包括 options=-crow_security=off；如果表所有者添加行安全策略，则该设置将导致复制停止，而不是执行该策略。必须在 pg_hba.conf 中配置角色的访问权限，并且它必须具有 LOGIN 属性。

The role used for the replication connection must have the REPLICATION attribute (or be a superuser). If the role lacks SUPERUSER and BYPASSRLS, publisher row security policies can execute. If the role does not trust all table owners, include options=-crow_security=off in the connection string; if a table owner then adds a row security policy, that setting will cause replication to halt rather than execute the policy. Access for the role must be configured in pg_hba.conf and it must have the LOGIN attribute.

为了能够复制初始表数据，用于复制连接的角色必须在已发布表上具有 SELECT 权限（或为超级用户）。

In order to be able to copy the initial table data, the role used for the replication connection must have the SELECT privilege on a published table (or be a superuser).

要创建发布，用户必须在数据库中具有 CREATE 权限。

To create a publication, the user must have the CREATE privilege in the database.

要将表添加到发布中，用户必须对该表拥有所有权。要将架构中的所有表添加到发布中，用户必须为超级用户。要创建发布以自动发布所有表或架构中的所有表，用户必须是超级用户。

To add tables to a publication, the user must have ownership rights on the table. To add all tables in schema to a publication, the user must be a superuser. To create a publication that publishes all tables or all tables in schema automatically, the user must be a superuser.

当前没有针对发布的权限。任何订阅（能够连接）都可以访问任何发布。因此，如果您打算通过使用行过滤器或列列表或通过不将整个表添加到发布中来向特定订阅者隐藏某些信息，请注意同一数据库中的其他发布可能会公开相同的信息。将来可能会向 PostgreSQL 添加发布权限，以允许更细粒度的访问控制。

There are currently no privileges on publications. Any subscription (that is able to connect) can access any publication. Thus, if you intend to hide some information from particular subscribers, such as by using row filters or column lists, or by not adding the whole table to the publication, be aware that other publications in the same database could expose the same information. Publication privileges might be added to PostgreSQL in the future to allow for finer-grained access control.

要创建订阅，用户必须拥有 pg_create_subscription 角色的权限，以及数据库中的 CREATE 权限。

To create a subscription, the user must have the privileges of the the pg_create_subscription role, as well as CREATE privileges on the database.

订阅应用进程将在会话级别使用订阅所有者的权限来运行。但是，在对特定表执行插入、更新、删除或截断操作时，它将切换角色为表所有者并以表所有者的权限执行操作。这意味着订阅所有者需要能够 SET ROLE 拥有复制表的每个角色。

The subscription apply process will, at a session level, run with the privileges of the subscription owner. However, when performing an insert, update, delete, or truncate operation on a particular table, it will switch roles to the table owner and perform the operation with the table owner’s privileges. This means that the subscription owner needs to be able to SET ROLE to each role that owns a replicated table.

如果已使用 run_as_owner = true 配置订阅，则不会发生用户切换。相反，所有操作都将通过订阅所有者的权限来执行。在这种情况下，订阅所有者只需对目标表的 SELECT、INSERT、UPDATE 和 DELETE 具有权限，而无需 SET ROLE 权限对表所有者。但是，这也意味着拥有正在复制到的表的所有者可以以订阅所有者的权限执行任意代码。例如，他们可以通过简单地将触发器附加到他们拥有的某个表来执行此操作。因为通常不想允许一个角色自由地承担另一个角色的权限，所以除非不关心数据库内的用户安全，否则应避免此选项。

If the subscription has been configured with run_as_owner = true, then no user switching will occur. Instead, all operations will be performed with the permissions of the subscription owner. In this case, the subscription owner only needs privileges to SELECT, INSERT, UPDATE, and DELETE from the target table, and does not need privileges to SET ROLE to the table owner. However, this also means that any user who owns a table into which replication is happening can execute arbitrary code with the privileges of the subscription owner. For example, they could do this by simply attaching a trigger to one of the tables which they own. Because it is usually undesirable to allow one role to freely assume the privileges of another, this option should be avoided unless user security within the database is of no concern.

在发布者上，只能在复制连接开始时检查一次权限，并且在读取每个更改记录时不会重新检查权限。

On the publisher, privileges are only checked once at the start of a replication connection and are not re-checked as each change record is read.

在订阅者上，每次应用事务时都会重新检查订阅所有者的权限。如果某个工作进程正在应用事务时订阅的所有权被并发事务更改，则当前事务的应用程序将继续在旧所有者的权限下运行。

On the subscriber, the subscription owner’s privileges are re-checked for each transaction when applied. If a worker is in the process of applying a transaction when the ownership of the subscription is changed by a concurrent transaction, the application of the current transaction will continue under the old owner’s privileges.