能否通过批处理脚本结合SELECT为集群各数据库创建角色？

需求说明 #

需要为PostgreSQL集群中除template0、template1、postgres外的每个数据库，创建格式为[数据库名]_connect的角色。

第一种方案：批处理生成psql命令 #

原方案问题 #

你编写的SELECT语句仅会输出创建角色的SQL文本和数据库切换命令，但这些输出只是字符串，并没有被psql实际执行，因此无法创建角色。该方案思路可行，但缺少将输出转为可执行命令的关键步骤。

修正方法 #

通过psql参数将SELECT输出的命令直接管道给psql执行，确保每个生成的命令都被实际运行：

# 替换your_user为实际数据库用户
psql -U your_user -d postgres -t -A -c "
SELECT '\c '||t1.datname||chr(10)||'CREATE ROLE "'"'"'||t1.datname||'_connect'"'"'"';'
FROM pg_database t1
WHERE t1.datname NOT IN ('template0', 'template1', 'postgres')
ORDER BY datname;" | psql -U your_user

参数说明：

• -t：去掉查询结果的表头和尾部统计信息
• -A：关闭对齐模式，避免输出多余空格干扰命令执行
• 管道|：将第一个psql输出的命令直接作为第二个psql的输入执行

第二种方案：PL/pgSQL匿名块 #

原方案问题 #

1. rec是record类型，直接拼接会生成带括号的字符串（如(mydb)），需通过rec.datname访问具体字段
2. \c是psql客户端命令，无法在PL/pgSQL的EXECUTE中执行（EXECUTE仅支持SQL语句）
3. PostgreSQL的角色是集群级对象，不需要切换数据库即可创建，切换数据库的操作完全多余

修正方法 #

直接遍历数据库名，使用format函数安全拼接SQL语句创建角色：

DO $$
DECLARE
  db_name text;
BEGIN
  FOR db_name IN 
    SELECT t1.datname
    FROM pg_database t1
    WHERE t1.datname NOT IN ('template0', 'template1', 'postgres')
    ORDER BY datname
  LOOP
    -- 使用%I自动处理标识符转义，避免特殊字符导致SQL错误
    EXECUTE format('CREATE ROLE %I_connect', db_name);
    RAISE NOTICE '已为数据库 % 创建角色 %_connect', db_name, db_name;
  END LOOP;
END $$;

关键优化点 #

• 用text类型变量db_name代替record，简化字段访问
• 使用format('%I', ...)处理数据库名，自动处理包含特殊字符或关键字的数据库名，避免SQL注入和语法错误
• 移除无效的数据库切换操作，直接在当前数据库执行即可创建集群级角色

内容的提问来源于stack exchange，提问作者Diogo dos Santos