PostgreSQL同集群内数据库备份恢复的角色权限处理问询

目标是在同一PostgreSQL集群内复制数据库，此前该功能通过集群超级用户实现，但目前执行备份恢复时遇到诸多问题，核心困扰在于对PostgreSQL角色与权限的理解，不确定当前操作方向是否正确。

当前操作流程 #

• 连接源数据库，删除并重新创建目标数据库
• 执行以下SQL终止目标数据库的所有连接：

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname='{target_database}';

• 生成.bat文件执行核心备份恢复命令：

pg_dump.exe -T dos_histomemo "postgresql://{username}:{encoded_pwd}@{ip}:{port}/{original_database}" | "{path_to_psql}psql.exe" -v ON_ERROR_STOP=1 "postgresql://{username}:{encoded_pwd}@{ip}:{port}/{target_database}"

测试后发现的问题 #

• 创建目标数据库的用户会自动成为其所有者，必须确保恢复操作与创建操作使用同一用户
• public schema的所有者为postgres：
  • 若使用--no-owner参数备份，需为user_app授予目标数据库schema的创建权限
  • 若保留所有权信息备份，则需以postgres身份执行恢复
• 以postgres身份执行操作时，因其他用户使用了ALTER DEFAULT PRIVILEGES，导致无法正常恢复角色

咨询问题 #

• 当前操作方向是否正确？
• 是否应该使用postgres用户管理集群所有权限？
• 该场景下更通用的角色处理方式是什么？
• 需符合ISO27001标准，该标准在此场景下的具体要求是什么？

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