Postgresql - Row Level Security Policy

Row Level Security Policy

除了通过GRANT提供的SQL标准特权系统之外，表还可以具有行安全策略，以每个用户为基础限制哪些行可以通过常规查询返回，或者通过数据修改命令插入、更新或删除。此功能也称为行级安全性。默认情况下，表没有任何策略，因此，如果用户根据SQL特权系统对表具有访问权限，则表中的所有行都同样可用于查询或更新。

当对表启用行安全性时（ALTER TABLE ... ENABLE ROW LEVEL SECURITY），行安全策略必须允许对表的所有常规访问（用于选择行或修改行）。（但是，表的所有者通常不受行安全策略的约束。）如果该表不存在任何策略，则使用默认的拒绝策略，这意味着没有可见或可以修改的行。应用于整个表的操作（如截断和引用）不受行安全性的约束。

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

要根据策略指定哪些行可见或可修改，需要一个返回布尔结果的表达式。在用户查询的任何条件或函数之前，将对每一行计算此表达式。（此规则的唯一例外是保证不会泄漏信息的防漏函数；优化器可以选择在行安全检查之前应用这些函数。）将不会处理表达式未返回true的行。可以指定单独的表达式来对可见行和允许修改的行提供独立的控制。策略表达式作为查询的一部分运行，并且具有运行查询的用户的特权，尽管可以使用安全定义函数访问调用用户不可用的数据。

具有BYPASSRLS属性的超级用户和角色在访问表时总是绕过行安全系统。表所有者通常也会绕过行安全性，尽管表所有者可以选择使用ALTER TABLE ... FORCE ROW LEVEL SECURITY.

启用和禁用行安全性以及向表中添加策略始终是表所有者的特权。

使用create policy命令创建策略，使用alter policy命令更改策略，并使用drop policy命令删除策略。要启用和禁用给定表的行安全性，请使用alter table命令。

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

当多个策略应用于一个给定的查询时，它们将使用或（对于允许策略，为默认）或使用和（对于限制策略）进行组合。这类似于一个给定角色拥有其所属所有角色的特权的规则。许可政策和限制政策将在下面进一步讨论。

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

上面的策略隐式地提供了一个与它的using子句相同的with check子句，这样约束既适用于命令选择的行（因此管理器无法选择、更新或删除属于不同管理器的现有行），也适用于命令修改的行（因此不能通过中的插入或更新）。

如果没有指定角色，或者使用了特殊的用户名public，那么该策略将应用于系统上的所有用户。要允许所有用户仅访问用户表中自己的行，可以使用简单策略：

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

要对添加到表中的行使用与可见行不同的策略，可以组合多个策略。这对策略将允许所有用户查看用户表中的所有行，但只修改自己的行：

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

在select命令中，这两个策略是使用或组合的，其净效果是可以选择所有行。在其他命令类型中，只有第二个策略适用，因此效果与以前相同。

也可以使用alter table命令禁用行安全性。禁用行安全性不会删除表中定义的任何策略；它们只是被忽略。然后，表中的所有行都是可见的和可修改的，这取决于标准的SQL权限系统。

当应用多个策略时，它们将使用“或”布尔运算符进行组合。虽然可以构造允许策略以仅允许在预期情况下访问行，但是可以更简单地将允许策略与限制策略（记录必须通过并且使用“和”布尔运算符组合）组合在一起。

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

在某些情况下，确保没有应用行安全性是很重要的。例如，在进行备份时，如果行安全性悄悄地导致某些行从备份中被省略，则可能会造成灾难性的后果。在这种情况下，可以将row_security configuration参数设置为off。这本身并没有绕过行安全性；它所做的是，如果任何查询的结果将被策略筛选，则抛出一个错误。然后可以调查并修复错误的原因。

在上面的示例中，策略表达式只考虑要访问或更新的行中的当前值。这是最简单和性能最好的情况；如果可能，最好设计行安全应用程序以这样工作。如果需要查询其他行或其他表来做出策略决策，可以在策略表达式中使用子选择或包含选择的函数来完成。但是要注意，如果不小心，这些访问可能会创建竞争条件，从而允许信息泄漏。

