PostgreSQL 行安全策略 - PostgreSQL 9.5 new feature - can define row security policy for table

背景

PostgreSQL 9.5 新增了一个非常给力的安全相关特性, 精细化控制用户对数据的可视性, 可写性.

这种方法有利于隔离控制共享表在多个用户之间的数据呈现和使用.

实现方法,

创建针对表和角色的策略, 不同的角色对表记录的查询, 插入, 更新, 删除 可以有不同的控制方法.

CREATE POLICY name ON table_name  
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]  
    [ TO { role_name | PUBLIC } [, ...] ]  
    [ USING ( using_expression ) ]  
    [ WITH CHECK ( check_expression ) ]  

using 针对已经存在的记录的校验. 可实施在select, update, delete, ALL上.

whth check 针对将要新增的记录的校验, 可实施在insert, update, ALL上.

需要注意的是, UPDATE因为涉及旧的记录和新的记录, 如果只写了using , 但是没有提供with check的话, using同时会当成with check来使用进行检查.

如果针对同样的命令创建了多个策略, 所有策略中任意一个为TRUE都通过.

例如ALL, SELECT个创建了一个策略for role r1, 执行select时任意一个为TRUE都通过.

例如SELECT个创建了多个策略for role r1, 执行select时任意一个为TRUE都通过.

测试 :　

去git下载一个开发版本

http://git.postgresql.org/gitweb/?p=postgresql.git;a=summary  
  
[root@db-172-16-3-150 soft_bak]# tar -zxvf postgresql-7320681.tar.gz  
[root@db-172-16-3-150 soft_bak]# cd postgresql-7320681  
[root@db-172-16-3-150 postgresql-7320681]# ./configure --prefix=/opt/pgsql9.5 --with-pgport=1922 --with-perl --with-python --with-tcl --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety --with-blocksize=32 --enable-debug  
[root@db-172-16-3-150 postgresql-7320681]# gmake world && gmake install-world  
  
[root@db-172-16-3-150 postgresql-7320681]# useradd pg95  
[root@db-172-16-3-150 ~]# su - pg95  
[pg95@db-172-16-3-150 ~]$ vi .bash_profile   
# add by digoal  
export PS1="$USER@`/bin/hostname -s`-> "  
export PGPORT=1922  
export PGDATA=/data02/pgdata95/pg_root  
export LANG=en_US.utf8  
export PGHOME=/opt/pgsql9.5  
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH  
export DATE=`date +"%Y%m%d%H%M"`  
export PATH=$PGHOME/bin:$PATH:.  
export MANPATH=$PGHOME/share/man:$MANPATH  
export PGHOST=$PGDATA  
export PGDATABASE=postgres  
alias rm='rm -i'  
alias ll='ls -lh'  
unalias vi  
  
[root@db-172-16-3-150 ~]# mkdir /data02/pgdata95  
[root@db-172-16-3-150 ~]# chown pg95:pg95 /data02/pgdata95  
  
[root@db-172-16-3-150 ~]# su - pg95  
pg95@db-172-16-3-150-> initdb -D $PGDATA -E UTF8 --locale=C -U postgres -W  

创建三个角色

postgres=# create role r1 login;  
CREATE ROLE  
postgres=# create role r2 login;  
CREATE ROLE  
postgres=# create role r3 login;  
CREATE ROLE  

创建测试表

postgres=# create table test(id int, r name);  
CREATE TABLE  
postgres=# insert into test values(1, 'r1');  
INSERT 0 1  
postgres=# insert into test values(2, 'r2');  
INSERT 0 1  
postgres=# insert into test values(3, 'r3');  
INSERT 0 1  
postgres=# grant all on table test to public;  
GRANT  

创建一个新增数据的策略(使用with check)

postgres=# create policy p on test for insert to r1 with check( r = current_user);  
CREATE POLICY  

默认情况下策略是disable状态的,

postgres=# \d+ test  
                         Table "public.test"  
 Column |  Type   | Modifiers | Storage | Stats target | Description   
--------+---------+-----------+---------+--------------+-------------  
 id     | integer |           | plain   |              |   
 r      | name    |           | plain   |              |   
Policies (Row Security Disabled):  
    POLICY "p" FOR INSERT  
      TO r1  
      WITH CHECK (r = "current_user"())  

通过pg_policies视图可以查看已经创建的策略.

postgres=# select * from pg_policies ;  
 schemaname | tablename | policyname | roles |  cmd   | qual |       with_check         
------------+-----------+------------+-------+--------+------+------------------------  
 public     | test      | p          | {r1}  | INSERT |      | (r = "current_user"())  
(1 row)  

在策略enable前, 是无视策略的.

postgres=> insert into test values(4,'r1');  
INSERT 0 1  
postgres=> insert into test values(4,'r2');  
INSERT 0 1  

使策略生效

