前段时间写过一篇关于使用视图来提供行级别的数据保护, 当创建视图时如果未使用security_barriers, 那么这个视图是不安全的, 攻击者可以利用低成本函数打印出隐藏的基表数据. 使用security_barriers可以规避这个问题, 但是牺牲了SQL优化器的作用, 查询将会变成seq scan, 全表扫描.
感兴趣的朋友可以参见如下BLOG :
本文讲述的是将要在9.4发布的行级别安全补丁RLS. 在数据保护方面和视图效果一样, 同时不会有security_barriers带来的弊端.
这个补丁尚未提交, 所以安装时需要注意.
首先下载一个PostgreSQL devel版本. 补丁在处理nodeFuncs.c时目前有点小问题, 使用以下snapshot可以正常打补丁.
下载补丁文件 :
打补丁
安装
初始化数据库
语法
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 }
测试 :
创建测试表
设置行安全策略
目前只支持all commands.
超级用户不受限制.
普通用户受到安全限制. id=1000的不会查出来.
从执行计划可以看到已经自动增加了安全限制条件id<=999
使用RLS不会像视图的security_barriers那样无法使用优化器. 所以索引是有效的.
attack测试 :
[参考]
2.
http://www.pgcon.org/2013/schedule/attachments/273_PGcon2013-kaigai-row-level-security.pdf
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的数据在这里是看不到的.
