PostgreSQL / 系统课时

准备复制账号与安全连接

准备复制账号与安全连接

逻辑复制的安全准备要解决三件事:谁能连到发布端、能读哪些发布表、连接是否经过可信加密。本课用最小权限复制角色、精确的 pg_hba.conf 规则和 TLS 校验,把订阅端到发布端的复制通道收紧到可验证的范围。

学习目标

  • 创建仅承担逻辑复制所需职责的登录角色,并补齐初始数据同步权限
  • 用 pg_hba.conf 限定数据库、账号和订阅端来源地址
  • 从订阅端验证 TLS、证书主机名校验和实际登录身份

复制连接需要哪些权限

逻辑复制的网络连接由订阅端主动发起,因此复制账号创建在发布端。账号需要 LOGIN 和 REPLICATION;如果订阅首次同步已有数据,还需要连接数据库并读取发布表。不要直接把迁移账号做成超级用户,也不要授予与迁移无关的写权限。

以下 SQL 在发布端 appdb 中执行。示例只授权 public.orders,如果实际发布包含更多表,应按发布清单逐表授权。

sql
CREATE ROLE repl_migrator
WITH LOGIN REPLICATION PASSWORD '<PASSWORD>';

GRANT CONNECT ON DATABASE appdb TO repl_migrator;
GRANT USAGE ON SCHEMA public TO repl_migrator;
GRANT SELECT ON TABLE public.orders TO repl_migrator;

预期输出

text
CREATE ROLE
GRANT
GRANT
GRANT

REPLICATION 负责建立复制协议连接,SELECT 主要用于表的初始同步。后续新增发布表时,要同步补授权;否则订阅可能能建立连接,却在表同步阶段因权限不足失败。

订阅端到发布端的最小权限安全连接路径

用 pg_hba.conf 限制复制入口

逻辑复制连接会指定真实数据库名,因此这里应匹配 appdb,而不是把数据库字段写成物理复制常见的 replication 关键字。以下是 pg_hba.conf 片段示例;先把 <SUBSCRIBER_IP> 替换为订阅端实际出口地址。

text
hostssl  appdb  repl_migrator  <SUBSCRIBER_IP>/32  scram-sha-256

这条规则同时要求 SSL、指定数据库、指定账号和指定来源地址,并使用 SCRAM 密码认证。生产环境应尽量使用单机地址或最小必要网段,不要为了省事放开到 0.0.0.0/0。

修改 pg_hba.conf 后可重新加载配置;如果是首次启用 ssl = on,通常还需要按部署方式重启 PostgreSQL。

sql
SELECT pg_reload_conf();

预期输出

text
 pg_reload_conf
----------------
 t
(1 row)

配置服务端 TLS 与客户端校验

发布端至少要启用 SSL,并配置服务端证书与私钥。以下为 postgresql.conf 片段示例,<PATH> 需要替换为实际证书目录,私钥文件权限应满足 PostgreSQL 的安全要求。

text
ssl = on
ssl_cert_file = '<PATH>/server.crt'
ssl_key_file = '<PATH>/server.key'

订阅端连接建议使用 sslmode=verify-full,同时提供签发服务端证书的 CA。这样不仅会加密流量,还会校验证书链与连接主机名;仅使用 require 只能保证有 TLS,不能提供同等级别的身份校验。

从订阅端验证账号与 TLS

不要只确认端口能通。应使用与后续订阅相同的账号、主机名和 CA 文件实际登录,并从当前会话检查 TLS 状态。证书的 SAN 或 CN 必须能匹配 source-db.example.internal。

bash
PGPASSWORD='<PASSWORD>' psql "host=source-db.example.internal port=5432 dbname=appdb user=repl_migrator sslmode=verify-full sslrootcert=<PATH>/ca.crt" -c "SELECT current_user, current_database(), s.ssl, s.version, s.cipher FROM pg_stat_ssl AS s WHERE s.pid = pg_backend_pid();"

预期输出

text
 current_user  | current_database | ssl | version | cipher
---------------+------------------+-----+---------+---------
 repl_migrator | appdb            | t   | TLSv1.3 | <VALUE>
(1 row)

实际 TLS 版本和加密套件可能不同,但 current_user 应为 repl_migrator,数据库应为 appdb,ssl 必须为 t。如果这里失败,应先修复证书、主机名、路由或 pg_hba.conf,再创建订阅,避免把网络与权限问题带进后续迁移流程。

常见失败应按哪一层定位

收到 no pg_hba.conf entry 时,先核对发布端看到的客户端地址、数据库名和账号是否命中正确规则;收到密码认证失败时,检查密码、SCRAM 配置和角色是否允许登录。若 verify-full 报证书错误,重点检查 CA 链、证书有效期以及连接主机名是否出现在证书 SAN 中。

连接已经成功但初始表同步报 permission denied,通常不是 TLS 问题,而是发布表的 SELECT 或 schema 的 USAGE 没有授予复制账号。按“网络入口 → TLS → 身份认证 → 表读取权限”的顺序定位,可以避免在错误层反复改配置。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

准备复制账号与安全连接

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通