postgres=# alter table test enable row level security;  
ALTER TABLE  
postgres=> \d+ test  
                         Table "public.test"  
 Column |  Type   | Modifiers | Storage | Stats target | Description   
--------+---------+-----------+---------+--------------+-------------  
 id     | integer |           | plain   |              |   
 r      | name    |           | plain   |              |   
Policies:  
    POLICY "p" FOR INSERT  
      TO r1  
      WITH CHECK (r = "current_user"())  

现在策略生效了, 再次插入, 你会看到只能插入和r1角色同名的r值.

postgres=# \c postgres r1  
You are now connected to database "postgres" as user "r1".  
postgres=> insert into test values(4,'r2');  
ERROR:  new row violates WITH CHECK OPTION for "test"  
postgres=> insert into test values(4,'r1');  
INSERT 0 1  

再新增一个策略, 现在r1角色插入test表时, 允许r字段的值为’r1’,’r2’.

postgres=# create policy p1 on test for insert to r1 with check( r = 'r2');  
CREATE POLICY  
postgres=# \c postgres r1  
You are now connected to database "postgres" as user "r1".  
postgres=> insert into test values(4,'r2');  
INSERT 0 1  
postgres=> insert into test values(4,'r1');  
INSERT 0 1  
postgres=> insert into test values(4,'r3');  
ERROR:  new row violates WITH CHECK OPTION for "test"  

接下来创建旧值的策略. r1用户只能查看到r=current_user的值.

postgres=> \c postgres postgres  
You are now connected to database "postgres" as user "postgres".\  
postgres=# create policy p2 on test for select to r1 using ( r = current_user);  
CREATE POLICY  
postgres=# \c postgres r1  
You are now connected to database "postgres" as user "r1".  
postgres=> select * from test;  
 id | r    
----+----  
  1 | r1  
  4 | r1  
  4 | r1  
  4 | r1  
(4 rows)  

当然, 我们也可以创建一个针对所有用户的策略, 例如, 所有用户只能看到 r = current_user 的值.

postgres=> \c postgres postgres  
You are now connected to database "postgres" as user "postgres".  
postgres=# create policy p3 on test for select to public using ( r = current_user);  
CREATE POLICY  
postgres=# \c postgres r2  
You are now connected to database "postgres" as user "r2".  
postgres=> select * from test;  
 id | r    
----+----  
  2 | r2  
  4 | r2  
  4 | r2  
(3 rows)  

当然了, 所有这些策略只针对非超级用户以及非owner

postgres=> \c postgres postgres  
You are now connected to database "postgres" as user "postgres".  
postgres=# select * from test;  
 id | r    
----+----  
  1 | r1  
  2 | r2  
  3 | r3  
  4 | r1  
  4 | r2  
  4 | r1  
  4 | r2  
  4 | r1  
(8 rows)  

把r1改为超级用户, 策略失效.

postgres=# alter role r1 superuser;  
ALTER ROLE  
postgres=# \c postgres r1  
You are now connected to database "postgres" as user "r1".  
postgres=# select * from test;  
 id | r    
----+----  
  1 | r1  
  2 | r2  
  3 | r3  
  4 | r1  
  4 | r2  
  4 | r1  
  4 | r2  
  4 | r1  
(8 rows)  

对于update操作, 因为先需要查看数据, 然后才是插入数据, 所以先会执行using检查, 然后执行with check检查. 如果只有using, 那么with check还是需要检查的, 只不过会使用using策略.

如果只有with check则在查询数据时不检查, 但是插入时检查.

另外需要说明一旦对用户创建了策略, 必须在所有命令上创建, 否则默认采用拒绝方式.

src/backend/rewrite/rowsecurity.c  
if row-level security is enabled on the table and there  
is no policy which applies, then a default-deny policy will be used.  

例如, 现在有1个update的策略.

postgres=# \d test  
     Table "public.test"  
 Column |  Type   | Modifiers   
--------+---------+-----------  
 id     | integer |   
 r      | name    |   
Policies:  
    POLICY "p4" FOR UPDATE  
      TO r3  
      USING (r = "current_user"())  
postgres=# \c postgres r3  
You are now connected to database "postgres" as user "r3".  

因为针对r3角色创建了update策略, 但是没有创建其他命令的策略, 所以其他命令的策略默认为FALSE?

postgres=> select * from test;  
 id | r   
----+---  
(0 rows)  

更新操作应用了策略.

postgres=> update test set id=4 where r='r3';  
UPDATE 1  
postgres=> select * from test;  
 id | r   
----+---  
(0 rows)  

现在创建SELECT的策略, 可以查询了

postgres=# create policy p1 on test for select to r3 using ( r = current_user);  
CREATE POLICY  
postgres=# \d+ test  
                         Table "public.test"  
 Column |  Type   | Modifiers | Storage | Stats target | Description   
--------+---------+-----------+---------+--------------+-------------  
 id     | integer |           | plain   |              |   
 r      | name    |           | plain   |              |   
