使用Auth Query通过PGBouncer认证失败问题求助

问题概述 #

使用PostgreSQL 14.3（默认密码加密为SCRAM-SHA-256）和PGBouncer 1.20，配置Auth Query实现认证时，将auth_type设置为scram-sha-256后连接失败，报错：

cannot do SCRAM authentication: password is SCRAM secret but client authentication did not provide SCRAM keys

仅当userlist.txt中pgbouncer用户的密码为明文或MD5格式时，连接才能正常建立。

核心原因 #

PGBouncer 1.20版本尚未修复Auth Query场景下的SCRAM认证兼容问题：当Auth Query返回的是SCRAM哈希密码时，PGBouncer无法正确处理客户端发起的SCRAM密钥交换流程，仅能识别明文或MD5格式的密码完成认证转发。

可行解决方案 #

1. 调整pgbouncer用户密码格式 #

将userlist.txt中的pgbouncer用户密码设置为明文或MD5哈希格式：

• 明文格式示例：

  "pgbouncer" "your_plaintext_password"
  

• MD5格式示例：

  "pgbouncer" "md5abcdef1234567890"
  

  （MD5哈希可通过PostgreSQL执行SELECT md5('password' || 'pgbouncer');生成）

2. 修改Auth Query返回明文密码 #

保持auth_type = scram-sha-256不变，调整你的lookup函数，使其返回用户的明文密码（而非SCRAM哈希值）。这样PGBouncer会自行处理与客户端的SCRAM密钥交换，再使用明文密码连接PostgreSQL（PostgreSQL会自动用SCRAM协议验证密码）。

注意：需严格限制pgbouncer用户的数据库权限，仅允许其执行lookup查询，避免明文密码泄露风险。

3. 升级PGBouncer到最新稳定版 #

PGBouncer后续版本（如1.21及以上）已修复该SCRAM Auth Query兼容问题，升级后可直接支持Auth Query返回SCRAM哈希密码的场景。

当前配置参考 #

你的pgbouncer.ini配置如下：

[databases]
postgres = host=localhost port=5432 dbname=postgres
sasdb = host=localhost port=5432 dbname=test auth_user=pgbouncer
[pgbouncer]
listen_port = 9000
auth_file = <path to userlist.txt>
auth_query = select p_user, p_password from lookup($1)
auth_type = scram-sha-256
listen_addr = *
logfile = <path to pgbouncer.log>
pidfile = <path to pgbouncer.pid>
max_user_connections = 100
pool_mode = transaction
ignore_startup_parameters = extra_float_digits

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