如何配置PostgreSQL的pg_hba.conf仅接受带有效证书的连接

已通过二进制包搭建本地PostgreSQL 15服务器，完成以下配置：

• 生成SSL证书并存储在c:/certs，同时复制到PostgreSQL data目录（与postgresql.conf同目录）
• 配置postgresql.conf启用SSL并指定证书文件

需求：仅接受携带有效证书的psql连接（如下命令），拒绝未携带证书的连接。

psql 'host=localhost port=5432 dbname=local-db user=admin sslmode=verify-full sslcert=c:/certs/cert.pem sslkey=c:/certs/cert-key.pem sslrootcert=c:/certs/ca.pem'

拒绝的连接命令：

psql 'host=localhost port=5432 dbname=local-db user=admin'

尝试配置pg_hba.conf时遇到的问题：

1. 仅配置IPv4规则时，因本地使用IPv6连接，出现无匹配pg_hba.conf条目错误
2. 配置IPv6规则后，能拒绝无证书连接，但带证书连接提示FATAL: certificate authentication failed for user "admin"，且openssl验证证书正常
3. 将clientcert=verify-full改为clientcert=1时，服务器无法启动，日志提示无法加载pg_hba.conf

1. 修正pg_hba.conf规则格式与协议适配 #

PostgreSQL 15中clientcert的有效值为0（不要求证书）、1（要求证书但不验证身份）、verify-ca（验证证书由可信CA签发）、verify-full（验证CA签发且证书身份匹配用户名）。clientcert=1启动失败大概率是规则语法位置错误，需按规范配置：

在pg_hba.conf顶部添加以下规则（规则匹配顺序从上到下，优先匹配）：

# IPv4本地连接：强制SSL+客户端证书全验证
hostssl  local-db  admin  127.0.0.1/32  scram-sha-256  clientcert=verify-full
# IPv6本地连接：强制SSL+客户端证书全验证
hostssl  local-db  admin  ::1/128  scram-sha-256  clientcert=verify-full

• hostssl：仅接受SSL加密连接，直接拦截未启用SSL的无证书连接
• clientcert=verify-full：要求客户端提供可信CA签发的证书，且证书身份标识必须匹配用户名admin
• 认证方法推荐用scram-sha-256（PostgreSQL 15默认标准），若用户为md5创建可暂时用md5，建议后续迁移到scram

2. 验证证书身份标识与用户名匹配 #

verify-full模式要求证书的身份标识必须和数据库用户名一致，执行以下命令检查：

openssl x509 -in c:/certs/cert.pem -noout -subject

输出需包含CN = admin（CN字段值与连接时的user=admin一致）；若使用SubjectAltName，需包含email:admin或URI:postgresql://admin@localhost等符合PostgreSQL要求的身份标识。

3. 确认postgresql.conf的SSL配置完整性 #

确保postgresql.conf中SSL相关配置正确：

ssl = on
ssl_cert_file = 'server.crt'  # 服务器证书文件名（需在data目录）
ssl_key_file = 'server.key'   # 服务器私钥文件名（需在data目录）
ssl_ca_file = 'root.crt'      # CA根证书文件名，用于验证客户端证书的合法性

注意：必须配置ssl_ca_file，否则PostgreSQL无法验证客户端证书的CA签名。

4. 验证配置并重启服务 #

1. 检查pg_hba.conf语法：

pg_ctl -D "你的PostgreSQL data目录路径" reload

若重启失败，查看data目录下pg_log文件夹的日志，定位语法错误位置。
2. 重启PostgreSQL服务：

• Windows通过服务管理器重启postgresql-x64-15服务
• 或执行命令：pg_ctl -D "data目录路径" restart

5. 连接测试 #

• 带证书连接：执行你提供的完整SSL命令，确认能正常访问数据库
• 无证书连接：执行不带SSL参数的命令，应收到FATAL: no pg_hba.conf entry for host "::1", user "admin", database "local-db", no encryption类错误，说明连接被拦截

• 若仍出现certificate authentication failed：
  1. 确认ssl_ca_file配置的CA证书与客户端证书使用的CA完全一致
  2. 检查客户端证书是否过期：openssl x509 -in c:/certs/cert.pem -noout -dates
  3. 查看服务器日志的详细错误信息，日志会明确提示验证失败原因（如CA不匹配、CN不匹配等）
• 若本地连接仍优先使用IPv6：确保pg_hba.conf中::1/128规则存在且优先级高于其他通用规则

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