PostgreSQL function's SECURITY DEFINER | INVOKER, SET configuration_parameter { TO value | = value |

Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:

• Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
• Postgres-XL的项目发起人Mason Sharp
• pgpool的作者石井达夫(Tatsuo Ishii)
• PG-Strom的作者海外浩平(Kaigai Kohei)
• Greenplum研发总监姚延栋
• 周正中(德哥), PostgreSQL中国用户会创始人之一
• 汪洋，平安科技数据库技术部经理
• ……

 

2015年度PG大象会报名地址：http://postgres2015.eventdove.com/

PostgreSQL中国社区： http://postgres.cn/

PostgreSQL专业1群： 3336901（已满）

PostgreSQL专业2群： 100910388

PostgreSQL专业3群： 150657323

PostgreSQL function's SECURITY DEFINER | INVOKER, SET configuration_parameter { TO value | = value | FROM CURRENT }  

PostgreSQL 函数可以设置被调用时的角色，以及参数。

详细的语法如下：

CREATE [ OR REPLACE ] FUNCTION    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )    [ RETURNS rettype      | RETURNS TABLE ( column_name column_type [, ...] ) ]  { LANGUAGE lang_name    | TRANSFORM { FOR TYPE type_name } [, ... ]    | WINDOW    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER    | COST execution_cost    | ROWS result_rows    | SET configuration_parameter { TO value | = value | FROM CURRENT }    | AS 'definition'    | AS 'obj_file', 'link_symbol'  } ...    [ WITH ( attribute [, ...] ) ]

当函数被调用时，可以选择以创建函数的角色执行函数，或者以调用者的角色执行函数(默认)。

同时，我们还可以设置函数被调用时的参数。

我们可以跟踪一下，跟踪角色需要用到session_user和current_user，这两者的差别可参考如下代码：

src/backend/utils/init/miscinit.c

session_user是指登陆数据库时的角色或者被SET SESSION AUTHORIZATION设置的角色。

current_user是指set role设置的角色，或者继承自session user，或者是函数调用时定义的角色。

举个例子，先搞明白这两个用户的含义：

create role digoal login;
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select session_user,current_user;
 session_user | current_user 
--------------+--------------
 postgres     | postgres
(1 row)
postgres=# set role digoal;
SET
postgres=> select session_user,current_user;
 session_user | current_user 
--------------+--------------
 postgres     | digoal
(1 row)

创建测试函数：

postgres=# create or replace function f1() returns void as $$
declare 
  x text;
begin
  show search_path into x; 
  raise notice 'search_path: % | session_role: % | current_role: %', x, session_user, current_user;
end;
$$ language plpgsql security definer set search_path to 'public';

这里的security definer表示调用函数时，使用函数owner的权限进行调用。

set search_path to 'public'，表示在调用函数时，使用这个值作为search_path。

postgres=# grant usage on schema postgres to digoal;
GRANT

使用digoal用户连接到postgres数据库，并调用postgres.f1()函数：

postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> select postgres.f1();
NOTICE:  search_path: public | session_role: digoal | current_role: postgres
 f1 
----
 
(1 row)

从NOTICE可以看到我们对函数的设置起作用了。search_path是我们设置的public, 而不是默认的 "$user",public。

current_role则是函数的definer postgres。

postgres=> select session_user,current_user;
 session_user | current_user 
--------------+--------------
 digoal       | digoal
(1 row)
postgres=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

因此我们使用security definer时，需特别注意，因为可能造成权限升级，例如本文使用超级用户创建的security definer函数。

我们把这个函数的security改为invoker。再次使用digoal调用f1()，可以看到current_role是digoal了。

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# alter function f1() security invoker;
ALTER FUNCTION
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> select postgres.f1();
NOTICE:  search_path: public | session_role: digoal | current_role: digoal
 f1 
----
 
(1 row)

下面举个例子，说明security definer的不安因素。使用超级用户创建一个函数如下，用于检查用户是否通过密码认证。

postgres=# create table postgres.pwds(username name,pwd text);
CREATE TABLE
postgres=# insert into pwds values ('digoal','hello');
INSERT 0 1
postgres=# CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY invoker;  -- 假设pwds这个表只有超级用户可以访问。所以普通用户调用这个函数时，如果设置为security invoker会有问题。

