如何在PostgreSQL中为用户授予全数据库级别的只读权限

PostgreSQL本身没有原生的数据库级SELECT权限授予机制，权限控制的最小有效粒度是schema和对象（表、视图等），但可以通过以下方法实现批量授予的效果：

1. 定时自动批量授予（推荐长期使用） #

借助pg_cron扩展实现定时批量处理所有数据库，同时覆盖现有对象和未来新增对象的权限：

• 先创建一个超级用户执行的函数，遍历所有非模板数据库，对每个库的所有schema批量授予权限并设置默认权限：

CREATE OR REPLACE FUNCTION grant_select_all_dbs(p_username text)
RETURNS void AS $$
DECLARE
    db_record record;
BEGIN
    FOR db_record IN SELECT datname FROM pg_database WHERE datistemplate = false AND datname NOT IN ('postgres', 'template0', 'template1') LOOP
        EXECUTE format('
            DO $$
            DECLARE
                schema_rec record;
            BEGIN
                -- 授予public schema现有表/视图的SELECT权限
                GRANT SELECT ON ALL TABLES IN SCHEMA public TO %I;
                -- 设置public schema默认权限，新表自动继承
                ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO %I;
                
                -- 遍历所有自定义schema执行相同操作
                FOR schema_rec IN SELECT nspname FROM pg_namespace WHERE nspname NOT IN (''public'', ''pg_catalog'', ''information_schema'') LOOP
                    EXECUTE format(''GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I;'', schema_rec.nspname, %L);
                    EXECUTE format(''ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO %I;'', schema_rec.nspname, %L);
                END LOOP;
            END $$;
        ', p_username, p_username, p_username, p_username)
        USING db_record.datname;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

• 用pg_cron设置每日定时执行，确保新创建的数据库也能自动应用权限：

SELECT cron.schedule('daily-grant-select', '0 0 * * *', 'SELECT grant_select_all_dbs(''你的目标用户名'');');

2. 手动批量处理（一次性操作） #

如果不需要自动适配新数据库，直接用命令行批量执行：

• 导出所有非模板数据库列表：

psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname NOT IN ('postgres', 'template0', 'template1');" > dbs.txt

• 遍历每个数据库执行授权：

while read db; do
  psql -U postgres -d $db -c "
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO 你的目标用户名;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO 你的目标用户名;
    
    -- 处理所有自定义schema
    DO $$
    DECLARE
        schema_rec record;
    BEGIN
        FOR schema_rec IN SELECT nspname FROM pg_namespace WHERE nspname NOT IN ('public', 'pg_catalog', 'information_schema') LOOP
            EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO 你的目标用户名;', schema_rec.nspname);
            EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO 你的目标用户名;', schema_rec.nspname);
        END LOOP;
    END $$;
  "
done < dbs.txt

注意事项 #

• 所有操作必须以超级用户（如postgres）身份执行
• 如果不需要用户访问系统元数据，可以去掉pg_catalog相关的权限语句
• 默认权限仅对执行命令后创建的对象生效，已存在的对象需要单独通过GRANT SELECT ON ALL TABLES语句授权

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