postgresql-行级安全-RLS

行级安全-RLS

行级安全策略

9.5版本新增的特性，该特性是在数据库授权体系下提供的更细粒度的控制。通俗的说就是不同用户可以看到表中不同的数据，这种控制是行级别的

9.5以前的数据库安全技术是通过grant/revoke来实现的，这两个指令提供了对象级的安全限制，针对表还有列级别的安全限制。

所有对数据的操作，暴扣数据查询和更新，都受策略的限制，如果没有配置安全策略，所有的查询和更新都会禁止，但是对全表进行操作的命令，truncate和refrences不受影响

行级安全策略可以加在命令上，也可以加在角色上，也可以两者都加。命令可以是 ALL, SELECT, INSERT, UPDATE 和
DELETE, 同一个策略也可以赋予多个角色。但是表的所有者，超级用户 (postgres) 以及加上了 BYPASSRLS
属性的角色不受安全性的限制。如果应用想忽略行级安全性机制的限制，也可以将 row_security 设置为 off。

CREATE POLICY ， ALTER POLICY ， DROP POLICY 命令分别用于策略的创建、修改和删除， ALTER TABLE 可以用于行级安全性的启用 / 禁用。

每个策略都有一个名字，每个表可以定义多个策略，因为策略是针对表的，所以表内的多个策略名字必须唯一，但是不同的表可以有同名的策略，当表有多个策略时，多个策略之间是 OR 的关系。

语法

CREATE POLICY name ON table_name
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]
using:针对已经存在的记录的校验，可实施在select，update，delete，all上
with check：针对将要新增的记录的校验，可实施在insert，update，all上
update因为涉及旧的数据和新的记录，如果只写了using，但是没有提供with check的话，using同时会当成with check来进行检查

如果针对同样的命令创建了多个策略, 所有策略中任意一个为TRUE都通过.

例如ALL, SELECT各创建了一个策略for role r1, 执行select时任意一个为TRUE都通过.

• 开启了安全策略后，普通用户能否正常访问数据？

• 更新策略（更新是标记为删除后插入，也可以看出先查询后插入）需要注意什么？

--创建三个用户
postgres=# create role r1 login;
CREATE ROLE
postgres=# create role r2 login;
CREATE ROLE
postgres=# create role r3 login;
CREATE ROLE

--创建表
postgres=# create table test(id int, r name);  
CREATE TABLE
postgres=#  insert into test values(1, 'r1');  
INSERT 0 1
postgres=# insert into test values(2, 'r2');  
INSERT 0 1
postgres=# insert into test values(3, 'r3');  
INSERT 0 1
postgres=#  grant all on table test to public;  
GRANT

--创建一个新增数据的策略，with check
postgres=# create policy p on test for insert to r1 with check( r = current_user);
CREATE POLICY
postgres=# 

--默认情况下状态是disable状态
postgres=# d+ test
                         Table "public.test"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
 r      | name    |           | plain   |              | 
Policies (row security disabled):
    POLICY "p" FOR INSERT
      TO r1
      WITH CHECK ((r = "current_user"()))

--pg_policies可以查看已经创建的策略
postgres=# select * from pg_policies;
 schemaname | tablename | policyname | roles |  cmd   | qual |       with_check       
------------+-----------+------------+-------+--------+------+------------------------
 public     | test      | p          | {r1}  | INSERT |      | (r = "current_user"())
(1 row)

postgres=# c postgres r1
FATAL:  permission denied for database "postgres"
DETAIL:  User does not have CONNECT privilege.
Previous connection kept
--赋予连接权限
postgres=# grant connect on database postgres to r1;
GRANT
postgres=# grant connect on database postgres to r2;
GRANT
postgres=# grant connect on database postgres to r3;
GRANT

--在策略是disable状态的时候，策略视为无效状态
postgres=# c postgres r1
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "r1".
postgres=> 
postgres=> insert into test values(4,'r1');
INSERT 0 1
postgres=> insert into test values(4,'r2');
INSERT 0 1
postgres=> 

--使策略生效
postgres=> c postgres sa
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "sa".
postgres=# alter table test enable row level security;
ALTER TABLE

--再次插入数据就只能插入和r1角色同名的r值，r1用户插入了r2值会报错
postgres=# c postgres r1
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "r1".
postgres=> insert into test values(4,'r2');
ERROR:  new row violates row-level security policy for table "test"
postgres=> insert into test values(4,'r1');
INSERT 0 1

• 新增策略

