buildwithalex.com | Running PgBouncer in Docker

TLDR: clone the github repo and run docker compose-up

Let’s explore setting up pgbouncer on your local system without the need to install various dependencies. I prefer running such tools in Docker for a hassle-free experience. This approach offers several advantages: you can experiment with setup and configuration without affecting your system, and it allows you to easily deploy the software with your preferred vendor.

This guide won’t delve into different use cases for pgbouncer or discuss alternatives like pgpool2, odyssey, or pgcat. Likewise, we won’t explore why connection pooling is beneficial in PostgreSQL; there are numerous articles available that cover those topics extensively.

Needless to say you will need to have docker and docker-compose installed to follow along.

On a very basic level we create a docker-compose.yaml that runs a PG server and a PgBouncer next to each other, allowing us to connect to the PG either directly, or through PgBouncer.

PG

Let’s start with getting a PG server running. We’re using the ‘latest’ postgres Docker image to always pull the most recent PG version. If you’d like to use a specific version simply replace the ‘latest’ with the supported version tag. You can also replace the hard-coded password for the postgres role. If you change the password, make sure you replace it in the different configuration files further down accordingly.

docker-compose.yaml

  
version: '1'
services:

  postgres:
    image: postgres:latest
    volumes:
      - pg_data:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=pgpassword
      - POSTGRES_USER=postgres
      - POSTGRES_DB=postgres
    ports:
      - 5433:5432

volumes:
  pg_data:
    driver: local

Save this docker-compose.yaml in a directory and then execute

  docker-compose up

It’ll start pulling the PG docker image and then you should be able to connect to the PG by running

  psql -h 127.0.0.1 -p 5433 -U postgres

and using the ‘POSTGRES_PASSWORD’ from the ‘docker-compose.yaml’. If you at any point need to start over, run

  docker-compose down --volumes

to tear down the containers as well as the volumes. If you make a configuration change only, you can restart the container by running

  docker-compose restart

Auth Setup

We want PgBouncer to dynamically authenticate (non-superuser) users. You can configure PgBouncer to use a custom authentication query to make this work. Apart from configuring PgBouncer this way, we also need to run a couple of SQL queries in PG to support this. Luckily, the official Docker PG image executes arbitrary SQL statements found at ‘/docker-entrypoint-initdb.d’ during the initdb process.

setup.sql

  
CREATE USER pgbouncer WITH PASSWORD 'pgbouncer_password';
CREATE USER pgbouncer_stats WITH PASSWORD 'pgbouncer_stats_password';

CREATE SCHEMA pgbouncer AUTHORIZATION pgbouncer;

CREATE OR REPLACE FUNCTION pgbouncer.get_auth(pb_username TEXT)
RETURNS TABLE (username TEXT, passwd TEXT) AS
$$
BEGIN
    RAISE WARNING 'pgbouncer auth request: %', pb_username;

    RETURN QUERY
    SELECT rolname::TEXT, rolpassword::TEXT
      FROM pg_authid
      WHERE NOT rolsuper
        AND rolname = pb_username;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

