PostgreSQL行级策略实验

行级策略实验

创建测试表并插入数据：

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)