Run PgBouncer with SCRAM Auth Using Docker

If you’re running PostgreSQL in production, PgBouncer is almost mandatory for efficient connection pooling.

This setup runs PgBouncer in Docker with SCRAM-SHA-256 authentication, using the exact password hash stored in Postgres — no weak MD5 hacks 🚫

---

🎯 Goal

• ⚡ Add connection pooling for PostgreSQL
• 🔐 Use SCRAM-SHA-256 authentication (secure by default)
• 🐳 Run PgBouncer in Docker
• 🌐 Connect services via Docker network
• 🔒 Expose PgBouncer locally only

---

🧾 Bash Script

bash
#!/usr/bin/env bash
set -euo pipefail

PGB_INI_PATH="/etc/pgbouncer/pgbouncer.ini"
PGB_USERLIST_PATH="/etc/pgbouncer/userlist.txt"

PG_HOST="postgres"
PG_PORT="5432"
PG_DB="postgres"
PG_USER="admin"

LISTEN_ADDR="0.0.0.0"
LISTEN_PORT="6432"

PGB_CONTAINER="pgbouncer"
PGB_IMAGE="edoburu/pgbouncer:v1.24.1-p1"

mkdir -p "$(dirname "$PGB_INI_PATH")" "$(dirname "$PGB_USERLIST_PATH")"

SCRAM_LINE="$(docker exec -i "$PG_HOST" psql -U "$PG_USER" -d "$PG_DB" -tA -c \
"SELECT '\"' || rolname || '\" \"' || rolpassword || '\"'
 FROM pg_authid
 WHERE rolname = '$PG_USER';" || true)"

if [[ -z "$SCRAM_LINE" ]]; then
  echo "ERROR: failed to fetch SCRAM hash from Postgres"
  exit 1
fi

if ! grep -q 'SCRAM-SHA-256' <<<"$SCRAM_LINE"; then
  echo "ERROR: Postgres password is not SCRAM-SHA-256"
  exit 1
fi

cat > "$PGB_INI_PATH" <<EOF
[databases]
$PG_DB = host=$PG_HOST port=$PG_PORT dbname=$PG_DB

[pgbouncer]
listen_addr = $LISTEN_ADDR
listen_port = $LISTEN_PORT
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 5
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
ignore_startup_parameters = extra_float_digits
EOF

printf "%s\n" "$SCRAM_LINE" > "$PGB_USERLIST_PATH"

docker run -d \
  --name "$PGB_CONTAINER" \
  --network app-net \
  -p 127.0.0.1:${LISTEN_PORT}:${LISTEN_PORT} \
  -v "$(realpath "$PGB_INI_PATH"):/etc/pgbouncer/pgbouncer.ini:ro" \
  -v "$(realpath "$PGB_USERLIST_PATH"):/etc/pgbouncer/userlist.txt:ro" \
  "$PGB_IMAGE"

echo "PgBouncer is up 🚀"