如何为PostgreSQL新用户批量授予分属不同服务用户的多数据库权限？

你不需要逐个登录各服务用户来授权，PostgreSQL提供了多种高效的批量授权方式，下面是具体方案：

1. 超级用户批量生成授权语句 #

以超级用户（如postgres）身份操作，直接遍历所有目标数据库、模式和表，自动生成并执行授权命令，无需切换到各个服务用户。

批量处理单数据库 #

在目标数据库中执行以下PL/pgSQL脚本，给指定用户授予所有非系统模式下普通表的SELECT权限：

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT n.nspname AS schema_name, c.relname AS table_name
        FROM pg_namespace n
        JOIN pg_class c ON n.oid = c.relnamespace
        WHERE c.relkind = 'r'  -- 仅针对普通表
          AND n.nspname NOT IN ('pg_catalog', 'information_schema')  -- 排除系统模式
    LOOP
        EXECUTE format('GRANT SELECT ON %I.%I TO 你的新用户名;', rec.schema_name, rec.table_name);
    END LOOP;
END $$;

批量处理所有数据库 #

如果需要覆盖实例中所有非模板数据库，可结合psql命令行工具批量执行上述脚本：

1. 把上面的PL/pgSQL脚本保存为grant_select.sql
2. 执行以下bash命令：

for db in $(psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;"); do
  psql -U postgres -d $db -f grant_select.sql
done

2. 用角色继承简化长期管理 #

创建一个专门的只读角色，把所有SELECT权限授予该角色，之后只需将新用户加入这个角色即可，避免重复授权：

-- 创建无登录权限的只读角色
CREATE ROLE read_only_access NOLOGIN;

-- 执行上面的批量授权脚本，将权限授予read_only_access
-- （把脚本中的"你的新用户名"替换为read_only_access）

-- 将新用户加入只读角色
GRANT read_only_access TO 你的新用户名;

3. 配置默认权限应对新增表 #

为了让后续服务用户创建的新表自动拥有只读权限，可批量设置模式的默认权限：

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT r.rolname AS owner_role, n.nspname AS schema_name
        FROM pg_namespace n
        JOIN pg_roles r ON n.nspowner = r.oid
        WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
    LOOP
        EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE %I IN SCHEMA %I GRANT SELECT ON TABLES TO read_only_access;', rec.owner_role, rec.schema_name);
    END LOOP;
END $$;

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