Databases AWS

PostgreSQL 17 + PgBouncer on AWS User Guide

| Product: PostgreSQL 17 + PgBouncer on Ubuntu 24.04 on AWS

Overview

This guide covers launching and verifying PostgreSQL 17 + PgBouncer on Ubuntu 24.04 from cloudimg on Amazon EC2. It is one version of the cloudimg Hardened Adminer & Database Admin and Backup Tools AMIs listing on AWS Marketplace; you pick it as the software version when you subscribe and launch. The other tools in the listing are described on the listing's guide hub.

PgBouncer is a lightweight connection pooler for PostgreSQL. Applications connect to PgBouncer instead of to PostgreSQL directly, and PgBouncer multiplexes many short-lived client connections onto a small pool of PostgreSQL server sessions. In transaction pooling mode a server session is lent to a client only for the length of one transaction, so a connection-heavy application (web workers, serverless functions, many microservices) stays fast without one PostgreSQL backend process per client.

The image installs PostgreSQL 17.11 and PgBouncer 1.26.0 from the official PostgreSQL (PGDG) apt repository. The packages are not held, so apt upgrade keeps delivering PGDG minor (security) releases, and unattended security upgrades stay on for the operating system.

What is on the image:

  • PostgreSQL 17.11 (postgresql@17-main.service) on 127.0.0.1:5432
  • PgBouncer 1.26.0 (pgbouncer.service) on 127.0.0.1:6432 in transaction pooling mode, scram-sha-256 authentication, TLS required for every client
  • A database appdb owned by the non-superuser role appuser, which is the account your application uses through the pooler
  • A PgBouncer admin console user pgbadmin for SHOW POOLS, SHOW STATS and the other admin commands
  • A TLS certificate generated on your instance's first boot, used by PgBouncer and PostgreSQL (TLS 1.2 minimum)

Default user: ubuntu · Architecture: x86_64 · Root volume: 8 GiB gp3

Security posture

  • No default password. The image ships with no password on any database role and no PgBouncer user list, and PgBouncer cannot start until first boot has run. First boot generates unique passwords for appuser and pgbadmin and writes them to /root/postgresql-pgbouncer-credentials.txt, readable by root only. The postgres superuser has no password and is reachable only as the local postgres system user (peer authentication); it is not in PgBouncer's user list, so it cannot log in through the pooler.
  • Per-instance TLS certificate. First boot creates this instance's own private key and self-signed certificate. No key ships in the image, so no two instances share one.
  • TLS required on the pooler. PgBouncer refuses any client that does not use TLS (client_tls_sslmode = require).
  • Localhost only. PostgreSQL and PgBouncer listen on 127.0.0.1, and the listing's security group opens SSH (22) only. Reach the pooler through an SSH tunnel, or open it to your VPC as described in Step 7.
  • Remote PostgreSQL connections require TLS. pg_hba.conf accepts non-loopback clients only over hostssl with scram-sha-256; there is no plain-text remote rule and no trust rule.
  • No metadata access for the database or the pooler. A database user can make PostgreSQL open outbound connections (for example COPY ... FROM PROGRAM as a superuser, or dblink), and PgBouncer connects to whatever hosts its [databases] section names, so PostgreSQL, PgBouncer and the first-boot service are all blocked from reaching the instance metadata service (169.254.169.254 and fd00:ec2::254). Amazon Time Sync (169.254.169.123) still works.
  • IMDSv2 required. The AMI is registered with ImdsSupport v2.0, so instances launched from it refuse token-less (IMDSv1) metadata requests.
  • Fails closed. If first boot cannot finish (certificate, passwords or the pooler login check), it stops PgBouncer and PostgreSQL rather than leave them half configured, and tries again on the next boot.
  • No secrets in logs. Passwords are never passed on a command line, so they do not appear in /var/log/auth.log or the journal.
  • Per-instance identity. SSH host keys and the machine ID are generated fresh on every instance.

Prerequisites

  1. An AWS account and permission to launch EC2 instances and create security groups
  2. An EC2 key pair in the Region you launch in
  3. A VPC subnet, with a public IP on the instance if you connect over the internet

Recommended instance type: m5.large. PgBouncer itself needs very little CPU or memory; size the instance for your PostgreSQL workload.

Connecting to your instance

Version Operating system SSH user
PostgreSQL 17 + PgBouncer on Ubuntu 24.04 Ubuntu 24.04 LTS ubuntu

