Supabase创建用户触发函数失效求助：初始角色设置异常

核心失效原因 #

原代码未处理raw_app_meta_data初始为NULL的情况——PostgreSQL中NULL与任何JSONB对象执行||合并操作时，结果仍为NULL，导致角色数组根本无法被写入。

另外还存在一个潜在的并发竞态问题：如果多个请求同时创建第一个用户，COUNT(*)会同时返回0，导致多个用户被错误设置为admin角色。

修正后的代码 #

先修复NULL值问题，同时优化查询效率（用EXISTS替代COUNT(*)避免全表扫描）：

CREATE OR REPLACE FUNCTION fn_set_initial_auth_user_role()
RETURNS trigger AS $body$
BEGIN
    -- 先将NULL转为空JSON对象，确保合并操作生效
    NEW.raw_app_meta_data := COALESCE(NEW.raw_app_meta_data, '{}'::jsonb);
    
    -- 判断是否已有用户，替代COUNT(*)提升性能
    IF EXISTS (SELECT 1 FROM auth.users) THEN
        NEW.raw_app_meta_data := NEW.raw_app_meta_data || jsonb_build_object('role', jsonb_build_array('user'));
    ELSE
        NEW.raw_app_meta_data := NEW.raw_app_meta_data || jsonb_build_object('role', jsonb_build_array('admin'));
    END IF;
    
    RETURN NEW;
END;
$body$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_set_initial_auth_user_role
BEFORE INSERT
ON auth.users
FOR EACH ROW
EXECUTE PROCEDURE fn_set_initial_auth_user_role();

并发场景优化（可选） #

如果需要严格避免多admin的情况，可以添加 advisory lock 防止竞态：

CREATE OR REPLACE FUNCTION fn_set_initial_auth_user_role()
RETURNS trigger AS $body$
BEGIN
    NEW.raw_app_meta_data := COALESCE(NEW.raw_app_meta_data, '{}'::jsonb);
    
    -- 加排他锁，确保同一时间只有一个请求执行判断逻辑
    PERFORM pg_advisory_lock(12345);
    BEGIN
        IF EXISTS (SELECT 1 FROM auth.users) THEN
            NEW.raw_app_meta_data := NEW.raw_app_meta_data || jsonb_build_object('role', jsonb_build_array('user'));
        ELSE
            NEW.raw_app_meta_data := NEW.raw_app_meta_data || jsonb_build_object('role', jsonb_build_array('admin'));
        END IF;
    FINALLY
        -- 释放锁
        PERFORM pg_advisory_unlock(12345);
    END;
    
    RETURN NEW;
END;
$body$ LANGUAGE plpgsql;

验证方法 #

插入用户后执行查询，检查角色是否正确写入：

SELECT id, raw_app_meta_data FROM auth.users;

首个用户的raw_app_meta_data应包含"role": ["admin"]，后续用户则为"role": ["user"]。

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