Postgresql 中文操作指南

5.8. Row Security Policies #

除了可通过 GRANT 获得的 SQL 标准 privilege system 之外，表还可以有 row security policies ，它按每个用户限制可以通过普通查询返回哪些行，或者通过数据修改命令插入、更新或删除哪些行。此功能也称为 Row-Level Security 。默认情况下，表没有任何策略，因此，如果用户根据 SQL 权限系统拥有对表的访问权限，则表中的所有行都可以被用于查询或更新。

In addition to the SQL-standard privilege system available through GRANT, tables can have row security policies that restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands. This feature is also known as Row-Level Security. By default, tables do not have any policies, so that if a user has access privileges to a table according to the SQL privilege system, all rows within it are equally available for querying or updating.

当某个表启用行安全性（ ALTER TABLE … ENABLE ROW LEVEL SECURITY），对该表的全部正常行选择访问或修改访问都必须由行安全策略允许。（然而，该表的拥有者通常不受行安全策略的约束。）如果没有该表策略，将使用默认拒绝策略，这意味着无法访问或修改任何行。适用于整个表的操作（比如_TRUNCATE_和_REFERENCES_）不受行安全策略的约束。

When row security is enabled on a table (with ALTER TABLE … ENABLE ROW LEVEL SECURITY), all normal access to the table for selecting rows or modifying rows must be allowed by a row security policy. (However, the table’s owner is typically not subject to row security policies.) If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified. Operations that apply to the whole table, such as TRUNCATE and REFERENCES, are not subject to row security.

行安全性策略可以特定于命令或角色，或同时特定于命令和角色。可以指定策略应用于 ALL 命令或应用于 SELECT、INSERT、UPDATE 或 DELETE。可以将多个角色分配给给定的策略，并且应用正常的角色成员资格和继承规则。

Row security policies can be specific to commands, or to roles, or to both. A policy can be specified to apply to ALL commands, or to SELECT, INSERT, UPDATE, or DELETE. Multiple roles can be assigned to a given policy, and normal role membership and inheritance rules apply.

要指定哪些行根据策略可见或可修改，需要一个返回布尔结果的表达式。对于任何来自用户查询的条件或函数之前，将针对每一行评估此表达式。（此规则的唯一例外是 leakproof 函数，保证不会泄露信息；优化器可能会选择在行安全性检查之前应用此类函数。）表达式未返回 true 的行将不会被处理。可以指定单独的表达式，以便对可见行和允许修改的行进行独立控制。策略表达式作为查询的一部分运行，并使用运行查询的用户的权限，尽管可以使用安全定义者函数来访问调用用户不可用的数据。

To specify which rows are visible or modifiable according to a policy, an expression is required that returns a Boolean result. This expression will be evaluated for each row prior to any conditions or functions coming from the user’s query. (The only exceptions to this rule are leakproof functions, which are guaranteed to not leak information; the optimizer may choose to apply such functions ahead of the row-security check.) Rows for which the expression does not return true will not be processed. Separate expressions may be specified to provide independent control over the rows which are visible and the rows which are allowed to be modified. Policy expressions are run as part of the query and with the privileges of the user running the query, although security-definer functions can be used to access data not available to the calling user.

在访问某个表时，超级用户和具有_BYPASSRLS_属性的角色将总是绕过行安全性系统。表的所有者通常也会绕过行安全性，尽管表所有者可以使用 ALTER TABLE … FORCE ROW LEVEL SECURITY选择受到行安全策略的约束。

Superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table. Table owners normally bypass row security as well, though a table owner can choose to be subject to row security with ALTER TABLE … FORCE ROW LEVEL SECURITY.

启用和禁用行安全性，以及向表添加策略，始终是表拥有者的专属权限。

Enabling and disabling row security, as well as adding policies to a table, is always the privilege of the table owner only.

使用 CREATE POLICY 命令创建策略，使用 ALTER POLICY 命令更改策略，并使用 DROP POLICY 命令废弃策略。要启用和禁用给定表的行安全性，请使用 ALTER TABLE 命令。

Policies are created using the CREATE POLICY command, altered using the ALTER POLICY command, and dropped using the DROP POLICY command. To enable and disable row security for a given table, use the ALTER TABLE command.

每个策略都有一个名称，并且可以为一个表定义多个策略。由于策略是特定于表的，因此表的每个策略必须有唯一的名称。不同的表可能有名称相同的策略。

Each policy has a name and multiple policies can be defined for a table. As policies are table-specific, each policy for a table must have a unique name. Different tables may have policies with the same name.

当多个策略应用于给定的查询时，它们要么使用 OR（用于宽松策略，这是默认值）整合，要么使用 AND（用于严格策略）整合。这类似于给定的角色具有其作为成员的所有角色的权限的规则。下面将进一步讨论宽松策略和严格策略。

