投稿
  1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL9.5：Row-Level Security Policies

• 数据库 • 阅读 49

“Row-Level Security (RLS) support” 是 9.5 版本的主要特性之一，提供了基于行的安全策略，限制数据库用户的查看表数据权限， 先来看以下例子。

创建测试表

创建测试表，插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
fdb=> create table test_row(id serial primary key, username text, log_event text, create_time timestamp(0) without time zone default clock_timestamp());  
CREATE TABLE  
  
fdb=> insert into test_row(username,log_event) values('user1','user1:aaa');  
INSERT 0  1  
fdb=> insert into test_row(username,log_event) values('user1','user1:aadsfdfa');  
INSERT 0  1  
fdb=> insert into test_row(username,log_event) values('user2','user2:aadsfdfa');  
INSERT 0  1  
fdb=> insert into test_row(username,log_event) values('user2','user2:test');  
INSERT 0  1  
fdb=> insert into test_row(username,log_event) values('user3','user3:test3');  
INSERT 0  1  
fdb=> insert into test_row(username,log_event) values('user3','user3:test3333');  
INSERT 0  1  
fdb=> insert into test_row(username,log_event) values('user4','user4:test3333');  
INSERT 0  1

创建测试用户

创建 user1,user2,user3 测试用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
[pg95@db1 ~]$ psql fdb  
psql (9.5alpha1)  
Type  "help"  for help.  
  
fdb=# create role user1 with login;  
CREATE ROLE  
fdb=# create role user2 with login;  
CREATE ROLE  
fdb=# create role user3 with login;  
CREATE ROLE  
  
fdb=> grant select on test_row to user1,user2,user3;  
GRANT  
  
fdb=> grant usage on schema fdb to user1,user2,user3;  
GRANT

以 User1 登陆可以查询全部数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
fdb=> /c fdb user1  
You are now connected to database "fdb"  as user "user1".  
  
fdb=>  select  *  from fdb.test_row;  
id | username |log_event | create_time   
----+----------+----------------+---------------------  
 1  | user1 | user1:aaa |  2015-07-30  14:48:49  
 2  | user1 | user1:aadsfdfa |  2015-07-30  14:48:54  
 3  | user2 | user2:aadsfdfa |  2015-07-30  14:48:59  
 4  | user2 | user2:test |  2015-07-30  14:49:06  
 5  | user3 | user3:test3 |  2015-07-30  14:49:15  
 6  | user3 | user3:test3333 |  2015-07-30  14:49:24  
 7  | user4 | user4:test3333 |  2015-07-30  14:49:29  
(7 rows)

备注：之前版本只要给数据库用户赋予 SELECT 权限，那么用户可以查看全表数据。

给表添加 Policy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
[pg95@db1 ~]$ psql fdb fdb  
psql (9.5alpha1)  
Type  "help"  for help.  
  
fdb=> CREATE POLICY policy_test_row ON test_row  
fdb-> FOR SELECT  
fdb-> TO PUBLIC  
fdb-> USING (username = current_user);  
CREATE POLICY  
  
fdb=>  select relname,relrowsecurity from pg_class where relname='test_row';  
relname | relrowsecurity   
----------+----------------  
test_row | f  
(1 row)  
  
fdb=> ALTER TABLE test_row ENABLE ROW LEVEL SECURITY;  
ALTER TABLE  
  
fdb=>  select relname,relrowsecurity from pg_class where relname='test_row';  
relname | relrowsecurity   
----------+----------------  
test_row | t  
(1 row)

备注：给表 test_row 添加 policy ，限制数据库登陆用户仅允许查看当前用户的日志记录。

验证用户的数据权限

user1 用户登陆

1
2
3
4
5
6
7
8
9
 
fdb=> /c fdb user1  
You are now connected to database "fdb"  as user "user1".  
  
fdb=>  select  *  from fdb.test_row;  
id | username |log_event | create_time   
----+----------+----------------+---------------------  
 1  | user1 | user1:aaa |  2015-07-30  14:48:49  
 2  | user1 | user1:aadsfdfa |  2015-07-30  14:48:54  
(2 rows)

user2 用户登陆

1
2
3
4
5
6
7
8
9
 
fdb=> /c fdb user2  
You are now connected to database "fdb"  as user "user2".  
  
fdb=>  select  *  from fdb.test_row;  
id | username |log_event | create_time   
----+----------+----------------+---------------------  
 3  | user2 | user2:aadsfdfa |  2015-07-30  14:48:59  
 4  | user2 | user2:test |  2015-07-30  14:49:06  
(2 rows)

备注：user1 用户仅能查看 username 值为 ‘user1’ 的记录，user2 用户仅能查看 username 值为 ‘user2’ 的记录。

参考

原创文章，作者：306829225，如若转载，请注明出处：https://blog.ytso.com/tech/database/239627.html

赞 (0)
0 0
上一篇 2022年2月12日 13:54
下一篇 2022年2月12日 13:54

相关推荐

发表回复

登录后才能评论