PostgreSQL14流复制场景下repl用户的正确权限配置方法

报错核心原因 #

当前报错是数据库级CONNECT权限缺失，之前的授权操作存在3个明显问题：

• 执行的schema权限收回语句REVOKE ALL ON SCHEMA public FROM public;仅作用于public schema的对象访问权限，未覆盖数据库级连接权限；收回public角色默认权限后，未给repl用户单独授予数据库连接权，直接触发连接拒绝。
• 第三条授权语句存在语法错误：GRANT ALL PRIVILEGES ON SCHEMA public FROM public to repl;中多余的FROM public片段会导致语句执行失败，授权根本未生效。
• 授予的pg_read_all_data、pg_write_all_data预置角色仅提供库内表、序列等对象的读写权限，不包含数据库CONNECT权限，也无法覆盖流复制所需的专属权限，属于无效授权。

流复制repl用户所需最小权限 #

物理流复制走WAL日志传输，不需要访问库内业务对象，仅需以下权限即可正常运行：

• 角色自带REPLICATION属性（当前配置已满足）
• 对所有流复制涉及的数据库（含template0、template1、postgres三个系统库，以及所有业务库）拥有CONNECT权限
• pg_hba.conf中放通对应来源地址的replication类型连接允许规则

正确配置步骤 #

1. 授予repl用户必要的数据库连接权限，在主库执行如下SQL：

   GRANT CONNECT ON DATABASE template1 TO repl;
   GRANT CONNECT ON DATABASE template0 TO repl;
   GRANT CONNECT ON DATABASE postgres TO repl;
   -- 存在自定义业务库时，按实际库名追加授权，示例如下
   -- GRANT CONNECT ON DATABASE 你的业务库名 TO repl;
   

2. 遵循最小权限原则，收回之前授予的不必要权限：

   REVOKE pg_read_all_data FROM repl;
   REVOKE pg_write_all_data FROM repl;
   

   物理流复制不需要schema、表级的读写权限，多余权限会增加安全风险。
3. 检查主库pg_hba.conf配置，添加备节点的流复制放通规则，示例如下：

   # 替换备节点实际IP段、认证方式为你环境的配置
   host    replication     repl            192.168.1.0/24         scram-sha-256
   

   修改完成后执行pg_ctl reload重载数据库配置生效。

配置验证 #

在备节点执行普通连接测试，确认权限正常：

psql -h db-master -p 5432 -U repl -d template1

连接无报错后，执行流复制端口检测：

pg_isready -h db-master -p 5432 -U repl -d replication

返回accepting connections即代表权限配置正确，可正常执行pg_basebackup拉取基础备份、搭建流复制。

补充：如果使用逻辑复制，才需要额外给复制用户授予schema使用权限、表的SELECT权限，物理流复制场景不需要此类配置。

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