如何在触发器中实现超级用户创建Schema后转所有权给普通用户

之前触发器执行失败的核心原因：普通触发器默认是SECURITY INVOKER权限，执行时沿用触发操作的当前用户权限，非超级用户本身没有切换超级用户角色、创建Schema的权限，哪怕函数里写了提权逻辑也会被权限拦截。以下是可直接落地的实现路径：

步骤1：先补全超级用户的基础创建权限 #

先使用超级用户账号登录目标数据库，修复你提到的「超级用户缺少创建权限」问题：

-- 给超级用户授予目标库的建Schema权限
GRANT CREATE ON DATABASE 你的目标业务库名 TO 你的超级用户账号名;

如果执行完还是报权限错误，先检查是否有全局事件触发器拦截了超级用户的DDL操作，临时禁用这类审计/拦截触发器即可。

步骤2：创建提权属性的触发器函数 #

保持超级用户身份创建触发器函数，核心是加SECURITY DEFINER属性——该属性会让函数执行时自动使用函数所有者（即超级用户）的权限，不需要在函数内手动写角色切换语句：

CREATE OR REPLACE FUNCTION auto_create_user_schema()
RETURNS event_trigger -- 如果是普通表DML触发器，这里改成RETURNS trigger即可
LANGUAGE plpgsql
SECURITY DEFINER -- 核心提权配置
SET search_path = pg_catalog, public -- 安全配置，防止搜索路径劫持
AS $$
DECLARE
    login_uname text;
    target_schema text;
BEGIN
    -- 注意必须用session_user取实际登录的非超级用户，SECURITY DEFINER环境下current_user会返回超级用户
    login_uname := session_user;
    -- 替换成你需要的Schema命名规则，示例为和登录用户名同名的Schema
    target_schema := 'biz_' || login_uname;

    -- 先判断Schema是否存在，避免重复创建报错
    IF NOT EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = target_schema) THEN
        -- 以超级用户权限创建Schema
        EXECUTE format('CREATE SCHEMA %I', target_schema);
        -- 将Schema所有权转移给当前登录的非超级用户
        EXECUTE format('ALTER SCHEMA %I OWNER TO %I', target_schema, login_uname);
        -- 按需给用户授予Schema下的操作权限
        EXECUTE format('GRANT ALL ON SCHEMA %I TO %I', target_schema, login_uname);
    END IF;

    RETURN NULL; -- 普通DML触发器这里返回NEW即可，事件触发器返回NULL
END;
$$;

安全注意：函数内动态执行语句必须用format(%I)做标识符转义，不要直接拼接用户传入的字符串，避免SQL注入风险。

步骤3：绑定对应触发器 #

保持超级用户身份，根据你的业务场景绑定触发器即可：

-- 示例1：用户登录时自动创建对应Schema（PG10及以上版本支持）
CREATE EVENT TRIGGER tri_auto_create_schema ON LOGIN
EXECUTE FUNCTION auto_create_user_schema();

-- 示例2：业务表插入新用户记录时触发创建Schema
-- CREATE TRIGGER tri_create_schema AFTER INSERT ON sys_user
-- FOR EACH ROW EXECUTE FUNCTION auto_create_user_schema();

常见踩坑点 #

• 不要在函数内写SET ROLE 超级用户类逻辑：非超级用户本身无权限切换到超级用户角色，写了必然触发权限报错，SECURITY DEFINER已经实现了执行权限的切换，不需要额外执行角色切换语句
• 不要用current_user取登录账号：SECURITY DEFINER执行环境下current_user返回的是函数所有者（即超级用户），会导致Schema所有权转移给错误的账号
• 不要省略search_path配置：如果不固定搜索路径，低权限用户可以把自定义恶意函数放在高优先级路径下，诱导超级用户权限执行恶意代码
• 不要给函数开放多余执行权限：触发器函数只需要给需要触发操作的用户授予执行权限即可，不要开放给PUBLIC

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