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"
'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)