PostgreSQL中SECURITY DEFINER函数如何获取登录用户身份？

问题背景 #

在PostgreSQL中，调用SECURITY DEFINER类型的函数或存储过程时，current_user和current_role会返回函数定义者的身份，而非当前实际登录（或通过SET ROLE切换后）的用户身份。若改用SECURITY INVOKER模式，虽然能获取真实用户身份，但会失去定义者权限，无法访问用户无权限的底层对象。因此需要在SECURITY DEFINER上下文下获取真实用户身份的方案。

解决方案 #

PostgreSQL提供两种关键方式获取真实用户/角色信息：

• session_user：始终返回初始登录的用户身份，不受SECURITY DEFINER或SET ROLE操作的影响。
• current_setting('role')：获取当前会话中通过SET ROLE切换后的角色身份，在SECURITY DEFINER函数中依然保留该会话级参数值。

测试验证 #

1. 创建测试函数 #

在原有测试环境基础上，新增两个SECURITY DEFINER函数：

postgres=# create or replace function test.real_session_user() returns varchar as $$
postgres$# begin
postgres$#     return session_user::text;
postgres$# end; $$ language plpgsql security definer set search_path = test, pg_temp, pg_catalog;
CREATE FUNCTION

postgres=# create or replace function test.real_current_role() returns varchar as $$
postgres$# begin
postgres$#     return current_setting('role')::text;
postgres$# end; $$ language plpgsql security definer set search_path = test, pg_temp, pg_catalog;
CREATE FUNCTION

postgres=# grant execute on function test.real_session_user() to public;
GRANT
postgres=# grant execute on function test.real_current_role() to public;
GRANT

2. 执行测试 #

使用testproxy账号登录并切换角色后调用函数：

postgres=> select current_role::text, current_user::text;
 current_role | current_user 
--------------+--------------
 testproxy    | testproxy

postgres=> set role testuser;
SET

postgres=> select current_role::text, current_user::text;
 current_role | current_user 
--------------+--------------
 testuser     | testuser
(1 row)

postgres=> select test.real_session_user(), test.real_current_role();
 real_session_user | real_current_role 
-------------------+-------------------
 testproxy         | testuser
(1 row)

可见，即使在SECURITY DEFINER函数中，也能正确获取到初始登录用户（testproxy）和切换后的当前角色（testuser）。

内容的提问来源于stack exchange，提问作者nvanwyen