通过pgbouncer连接Docker内Postgres容器失败，求问题排查

问题背景 #

使用pgBouncer创建连接池，对应的docker-compose.yml配置如下：

version: '3'
services:

  db:
    image: postgres:12
    volumes:
      - pg_data:/var/lib/postgresql/data
      - ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=pass

  pgbouncer:
    image: edoburu/pgbouncer
    environment:
      - DB_USER=postgres
      - DB_PASSWORD=pass
      - DB_HOST=db
      # - DB_NAME=test
      # for postgres:14 and above
      - AUTH_TYPE=scram-sha-256 
      - POOL_MODE=transaction
      - ADMIN_USERS=postgres,dbuser
    ports:
      - "5432:5432"
    depends_on:
      - db

volumes:
  pg_data:

两个容器运行正常，但通过pgBouncer连接PostgreSQL时出现密码认证失败错误。执行psql命令的报错：

$ psql -h localhost -p 5432 -U postgres 
Password for user postgres: 
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "postgres"

容器日志显示：

compose-db-1         |  Connection matched pg_hba.conf line 99: "host all all all md5"
compose-pgbouncer-1  | 2024-03-29 02:19:45.445 UTC [1] WARNING C-0x7f3475c6f3a0: postgres/postgres@172.19.0.1:59276 pooler error: password authentication failed for user "postgres"
compose-pgbouncer-1  | 2024-03-29 02:19:45.445 UTC [1] LOG S-0x7f3475c193c0: postgres/postgres@172.19.0.2:5432 closing because: login failed (age=0s)

问题原因 #

核心原因是认证方式不匹配：

• PostgreSQL 12版本默认的密码认证方式为md5，容器日志中pg_hba.conf line 99: "host all all all md5"也能佐证这一点。
• 但pgBouncer配置中设置了AUTH_TYPE=scram-sha-256，该认证方式是PostgreSQL 14及以上版本默认启用的，PostgreSQL 12并不默认支持此方式作为密码存储与认证逻辑，导致pgBouncer与PostgreSQL 12的认证交互不兼容，最终触发密码认证失败。

解决办法 #

有两种可行修复方案：

方案1：修改pgBouncer的认证方式为md5 #

将pgBouncer环境变量中的AUTH_TYPE=scram-sha-256改为AUTH_TYPE=md5，让pgBouncer使用与PostgreSQL 12匹配的认证方式。修改后的pgBouncer配置片段：

pgbouncer:
  image: edoburu/pgbouncer
  environment:
    - DB_USER=postgres
    - DB_PASSWORD=pass
    - DB_HOST=db
    # - DB_NAME=test
    - AUTH_TYPE=md5 
    - POOL_MODE=transaction
    - ADMIN_USERS=postgres,dbuser
  ports:
    - "5432:5432"
  depends_on:
    - db

方案2：在PostgreSQL 12中启用SCRAM-SHA-256认证 #

若希望使用SCRAM认证，需修改PostgreSQL配置：

1. 在docker-compose.yml的db服务中添加环境变量POSTGRES_INITDB_ARGS="--auth-host=scram-sha-256 --auth-local=scram-sha-256"，确保初始化时用SCRAM方式存储密码。
2. 修改pg_hba.conf将认证方式改为scram-sha-256，可通过挂载自定义pg_hba.conf到容器内/var/lib/postgresql/data/pg_hba.conf，或在初始化脚本中修改。

注意：方案2需删除现有pg_data卷后重启容器，重新初始化数据库，因为密码哈希方式初始化后无法直接修改。

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