5.7. Row Security Policies

5.7. Row Security Policies

5.7.行级安全策略

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.

除了可通过GRANT使用的SQL标准权限系统外，表还可以有行级安全策略，以基于每个用户限制通过常规查询能够返回的行或者通过数据操纵语言可以插入、更新或删除的行。此特性称为行级安全策略。默认情况下，表是没有相关权限的，所以，只要一个用户对此表有访问权限，那么表里面的所有行对该用户来说均可查看或更改。

 

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.

当行级安全在表中启用（使用命令ALTER TABLE...ENABLE ROW LEVEL SECURITY），那么所有对于表进行选择行或更改行的这些常规访问均需要经过行级安全策略的允许才可以。（然而，表的所有者一般不受行级安全策略的限制。）如果表上并没有相关的策略，那么会使用默认的拒绝权限，也就是，所有行均不可见、不可更改。针对全表的操作，例如TRUNCATE和REFERENCES，不受行级安全的限制。

 

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.

行级安全策略可特定于命令或角色或两者皆可。策略可指定对所有命令有效，或SELECT，INSERT，UPDATE或DELETE。 可以将多个角色分配给给定策略，并应用给常规角色成员身份和继承规则。

 

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.

使用可以返回布尔值的表达式以根据策略指定可见或可更改的行。这些表达式会先于用户查询中的条件或函数对每行进行检测。（此规则唯一的例外是leakproof函数，此函数用以保证不泄露信息；优化器会优先执行此函数。）表达式不能返回true的不会被处理。对于可见的行和可更新的行，可以指定各自单独的表达式，用以进行独立的控制。策略表达式作为查询的一部分运行，并以运行查询的用户的权限运行，然而，安全定义函数可以用来访问执行用户不能访问的数据。

 

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.

超级用户和具有BYPASSRLS属性的角色，在访问表的时候不受行级安全系统限制。表所有者通常也不受行级安全限制，但是表所有者可以通过使用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.

一般仅表的所有者具有启用和禁用行级安全，以及给表添加策略的这些权限。

 

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.

使用 CREATE POLICY命令创建策略，使用 ALTER POLICY命令更改策略，使用 DROP POLICY命令删除策略。使用ALTER TABLE命令启用或禁用表的行级安全。

 

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.

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

 

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.

当对于给定查询应用多个策略的时候，它们使用or（对于允许的策略，这是默认值）或者and（对于限制性的策略）组合。这类似于给定角色具有其所属所有角色具有的权限。允许的和限定性的策略，稍后探讨。

 

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:

以下为一个简单的示例，示例内容为对表account创建一个策略以只允许属于managers角色的成员能够访问表行，且只能访问他们账号的表行：

 

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);

 

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).

上面的策略隐式提供了与其USING子句相同的WITH CHECK子句，因此该约束既适用于命令选择的行（因此管理者不能SELECT，UPDATE或DELETE属于不同管理者的现有行），也适用于通过命令修改的行（因此不能通过INSERT或UPDATE创建属于其他管理者的行）。

 

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:

如果没有指定角色，或者指定了PUBLIC，那么策略对系统内所有用户有效。以下的简单策略，可以允许用户仅访问users表中他们自己的行：

 

CREATE POLICY user_policy ON users

USING (user_name = current_user);

 

This works similarly to the previous example.

这与前面的示例类似。

 

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:

可以通过设置多个策略，为表中可见的行及插入表中的行使用不同的规则。以下的两个策略，允许所有用户访问users表中所有的行，但仅允许更新自己的行：

 

CREATE POLICY user_sel_policy ON users

FOR SELECT

USING (true);

CREATE POLICY user_mod_policy ON users

USING (user_name = current_user);

 

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.

使用SELECT命令时，此两个策略使用OR连接，是的用户可以访问表中的所有行。而对于其他命令，仅第二个策略有效，因此与之前的示例效果一致。

 

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.

行级安全可以使用命令ALTER TABLE禁用。禁用行级安全并不会删掉表上定义的策略；它们只是被忽略了而已。 然后，表中的所有行都是可见且可以修改的了，但要遵循标准的SQL权限体系。

 

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