===================================

实验  （环境 pg 11.1）

CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

1. 建实验表，插数据，创建用户

mytest=# create user user1;
CREATE ROLE
mytest=# create user user2;
CREATE ROLE
mytest=# grant ALL ON DATABASE mytest TO user1 ;
GRANT
mytest=# grant ALL ON DATABASE mytest TO user2;
GRANT
mytest=# create table test0521(id int, col1 varchar(20), col2 varchar(20),user_name varchar(20));
CREATE TABLE
mytest=# insert into test0521 values (1,'a','aa', 'user1');
INSERT 0 1
mytest=# insert into test0521 values (2,'a','bb', 'user1');
INSERT 0 1
mytest=# insert into test0521 values (3,'b','cc', 'user1');
INSERT 0 1
mytest=# insert into test0521 values (11,'b','aa', 'user2');
INSERT 0 1
mytest=# insert into test0521 values (12,'b','bb', 'user2');
INSERT 0 1
mytest=# insert into test0521 values (13,'a','cc', 'user2');
INSERT 0 1

2. 表改为 Row Level Security

mytest=# alter table test0521 enable row level security ;
ALTER TABLE
mytest=# select * from pg_tables where tablename = 'test0521' ;
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | test0521  | dbadmin    |            | f          | f        | f           | t
(1 row)

3. 为表创建Policy

3.1 对某一个用户限制，对于表内的其他用户，匹配上用户=当前用户返回空。

mytest=# create policy test0521_user on test0521 to user1 using( user_name = current_user );
CREATE POLICY
mytest=> \c mytest user1
You are now connected to database "mytest" as user "user1".
mytest=> select * from test0521;
 id | col1 | col2 | user_name
----+------+------+-----------
  1 | a    | aa   | user1
  2 | a    | bb   | user1
  3 | b    | cc   | user1
(3 rows)

mytest=> \c mytest user2
You are now connected to database "mytest" as user "user2".
mytest=> select * from test0521;
 id | col1 | col2 | user_name
----+------+------+-----------
(0 rows)

3.2 对所有用户做限制。

mytest=# create policy test0521_user on test0521 using ( user_name = current_user);
CREATE POLICY
mytest=# \c mytest user1
You are now connected to database "mytest" as user "user1".
mytest=> select * from test0521;
 id | col1 | col2 | user_name
----+------+------+-----------
  1 | a    | aa   | user1
  2 | a    | bb   | user1
  3 | b    | cc   | user1
(3 rows)
mytest=> \c mytest user2
You are now connected to database "mytest" as user "user2".
mytest=> select * from test0521;
 id | col1 | col2 | user_name
----+------+------+-----------
 11 | b    | aa   | user2
 12 | b    | bb   | user2
 13 | a    | cc   | user2
(3 rows)

3.3

Session A:
mytest=# select current_user;
 current_user
--------------
 dbadmin
(1 row)
mytest=# create policy test0521_policy_01 on test0521 to user1 using(true) with check (true);
CREATE POLICY
mytest=# create policy test0521_policy_02 on test0521 for select using (true);
CREATE POLICY
mytest=# create policy test0521_policy_03 on test0521 for update using (current_user = user_name)
with check (current_user = user_name and col1 in ('x','y','z','m','n'));
CREATE POLICY

Session B:
mytest=> select current_user;
 current_user
--------------
 user2
(1 row)
# 更新其他条目显示0条
mytest=> update test0521 set col2 = 'xx' where id = 1;
UPDATE 0
# 更新不为  col1 in ('x','y','z','m','n')，报错
mytest=> update test0521 set col2 = 'xx' where id = 11;
ERROR:  new row violates row-level security policy for table "test0521"
mytest=> update test0521 set col1 = 'a' where id = 11;
ERROR:  new row violates row-level security policy for table "test0521"
# 只有当更新符合policy时，成功。
mytest=> update test0521 set col1 = 'x' where id = 11;
UPDATE 1
# 删除条目为0
mytest=> delete from test0521 where id = 1;
DELETE 0
mytest=> delete from test0521 where id = 11;
DELETE 0
# 插入报错
mytest=> insert into test0521 values (15,'a','aa','user2');
ERROR:  new row violates row-level security policy for table "test0521"

