postgresqlauthenticationmd5pgbouncer

Problem with PgBouncer auth method not same type


I have some issues with the auth method in PgBouncer and Postgresql.

Error:


2024-09-28 10:49:28.750 UTC [120] LOG process up: PgBouncer 1.23.1, libevent 2.1.12-stable (epoll), adns: evdns2, tls: OpenSSL 3.0.7 1 Nov 2022
2024-09-28 10:49:32.468 UTC [120] LOG C-0x274199c0: database/username@127.0.0.1:38382 login attempt: db=database user=username tls=no replication=no
2024-09-28 10:49:35.840 UTC [120] LOG C-0x274199c0: database/username@127.0.0.1:45156 login attempt: db=database user=username tls=no replication=no
2024-09-28 10:49:35.849 UTC [120] LOG S-0x274446b0: database/username@127.0.0.1:5432 new connection to server (from 127.0.0.1:57562)
2024-09-28 10:49:35.859 UTC [120] ERROR S-0x274446b0: database/username@127.0.0.1:5432 cannot do SCRAM authentication: wrong password type
2024-09-28 10:49:35.859 UTC [120] LOG C-0x274199c0: database/username@127.0.0.1:45156 closing because: server login failed: wrong password type (age=0s)
2024-09-28 10:49:35.859 UTC [120] WARNING C-0x274199c0: database/username@127.0.0.1:45156 pooler error: server login failed: wrong password type
2024-09-28 10:49:35.859 UTC [120] LOG S-0x274446b0: database/username@127.0.0.1:5432 closing because: failed to answer authreq (age=0s)

I don't know why Postgresql is trying to authenticate with SCRAM method.

Postgresql hba file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

# ---
# @PgCloud : Add replication user
host    replication     replica_user    0.0.0.0/0               md5

host    all             all             0.0.0.0/0               md5

PgBouncer file:

[databases]
db_pgcloud = host=127.0.0.1 port=5432 dbname=database user=username
[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type   = md5
auth_file   = /etc/pgbouncer/auth_file.cfg
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 100

And auth_file.cfg

"username" "md5aaaa0cce3756d15429bdb3647b144704"

Solution

  • 'md5' in the hba is interpreted as 'md5 or better', where (at the moment) better can only mean SCRAM.

    If the hash stored in pg_authid is in the SCRAM format, then that is not compatible with the md5 stored in auth_file.cfg, so you get the 'wrong password type' error.

    You can log into the real server (either bypassing pgbouncer, or using a superuer), and then reset the password so that it is hashed in the md5 format by setting password_encryption appropriately before doing the reset. (There are about a million other things you can do instead, but this method seems most in keeping with your implied intentions)