京公网安备11010502063487号“FATAL: no pg_hba.conf entry for host、password authentication failed for user 怎么排查” 是 PostgreSQL 新手最常搜的两个报错。这篇在本机自己起一个 PostgreSQL 16.15,把 pg_hba.conf 换成自己写的几行,逐个复现这些连接报错,对照客户端和服务器日志,并验证规则顺序、写错时的行为和连不上时的各种报错。
psql -h 127.0.0.1 -U alice(退出码 2):
psql: error: connection to server at "127.0.0.1", port 9261 failed: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "alice", database "postgres", no encryption
服务器日志:FATAL: no pg_hba.conf entry for host "127.0.0.1", user "alice", database "postgres", no encryption
加了 “host all alice 127.0.0.1/32 scram-sha-256” 并 reload 之后(退出码 0): alice
连 appdb(规则里 database 是 all)(退出码 0): alice
规则改成只允许 appdb 之后连 postgres 库(退出码 2):
psql: error: connection to server at "127.0.0.1", port 9261 failed: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "alice", database "postgres", no encryption没有规则同时匹配 host、user、database 和加密状态;加一行并 reload 即可。
密码正确(退出码 0):alice
密码错误(退出码 2):psql: error: connection to server at "127.0.0.1", port 9261 failed: FATAL: password authentication failed for user "alice"
服务器日志:FATAL: password authentication failed for user "alice"
服务器日志:DETAIL: Connection matched file "<tmp>/data/pg_hba.conf" line 2: "host all all 127.0.0.1/32 scram-sha-256"
用户没有设置密码(退出码 2):psql: error: connection to server at "127.0.0.1", port 9261 failed: FATAL: password authentication failed for user "nopass"
服务器日志:FATAL: password authentication failed for user "nopass"
服务器日志:DETAIL: User "nopass" has no password assigned.
没有给密码(-w)(退出码 2):psql: error: connection to server at "127.0.0.1", port 9261 failed: fe_sendauth: no password supplied客户端报错相同,原因在服务器日志 DETAIL 里:密码错的 Connection matched … line N,或者用户没设密码。
用户不存在(退出码 2): psql: error: connection to server at "127.0.0.1", port 9261 failed: FATAL: role "bob" does not exist 数据库不存在(退出码 2): psql: error: connection to server at "127.0.0.1", port 9261 failed: FATAL: database "nosuchdb" does not exist reject 写在 trust 前面,用户 alice(退出码 2): psql: error: connection to server at "127.0.0.1", port 9261 failed: FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "alice", database "postgres", no encryption 同样的规则,其他用户 nopass(退出码 0): nopass 顺序反过来(trust 在前、reject 在后),用户 alice(退出码 0): alice ——后面的 reject 永远轮不到
从上到下第一个匹配的规则生效;reject 写在 trust 前面才有效。
pg_hba_file_rules 第 1 行:type=local,auth_method=trust,error=None
pg_hba_file_rules 第 2 行:type=None,auth_method=None,error=invalid authentication method "scram-sha-257"
pg_hba_file_rules 第 3 行:type=host,auth_method=trust,error=None
pg_hba_file_rules 第 4 行:type=None,auth_method=None,error=invalid connection type "hots"
服务器日志:LOG: invalid authentication method "scram-sha-257"
服务器日志:CONTEXT: line 2 of configuration file "<tmp>/data/pg_hba.conf"
服务器日志:CONTEXT: line 4 of configuration file "<tmp>/data/pg_hba.conf"
服务器日志:LOG: <tmp>/data/pg_hba.conf was not reloaded
reload 之后 alice 还能连吗(旧配置还在生效)(退出码 0): alicepg_hba_file_rules 的 error 列能在 reload 之前看出错在哪一行;reload 失败时旧配置继续生效。
端口不对(退出码 2): psql: error: connection to server at "127.0.0.1", port 9999 failed: Connection refused socket 目录不对(退出码 2): psql: error: connection to server on socket "/nonexistent/dir/.s.PGSQL.9261" failed: No such file or directory pg_isready 服务器在线(退出码 0): 127.0.0.1:9261 - accepting connections pg_isready 端口没有服务(退出码 2): 127.0.0.1:9999 - no response 服务器停掉之后(退出码 2): psql: error: connection to server at "127.0.0.1", port 9261 failed: Connection refused
Connection refused 是端口没服务,No such file or directory 是 socket 路径不对;pg_isready 退出码 0 在线、2 无响应。