PostgreSQL触发器函数循环授予权限失效问题排查

问题排查与解决步骤 #

1. 确认数组内容是否正确
   先检查插入记录中project数组的实际值，执行：

SELECT project FROM common.users WHERE name = 'user01';

如果数组元素带双引号（比如{"\"schema01\"", "\"schema02\""}），说明插入语句格式错误，导致循环处理的是带引号的schema名，而实际schema不存在带引号的版本。此时应改用正确的数组插入写法：

-- 方式1：简化数组字符串
INSERT INTO common.users (name, project) VALUES ('user01', '{schema01, schema02}');
-- 方式2：数组构造器（更清晰）
INSERT INTO common.users (name, project) VALUES ('user01', ARRAY['schema01', 'schema02']);

2. 修复触发器函数的标识符转义问题
   直接拼接字符串会引发语法错误（比如大小写敏感的标识符、含特殊字符的用户名），还存在SQL注入风险。必须用quote_ident()函数转义标识符，修改后的触发器函数：

CREATE OR REPLACE FUNCTION common.add_user()
    RETURNS trigger
    LANGUAGE plpgsql
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    u_name text = NEW.name;
    m text;
BEGIN
    FOREACH m IN ARRAY NEW.project LOOP
        EXECUTE 'GRANT USAGE ON SCHEMA ' || quote_ident(m) || ' TO ' || quote_ident(u_name);
    END LOOP;
    RETURN NEW;
END;
$BODY$;

3. 确认权限是否真的未授予
   用官方标准语句验证权限，避免直观判断出错：

-- 检查user01对schema01的USAGE权限
SELECT has_schema_privilege('user01', 'schema01', 'usage');
-- 查看user01的所有schema权限
SELECT nspname, privilege_type
FROM information_schema.schema_privileges
WHERE grantee = 'user01';

4. 检查触发器函数的执行权限
   触发器函数默认是SECURITY INVOKER（使用插入语句的用户权限执行），若你确认插入用户有授权权限则无需调整；若函数是SECURITY DEFINER模式，需确保函数定义者具备GRANT权限。可查看函数属性：

SELECT proname, prosecdef 
FROM pg_proc 
WHERE proname = 'add_user' AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'common');

若prosecdef为t，需确认定义者对目标schema有GRANT USAGE权限，且能将该权限授予其他用户。

5. 确认事务是否提交
   即便记录已插入，若未提交事务，GRANT的权限也不会生效。可重新连接数据库后再查询权限，确保事务已正常提交。

验证方法 #

修改函数和插入语句后，插入测试记录：

INSERT INTO common.users (name, project) VALUES ('user02', ARRAY['schema01', 'schema02']);

再执行权限查询语句，确认权限已成功授予。

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