Connection pooling with PgBouncer

Before you begin

You need:

• A Databases for PostgreSQL deployment with the admin password set.
• A database user for your application, created in the UI, CLI, or API.
• PgBouncer 1.11.0 or later, which supports SCRAM authentication, installed on infrastructure that you control. If you plan to use protocol-level prepared statements in transaction pooling mode, use PgBouncer 1.21.0 or later.
• A psql client.
• Your deployment connection information:
  • Hostname and port, from the [connection strings]...
  • CA certificate, retrieved with ibmcloud cdb deployment-cacert.

Support for pgbouncer_lookup is rolling out across deployments. To confirm that your deployment has the function, connect with psql as admin and run \df public.pgbouncer_lookup. If the result is empty, your deployment receives the function with an upcoming maintenance update.

Creating a dedicated authentication user

PgBouncer runs auth_query as one designated login role, its auth_user. Create a role that is used only for this purpose. A dedicated role that owns no data and runs nothing else is the least-privilege choice.

Connect with psql as the admin user, then create the role and grant it pgbouncer_auth:

CREATE ROLE pool_auth WITH LOGIN PASSWORD '<POOL_AUTH_PASSWORD>';
GRANT pgbouncer_auth TO pool_auth;

The pgbouncer_auth role carries a single privilege: permission to run the pgbouncer_lookup function. The admin user holds pgbouncer_auth with the admin option, so you grant and revoke membership yourself. You can also create the user with ibmcloud cdb user-create if you want it to appear in your service credentials, but users created that way are members of ibm-cloud-base-user and can create users and databases, which is more than the auth user needs.

Grant pgbouncer_auth only to the dedicated auth user. Any member of the role can read the stored password verifiers of your other database users, so every additional member widens the impact of a compromised credential.

To retire an auth user, revoke its membership:

REVOKE pgbouncer_auth FROM pool_auth;

Configuring PgBouncer

The following PgBouncer settings are required when connecting to a Databases for PostgreSQL deployment:

• auth_type = scram-sha-256, because deployments store SCRAM-SHA-256 password verifiers.
• auth_query = SELECT * FROM public.pgbouncer_lookup($1), because PgBouncer's default auth_query reads pg_authid directly and your database users cannot read it.
• Server-side TLS, because deployments only accept TLS connections. Set server_tls_sslmode = verify-full, and save the certificate that you retrieved with ibmcloud cdb deployment-cacert to the path that you set in server_tls_ca_file.

PgBouncer reads the credentials of its own auth_user from auth_file, so in this configuration, userlist.txt contains only the auth_user credentials. Every other user resolves through auth_query. Restrict the file's permissions to the PgBouncer process owner, for example with mode 0600.

"pool_auth" "<POOL_AUTH_PASSWORD>"

A complete minimal configuration, where <HOSTNAME> and <PORT> come from your connection strings:

[databases]
ibmclouddb = host=<HOSTNAME> port=<PORT> dbname=ibmclouddb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_user = pool_auth
auth_query = SELECT * FROM public.pgbouncer_lookup($1)

server_tls_sslmode = verify-full
server_tls_ca_file = /etc/pgbouncer/ca-certificate.crt

pool_mode = session
max_client_conn = 200
default_pool_size = 20

Do not set auth_dbname = postgres. The lookup function is not installed in the postgres database. Leave auth_dbname unset, so the authentication query runs in the database that the client connects to. Databases that you create later include the function automatically.

For descriptions of every setting, see the PgBouncer configuration reference.

Verifying the setup

1. Confirm that the lookup resolves one of your database users. Connect with psql as admin and run:

   SELECT usename, passwd IS NOT NULL AS can_authenticate
     FROM public.pgbouncer_lookup('<APP_USERNAME>');
   

   The result is one row with can_authenticate = t. The query is written so that the password verifier is not displayed. Service-reserved users, including admin, return no rows.

2. Connect through PgBouncer as the database user:

   psql "host=127.0.0.1 port=6432 dbname=ibmclouddb user=<APP_USERNAME>"
   

   If the connection succeeds, PgBouncer is correctly authenticating users through auth_query.

3. Rotate the database user's password, then reconnect through PgBouncer with the new password:

   ibmcloud cdb user-password <DEPLOYMENT_NAME_OR_CRN> <APP_USERNAME> <NEW_PASSWORD>
   

   The new password works immediately. No PgBouncer restart, reload, or userlist.txt change is needed.

How the security model works

The pgbouncer_lookup function exposes only the information that PgBouncer requires for authentication.

• The function runs with SECURITY DEFINER and a pinned search_path, and reads pg_catalog.pg_authid on your behalf. Direct access to pg_authid and pg_shadow stays blocked.
• It returns hashed SCRAM verifiers, never plain-text passwords.
• Only roles that are allowed to log in resolve. Service-reserved users, such as admin and the internal replication and operations users, never resolve.
• If a role's VALID UNTIL timestamp is in the past, the function returns a NULL password, so authentication fails and password expiry stays enforced.
• Permission to run the function is revoked from PUBLIC and granted only to admin and to members of pgbouncer_auth.

PgBouncer's default auth_query reads pg_authid directly, which your database users cannot do. For this situation, the PgBouncer documentation recommends calling a SECURITY DEFINER function through a non-superuser instead. The pgbouncer_lookup function is that function, with the service's reserved users excluded.

Limitations and considerations

• PgBouncer does not raise your deployment's max_connections. Size default_pool_size so that the total number of server connections from all your PgBouncer instances stays within the connection limit. If you reach the limit, see increasing max connections.
• The admin user cannot authenticate through auth_query. For administrative tasks, connect as admin directly to the deployment, not through PgBouncer.
• The postgres database does not have the lookup function, so never set auth_dbname = postgres.
• In transaction pooling mode (pool_mode = transaction), session state such as SET variables, temporary tables, advisory locks, and LISTEN channels does not carry over between transactions. Protocol-level prepared statements require max_prepared_statements and PgBouncer 1.21.0 or later. For more information, see PgBouncer features.
• During an in-place major version upgrade, your deployment experiences a brief period of downtime and open connections are terminated (SQLSTATE 57P01). PgBouncer re-establishes its server connections automatically, but your applications must retry interrupted transactions and re-prepare statements.
• Read-only users cannot connect to the primary endpoint. To pool their connections, add a separate [databases] entry that points at your read-only replica.

Next steps

• Managing connections
• Getting connection strings
• Managing users, roles, and privileges
• PgBouncer configuration reference