When multiple policies apply to a given query, they are combined using either OR (for permissive policies, which are the default) or using AND (for restrictive policies). This is similar to the rule that a given role has the privileges of all roles that they are a member of. Permissive vs. restrictive policies are discussed further below.

作为一个简单的示例，以下是如何在 account 关系上创建一个策略，仅允许 managers 角色的成员访问行，且仅允许访问其帐户的行：

As a simple example, here is how to create a policy on the account relation to allow only members of the managers role to access rows, and only rows of their accounts:

CREATE TABLE accounts (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers
    USING (manager = current_user);

上面的策略隐式地提供了一个 WITH CHECK 子句，它与其 USING 子句相同，以便该约束既适用于某个命令所选择的行（因此，经理无法访问、修改或删除属于另一位经理的现有行），也适用于某个命令所修改的行（因此，不属于同一经理的行无法通过 INSERT 或 UPDATE 创建）。

The policy above implicitly provides a WITH CHECK clause identical to its USING clause, so that the constraint applies both to rows selected by a command (so a manager cannot SELECT, UPDATE, or DELETE existing rows belonging to a different manager) and to rows modified by a command (so rows belonging to a different manager cannot be created via INSERT or UPDATE).

如果未指定角色或者使用了特殊用户名 PUBLIC，则该策略将适用于系统上的所有用户。要允许所有用户仅访问其在某个 users 表中的行，可以使用一个简单的策略：

If no role is specified, or the special user name PUBLIC is used, then the policy applies to all users on the system. To allow all users to access only their own row in a users table, a simple policy can be used:

CREATE POLICY user_policy ON users
    USING (user_name = current_user);

其工作方式类似于前面的示例。

This works similarly to the previous example.

为了针对要添加到表中的行和那些可见行使用不同的策略，可以合并多个策略。此对策略将允许所有用户查看 users 表中的所有行，但只能修改其自己的行：

To use a different policy for rows that are being added to the table compared to those rows that are visible, multiple policies can be combined. This pair of policies would allow all users to view all rows in the users table, but only modify their own:

CREATE POLICY user_sel_policy ON users
    FOR SELECT
    USING (true);
CREATE POLICY user_mod_policy ON users
    USING (user_name = current_user);

在 SELECT 命令中，使用 OR 合并这两个策略，其结果是所有行均可被选择。在其他命令类型中，仅应用第二个策略，以便其效果与之前相同。

In a SELECT command, these two policies are combined using OR, with the net effect being that all rows can be selected. In other command types, only the second policy applies, so that the effects are the same as before.

行安全性也可以通过 ALTER TABLE 命令禁用。禁用行安全性不会移除针对表定义的任何策略；这些策略只会遭到忽略。随后，表中的所有行都将可被查看和修改，要遵守标准 SQL 权限系统。

Row security can also be disabled with the ALTER TABLE command. Disabling row security does not remove any policies that are defined on the table; they are simply ignored. Then all rows in the table are visible and modifiable, subject to the standard SQL privileges system.

以下是一个较大的示例，说明如何在生产环境中使用这一特性。表 passwd 模拟了一个 Unix 密码文件：

Below is a larger example of how this feature can be used in production environments. The table passwd emulates a Unix password file:

-- Simple passwd-file based example
CREATE TABLE passwd (
  user_name             text UNIQUE NOT NULL,
  pwhash                text,
  uid                   int  PRIMARY KEY,
  gid                   int  NOT NULL,
  real_name             text NOT NULL,
  home_phone            text,
  extra_info            text,
  home_dir              text NOT NULL,
  shell                 text NOT NULL
);

CREATE ROLE admin;  -- Administrator
CREATE ROLE bob;    -- Normal user
CREATE ROLE alice;  -- Normal user

-- Populate the table
INSERT INTO passwd VALUES
  ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT INTO passwd VALUES
  ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT INTO passwd VALUES
  ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');

-- Be sure to enable row-level security on the table
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- Create policies
-- Administrator can see all rows and add any rows
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- Normal users can view all rows
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- Normal users can update their own records, but
-- limit which shells a normal user is allowed to set
CREATE POLICY user_mod ON passwd FOR UPDATE
  USING (current_user = user_name)
  WITH CHECK (
    current_user = user_name AND
    shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
  );

-- Allow admin all normal rights
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Users only get select access on public columns
GRANT SELECT
  (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
  ON passwd TO public;
-- Allow users to update certain columns
GRANT UPDATE
  (pwhash, real_name, home_phone, extra_info, shell)
  ON passwd TO public;

与任何安全设置一样，测试并确保系统按预想的那样运作至关重要。使用上面的示例可以证明权限系统工作正常。

As with any security settings, it’s important to test and ensure that the system is behaving as expected. Using the example above, this demonstrates that the permission system is working properly.

-- admin can view all rows and fields
postgres=> set role admin;
SET
postgres=> table passwd;
 user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info | home_dir    |   shell
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
 admin     | xxx    |   0 |   0 | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | xxx    |   2 |   1 | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

-- Test what Alice is able to do
postgres=> set role alice;
SET
postgres=> table passwd;
ERROR:  permission denied for table passwd
postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info | home_dir    |   shell
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

postgres=> update passwd set user_name = 'joe';
ERROR:  permission denied for table passwd
-- Alice is allowed to change her own real_name, but no others
postgres=> update passwd set real_name = 'Alice Doe';
UPDATE 1
postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
postgres=> update passwd set shell = '/bin/xx';
ERROR:  new row violates WITH CHECK OPTION for "passwd"
postgres=> delete from passwd;
ERROR:  permission denied for table passwd
postgres=> insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for table passwd
-- Alice can change her own password; RLS silently prevents updating other rows
postgres=> update passwd set pwhash = 'abc';
UPDATE 1

到目前为止构建的所有策略均为许可策略，这意味着，当应用多个策略时，使用“OR”布尔操作符将它们组合在一起。虽然可以构建许可策略，使其仅允许在指定情况下访问行，但将许可策略与限制策略（记录必须通过这些策略，并且使用“AND”布尔操作符对其进行组合）进行组合可能会更简单。基于上面的示例，我们额外添加一个限制策略，要求管理员通过本地 Unix 套接字连接才能访问 passwd 表的记录：

All of the policies constructed thus far have been permissive policies, meaning that when multiple policies are applied they are combined using the “OR” Boolean operator. While permissive policies can be constructed to only allow access to rows in the intended cases, it can be simpler to combine permissive policies with restrictive policies (which the records must pass and which are combined using the “AND” Boolean operator). Building on the example above, we add a restrictive policy to require the administrator to be connected over a local Unix socket to access the records of the passwd table:

CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
    USING (pg_catalog.inet_client_addr() IS NULL);

随后，我们可以看到，由于限制策略，一个通过网络连接的管理员将看不到任何记录：

We can then see that an administrator connecting over a network will not see any records, due to the restrictive policy:

=> SELECT current_user;
 current_user
--------------
 admin
(1 row)

=> select inet_client_addr();
 inet_client_addr
------------------
 127.0.0.1
(1 row)

=> TABLE passwd;
 user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
-----------+--------+-----+-----+-----------+------------+------------+----------+-------
(0 rows)

=> UPDATE passwd set pwhash = NULL;
UPDATE 0

引用完整性检查（如唯一或主键约束和外键引用）始终会绕过行安全性，以确保维护数据完整性。在开发模式和行级别策略时，必须小心，以避免信息的“隐蔽通道”泄漏通过此类引用完整性检查。

Referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row security to ensure that data integrity is maintained. Care must be taken when developing schemas and row level policies to avoid “covert channel” leaks of information through such referential integrity checks.

在某些情况下，确定不应用行安全性非常重要。例如，当进行备份时，如果行安全性在没有提示的情况下导致某些行从备份中遗漏，这可能是灾难性的。在这种情况下，可以将 row_security配置参数设置为_off_。这本身不会绕过行安全性；它所做的是在任何查询的结果会被策略过滤掉的情况下抛出一个错误。然后可以调查并修复错误原因。

In some contexts it is important to be sure that row security is not being applied. For example, when taking a backup, it could be disastrous if row security silently caused some rows to be omitted from the backup. In such a situation, you can set the row_security configuration parameter to off. This does not in itself bypass row security; what it does is throw an error if any query’s results would get filtered by a policy. The reason for the error can then be investigated and fixed.

在上面的示例中，策略表达式仅考虑要访问或更新的行中的当前值。这是最简单、性能最好的情况；如果可能的话，最好以此方式设计行安全性应用程序。如果需要咨询其他行或其他表以做出策略决策，则可以在策略表达式中使用子 SELECT 或包含 SELECT 的函数来实现此目的。然而，请注意，此类访问可能会创建竞争条件，如果不小心的话，这可能会导致信息泄漏。作为一个示例，考虑以下表设计：

In the examples above, the policy expressions consider only the current values in the row to be accessed or updated. This is the simplest and best-performing case; when possible, it’s best to design row security applications to work this way. If it is necessary to consult other rows or other tables to make a policy decision, that can be accomplished using sub-_SELECT_s, or functions that contain _SELECT_s, in the policy expressions. Be aware however that such accesses can create race conditions that could allow information leakage if care is not taken. As an example, consider the following table design:

-- definition of privilege groups
CREATE TABLE groups (group_id int PRIMARY KEY,
                     group_name text NOT NULL);

INSERT INTO groups VALUES
  (1, 'low'),
  (2, 'medium'),
  (5, 'high');

GRANT ALL ON groups TO alice;  -- alice is the administrator
GRANT SELECT ON groups TO public;

-- definition of users' privilege levels
CREATE TABLE users (user_name text PRIMARY KEY,
                    group_id int NOT NULL REFERENCES groups);

INSERT INTO users VALUES
  ('alice', 5),
  ('bob', 2),
  ('mallory', 2);

GRANT ALL ON users TO alice;
GRANT SELECT ON users TO public;

-- table holding the information to be protected
CREATE TABLE information (info text,
                          group_id int NOT NULL REFERENCES groups);

INSERT INTO information VALUES
  ('barely secret', 1),
  ('slightly secret', 2),
  ('very secret', 5);

ALTER TABLE information ENABLE ROW LEVEL SECURITY;

-- a row should be visible to/updatable by users whose security group_id is
-- greater than or equal to the row's group_id
CREATE POLICY fp_s ON information FOR SELECT
  USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY fp_u ON information FOR UPDATE
  USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));

