PostgreSQL 9.4 patch : Row-Level Security

最新推荐文章于 2025-08-22 20:35:20 发布

weixin_34199405

最新推荐文章于 2025-08-22 20:35:20 发布

阅读量202

点赞数

CC 4.0 BY-SA版权

文章标签： runtime 数据库 git

原文链接：https://yq.aliyun.com/articles/14729

介绍 PostgreSQL 9.4 中即将发布的行级安全（RLS）特性，该特性允许对不同用户显示不同的行数据，同时保持查询优化，适用于需要精细访问控制的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前段时间写过一篇关于使用视图来提供行级别的数据保护, 当创建视图时如果未使用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/