以下是在生产环境中如何使用此功能的示例。 表passwd模拟Unix中的密码文件：

 

-- 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 relation 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 relation 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 relation passwd

postgres=> insert into passwd (user_name) values ('xxx');

ERROR: permission denied for relation passwd

-- Alice can change her own password; RLS silently prevents updating other rows

postgres=> update passwd set pwhash = 'abc';

UPDATE 1

 

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:

到目前为止，构造的所有策略都是许可策略，这意味着当应用多个策略时，将使用“or”布尔运算符将它们组合在一起。虽然可以将许可策略构造为仅允许在预期的情况下访问行，但是将许可策略与限制性策略（记录必须符合且使用“ AND”布尔运算符组合的限制性策略）组合起来会更简单。 在上面的示例的基础上，我们添加了一个限制性策略，以限制管理员仅可通过本地Unix套接字连接访问passwd表中的记录：

 

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)

=> SELECT current_user;

current_user

--------------

admin

(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.

引用完整性检查（例如，唯一或主键约束和外键引用）始终绕过行安全策略，以确保维护数据完整性。 在规划模式和行级策略时，必须小心，以免因为此类参照完整性检查而导致信息的泄漏。

 

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.

在某些情况下，确保未启用行级安全很重要。例如，在进行备份时，如果因为行级安全而导致某些行未备份，那将是灾难性的。在这种情况下，可以将row_security参数设置为off。这本身并不绕过行级安全；而仅当查询的结果被策略过滤时，触发报错。 然后可以调查错误原因并修复该错误。

 

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-SELECTs, or functions that contain SELECTs,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:

在上面的示例中，策略表达式仅考虑要访问或更新的当前行中的值。 这是最简单，性能最好的情况；在可能的情况下，最好设计行安全性应用程序以这种方式工作。如果需要关联其他行或其他表来制定策略决策，则可以在策略表达式中使用SELECT子查询或包含SELECT的函数来完成。但是请注意，如果不注意，此类访问会造成竞争条件，从而可能导致信息泄漏。 例如，请考虑以下表格设计：

 

-- 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;

 

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:

现在，假设alise希望更改机密信息，决定不让mallory看到行里面新的内容，所以，她做了以下操作：

 

BEGIN;

UPDATE users SET group_id = 1 WHERE user_name = 'mallory';

UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;

COMMIT;

 

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,

这看起来是安全；mallory无法看到字符串“secret from mallory”。然而，此处存在竞赛条件。如果mallory与此同时进行如下操作：

 

SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

 

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.

因为她的事务处于READ COMMITTED模式，所以可能看到“secret from mallory”句子。如果她的事务紧随alice的操作访问information行，那么就会发生这种情形。因为FOR UPDATE语句，它会停止等待alice事务的提交，然后查看到更新的行内容。然而，因为子查询中没有FOR UPDATE子句，所以隐式SELECT不会从users中读取到更新的行；而是读取到开始执行查询时的users行的快照。因此，此策略表达式检测了mallory旧的权限级别然后使得她有权限读取更新的行。

 

There are several ways around this problem. One simple answer is to use SELECT ... FOR SHARE in sub-SELECTs 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 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.

针对此问题，可以有多种方法解决。一个简单方式是在行级安全策略子查询中使用SELECT ... FOR SHARE。但是，这需要向受影响的用户授予对引用表（此处为users）的UPDATE特权，这可能是不希望的。（但是，可以通过应用另一个行级安全策略来防止实际执行该权限；或者可以将子查询嵌入到安全定义函数中。）而且，在引用表上大量同时使用行共享锁可能会带来性能问题，特别是当更新频繁的时候。另一种解决方案，在不经常更新被引用表的时候特别实用，那就是当更新行的时候在表上添加排他锁，以使没有并行事务可以查看旧行值。或者，可以在提交引用表的更新之后并在依赖新的安全状况进行更改之前等待所有并发事务结束。

 

For additional details see CREATE POLICY and ALTER TABLE.

更对详情，请参见 CREATE POLICY和 ALTER TABLE。