3.4

mytest=# create policy test0521_policy_04 on test0521 AS RESTRICTIVE to user3 using(pg_catalog.inet_client_addr() IS NULL);
CREATE POLICY
session B :
mytest=> select current_user;
 current_user
--------------
 user3
(1 row)
mytest=# select pg_catalog.inet_client_addr();
 inet_client_addr
------------------
 127.0.0.1
(1 row)
mytest=> select * from test0521;
 id | col1 | col2 | user_name
----+------+------+-----------
(0 rows)
Session C:
mytest=> select pg_catalog.inet_client_addr();
 inet_client_addr
------------------

(1 row)
mytest=> select * from test0521;
 id | col1 | col2 | user_name
----+------+------+-----------
  1 | a    | aa   | user1
  2 | b    | bb   | user1
  3 | b    | cc   | user1
 12 | b    | bb   | user2
 13 | a    | cc   | user2
 21 | a    | aa   | user3
 22 | b    | aa   | user3
 23 | a    | aa   | user3
 31 | a    | aa   | user4
 32 | b    | aa   | user4
 33 | a    | aa   | user4
 11 | x    | aa   | user2
(12 rows)

3.5

mytest=# select current_user;
 current_user
--------------
 dbadmin
(1 row)
mytest=# create table test0521_user(id int primary key, username varchar(20));
CREATE TABLE
mytest=# create table test0521_info(id int primary key, info varchar(20), user_id int not null references test0521_user);
CREATE TABLE
mytest=# alter table test0521_info enable row level security ;
ALTER TABLE
mytest=# insert into  test0521_user values (1, 'user1'), (2, 'user2'), (3, 'user3'), (4, 'user4');
INSERT 0 4
mytest=# insert into test0521_info values (1,'a',1),(2,'b',2),(3,'c',3),(4,'d',4);
INSERT 0 4
mytest=# create policy test0521_info_policy_s on test0521_info for select using ( user_id <= (select id from test0521_user where username = current_user));
CREATE POLICY
mytest=#  create policy test0521_info_policy_u on test0521_info for update using ( user_id <= (select id from test0521_user where username = current_user));
CREATE POLICY
Session B:
mytest=> select current_user;
 current_user
--------------
 user1
(1 row)
mytest=> select * from test0521_info ;
 id | info | user_id
----+------+---------
  1 | a    |       1
(1 row)
mytest=> update test0521_info set info = 'aaa' where id = 2;
UPDATE 0
mytest=> update test0521_info set info = 'aaa' where id = 1;
UPDATE 1
Session C:
mytest=> select current_user;
 current_user
--------------
 user2
(1 row)
mytest=> select * from test0521_info ;
 id | info | user_id
----+------+---------
  1 | a    |       1
  2 | b    |       2
(2 rows)
mytest=> update test0521_info set info = 'aaaaa' where id = 1;
UPDATE 1
mytest=> update test0521_info set info = 'aaaaa' where id = 2;
UPDATE 1
mytest=> update test0521_info set info = 'aaaaa' where id = 3;
UPDATE 0
Session D:
mytest=> select current_user;
 current_user
--------------
 user4
(1 row)
mytest=> select * from test0521_info ;
 id | info | user_id
----+------+---------
  1 | a    |       1
  2 | b    |       2
  3 | c    |       3
  4 | d    |       4
(4 rows)
mytest=> update test0521_info set info = 'aaaaaaaaaa' where id = 2;
UPDATE 1
mytest=> update test0521_info set info = 'aaaaaaaaaa' where id = 4;
UPDATE 1