PostgreSQL 13.3 SCRAM认证失败：Java应用无法连接数据库

部署与配置情况 #

使用Docker部署postgres:13.3容器，启动命令如下：

docker run -d --name my-postgres13 -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=fbp123 -e POSTGRES_DB=mydb -e POSTGRES_HOST_AUTH_METHOD=scram-sha-256 -v pgdata13:/var/lib/postgresql/data postgres:13.3

尝试配置SCRAM认证：

• 修改postgresql.conf，设置：

  password_encryption = scram-sha-256
  

• 修改pg_hba.conf，配置：

  hostnossl all all 0.0.0.0/0 scram-sha-256
  local all all scram-sha-256
  

配置完成并重启容器后，创建用户fbp2并设置密码fbp123，查询pg_authid表显示密码已以SCRAM格式存储：

16386 | fbp2                      | t        | t          | f             | f           | t           | f              | f            |           -1 | SCRAM-SHA-256$4096:yw+jyaEzlvlOjZnc/L/flA==$tqPlJIDXv9zueaGd8KpQf11N82IGgAOsK4
Lhb7lPhi4=:+mCXFKb2y5PG6ycIKCz7xaY8U5MNLnkzlPZK8pt3to0= | 

通过psql工具可正常使用fbp2连接mydb：

root@a00ccf79f08a:/# psql -h localhost -p 5432 -U fbp2 -d mydb
Password for user fbp2:
psql (13.3 (Debian 13.3-1.pgdg100+1))
Type "help" for help.

mydb=#

Java连接失败现象 #

Java应用使用HikariCP连接池，代码如下：

hikariConfig = new HikariConfig();
hikariConfig.setUsername("fbp2");
hikariConfig.setPassword("fbp123");
hikariConfig.setJdbcUrl("jdbc:postgresql://%s:%s/%s".formatted("localhost", 5432, "mydb"));
HikariDataSource dataSource = new HikariDataSource(hikariConfig);
return dataSource.getConnection();

日志显示连接URL为jdbc:postgresql://localhost:5432/mydb，但出现认证失败：

2024-03-30 14:38:03.372 DEBUG 22440 [           main] c.z.h.u.DriverDataSource                 : Loaded driver with class name org.postgresql.Driver for jdbcUrl=jdbc:postgresql://localhost:5432/mydb
2024-03-30 14:38:03.601 DEBUG 22440 [           main] c.z.h.p.PoolBase                         : HikariPool-1 - Failed to create/setup connection: FATAL: password authentication failed for user "fbp2"
2024-03-30 14:38:03.601 DEBUG 22440 [           main] c.z.h.p.HikariPool                       : HikariPool-1 - Cannot acquire connection from data source
org.postgresql.util.PSQLException: FATAL: password authentication failed for user "fbp2"
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:693)
    at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:203)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:258)

若将pg_hba.conf改为trust认证且Java代码不提供密码，会报错：

org.postgresql.util.PSQLException: The server requested SCRAM-based authentication, but no password was provided.

可见服务器仅接受SCRAM认证，尝试切换为MD5认证也无效。

依赖与系统环境 #

• Maven依赖：

  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.3.0</version>
  </dependency>
  
  <dependency>
     <groupId>com.zaxxer</groupId>
     <artifactId>HikariCP</artifactId>
     <version>5.1.0</version>
   </dependency>
  

• 系统环境：Windows11 + Docker Desktop，Oracle OpenJDK 20.0.1

后续排查更新 #

更新1：服务器日志（含连接尝试） #

2024-03-30 15:21:19.566 UTC [1] LOG:  starting PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2024-03-30 15:21:19.567 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-03-30 15:21:19.567 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2024-03-30 15:21:19.571 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-03-30 15:21:19.576 UTC [27] LOG:  database system was shut down at 2024-03-30 15:21:18 UTC
2024-03-30 15:21:19.582 UTC [1] LOG:  database system is ready to accept connections

更新2：pg_settings查询结果 #

执行select name, setting, source, sourcefile from pg_settings where name = 'password_encryption';，结果显示：

mydb=# select name, setting, source, sourcefile from pg_settings where name = 'password_encryption';
        name         | setting | source  | sourcefile 
---------------------+---------+---------+------------
 password_encryption | md5     | default | 
(1 row)

更新3：postgresql.conf实际内容 #

查看/var/lib/postgresql/data/postgresql.conf，发现配置项仍处于注释状态：

#password_encryption = scram-sha-256        # md5 or scram-sha-256

已执行SELECT pg_reload_conf();并重启容器，但配置未生效。

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