如何为PostgreSQL用户程序化赋予所有现有及未来Schema的只读权限？

一、一次性授权所有现有Schema #

通过PL/pgSQL脚本遍历所有用户自定义Schema（排除系统Schema），批量完成只读权限授予：

DO $$
DECLARE
    schema_rec RECORD;
BEGIN
    -- 遍历所有非系统Schema
    FOR schema_rec IN SELECT schema_name FROM information_schema.schemata 
                      WHERE schema_name NOT LIKE 'pg_%' 
                        AND schema_name != 'information_schema' LOOP
        -- 授予Schema的USAGE权限（允许访问Schema内对象）
        EXECUTE format('GRANT USAGE ON SCHEMA %I TO 你的只读用户名;', schema_rec.schema_name);
        -- 授予Schema内所有现有表的SELECT权限
        EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO 你的只读用户名;', schema_rec.schema_name);
        -- 若Schema包含序列，需额外授权（避免查询带序列字段的表时报错）
        EXECUTE format('GRANT SELECT ON ALL SEQUENCES IN SCHEMA %I TO 你的只读用户名;', schema_rec.schema_name);
    END LOOP;
END $$;

二、自动授权未来新增的Schema #

利用PostgreSQL的事件触发器监听Schema创建事件，自动完成授权，无需手动重复执行脚本：

1. 创建授权处理函数 #

CREATE OR REPLACE FUNCTION grant_read_access_to_new_schema()
RETURNS event_trigger AS $$
BEGIN
    -- 捕获所有新创建的Schema事件
    FOR each IN SELECT * FROM pg_event_trigger_ddl_commands() 
                WHERE command_tag = 'CREATE SCHEMA' LOOP
        -- 授予新Schema的USAGE权限
        EXECUTE format('GRANT USAGE ON SCHEMA %I TO 你的只读用户名;', each.object_identity);
        -- 授予新Schema内现有表/序列的只读权限
        EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO 你的只读用户名;', each.object_identity);
        EXECUTE format('GRANT SELECT ON ALL SEQUENCES IN SCHEMA %I TO 你的只读用户名;', each.object_identity);
        -- 设置默认权限：新Schema后续创建的表/序列自动继承只读权限
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO 你的只读用户名;', each.object_identity);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON SEQUENCES TO 你的只读用户名;', each.object_identity);
    END LOOP;
END $$ LANGUAGE plpgsql;

2. 创建事件触发器绑定函数 #

CREATE EVENT TRIGGER grant_read_on_new_schema
ON ddl_command_end
WHEN TAG IN ('CREATE SCHEMA')
EXECUTE FUNCTION grant_read_access_to_new_schema();

关键注意事项 #

• 替换所有代码中的你的只读用户名为实际的数据库用户名称
• 事件触发器需要超级用户权限才能创建，执行脚本时请使用postgres或具备超级权限的账号
• 若业务中无需访问序列，可删除所有与序列相关的授权语句
• 验证方法：创建新Schema并在其中建表，用只读用户登录后尝试查询，确认权限生效

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