Policies:  
    POLICY "p1" FOR SELECT  
      TO r3  
      USING (r = "current_user"())  
    POLICY "p4" FOR UPDATE  
      TO r3  
      USING (r = "current_user"())  
postgres=# \c postgres r3  
You are now connected to database "postgres" as user "r3".  
postgres=> select * from test;  
 id | r    
----+----  
  4 | r3  
(1 row)  

但是delete命令上还没有创建策略, 所以删除操作直接FALSE.

postgres=> delete from test ;  
DELETE 0  

在r1角色上, 没有创建任何策略, 所以操作是允许的.

postgres=> \c postgres r1  
You are now connected to database "postgres" as user "r1".  
postgres=# select * from test;  
 id | r    
----+----  
  1 | r1  
  2 | r2  
  4 | r1  
  4 | r2  
  4 | r1  
  4 | r2  
  4 | r1  
  4 | r3  
(8 rows)  

在使用pg_dump导出或者使用pg_restore导入时. 如果表enable了row security策略.

普通用户在执行pg_dump或pg_restore时会报错.

例如 :

pg95@db-172-16-3-150-> pg_dump -U r2 -t test postgres  
.....  
COPY test (id, r) FROM stdin;  
pg_dump: [archiver (db)] query failed: ERROR:  insufficient privilege to bypass row security.  
pg_dump: [archiver (db)] query was: COPY public.test (id, r) TO stdout;  

最后要讲一下使用策略的过滤顺序, 还记得视图攻击吗?

http://blog.163.com/digoal@126/blog/static/163877040201431410032638

同样, leakproof 函数也在策略过滤器前调用.

例子 :

普通函数

postgres=> \c postgres postgres  
You are now connected to database "postgres" as user "postgres".  
postgres=# create or replace function attack(int,name) returns boolean as $$  
postgres$# declare  
postgres$# begin  
postgres$#   raise notice '%,%', $1,$2;  
postgres$#   return true;  
postgres$# end;  
postgres$# $$ language plpgsql cost 0.00000000000000000000001 ;  
CREATE FUNCTION  
postgres=# \c postgres r3  
You are now connected to database "postgres" as user "r3".  
postgres=> select * from test where attack(id,r);  
NOTICE:  4,r3  
 id | r    
----+----  
  4 | r3  
(1 row)  

普通函数在语句中的过滤是在策略过滤器后面执行

postgres=> explain select * from test where attack(id,r);  
                             QUERY PLAN                               
--------------------------------------------------------------------  
 Subquery Scan on test  (cost=0.00..61.32 rows=6 width=68)  
   Filter: attack(test.id, test.r)  
   ->  Seq Scan on test test_1  (cost=0.00..61.15 rows=17 width=68)  
         Filter: (r = "current_user"())  
(4 rows)  

如果修改为leakproof函数的话

postgres=> \c postgres postgres  
You are now connected to database "postgres" as user "postgres".  
postgres=# alter function attack(int,name) LEAKPROOF;  
ALTER FUNCTION  

就直接上升到和过滤器一起执行了

postgres=# \c postgres r3  
postgres=> explain select * from test where attack(id,r);  
                      QUERY PLAN                        
------------------------------------------------------  
 Seq Scan on test  (cost=0.00..61.15 rows=6 width=68)  
   Filter: (attack(id, r) AND (r = "current_user"()))  
(2 rows)  

可以从函数中读取出所有数据

postgres=> select * from test where attack(id,r);  
NOTICE:  1,r1  
NOTICE:  2,r2  
NOTICE:  4,r1  
NOTICE:  4,r2  
NOTICE:  4,r1  
NOTICE:  4,r2  
NOTICE:  4,r1  
NOTICE:  4,r3  
 id | r    
----+----  
  4 | r3  
(1 row)  

参考

1. http://www.postgresql.org/docs/devel/static/sql-createpolicy.html

2. http://www.postgresql.org/docs/devel/static/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-STATEMENT

row_security (enum)

This variable controls if row security policies are to be applied to queries which are run against tables that have row security enabled. The default is 'on'. When set to 'on', all users, except superusers and the owner of the table, will have the row policies for the table applied to their queries. The table owner and superuser can request that row policies be applied to their queries by setting this to 'force'. Lastly, this can also be set to 'off' which will bypass row policies for the table, if possible, and error if not.  
  
For a user who is not a superuser and not the table owner to bypass row policies for the table, they must have the BYPASSRLS role attribute. If this is set to 'off' and the user queries a table which has row policies enabled and the user does not have the right to bypass row policies then a permission denied error will be returned.  
  
The allowed values of row_security are on (apply normally - not to superuser or table owner), off (fail if row security would be applied), and force (apply always - even to superuser and table owner).  
  
For more information on row security policies, see CREATE POLICY.  

digoal’s 大量PostgreSQL文章入口