行级策略实验
创建测试表并插入数据：
TEST=# create table test(id serial primary key, user_name varchar(32), crt_time timestamp default now());
CREATE TABLE
TEST=# insert into test(user_name) values('U1');
INSERT 0 1
TEST=# insert into test(user_name) values('U2');
INSERT 0 1
TEST=# insert into test(user_name) values('U3');
INSERT 0 1
TEST=# insert into test(user_name) values('U1');
INSERT 0 1
TEST=# insert into test(user_name) values('U2');
INSERT 0 1
TEST=# insert into test(user_name) values('U3');
INSERT 0 1
创建用户：
TEST=# create user U1 password 'u1'; TEST=# create user U2 password 'u2'; TEST=# create user U3 password 'u3';
开启表的行级策略：
TEST=# alter table test enable row level security; ALTER TABLE
创建行级策略：
TEST=# h create policy
Command: CREATE POLICY
Description: define a new row level security policy for a table
Syntax:
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 ) ]
--name -- 策略名称
--table_name -- 适用于该策略的表
--FOR -- 该策略适用的DML命令，ALL表示所有
--TO -- 该策略适用的角色
--USING -- 应用在表上的CHECK表达式，返回true的行可见，false不可见
--WITH CHECK -- 应用在该表的INSERT或UPDATE的SQL表达式，true的可以操作，false操作失败
创建查询的行级策略：
TEST=# create policy plcy1 on test for select using (user_name = current_user); CREATE POLICY TEST=# grant all on table test to u1, u2, u3; GRANT TEST=# c TEST U1 Password for user U1: You are now connected to database "TEST" as user "U1". TEST=> select * from test; ID | USER_NAME | CRT_TIME ----+-----------+---------------------------- 1 | U1 | 2020-02-04 01:18:32.744308 4 | U1 | 2020-02-04 01:18:46.369409 (2 rows)
只有查询的行级策略，是不能修改对应的记录：
TEST=> update test set crt_time = now() where id = 1;
UPDATE 0
TEST=> update test set crt_time = now() where id = 4;
UPDATE 0
只有查询的行级策略，也不能插入对应的数据：
TEST=> insert into test(user_name) values('U1');
ERROR: permission denied for sequence TEST_ID_SEQ
TEST=> insert into test(id, user_name) values(8,'U1');
ERROR: new row violates row-level security policy for table "TEST"
添加策略，将所有DML命令都进行判断：
TEST=# create policy plcy2 on test for all using (user_name = current_user);
CREATE POLICY
TEST=# c TEST U1
Password for user U1:
You are now connected to database "TEST" as user "U1".
TEST=> update test set crt_time = now() where id = 4;
UPDATE 1
TEST=> insert into test(user_name) values('U1');
ERROR: permission denied for sequence TEST_ID_SEQ
TEST=> insert into test(id,user_name) values(8,'U1');
INSERT 0 1
TEST=> select * from test;
ID | USER_NAME | CRT_TIME
----+-----------+----------------------------
1 | U1 | 2020-02-04 01:18:32.744308
4 | U1 | 2020-02-04 01:32:21.707241
8 | U1 | 2020-02-04 01:33:03.484166
(3 rows)
实验with check的检查，针对insert不能有using部分，update和delete则允许：
TEST=# create policy plcy5 on test for insert using (user_name = current_user) with check (user_name = current_user and id != 100); ERROR: only WITH CHECK expression allowed for INSERT TEST=# create policy plcy3 on test for insert with check (user_name = current_user and id != 100); CREATE POLICY TEST=# c TEST U1 Password for user U1: You are now connected to database "TEST" as user "U1". TEST=> select * from test; ID | USER_NAME | CRT_TIME ----+-----------+---------------------------- 1 | U1 | 2020-02-04 01:18:32.744308 4 | U1 | 2020-02-04 01:32:21.707241 8 | U1 | 2020-02-04 01:33:03.484166 (3 rows) TEST=> insert into test(id, user_name) values(22, 'U1'); INSERT 0 1 还是能插入id为100的数据，因为前面有all的策略，导致所有只要是user_name = current_user 都可以插入，多个策略之间使用的是OR进行处理的： TEST=> insert into test(id, user_name) values(100, 'U1'); INSERT 0 1 TEST=> select * from test; ID | USER_NAME | CRT_TIME -----+-----------+---------------------------- 1 | U1 | 2020-02-04 01:18:32.744308 4 | U1 | 2020-02-04 01:32:21.707241 8 | U1 | 2020-02-04 01:33:03.484166 22 | U1 | 2020-02-04 01:40:46.900007 100 | U1 | 2020-02-04 01:40:54.127757 (5 rows) TEST=> delete from test where id > 10; DELETE 2 删除all的权限plcy2，plcy3就生效了： TEST=> c TEST SYSTEM Password for user SYSTEM: You are now connected to database "TEST" as user "SYSTEM". TEST=# drop policy plcy2 on test; DROP POLICY TEST=# c TEST U1 Password for user U1: You are now connected to database "TEST" as user "U1". TEST=> insert into test(id, user_name) values(22, 'U1'); INSERT 0 1 TEST=> insert into test(id, user_name) values(100, 'U1'); ERROR: new row violates row-level security policy for table "TEST" TEST=>
实验update策略：
TEST=# create policy plcy4 on test for update using (user_name = current_user) with check (user_name = current_user and id = 1); CREATE POLICY TEST=# c TEST SYSTEM You are now connected to database "TEST" as user "SYSTEM". TEST=# c TEST U1 Password for user U1: You are now connected to database "TEST" as user "U1". TEST=> select * from test; ID | USER_NAME | CRT_TIME ----+-----------+---------------------------- 1 | U1 | 2020-02-04 01:18:32.744308 4 | U1 | 2020-02-04 01:32:21.707241 8 | U1 | 2020-02-04 01:33:03.484166 22 | U1 | 2020-02-04 01:44:40.663172 (4 rows) TEST=> update test set crt_time = now() where id = 4; ERROR: new row violates row-level security policy for table "TEST" TEST=> update test set crt_time = now() where id = 1; UPDATE 1 TEST=> update test set crt_time = now() where id = 2; UPDATE 0 TEST=>
因此，常用的使用方法应该是：
create policy plcy2 on test for all using (user_name = current_user);
这样所有的用户只能看到自己的数据，而超级用户和表的所有者可以查看所有的数据：
TEST=# select * from test; ID | USER_NAME | CRT_TIME ----+-----------+---------------------------- 2 | U2 | 2020-02-04 01:18:35.119190 3 | U3 | 2020-02-04 01:18:37.341201 5 | U2 | 2020-02-04 01:18:49.056104 6 | U3 | 2020-02-04 01:18:51.435371 4 | U1 | 2020-02-04 01:32:21.707241 8 | U1 | 2020-02-04 01:33:03.484166 22 | U1 | 2020-02-04 01:44:40.663172 1 | U1 | 2020-02-04 01:48:59.705486 (8 rows) TEST=# c TEST U1 Password for user U1: You are now connected to database "TEST" as user "U1". TEST=> select * from test; ID | USER_NAME | CRT_TIME ----+-----------+---------------------------- 4 | U1 | 2020-02-04 01:32:21.707241 8 | U1 | 2020-02-04 01:33:03.484166 22 | U1 | 2020-02-04 01:44:40.663172 1 | U1 | 2020-02-04 01:48:59.705486 (4 rows)
查看表的行级策略：
TEST=> d test
Table "PUBLIC.TEST"
Column | Type | Modifiers
-----------+-----------------------------+---------------------------------------------------
ID | INTEGER | not null default NEXTVAL('TEST_ID_SEQ'::REGCLASS)
USER_NAME | CHARACTER VARYING(32 byte) |
CRT_TIME | TIMESTAMP WITHOUT TIME ZONE | default NOW()
Indexes:
"TEST_PKEY" PRIMARY KEY, BTREE (ID)
Policies:
POLICY "PLCY1" FOR SELECT
USING (((USER_NAME)::NAME = "CURRENT_USER"()))
POLICY "PLCY3" FOR INSERT
WITH CHECK ((((USER_NAME)::NAME = "CURRENT_USER"()) AND (ID <> 100)))
POLICY "PLCY4" FOR UPDATE
USING (((USER_NAME)::NAME = "CURRENT_USER"()))
WITH CHECK ((((USER_NAME)::NAME = "CURRENT_USER"()) AND (ID = 1)))
TEST=> select polname, polrelid, relname from pg_policy, pg_class c where polrelid = c.oid;
POLNAME | POLRELID | RELNAME
-------------------+----------+-----------------------
DENY_MODIFICATION | 14749 | PATHMAN_CONFIG
DENY_MODIFICATION | 14759 | PATHMAN_CONFIG_PARAMS
ALLOW_SELECT | 14749 | PATHMAN_CONFIG
ALLOW_SELECT | 14759 | PATHMAN_CONFIG_PARAMS
PLCY1 | 25146 | TEST
PLCY3 | 25146 | TEST
PLCY4 | 25146 | TEST
(7 rows)