-- we rely only on RLS to protect the information table
GRANT ALL ON information TO public;

现在，假设 alice 希望更改“略微秘密”信息，但认为 mallory 不应该被授予该行的新的内容，因此她这样做了：

Now suppose that alice wishes to change the “slightly secret” information, but decides that mallory should not be trusted with the new content of that row, so she does:

BEGIN;
UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
COMMIT;

它看起来很安全；在 mallory 应该能够看到“secret from mallory”字符串的窗口中没有出现该字符串。但是，此处存在竞争条件。如果 mallory 同时正在执行（例如）：

That looks safe; there is no window wherein mallory should be able to see the “secret from mallory” string. However, there is a race condition here. If mallory is concurrently doing, say,

SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

且其事务处于 READ COMMITTED 模式，则有可能让她看到“secret from mallory”。如果她的事务在 alice 完成之后到达 information 行，则会发生这种情况。它会阻塞等待 alice 的事务提交，然后由于 FOR UPDATE 子句，它会获取更新后的行内容。但是，它确实从 users 中_not_ 获取 SELECT 的更新后的行，因为该子 SELECT 没有 FOR UPDATE；相反，users 行被使用在查询开始时拍摄的快照来读取。因此，策略表达式会测试 mallory 的权限级别的旧值，并允许她看到更新后的行。

