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

背景

PostgreSQL的函数支持两种权限检测

invoker, 调用者权限

definer, 定义者权限

比如一个普通用户，定义了一个函数是调用者权限的，当超级用户调用这个函数时，会以超级用户的权限来执行，可以为所欲为。

因此可能被普通用户用来设计陷阱。

正文

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.  
 * ----------------------------------------------------------------  
 */  

digoal’s 大量PostgreSQL文章入口