除了可以通过 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。