Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:
- Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
- Postgres-XL的项目发起人Mason Sharp
- pgpool的作者石井达夫(Tatsuo Ishii)
- PG-Strom的作者海外浩平(Kaigai Kohei)
- Greenplum研发总监姚延栋
- 周正中(德哥), PostgreSQL中国用户会创始人之一
- 汪洋，平安科技数据库技术部经理
- ……
|
 |
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 postgresYou 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;SETpostgres=> select session_user,current_user;session_user | current_user--------------+--------------postgres | digoal(1 row)
创建测试函数：
postgres=# create or replace function f1() returns void as $$declarex text;beginshow 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 digoalYou are now connected to database "postgres" as user "digoal".postgres=> select postgres.f1();NOTICE: search_path: public | session_role: digoal | current_role: postgresf1----(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 postgresYou are now connected to database "postgres" as user "postgres".postgres=# alter function f1() security invoker;ALTER FUNCTIONpostgres=# \c postgres digoalYou are now connected to database "postgres" as user "digoal".postgres=> select postgres.f1();NOTICE: search_path: public | session_role: digoal | current_role: digoalf1----(1 row)
下面举个例子，说明security definer的不安因素。使用超级用户创建一个函数如下，用于检查用户是否通过密码认证。
postgres=# create table postgres.pwds(username name,pwd text);CREATE TABLEpostgres=# insert into pwds values ('digoal','hello');INSERT 0 1postgres=# CREATE FUNCTION check_password(uname TEXT, pass TEXT)RETURNS BOOLEAN AS $$DECLARE passed BOOLEAN;BEGINSELECT (pwd = $2) INTO passedFROM pwdsWHERE username = $1;
RETURN passed;END;$$ LANGUAGE plpgsqlSECURITY invoker; -- 假设pwds这个表只有超级用户可以访问。所以普通用户调用这个函数时，如果设置为security invoker会有问题。
\c postgres digoalpostgres=> show search_path;search_path---------------------------postgres, "$user", public(1 row)postgres=> select postgres.check_password('digoal','hello');ERROR: permission denied for relation pwdsCONTEXT: SQL statement "SELECT (pwd = $2) FROM pwdsWHERE 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 FUNCTIONpostgres=# \c postgres digoalYou 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 TABLEpostgres=> insert into pwds values ('digoal','err');INSERT 0 1postgres=> 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;BEGINSELECT (pwd = $2) INTO passedFROM postgres.pwdsWHERE username = $1;
RETURN passed;END;$$ LANGUAGE plpgsqlSECURITY definer;CREATE FUNCTION
现在钻不了空子了：
postgres=# \c postgres digoalYou are now connected to database "postgres" as user "digoal".postgres=> create temp table pwds(username text,pwd text);CREATE TABLEpostgres=> insert into pwds values ('digoal','err');INSERT 0 1postgres=> 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;BEGINSELECT (pwd = $2) INTO passedFROM pwds -- 不使用schemaWHERE username = $1;
RETURN passed;END;$$ LANGUAGE plpgsqlSECURITY definer set search_path to "$user",public,pg_temp; -- 将临时表schema放到最后CREATE FUNCTION
现在也安全了：
postgres=# \c postgres digoalpostgres=> create temp table pwds(username text,pwd text);CREATE TABLEpostgres=> insert into pwds values ('digoal','err');INSERT 0 1postgres=> set search_path=pg_temp,postgres,"$user",public;
SETpostgres=> 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，防止这类问题。
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.* ----------------------------------------------------------------*/
502
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
