PgBouncer与PostgreSQL认证方法类型不匹配故障求助

报错日志 #

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)

相关配置 #

PostgreSQL pg_hba.conf #

# 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 配置文件 #

[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

auth_file.cfg 内容 #

"username" "md5aaaa0cce3756d15429bdb3647b144704"

原因分析 #

虽然pg_hba.conf中配置了md5认证方法，但PostgreSQL 10及以上版本默认使用SCRAM-SHA-256作为密码哈希存储格式。如果目标用户的密码是以SCRAM格式存储的，即使pg_hba.conf指定md5，PostgreSQL仍会要求客户端使用SCRAM认证，而PgBouncer当前配置为auth_type = md5，使用的是MD5格式的密码哈希，两者不匹配导致报错。

解决方案 #

方案1：修改PgBouncer支持SCRAM认证 #

1. 编辑PgBouncer配置文件，将auth_type改为scram-sha-256：

   auth_type = scram-sha-256
   

2. 更新auth_file.cfg中的密码为SCRAM格式的哈希值：
   • 从PostgreSQL的pg_shadow表中获取用户的SCRAM哈希：

     SELECT passwd FROM pg_shadow WHERE usename = 'username';
     

   • 将获取到的哈希值替换到auth_file.cfg中，格式保持"username" "SCRAM-SHA-256..."
3. 重启PgBouncer使配置生效。

方案2：将PostgreSQL用户密码改为MD5格式 #

1. 登录PostgreSQL执行以下命令，强制将用户密码改为MD5加密格式：

   ALTER USER username WITH PASSWORD 'your_actual_password' ENCRYPTION 'md5';
   

2. 重新生成MD5哈希值并更新auth_file.cfg：
   • 计算MD5哈希（格式为md5 + 密码与用户名拼接后的MD5值）：

     echo -n 'your_actual_passwordusername' | md5sum
     

   • 将结果替换到auth_file.cfg中对应的位置
3. 重启PgBouncer。

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