PostgreSQL 行级安全性策略应用

最新推荐文章于 2022-06-30 16:22:39 发布

weixin_33924220

最新推荐文章于 2022-06-30 16:22:39 发布

阅读量667

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/tianbing/blog/1553892

版权

2019独角兽企业重金招聘Python工程师标准>>>

1. 概述

行级安全性策略是PG9.5新加的一个功能，它是一种粒度更细的，针对行级所做的权限控制。

它可以对每一个用户限制哪些行可以进行查询操作或增删改的操作。

2. 特性介绍

默认情况下，表是没有行级安全性的，要想使表有行级安全性，需要使用命令：

ALTER TABLE .... ENABLE ROW LEVEL SECURITY;

是表有行级安全性检查。所有对该表的操作（表的属主除外），都要经过行级安全性策略的允许。

但是表级的操作，如truncate不服从行级安全性策略。

下面介绍下怎么创建行级安全性策略。

语法为：

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操作失败

针对行级安全策略，有两个系统表记录信息。

pg_policy 系统表

polname	策略名称
polrelid	策略适用的表
polcmd	策略命令类型：r-select，a-insert，w-update，d-delete，*-所有类型
polroles	策略适用角色
polqual	策略限制的查询条件
polwithcheck	被作为WITH CHECK 条件增加到尝试向表增加行的查询的表达式树

pg_policies系统表

schemaname	策略所在表的模式名
tablename	策略所在的表名
policyname	策略名
roles	策略所适用的角色
cmd	策略所适用的命令类型
qual	策略限制的查询条件
with_check	被作为WITH CHECK 条件增加到尝试向表增加行的查询的表达式树

3. 示例

a ) 建表

postgres=# create table test_policy(id int, usr name,  tm timestamp default clock_timestamp());
CREATE TABLE
postgres=# insert into test_policy(id, usr) values(1, 'user1');
INSERT 0 1
postgres=# insert into test_policy(id, usr) values(2, 'user2');
INSERT 0 1
postgres=# insert into test_policy(id, usr) values(3, 'user2'); 
INSERT 0 1
postgres=# insert into test_policy(id, usr) values(4, 'user2');
INSERT 0 1
postgres=# insert into test_policy(id, usr) values(5, 'user1');
INSERT 0 1
postgres=# insert into test_policy(id, usr) values(5, 'user3');
INSERT 0 1

b) 设置行级安全检查

postgres=# ALTER TABLE test_policy ENABLE ROW LEVEL SECURITY;
ALTER TABLE

c) 创建测试角色

postgres=# create role user1 with login;
CREATE ROLE
postgres=# create role user2 with login;
CREATE ROLE
postgres=# create role user3 with login;
CREATE ROLE
postgres=# grant select on test_policy to user1,user2,user3;
GRANT

d) 创建策略

postgres=# CREATE POLICY pol1 ON test_policy
postgres-# FOR SELECT TO PUBLIC
postgres-# USING (usr = current_user);
CREATE POLICY

e) 以user1登录数据库，测试

postgres=# \c postgres user1
You are now connected to database "postgres" as user "user1".
postgres=> select * from test_policy ;
 id |  usr  |             tm             
----+-------+----------------------------
  1 | user1 | 2017-10-20 14:37:38.065646
  5 | user1 | 2017-10-20 14:37:38.0983
(2 rows)

可以看出以只有usr字段值为user1才对用户user1可见。

f) 多个策略共同作用一张表时

postgres=# CREATE POLICY pol2 ON test_policy
postgres-# FOR SELECT TO PUBLIC
postgres-# USING (id = 5);
CREATE POLICY

再创建一个策略作用同一张表，设置其他的限制查询条件。

再以user1登录，执行查询，

postgres=# \c postgres user1
You are now connected to database "postgres" as user "user1".
postgres=> select * from test_policy ;
 id |  usr  |             tm             
----+-------+----------------------------
  1 | user1 | 2017-10-20 14:37:38.065646
  5 | user1 | 2017-10-20 14:37:38.0983
  5 | user3 | 2017-10-20 14:37:38.645789
(3 rows)

可以看出其查询结果是pol1和pol2两个策略进行OR的结果。

目前9.6之前的版本还不支持多个策略AND的结果，据说PG10已经新增了该功能。

转载于:https://my.oschina.net/tianbing/blog/1553892