Step 1: Launch the instance

Option A: AWS Marketplace console

  1. Open the cloudimg Hardened Adminer & Database Admin and Backup Tools AMIs listing on AWS Marketplace and choose View purchase options, then Subscribe. One subscription covers every version in the listing.
  2. Once the subscription is active, choose Launch your software.
  3. Choose One-click launch from AWS Marketplace (or Launch from EC2 Console for the full launch wizard). In Version, choose PostgreSQL 17 + PgBouncer on Ubuntu 24.04, then choose your Region.
  4. Pick an instance type (m5.large recommended; the software charge is $0.04 per vCPU per hour) and your key pair.
  5. Security group: the listing's default group allows SSH (22) only. Restrict it to your own IP address. Do not open 6432 or 5432 to the internet.
  6. Choose your VPC and subnet, leave the metadata defaults (the image already requires IMDSv2), then choose Launch.

Option B: AWS CLI

Run this on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page, and the VPC, subnet, key pair and CIDR with your own:

SG=$(aws ec2 create-security-group --group-name pgbouncer \
  --description "PostgreSQL + PgBouncer" --vpc-id vpc-0123456789abcdef0 \
  --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 22 --cidr <your-mgmt-cidr>
aws ec2 run-instances \
  --image-id ami-0123456789abcdef0 \
  --instance-type m5.large \
  --key-name my-key-pair \
  --security-group-ids "$SG" \
  --subnet-id subnet-0123456789abcdef0 \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=pgbouncer}]'

You do not need --metadata-options: the AMI already makes IMDSv2 required.

Step 2: Connect and read your credentials

Connect as ubuntu with the key pair you chose:

ssh -i ~/.ssh/my-key-pair.pem ubuntu@<public-ip>

First boot finishes within a minute of launch. It creates this instance's TLS key and certificate, turns TLS on, sets the appuser and pgbadmin passwords, starts PgBouncer, checks that it can log in through it and then writes the credentials file. Show the connection details (the two password lines are left out here; read them with sudo cat /root/postgresql-pgbouncer-credentials.txt and keep them in your secrets manager):

sudo grep -vE '^(APP_PASSWORD|PGBOUNCER_ADMIN_PASSWORD)=' /root/postgresql-pgbouncer-credentials.txt

# PostgreSQL 17 + PgBouncer - generated on first boot by postgresql-pgbouncer-firstboot.service
# These passwords are unique to this instance. Store them somewhere safe.

APP_USERNAME=appuser
APP_DATABASE=appdb
PGBOUNCER_HOST=127.0.0.1
PGBOUNCER_PORT=6432
PGBOUNCER_ADMIN_USER=pgbadmin
POSTGRES_PORT=5432
PRIVATE_IP=<private-ip>
TLS_CERTIFICATE=/etc/postgresql/17/main/cloudimg-server.crt

# Connect THROUGH PgBouncer (transaction pooling, TLS required) on the instance:
#   psql "host=127.0.0.1 port=6432 dbname=appdb user=appuser sslmode=require"
...

The commands below read the passwords from that file at run time, so they never appear on screen. In your own sessions you can instead put them in ~/.pgpass (mode 0600), for example 127.0.0.1:6432:appdb:appuser:<APP_PASSWORD>.

Step 3: Confirm PostgreSQL and PgBouncer are running

psql --version
pgbouncer --version | head -1
systemctl is-active postgresql@17-main.service pgbouncer.service
sudo ss -Hltn

psql (PostgreSQL) 17.11 (Ubuntu 17.11-1.pgdg24.04+2)
PgBouncer 1.26.0
active
active
LISTEN 0      200        127.0.0.1:5432 0.0.0.0:*
LISTEN 0      4096         0.0.0.0:22   0.0.0.0:*
LISTEN 0      4096   127.0.0.53%lo:53   0.0.0.0:*
LISTEN 0      128        127.0.0.1:6432 0.0.0.0:*
LISTEN 0      4096      127.0.0.54:53   0.0.0.0:*
LISTEN 0      4096            [::]:22      [::]:*

PgBouncer listens on 127.0.0.1:6432 and PostgreSQL on 127.0.0.1:5432; port 53 on 127.0.0.53/127.0.0.54 is the operating system's local DNS stub.

Step 4: Connect through the pooler

Connect as appuser through PgBouncer over TLS and check who you are and how you are connected:

export PGPASSWORD="$(sudo sed -n 's/^APP_PASSWORD=//p' /root/postgresql-pgbouncer-credentials.txt)"
psql "host=127.0.0.1 port=6432 dbname=appdb user=appuser sslmode=require" \
  -c "SELECT current_user, current_database(), current_setting('server_version') AS server_version;" \
  -c '\conninfo'

 current_user | current_database |           server_version
--------------+------------------+------------------------------------
 appuser      | appdb            | 17.11 (Ubuntu 17.11-1.pgdg24.04+2)
(1 row)

You are connected to database "appdb" as user "appuser" on host "127.0.0.1" at port "6432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)

Step 5: Run transactions through the pool

Create a table, write to it in a transaction, read it back and drop it again. Each psql call is a separate client connection; PgBouncer lends each one a server session for the length of its transaction:

export PGPASSWORD="$(sudo sed -n 's/^APP_PASSWORD=//p' /root/postgresql-pgbouncer-credentials.txt)"
P="host=127.0.0.1 port=6432 dbname=appdb user=appuser sslmode=require"
psql "$P" -c "CREATE TABLE IF NOT EXISTS orders (id serial PRIMARY KEY, item text NOT NULL, created timestamptz DEFAULT now());"
psql "$P" -c "BEGIN; INSERT INTO orders (item) VALUES ('widget'), ('gadget'); COMMIT;"
psql "$P" -c "SELECT id, item FROM orders ORDER BY id;"
psql "$P" -c "DROP TABLE orders;"

CREATE TABLE
BEGIN
INSERT 0 2
COMMIT
 id |  item
----+--------
  1 | widget
  2 | gadget
(2 rows)

DROP TABLE

Transaction pooling and your application. Because a client gets a server session only for one transaction, session state does not carry over between transactions: use SET LOCAL instead of SET, avoid session-level advisory locks, LISTEN and WITH HOLD cursors, and keep temporary tables inside one transaction. Protocol-level prepared statements work (PgBouncer 1.26 tracks them; max_prepared_statements defaults to 200). If an application needs full session semantics, change pool_mode to session in /etc/pgbouncer/pgbouncer.ini (see Maintenance).

Step 6: Watch the pooler from the admin console

pgbadmin connects to PgBouncer's virtual pgbouncer database, which accepts admin commands such as SHOW DATABASES, SHOW POOLS, SHOW STATS, SHOW CLIENTS and RELOAD:

export PGPASSWORD="$(sudo sed -n 's/^PGBOUNCER_ADMIN_PASSWORD=//p' /root/postgresql-pgbouncer-credentials.txt)"
A="host=127.0.0.1 port=6432 dbname=pgbouncer user=pgbadmin sslmode=require"
psql "$A" -c "SHOW POOLS;"
psql "$A" -tAc "SHOW CONFIG;" | grep -E '^(pool_mode|auth_type|client_tls_sslmode|listen_addr|listen_port|max_client_conn|default_pool_size)\|' | cut -d'|' -f1,2

 database  |   user    | cl_active | cl_waiting | cl_active_cancel_req | cl_waiting_cancel_req | sv_active | sv_active_cancel | sv_being_canceled | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us |  pool_mode  | load_balance_hosts
-----------+-----------+-----------+------------+----------------------+-----------------------+-----------+------------------+-------------------+---------+---------+-----------+----------+---------+------------+-------------+--------------------
 appdb     | appuser   |         0 |          0 |                    0 |                     0 |         0 |                0 |                 0 |       1 |       0 |         0 |        0 |       0 |          0 | transaction |
 pgbouncer | pgbouncer |         1 |          0 |                    0 |                     0 |         0 |                0 |                 0 |       0 |       0 |         0 |        0 |       0 |          0 | statement   |
(2 rows)

auth_type|scram-sha-256
client_tls_sslmode|require
default_pool_size|20
listen_addr|127.0.0.1
listen_port|6432
max_client_conn|200
pool_mode|transaction

sv_idle is the number of PostgreSQL server sessions PgBouncer holds open for reuse; cl_waiting above zero for long means the pool is too small for your load (raise default_pool_size).

Step 7: Connect from your application

