行级安全-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 的关系。