--新增一个策略，r1角色插入test表时，允许r字段的值为‘r1’和’r2‘
postgres=> c postgres sa
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "sa".
postgres=# create policy p1 on test for insert to r1 with check( r = 'r2');  
CREATE POLICY
postgres=# 
postgres=# c postgres r1
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "r1".
postgres=> insert into test values(4,'r2');  
INSERT 0 1
postgres=> insert into test values(4,'r1');  
INSERT 0 1
postgres=> insert into test values(4,'r3');  
ERROR:  new row violates row-level security policy for table "test"

postgres=> d+ test
                         Table "public.test"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
 r      | name    |           | plain   |              | 
Policies:
    POLICY "p" FOR INSERT
      TO r1
      WITH CHECK ((r = "current_user"()))
    POLICY "p1" FOR INSERT
      TO r1
      WITH CHECK ((r = 'r2'::name))

• 开启策略后，普通用户无法在访问数据

postgres=> c postgres r2
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "r2".
postgres=> select  * from test;
 id | r 
----+---
(0 rows)

postgres=> c postgres r1
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "r1".
postgres=> select * from test;
 id | r 
----+---
(0 rows)

• 查询策略

--r1只能查看到r='r1'的数据
postgres=> c postgres sa
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "sa".
postgres=# create policy p2 on test for select to r1 using ( r = current_user);  
CREATE POLICY
postgres=# 
postgres=# c postgres r1
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "r1".
postgres=> select * from test;
 id | r  
----+----
  1 | r1
  4 | r1
  4 | r1
  4 | r1
(4 rows)


--创建所有用户只能看到r= current_user，to public
postgres=> c postgres sa
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "sa".
postgres=# create policy p3 on test for select to public using ( r = current_user);  
CREATE POLICY
postgres=# c postgres r2
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "r2".
postgres=> select * from test;
 id | r  
----+----
  2 | r2
  4 | r2
  4 | r2
(3 rows)

• 更新策略

--创建更新策略
postgres=# create policy p4 on test for update to r3 using(r=current_user);
CREATE POLICY

--删除开始的策略
postgres=# drop policy p on test;
DROP POLICY
postgres=# drop policy p1 on test;
DROP POLICY
postgres=# drop policy p2 on test;
DROP POLICY
postgres=# drop policy p3 on test;
DROP POLICY
postgres=# d+ test
                         Table "public.test"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
 r      | name    |           | plain   |              | 
Policies:
    POLICY "p4" FOR UPDATE
      TO r3
      USING ((r = "current_user"()))

postgres=# c postgres r3
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "r3".
--更新数据为0，是没有创建查询策略？
postgres=> update test set id=4 where r = 'r3';
UPDATE 0
postgres=> select * from test;
 id | r 
----+---
(0 rows)

--创建了查询策略后，数据可以正常更新
postgres=> c postgres sa
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "sa".
postgres=# create policy p1 on test for select to r3 using ( r = current_user);  
CREATE POLICY
postgres=# c postgres r3
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "r3".
postgres=> select * from test;
 id | r  
----+----
  3 | r3
(1 row)
--常见查询策略后，更新了数据
postgres=> update test set id=4 where r = 'r3';
UPDATE 1

• 策略只针对非超级用户和非owner

--超级用户可以看到所有数据
postgres=> c postgres sa
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "sa".
postgres=# select * from test;
 id | r  
----+----
  1 | r1
  2 | r2
  3 | r3
  4 | r1
  4 | r2
  4 | r1
  4 | r2
  4 | r1
(8 rows)

postgres=# alter role r1 superuser;
ALTER ROLE
postgres=# c postgres r1
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "r1".
postgres=# select * from test;
 id | r  
----+----
  1 | r1
  2 | r2
  3 | r3
  4 | r1
  4 | r2
  4 | r1
  4 | r2
  4 | r1
(8 rows)

• 普通用户对添加了行级安全策略的表pg_dump或者pg_restore时会报错

 pg_dump -U r1 -d postgres -p 5432 -t public.test
pg_dump: [archiver (db)] query failed: ERROR:  query would be affected by row-level security policy for table "test"
pg_dump: [archiver (db)] query was: COPY public.test (id, r) TO stdout;

CREATE POLICY ， ALTER POLICY ， DROP POLICY 命令分别用于策略的创建、修改和删除， ALTER TABLE 可以用于行级安全性的启用 / 禁用。

每个策略都有一个名字，每个表可以定义多个策略，因为策略是针对表的，所以表内的多个策略名字必须唯一，但是不同的表可以有同名的策略，当表有多个策略时，多个策略之间是 OR 的关系。

RLS参考资料
RLS参考资料