Introduction When PgBouncer connects to PostgreSQL with `sslmode=verify-full` or `verify-ca`, it validates the server's SSL certificate. If the certificate chain is incomplete, the hostname does not match, or the CA is not trusted, the connection fails and PgBouncer cannot pool connections to the database.

Symptoms - PgBouncer logs show `SSL error: certificate verify failed` - Application cannot connect through PgBouncer but direct PostgreSQL connection works - `pgbouncer -R` (reload) does not fix the issue - `openssl s_client` shows certificate chain verification failure - PgBouncer reports `LOG: C-0x55e: could not connect to server: SSL error`

Common Causes - PostgreSQL server certificate expired or not yet valid - CA certificate not in PgBouncer's `server_tls_ca_file` - Certificate CN/SAN does not match the hostname PgBouncer connects to - Intermediate CA certificate missing from the chain - PgBouncer configured with `sslmode=verify-full` but connecting by IP address

Step-by-Step Fix 1. **Test the SSL connection from the PgBouncer server": ```bash openssl s_client -connect db-host:5432 -CAfile /etc/pgbouncer/root.crt \ -servername db-host </dev/null 2>&1 | grep -E "Verify|subject|issuer" # Expected: Verify return code: 0 (ok) ```

  1. 1.**Check PgBouncer SSL configuration":
  2. 2.```ini
  3. 3.; /etc/pgbouncer/pgbouncer.ini
  4. 4.[databases]
  5. 5.production = host=db-host port=5432 dbname=production

[pgbouncer] server_tls_sslmode = verify-ca server_tls_ca_file = /etc/pgbouncer/root.crt server_tls_cert_file = /etc/pgbouncer/client.crt server_tls_key_file = /etc/pgbouncer/client.key ```

  1. 1.**Update the CA certificate bundle":
  2. 2.```bash
  3. 3.# Get the CA certificate from the PostgreSQL server
  4. 4.openssl s_client -connect db-host:5432 -showcerts </dev/null 2>/dev/null | \
  5. 5.openssl x509 -out /etc/pgbouncer/root.crt

# Or copy from the PostgreSQL server scp db-host:/etc/postgresql/ssl/root.crt /etc/pgbouncer/

# Set permissions chown pgbouncer:pgbouncer /etc/pgbouncer/root.crt chmod 644 /etc/pgbouncer/root.crt ```

  1. 1.**If connecting by IP, use verify-ca instead of verify-full":
  2. 2.```ini
  3. 3.; When connecting by IP, verify-full requires the IP in the cert SAN
  4. 4.; Use verify-ca to skip hostname verification
  5. 5.server_tls_sslmode = verify-ca
  6. 6.`
  7. 7.**Reload PgBouncer configuration":
  8. 8.```bash
  9. 9.# Reload without dropping connections
  10. 10.pgbouncer -R /etc/pgbouncer/pgbouncer.ini

# Or via admin console echo "RELOAD;" | psql -h /var/run/postgresql -U pgbouncer pgbouncer ```

Prevention - Automate certificate renewal and distribution to PgBouncer servers - Use `sslmode=verify-ca` when connecting by IP, `verify-full` when connecting by hostname - Include PgBouncer servers in certificate SAN when using `verify-full` - Test SSL connections after any certificate rotation - Monitor PgBouncer logs for SSL errors with alerting - Use configuration management (Ansible, Chef) to distribute CA certificates - Document the SSL certificate chain and renewal process