PostgreSQL 教程

Appearance

5.8. 行安全策略# ​

除了可以通过GRANT获得的 SQL 标准权限系统之外，表还可以有行安全策略，根据每个用户限制普通查询可以返回的行，或者数据修改命令可以插入、更新或删除的行。此功能也称为行级安全。默认情况下，表没有任何策略，因此，如果用户根据 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子句相同，以便该约束既适用于命令所选的行（因此，管理员无法SELECT、UPDATE或DELETE属于其他管理员的现有行），也适用于命令修改的行（因此，无法通过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希望更改““slightly secret””信息，但决定不应信任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模式，她有可能看到“来自 mallory 的秘密”。如果她的事务在alice的事务之后立即到达information行，就会发生这种情况。它会阻塞，等待alice的事务提交，然后由于FOR UPDATE子句而获取更新的行内容。但是，它不会为users中的隐式SELECT获取更新的行，因为该子SELECT没有FOR UPDATE；相反，users行会使用在查询开始时获取的快照进行读取。因此，策略表达式会测试mallory权限级别的旧值，并允许她看到更新的行。

解决此问题的方法有很多。一个简单的答案是在行安全策略中的子SELECT中使用SELECT ... FOR SHARE。但是，这需要向受影响的用户授予对引用表（此处为users）的UPDATE权限，这可能不受欢迎。（但可以应用另一个行安全策略来阻止他们实际行使该权限；或者可以将子SELECT嵌入到安全定义器函数中。）此外，对引用表进行大量并发行共享锁使用可能会造成性能问题，尤其是当对它的更新很频繁时。另一种解决方案是，如果引用表的更新不频繁，则在更新引用表时对其进行ACCESS EXCLUSIVE锁定，以便没有并发事务可以检查旧行值。或者，可以等到所有并发事务在提交引用表的更新后结束，然后再进行依赖于新安全情况的更改。

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