Redshift中使用SECURITY DEFINER存储过程返回临时表权限问题

当存储过程以SECURITY DEFINER模式执行时，所有操作均以存储过程定义者的身份完成，包括创建临时表。这意味着你生成的临时表myresult所有者是定义存储过程的超级用户，而调用者test123没有该表的访问权限，因此查询时触发权限拒绝错误。

解决方案1：在存储过程中动态给调用者授权临时表权限 #

在创建临时表后，自动为当前调用者授予该表的SELECT权限。需使用安全的动态SQL避免注入风险，同时获取会话的实际调用者身份：

CREATE OR REPLACE PROCEDURE "test".test_sp(INOUT tmp_name character varying(256))
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $$
DECLARE
  current_user_name text := session_user; -- 获取实际调用者身份
BEGIN
  -- 安全删除临时表（使用quote_ident避免SQL注入）
  EXECUTE 'drop table if exists ' || quote_ident(tmp_name);
  -- 创建临时表
  EXECUTE 'create temp table ' || quote_ident(tmp_name) || ' as select * from test.test_tbl;';
  -- 给调用者授予查询权限
  EXECUTE 'grant select on table ' || quote_ident(tmp_name) || ' to ' || quote_literal(current_user_name);
END;
$$;

该方案保留SECURITY DEFINER的特性（调用者无需直接访问test.test_tbl），同时确保调用者能访问生成的临时表。

解决方案2：改用SECURITY INVOKER模式（业务允许时） #

如果可以给调用者授予test.test_tbl的访问权限，可将存储过程改为SECURITY INVOKER模式，此时临时表以调用者身份创建，天然属于调用者，无需额外授权：

-- 先给调用者授予源表访问权限
GRANT SELECT ON test.test_tbl TO test123;

-- 修改存储过程为调用者身份执行
CREATE OR REPLACE PROCEDURE "test".test_sp(INOUT tmp_name character varying(256))
 LANGUAGE plpgsql
 SECURITY INVOKER
AS $$
BEGIN
  EXECUTE 'drop table if exists ' || quote_ident(tmp_name);
  EXECUTE 'create temp table ' || quote_ident(tmp_name) || ' as select * from test.test_tbl;';
END;
$$;

该方案权限模型清晰简洁，但需确保调用者具备源表访问权限，适合无需隐藏源表权限的场景。

解决方案3：直接返回结果集（无需临时表） #

如果仅需获取test.test_tbl的数据，可改用函数返回结果集，彻底避免临时表的权限管理问题：

CREATE OR REPLACE FUNCTION "test".test_func()
 RETURNS TABLE(a int)
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY SELECT * FROM test.test_tbl;
END;
$$;

-- 给调用者授予函数执行权限
GRANT EXECUTE ON FUNCTION "test".test_func() TO test123;

-- 调用方式
SET SESSION AUTHORIZATION test123;
SELECT * FROM "test".test_func();

该方案无需临时表，直接返回数据，是最简洁的实现方式，适合仅需获取数据的场景。

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