PostgreSQL行级策略实验
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了PostgreSQL行级策略实验，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7849字，纯文字阅读大概需要12分钟。
内容图文
行级策略实验
创建测试表并插入数据：
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)
PostgreSQL行级策略实验
标签：now() script HERE out ESS zone false 创建用户 params
本文系统来源：https://www.cnblogs.com/kuang17/p/12260332.html
内容总结
以上是互联网集市为您收集整理的PostgreSQL行级策略实验全部内容，希望文章能够帮你解决PostgreSQL行级策略实验所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】