PostgreSQL 9.4 patch : Row-Level Security

背景

前段时间写过一篇关于使用视图来提供行级别的数据保护, 当创建视图时如果未使用security_barriers, 那么这个视图是不安全的, 攻击者可以利用低成本函数打印出隐藏的基表数据. 使用security_barriers可以规避这个问题, 但是牺牲了SQL优化器的作用, 查询将会变成seq scan, 全表扫描.

感兴趣的朋友可以参见如下BLOG :

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

本文讲述的是将要在9.4发布的行级别安全补丁RLS. 在数据保护方面和视图效果一样, 同时不会有security_barriers带来的弊端.

这个补丁尚未提交, 所以安装时需要注意.

正文

首先下载一个PostgreSQL devel版本. 补丁在处理nodeFuncs.c时目前有点小问题, 使用以下snapshot可以正常打补丁.

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=10a509d82956dee14eb2011bd266cd3c728ae188

下载补丁文件 :

wget http://www.postgresql.org/message-id/attachment/29700/pgsql-v9.4-row-level-security.v3b.patch

打补丁

tar -zxvf postgresql-10a509d.tar.gz  
cd postgresql-10a509d  
patch -p1 < ./pgsql-v9.4-row-level-security.v3b.patch  

安装

./configure --prefix=/home/pg94/pgsql9.4devel --with-pgport=1921 --with-perl --with-tcl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 && gmake && gmake install  

初始化数据库

initdb -E UTF8 -D $PGDATA --locale=C -W -U postgres  
pg_ctl start  

语法

ALTER TABLE   
    SET ROW SECURITY FOR rowsec_command TO (condition)  
    RESET ROW SECURITY FOR rowsec_command  
and rowsec_command is:  
    { ALL | SELECT | INSERT | UPDATE | DELETE }  

测试 :

创建测试表

digoal=# create table test (id int, info text);  
CREATE TABLE  
digoal=# insert into test select generate_series(1,1000), md5(random()::text);  
INSERT 0 1000  

设置行安全策略

digoal=# alter table test SET ROW SECURITY FOR select TO (id<=999);  
ERROR:  0A000: Row-security for "select" is not implemented yet  
LOCATION:  ATExecSetRowSecurity, pg_rowsecurity.c:305  
digoal=# alter table test SET ROW SECURITY FOR insert TO (id<=999);  
ERROR:  0A000: Row-security for "insert" is not implemented yet  
LOCATION:  ATExecSetRowSecurity, pg_rowsecurity.c:305  
digoal=# alter table test SET ROW SECURITY FOR update TO (id<=999);  
ERROR:  0A000: Row-security for "update" is not implemented yet  
LOCATION:  ATExecSetRowSecurity, pg_rowsecurity.c:305  
digoal=# alter table test SET ROW SECURITY FOR delete TO (id<=999);  
ERROR:  0A000: Row-security for "delete" is not implemented yet  
LOCATION:  ATExecSetRowSecurity, pg_rowsecurity.c:305  

目前只支持all commands.

digoal=# alter table test SET ROW SECURITY FOR all TO (id<=999);  
ALTER TABLE  

超级用户不受限制.

digoal=# select * from test  where id>=998;  
  id  |               info                 
------+----------------------------------  
  998 | 7177340c488270f432b1476d001f3b9d  
  999 | a609aef006b1147dad10f3e43993dfea  
 1000 | c7fa1acdd43d442be5a940c9f7091abc  
(3 rows)  
digoal=# create role digoal nosuperuser nocreatedb login encrypted password 'digoal';  
CREATE ROLE  
digoal=# grant select on test to digoal;  
GRANT  

普通用户受到安全限制. id=1000的不会查出来.

digoal=# \c digoal digoal  
You are now connected to database "digoal" as user "digoal".  
digoal=> select * from test  where id>=998;  
 id  |               info                 
-----+----------------------------------  
 998 | 7177340c488270f432b1476d001f3b9d  
 999 | a609aef006b1147dad10f3e43993dfea  
(2 rows)  

从执行计划可以看到已经自动增加了安全限制条件id<=999

digoal=> explain analyze select * from test  where id>=998;  
                                           QUERY PLAN                                             
------------------------------------------------------------------------------------------------  
 Seq Scan on test  (cost=0.00..24.00 rows=1 width=37) (actual time=0.271..0.271 rows=2 loops=1)  
   Filter: ((id <= 999) AND (id >= 998))  
   Rows Removed by Filter: 998  
 Total runtime: 0.308 ms  
(4 rows)  

使用RLS不会像视图的security_barriers那样无法使用优化器. 所以索引是有效的.

