[SOLVED] FATAL: password authentication failed for user with Postgres13.3 and SCRAM authentication

FATAL: password authentication failed for user with Postgres13.3 and SCRAM authentication

I have setup a postgres:13.3 docker container and scram-sha-256 authentication.

Initially, I ran:

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

Postgres.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

After above done and restarted container, I created a new fbp2 user and applied password 'fbp123', and password seems to be saved as scram in pg_authid table:

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

I use the original plain-text from within my java app to connect:

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();

From logs, this url is used: jdbc:postgresql://localhost:5432/mydb

The issue is I'm having authentication issue, although I use the plain-text password that I used in postgres server:

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)

Note that If I revert to "trust" and send no passwords, I have this:

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

So, it seems server only wants scram. I have tried md5 with no success.

Some relevant dependencies:

 <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>

My docker desktop runs on windows 11. I use Oracle OpenJDK 20.0.1

I can connect to mydb with fbp2 user with no problem via psql admin tool (after plain password):

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=#

UPDATE 1

Server logs (includes connection attempt):

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

UPDATE 2

Result of "mydb=# 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)

UPDATE 3

/var/lib/postgresql/data/postgresql.conf:

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

I see only one postgresql.conf file. To reload the change, I did:

mydb=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

mydb=#

and also docker restart container.

Solution

Thanks @jjanes and Adrian. That was the issue. I was unaware of a previous local installation of postgres 14 and 16. I re-installed them and problem was solved.

After removing forgotten postgres installations, I now have below and problem is resolved:

PS C:\Users\tioan> netstat -aon | findstr "5432"

  TCP    0.0.0.0:5432           0.0.0.0:0              LISTENING       7828
  TCP    [::]:5432              [::]:0                 LISTENING       7828

Before, I had 4 rows. I seems the error description didn't help to find the cause here.