Postgresql 中文操作指南

5.8. Row Security Policies #

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

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

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

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

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

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

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

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

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

作为一个简单的示例，以下是如何在 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);

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

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

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

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

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

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 合并这两个策略，其结果是所有行均可被选择。在其他命令类型中，仅应用第二个策略，以便其效果与之前相同。

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

以下是一个较大的示例，说明如何在生产环境中使用这一特性。表 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;

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

-- 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 表的记录：

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

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

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

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

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

在上面的示例中，策略表达式仅考虑要访问或更新的行中的当前值。这是最简单、性能最好的情况；如果可能的话，最好以此方式设计行安全性应用程序。如果需要咨询其他行或其他表以做出策略决策，则可以在策略表达式中使用子 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;

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

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 同时正在执行（例如）：

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 的权限级别的旧值，并允许她看到更新后的行。

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

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