pgAdmin v4 8.11远程执行Vacuum时SSL证书验证失败问题排查

问题现象 #

• 使用pgAdmin v4 8.11对本地网络外的远程PostgreSQL服务器执行Vacuum清理表操作失败，但执行查询操作无异常，本地网络内的数据库维护操作也正常。
• 采用sslmode=verify-full模式，已配置sslrootcert和passfile。

报错信息 #

psql: error: connection to server at "xx.xxx.xx.xx", port 5432 failed:
SSL error: certificate verify failed connection to server at
"xx.xxx.xx.xx", port 5432 failed: FATAL: no pg_hba.conf entry for host
"12.345.678.90", user "postgres", database "dbname", no encryption

数据库服务器pg_hba.conf配置 #

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
hostssl    all             all             12.345.678.90/24        scram-sha-256
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

数据库服务器日志 #

2024-08-28 11:47:15.245 UTC [2462003] LOG: could not accept SSL
connection: tlsv1 alert unknown ca 2024-08-28 11:47:15.290 UTC
[2462004] FATAL: no pg_hba.conf entry for host "12.345.678.90", user
"postgres", database "dbname", no encryption

证书及连接测试 #

1. OpenSSL证书验证测试 #

执行命令：

openssl s_client -starttls postgres -connect xx.xxx.xx.xx:5432 -CAfile /path/to/CAchain.pem

返回结果：

SSL handshake has read 4201 bytes and written 737 bytes Verification:
OK
--- New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384 Server public key is 2048 bit Secure Renegotiation IS NOT supported Compression: NONE
Expansion: NONE No ALPN negotiated Early data was not sent Verify
return code: 0 (ok) error:0A000126:SSL routines:ssl3_read_n:unexpected
eof while reading:../ssl/record/rec_layer_s3.c:307:

2. psql命令行连接测试 #

使用连接字符串：

psql "postgresql://xx.xxx.xx.xx:5432/dbname?&sslmode=verify-full&sslrootcert=C:\path\CAchain.pem&user=postgres"

返回内容（仅含编码警告，连接正常）：

psql (16.0, server 15.8) WARNING: Console code page (437) differs from
Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details. SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

3. pgAdmin内置psql终端测试 #

在pgAdmin窗口中打开psql终端，手动输入Vacuum命令，成功完成表清理操作。

结论 #

结合所有测试结果（OpenSSL验证通过、命令行连接正常、pgAdmin内置psql执行Vacuum成功），推测该问题为pgAdmin v4 8.11的Bug导致。

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