PgBouncer and PostgreSQL accept connections on localhost only. Two ways to reach the pooler:

  • SSH tunnel (no change to the instance). On your workstation run ssh -N -L 6432:127.0.0.1:6432 ubuntu@<public-ip>, then connect your client to 127.0.0.1:6432 as appuser, database appdb, with sslmode=require.
  • Open it to your VPC. Set PgBouncer's listener to all interfaces (or to this instance's private IP), restart it, then add an inbound rule for 6432 to the instance's security group from your application subnet or VPC CIDR only (never 0.0.0.0/0). Clients must use TLS (sslmode=require, or verify-full with this instance's certificate /etc/postgresql/17/main/cloudimg-server.crt) and the appuser password. PostgreSQL itself stays on localhost behind the pooler.
sudo sed -i 's/^listen_addr = 127.0.0.1$/listen_addr = */' /etc/pgbouncer/pgbouncer.ini
sudo systemctl restart pgbouncer.service

An application connection string through the pooler looks like this:

postgresql://appuser:<APP_PASSWORD>@<private-ip>:6432/appdb?sslmode=require

PgBouncer refuses a client that does not use TLS, and a wrong password is refused by scram-sha-256; the postgres superuser cannot log in through the pooler at all:

for args in "dbname=appdb user=appuser sslmode=disable" "dbname=appdb user=appuser sslmode=require" "dbname=postgres user=postgres sslmode=require"; do
  OUT="$(PGPASSWORD=wrong-password psql "host=127.0.0.1 port=6432 $args" -c "SELECT 1" 2>&1 || true)"
  echo "${OUT#*failed: }"
done

FATAL:  SSL required
FATAL:  SASL authentication failed
FATAL:  SASL authentication failed

Step 8: Check the security controls

Every PostgreSQL and PgBouncer unit carries the metadata block, appuser is not a superuser, and the postgres superuser has no password:

systemctl show -p IPAddressDeny postgresql@17-main.service pgbouncer.service postgresql-pgbouncer-firstboot.service
sudo -u postgres psql -tAc "SELECT rolname, rolsuper, rolpassword IS NOT NULL AS has_password FROM pg_authid WHERE rolname IN ('appuser','postgres') ORDER BY 1;"

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128
appuser|f|t
postgres|t|f

Maintenance

  • Change the appuser password. Run sudo -u postgres psql and type \password appuser at the prompt (it asks twice and never puts the password on a command line). Then put the same password in /etc/pgbouncer/userlist.txt (the line "appuser" "<password>"), run sudo systemctl reload pgbouncer.service, and update /root/postgresql-pgbouncer-credentials.txt and your secrets manager.
  • Change the pgbadmin password. Edit its line in /etc/pgbouncer/userlist.txt, then sudo systemctl reload pgbouncer.service. pgbadmin exists only in PgBouncer, not in PostgreSQL.
  • Add an application role. Create it in PostgreSQL (CREATE ROLE ... LOGIN, then \password it), add a matching "role" "password" line to /etc/pgbouncer/userlist.txt and reload PgBouncer. The * = host=127.0.0.1 port=5432 entry in [databases] routes any database name you create.
  • Pool settings. /etc/pgbouncer/pgbouncer.ini: pool_mode, default_pool_size (server sessions per database/user pair), max_client_conn (client connections PgBouncer accepts). Keep default_pool_size times the number of database/user pairs below PostgreSQL's max_connections (100 by default). Apply with sudo systemctl reload pgbouncer.service (a change to listen_addr or listen_port needs a restart).
  • PostgreSQL configuration. cloudimg's settings are in /etc/postgresql/17/main/conf.d/10-cloudimg.conf (listener, password encryption) and 20-cloudimg-tls.conf (this instance's certificate). Apply changes with sudo systemctl restart postgresql@17-main.service.
  • Backups. Take EBS snapshots or pg_dump backups regularly (sudo -u postgres pg_dump appdb > appdb.sql).
  • Updates. sudo apt update && sudo apt upgrade installs PostgreSQL 17 minor releases, PgBouncer releases from PGDG and Ubuntu security updates.
  • Logs. journalctl -u pgbouncer.service, /var/log/postgresql/postgresql-17-main.log and journalctl -u postgresql@17-main.service; first boot logs to journalctl -u postgresql-pgbouncer-firstboot.service.

Support

cloudimg supports this image by email at support@cloudimg.co.uk. Include the instance ID, the version (PostgreSQL 17 + PgBouncer on Ubuntu 24.04) and what you were doing.

PostgreSQL is a trademark of the PostgreSQL Community Association of Canada. PgBouncer is developed by the PgBouncer project. Ubuntu is a trademark of Canonical Ltd. cloudimg is not affiliated with or endorsed by these organisations.