postgresql - 记录设置为 SECURITY DEFINER 的 Postgres 函数的调用程序

Question

如果函数设置为 SECURITY DEFINER，Postgres(使用 9.4)中的函数是否有办法找出调用它的用户？

我的设计问题是我想通过我的网络应用程序进行用户身份验证(以便我可以共享一个连接池)，但仍然在数据库中维护审计记录，这些记录引用来自网络应用程序的经过身份验证的最终用户。

目前的流程是:

• 用户连接到一个网络应用程序，它会进行身份验证以验证他们的真实身份。
• 然后网络应用通过应用用户连接到 Postgres。
• 当代表用户执行任何查询时，网络应用会运行 SET ROLE [username]，然后是相关查询，然后是 RESET ROLE，然后返回到连接池的连接。
• 所有用户查询都是通过函数完成的。用户有运行函数的权限，但没有直接修改表的权限(web 应用程序帐户除了模拟某些用户的权限外没有其他权限)。这些函数还通过使用 CURRENT_USER 和 CURRENT_TIMESTAMP 插入/更新适当的值来维护“创建者”、“最后更新”等概念的列。

我遇到的问题是 CURRENT_USER 总是返回函数所有者，而 SESSION_USER 总是返回网络应用程序帐户，而我真正想要的是调用该函数的帐户的名称。

这可能吗？我在文档中没有看到任何特别有前途的内容。相反，其他人是如何解决这个设计问题的？

Answer 1

你可以定义一个DOMAIN将 NAME 值限制为 CURRENT_USER:

CREATE DOMAIN whoami AS NAME
CHECK( VALUE = CURRENT_USER )
;

然后将此 DOMAIN 应用于您的函数的参数。该参数可以定义为可选，默认值为 CURRENT_USER(默认值在调用者的上下文中计算):

CREATE FUNCTION restricted_area( caller whoami DEFAULT CURRENT_USER )
RETURNS TABLE ( caller NAME, owner NAME )
SECURITY DEFINER
LANGUAGE SQL
AS $SQL$
  SELECT caller, CURRENT_USER;
$SQL$
;

CREATE ROLE restricted_area_owner;
ALTER FUNCTION restricted_area( whoami )
OWNER TO restricted_area_owner
;

CREATE ROLE web_user;
GRANT EXECUTE ON FUNCTION restricted_area( whoami )
TO web_user
;

SET ROLE web_user;
SELECT *
FROM restricted_area()
;

这给出了预期的结果:

  caller  |         owner         
----------+-----------------------
 web_user | restricted_area_owner
(1 row)

CURRENT_USER 也可以显式提供:

SELECT *
FROM restricted_area('web_user')

  caller  |         owner         
----------+-----------------------
 web_user | restricted_area_owner
(1 row)

但不接受其他值:

SELECT *
FROM restricted_area('postgres')
;

ERROR:  value for domain whoami violates check constraint "whoami_check"