REVOKE ALL ON FUNCTION pgbouncer.get_auth(pb_username TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(pb_username TEXT) TO pgbouncer;

This creates two users ‘pgbouncer’ & ‘pg_bouncer_stats’ together with a new schema ‘pgbouncer’ in which the authentication function ‘get_auth’ lives.

We only give the ‘pgbouncer’ user the minimal privileges necessary to perform the authentication look-up.

Run ‘docker-compose down —volumes’ to tear down the existing installation so that initdb is run again next time. Use the following docker-compose.yaml, note we’ve added the ‘setup.sql’ file.

docker-compose.yaml

  
version: '1'
services:

  postgres:
    image: postgres:latest
    volumes:
      - pg_data:/var/lib/postgresql/data
      - ./conf/setup.sql:/docker-entrypoint-initdb.d/setup.sql
    environment:
      - POSTGRES_PASSWORD=pgpassword
      - POSTGRES_USER=postgres
      - POSTGRES_DB=postgres
    ports:
      - 5433:5432

Run ‘docker-compose up’, then connect as above with the ‘postgres’ user and run ‘\df pgbouncer.get_auth’:

  
postgres=# \df pgbouncer.get_auth
                                   List of functions
  Schema   |   Name   |         Result data type          | Argument data types | Type
-----------+----------+-----------------------------------+---------------------+------
 pgbouncer | get_auth | TABLE(username text, passwd text) | pb_username text    | func
(1 row)

This means the setup.sql was run successfully!

PgBouncer

PgBouncer has lots of configuration options which would be impossible to cover here.

Our basic configuration should do the following:

listen on port 6432
use SCRAM-SHA-256 authentication for client connections
use an auth query to fetch passwords from pg_shadow
set up a user that is allowed to log into the pgbouncer admin console
run in transaction mode

Basic configuration

Before we configure PgBouncer to use the ‘pgbouncer.get_auth’ function, let us look at the more basic option of giving PgBouncer a list of users and passwords to use for authentication. This is done by supplying a ‘userlist.txt’ file to PgBouncer and configuring ‘auth_file’ in ‘pgbouncer.ini’ to use it.

pgbouncer.ini

  
[databases]
* = host=postgres
[pgbouncer]
listen_addr = 0.0.0.0 
listen_port = 6432
auth_file = /opt/bitnami/pgbouncer/conf/userlist.txt
auth_type = scram-sha-256
stats_users = pgbouncer_stats
logfile = /dev/stdout
default_pool_size = 50
max_client_conn = 5000
pool_mode = transaction
min_pool_size = 0
query_wait_timeout = 120.0

userlist.txt

  
"postgres" "pgpassword"
"pgbouncer" "pgbouncer_password"
"pgbouncer_stats" "pgbouncer_stats_password"

Note these passwords align with the passwords used above when standing up PG and in the setup.sql file.

Next, we add PgBouncer to docker-compose.yml:

docker-compose.yaml

  
version: '1'
services:

  postgres:
    image: postgres:latest
    volumes:
      - pg_data:/var/lib/postgresql/data
      - ./conf/setup.sql:/docker-entrypoint-initdb.d/setup.sql
    environment:
      - POSTGRES_PASSWORD=pgpassword
      - POSTGRES_USER=postgres
      - POSTGRES_DB=postgres
    ports:
      - 5433:5432

  pgbouncer:
    image: bitnami/pgbouncer:latest
    ports:
      - 6432:6432
    environment:
      - POSTGRESQL_HOST=postgres
      - POSTGRESQL_PASSWORD=pgpassword
    volumes:
      - './conf:/bitnami/pgbouncer/conf/'
    depends_on:
      - postgres

volumes:
  pg_data:
    driver: local

After running ‘docker-compose up’ you can now try to connect to PG via PgBouncer using the postgres role. Any role not listed in ‘userlist.txt’ will fail to connect with a SASL (Simple Authentication and Security Layer) connection error. Try yourself using:

  PGPASSWORD=pgpassword psql -p 6432 -h localhost -U postgres

You can still connect directly to PG with

  PGPASSWORD=pgpassword psql -p 5433 -h localhost -U postgres

Using Custom Auth

Manually maintaining the allowed roles with passwords in ‘userlist.txt’ might be ok for some scenarios. But what if we don’t know all the roles and passwords beforehand? Or if the list is dynamic? That’s where the ‘pgbouncer.get_auth()’ function comes in that we created as part of ‘setup.sql’.

We update entry in the ‘[databases]’ section to:

  *=host=postgres dbname=postgres auth_user=pgbouncer

This means PgBouncer is going to use the ‘pgbouncer’ to connect to database ‘postgres’ when trying to authenticate roles. We also add the following to the ‘pgbouncer’ pool configuration:

  auth_query = SELECT username, passwd FROM pgbouncer.get_auth($1)

This means PgBouncer will use this query to authenticate a given role.

The complete ‘pgbouncer.ini’ looks like this:

pgbouncer.ini

  
[databases]
*=host=postgres dbname=postgres auth_user=pgbouncer

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_query = SELECT username, passwd FROM pgbouncer.get_auth($1)
auth_file = /opt/bitnami/pgbouncer/conf/userlist.txt
stats_users = pgbouncer_stats
logfile = /dev/stdout
default_pool_size = 50
max_client_conn = 5000
pool_mode = transaction
min_pool_size = 0
query_wait_timeout = 120.0

Note we still need the ‘pgbouncer’ role in ‘userlist.txt’, otherwise PgBouncer does not know how to connect with role ‘pgbouncer’ to run the authentication query. At this point you could also create a second, separate, pool for superusers and e.g. choose not to pool superuser connections at all!

Run ‘docker-compose restart’, then create a new role ‘solobuilds’ in PG with LOGIN privileges and a password:

  CREATE ROLE solobuilds LOGIN password 'test';

Try to connect using this role to PG via PgBouncer:

  PGPASSWORD=test psql -p 6432 -h localhost -U solobuilds -d postgres

  pgbouncer_docker-postgres-1   | 2024-03-11 13:54:13.651 UTC [36] WARNING:  pgbouncer auth request: solobuilds

PgBouncer database

You can connect to the special administration database ‘pgbouncer’ using

  psql -p 6432 -h localhost -U pgbouncer_stats -d pgbouncer

Then running ‘SHOW HELP;’ which will output

  
NOTICE:  Console usage
DETAIL:
        SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
        SHOW PEERS|PEER_POOLS
        SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM|STATE
        SHOW DNS_HOSTS|DNS_ZONES
        SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
        SET key = arg
        RELOAD
        PAUSE [<db>]
        RESUME [<db>]
        DISABLE <db>
        ENABLE <db>
        RECONNECT [<db>]
        KILL <db>
        SUSPEND
        SHUTDOWN
        WAIT_CLOSE [<db>]
SHOW

Have a look at some of the commands to see what they do! Our ‘pgbouncer.ini’ only contains a ‘stats_users’ entry for read-only queries. You can also define ‘admin_users’ that have more access. For the rest of the configuration options, take a look at the PgBouncer documentation.

Running PgBouncer in Docker

Alexander | Sunday, February 4, 2024

PG

Auth Setup

PgBouncer

Basic configuration

Using Custom Auth

PgBouncer database