and her transaction is in READ COMMITTED mode, it is possible for her to see “secret from mallory”. That happens if her transaction reaches the information row just after alice's does. It blocks waiting for alice's transaction to commit, then fetches the updated row contents thanks to the FOR UPDATE clause. However, it does not fetch an updated row for the implicit SELECT from users, because that sub-SELECT did not have FOR UPDATE; instead the users row is read with the snapshot taken at the start of the query. Therefore, the policy expression tests the old value of mallory's privilege level and allows her to see the updated row.

有几种方法可以解决这个问题。一个简单的答案是针对受影响的用户在被引用的表（此处为 users）上使用 SELECT … FOR SHARE 子 SELECT_s in row security policies. However, that requires granting _UPDATE 权限，但这可能不受欢迎。（但可以应用另一个行安全性策略来防止他们实际行使该权限；或者，子 SELECT 可以嵌入到安全定义函数中。）另外，频繁并行使用被引用的表上的行共享锁可能会造成性能问题，尤其是如果其更新很频繁的话。另一个解决方案是，如果对被引用的表的更新不频繁，则在更新被引用的表时获取该表上的 ACCESS EXCLUSIVE 锁，以便没有并行事务能够检查旧的行值。或者，人们只需等待所有并行事务在提交某个被引用的表的更新后结束，然后再基于新安全状态进行依靠的更改。

There are several ways around this problem. One simple answer is to use SELECT … FOR SHARE in sub-SELECT_s in row security policies. However, that requires granting _UPDATE privilege on the referenced table (here users) to the affected users, which might be undesirable. (But another row security policy could be applied to prevent them from actually exercising that privilege; or the sub-SELECT could be embedded into a security definer function.) Also, heavy concurrent use of row share locks on the referenced table could pose a performance problem, especially if updates of it are frequent. Another solution, practical if updates of the referenced table are infrequent, is to take an ACCESS EXCLUSIVE lock on the referenced table when updating it, so that no concurrent transactions could be examining old row values. Or one could just wait for all concurrent transactions to end after committing an update of the referenced table and before making changes that rely on the new security situation.

有关其他详细信息，请参阅 CREATE POLICY 和 ALTER TABLE 。

For additional details see CREATE POLICY and ALTER TABLE.