digoal=> \c digoal postgres  
You are now connected to database "digoal" as user "postgres".  
digoal=# create index idx_test_1 on test(id);  
CREATE INDEX  
digoal=# \c digoal digoal  
You are now connected to database "digoal" as user "digoal".  
digoal=> explain analyze select * from test  where id>=998;  
                                                    QUERY PLAN                                                      
------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_test_1 on test  (cost=0.28..2.29 rows=1 width=37) (actual time=0.007..0.008 rows=2 loops=1)  
   Index Cond: ((id <= 999) AND (id >= 998))  
 Total runtime: 0.065 ms  
(3 rows)  

attack测试 :

digoal=# \c digoal digoal  
You are now connected to database "digoal" as user "digoal".  
digoal=> create or replace function attack(test) returns boolean as $$  
digoal$> declare  
digoal$> begin  
digoal$>   raise notice '%', $1;  
digoal$>   return true;  
digoal$>   
digoal$> end;  
digoal$> $$ language plpgsql strict cost 0.000000000000001;  
CREATE FUNCTION  
digoal=> select * from test where id>997 and attack(test);  
NOTICE:  (998,7177340c488270f432b1476d001f3b9d)  
NOTICE:  (999,a609aef006b1147dad10f3e43993dfea)  
 id  |               info                 
-----+----------------------------------  
 998 | 7177340c488270f432b1476d001f3b9d  
 999 | a609aef006b1147dad10f3e43993dfea  
(2 rows)  
digoal=> explain analyze verbose select * from test where id>997 and attack(test);  
NOTICE:  (998,7177340c488270f432b1476d001f3b9d)  
NOTICE:  (999,a609aef006b1147dad10f3e43993dfea)  
                                                              QUERY PLAN                                                               
------------------------------------------------------------------------------------------------------------------------------------  
 Subquery Scan on test  (cost=0.28..2.33 rows=1 width=37) (actual time=0.113..0.138 rows=2 loops=1)  
   Output: test.id, test.info  
   Filter: attack(test.test)  
   ->  Index Scan using idx_test_1 on public.test test_1  (cost=0.28..2.31 rows=2 width=98) (actual time=0.014..0.018 rows=2 loops=1)  
         Output: test_1.id, test_1.info, test_1.*  
         Index Cond: ((test_1.id <= 999) AND (test_1.id > 997))  
 Total runtime: 0.343 ms  
(7 rows)  
digoal=> \c digoal postgres  
You are now connected to database "digoal" as user "postgres".  
digoal=# drop index idx_test_1;  
DROP INDEX  
digoal=# \c digoal digoal  
You are now connected to database "digoal" as user "digoal".  
digoal=> select * from test where id>997 and attack(test);  
NOTICE:  (998,7177340c488270f432b1476d001f3b9d)  
NOTICE:  (999,a609aef006b1147dad10f3e43993dfea)  
 id  |               info                 
-----+----------------------------------  
 998 | 7177340c488270f432b1476d001f3b9d  
 999 | a609aef006b1147dad10f3e43993dfea  
(2 rows)  
digoal=> explain analyze verbose select * from test where id>997 and attack(test);  
NOTICE:  (998,7177340c488270f432b1476d001f3b9d)  
NOTICE:  (999,a609aef006b1147dad10f3e43993dfea)  
                                                     QUERY PLAN                                                       
--------------------------------------------------------------------------------------------------------------------  
 Subquery Scan on test  (cost=0.00..24.02 rows=1 width=37) (actual time=0.381..0.403 rows=2 loops=1)  
   Output: test.id, test.info  
   Filter: attack(test.test)  
   ->  Seq Scan on public.test test_1  (cost=0.00..24.00 rows=2 width=98) (actual time=0.289..0.292 rows=2 loops=1)  
         Output: test_1.id, test_1.info, test_1.*  
         Filter: ((test_1.id <= 999) AND (test_1.id > 997))  
         Rows Removed by Filter: 998  
 Total runtime: 0.439 ms  
(8 rows)  

从执行计划可以看出设置RLS后, RLS的条件作为子查询, attack(test)在子查询外面. 所以不可能从attack中窥探子查询外的数据, 因此id=1000的数据在这里是看不到的.

参考

1. http://www.postgresql.org/message-id/flat/CADyhKSWGtZqpsXtF7_q2FvKRvX6RqW+xv7VmxNmj4gubSBoo-g@mail.gmail.com

2. http://www.pgcon.org/2013/schedule/attachments/273_PGcon2013-kaigai-row-level-security.pdf

3. https://github.com/kaigai/sepgsql/tree/rowsec

4. http://wiki.postgresql.org/wiki/RLS

5. http://blog.163.com/digoal@126/blog/static/163877040201361031431669/

digoal’s 大量PostgreSQL文章入口