\c postgres digoal
postgres=> show search_path;
        search_path        
---------------------------
 postgres, "$user", public
(1 row)
postgres=> select postgres.check_password('digoal','hello');
ERROR:  permission denied for relation pwds
CONTEXT:  SQL statement "SELECT  (pwd = $2)                     FROM    pwds
        WHERE   username = $1"
PL/pgSQL function check_password(text,text) line 4 at SQL statement

但是如果设置为security definer，想想有什么安全隐患呢？

postgres=# alter function check_password(text,text) security definer;
ALTER FUNCTION
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> select postgres.check_password('digoal','hello');
 check_password 
----------------
 t
(1 row)
postgres=> select postgres.check_password('digoal','hello1');
 check_password 
----------------
 f
(1 row)

这样看貌似没有隐患，但是因为函数中没有使用schema.table的方式，所以我们可以使用普通用户自己建立一张认证表，并自定义search_path来修改扫描优先级，来通过认证，甚至可以使用临时表的SCHEMA，都不需要修改search_path(因为临时表schema优先级被排在最前)，偷偷就搞定了。

postgres=> create temp table pwds(username text,pwd text);
CREATE TABLE
postgres=> insert into pwds values ('digoal','err');
INSERT 0 1
postgres=> select postgres.check_password('digoal','err');
 check_password 
----------------
 t
(1 row)

为了提高security definer函数的安全性。可以有以下方法。

1. 建议在里面使用的函数或表等一切对象，都使用schema强制指定。

2. 设置search_path, 防止普通用户钻空子。

例如：

postgres=# CREATE or replace FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    postgres.pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY definer;
CREATE FUNCTION

现在钻不了空子了：

postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create temp table pwds(username text,pwd text);
CREATE TABLE
postgres=> insert into pwds values ('digoal','err');
INSERT 0 1
postgres=> select postgres.check_password('digoal','err');
 check_password 
----------------
 f
(1 row)

或者在调用函数时使用设置的search_path，将普通用户能创建表的schema都去除。

postgres=# CREATE or replace FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds         --  不使用schema
        WHERE   username = $1;  

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY definer set search_path to "$user",public,pg_temp;  -- 将临时表schema放到最后
CREATE FUNCTION

现在也安全了：

postgres=# \c postgres digoal
postgres=> create temp table pwds(username text,pwd text);
CREATE TABLE
postgres=> insert into pwds values ('digoal','err');
INSERT 0 1
postgres=> set search_path=pg_temp,postgres,"$user",public;
SET
postgres=> select * from pwds ;
 username | pwd 
----------+-----
 digoal   | err
(1 row)
因为函数中设置了search_path to "$user",public,pg_temp; 所以还是会用postgres.pwds这个表的数据。
postgres=> select postgres.check_password('digoal','err');
 check_password 
----------------
 f
(1 row)

不过这里还是推荐在函数中使用schema，防止这类问题。

[参考]

1. http://www.postgresql.org/docs/9.5/static/sql-createfunction.html

2. src/backend/utils/init/miscinit.c

/* ----------------------------------------------------------------
 *      User ID state
 *
 * We have to track several different values associated with the concept
 * of "user ID".
 *
 * AuthenticatedUserId is determined at connection start and never changes.
 *
 * SessionUserId is initially the same as AuthenticatedUserId, but can be
 * changed by SET SESSION AUTHORIZATION (if AuthenticatedUserIsSuperuser).
 * This is the ID reported by the SESSION_USER SQL function.
 *
 * OuterUserId is the current user ID in effect at the "outer level" (outside
 * any transaction or function).  This is initially the same as SessionUserId,
 * but can be changed by SET ROLE to any role that SessionUserId is a
 * member of.  (XXX rename to something like CurrentRoleId?)
 *
 * CurrentUserId is the current effective user ID; this is the one to use
 * for all normal permissions-checking purposes.  At outer level this will
 * be the same as OuterUserId, but it changes during calls to SECURITY
 * DEFINER functions, as well as locally in some specialized commands.
 *
 * SecurityRestrictionContext holds flags indicating reason(s) for changing
 * CurrentUserId.  In some cases we need to lock down operations that are
 * not directly controlled by privilege settings, and this provides a
 * convenient way to do it.
 * ----------------------------------------------------------------
 */