P2
Databases AWS

pgagroal 2.1.0 on AWS User Guide

| Product: pgagroal 2.1.0 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and verifying pgagroal 2.1.0 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.

pgagroal is a high-performance connection pool for PostgreSQL, written in C. Applications connect to pgagroal instead of to PostgreSQL directly, and pgagroal keeps a pool of PostgreSQL server connections open and hands them to clients, so an application that opens and closes connections rapidly stops paying PostgreSQL's per-connection start-up cost.

The image builds pgagroal 2.1.0 from the upstream release (BSD-3-Clause; the tarball's SHA-256 is checked at build) and installs PostgreSQL 17.11 from the official PostgreSQL (PGDG) apt repository as the pooled backend, so the pooler works the moment the instance boots. The PostgreSQL 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:

  • pgagroal 2.1.0 (pgagroal.service) on port 2345, TLS required for every client, scram-sha-256 authentication
  • PostgreSQL 17.11 (postgresql@17-main.service) on 127.0.0.1:5432 only, as pgagroal's backend
  • A database appdb owned by the non-superuser role appuser, which is the account your application uses through the pooler
  • A TLS certificate and a pgagroal master key generated on your instance's first boot
  • pgagroal-cli for pool status over the local Unix socket, and pgagroal-admin for managing pooler users

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, no pgagroal master key and no pgagroal user vaults, and pgagroal cannot start until first boot has run. First boot generates the master key and unique passwords and writes them to /root/pgagroal-credentials.txt, readable by root only.
  • Separate pool and backend passwords. Your application logs in to pgagroal with the pool password. pgagroal logs in to PostgreSQL with a different backend password that never leaves the instance; the backend password is refused at the pooler, and the pool password is refused by PostgreSQL.
  • TLS required on the pooler. pgagroal's access rules contain only hostssl lines, so a client that does not use TLS is refused. The certificate and key are generated on your instance; no key ships in the image, so no two instances share one.
  • One database port, the pooler. pgagroal listens on 2345. PostgreSQL listens on 127.0.0.1:5432 only, and pgagroal's metrics, remote management and console ports are switched off. Open 2345 in the security group to your application network only.
  • Unknown users refused. Only users in pgagroal's vault can connect (allow_unknown_users = false). The postgres superuser has no password, is reachable only as the local postgres system user, and is not in the vault. pgagroal's own health-check login (pgahealth, its own empty database) is refused for every client.
  • No metadata access for the database or the pooler. PostgreSQL, pgagroal 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, master key, passwords or the pooler login check), it stops pgagroal and PostgreSQL rather than leave them half configured, and tries again on the next boot.
  • No secrets in logs. Passwords and the master key 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; SSH accepts keys only (no passwords, no root login).

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. pgagroal itself needs very little CPU or memory; size the instance for your PostgreSQL workload.

Connecting to your instance

Version Operating system SSH user
pgagroal 2.1.0 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 pgagroal 2.1.0 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) and the pooler (2345). Restrict SSH to your own IP address and 2345 to your application subnet or VPC CIDR. Never open 5432.
  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 CIDRs with your own:

SG=$(aws ec2 create-security-group --group-name pgagroal \
  --description "pgagroal + PostgreSQL" --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 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 2345 --cidr <your-app-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=pgagroal}]'

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 and the pgagroal master key, sets the passwords, starts pgagroal, checks that it can log in through it over TLS and then writes the credentials file. Show the connection details (the secret lines are left out here; read them with sudo cat /root/pgagroal-credentials.txt and keep them in your secrets manager):

sudo grep -vE '^(POOL_PASSWORD|BACKEND_PASSWORD|HEALTH_PASSWORD|PGAGROAL_MASTER_KEY)=' /root/pgagroal-credentials.txt

# pgagroal 2.1.0 + PostgreSQL 17 - generated on first boot by pgagroal-firstboot.service
# These values are unique to this instance. Store them somewhere safe.

POOL_HOST=<private-ip>
POOL_PORT=2345
POOL_DATABASE=appdb
POOL_USER=appuser
TLS_CERTIFICATE=/etc/pgagroal/tls/server.crt

# pgagroal -> PostgreSQL (127.0.0.1:5432) backend credential; applications never need it.
BACKEND_USER=appuser
# pgagroal health-check login (backend only; refused at the pooler).
HEALTH_USER=pgahealth
# Key that encrypts pgagroal's credential vaults (/var/lib/pgagroal/.pgagroal/master.key holds it for pgagroal-admin).
...

POOL_PASSWORD is the one your application uses. The commands below read it from that file at run time, so it never appears on screen. In your own sessions you can instead put it in ~/.pgpass (mode 0600), for example 127.0.0.1:2345:appdb:appuser:<POOL_PASSWORD>.

Step 3: Confirm pgagroal and PostgreSQL are running

pgagroal-cli --version
psql --version
systemctl is-active postgresql@17-main.service pgagroal.service
sudo ss -Hltn

pgagroal-cli 2.1.0
psql (PostgreSQL) 17.11 (Ubuntu 17.11-1.pgdg24.04+2)
active
active
LISTEN 0      200        127.0.0.1:5432 0.0.0.0:*
LISTEN 0      22           0.0.0.0:2345 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      4096      127.0.0.54:53   0.0.0.0:*
LISTEN 0      4096            [::]:22      [::]:*

pgagroal listens on 2345 and PostgreSQL on 127.0.0.1:5432 only; 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 pgagroal over TLS and check who you are and how you are connected:

export PGPASSWORD="$(sudo sed -n 's/^POOL_PASSWORD=//p' /root/pgagroal-credentials.txt)"
psql "host=127.0.0.1 port=2345 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 "2345".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: none)

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 that pgagroal serves from its pool:

export PGPASSWORD="$(sudo sed -n 's/^POOL_PASSWORD=//p' /root/pgagroal-credentials.txt)"
P="host=127.0.0.1 port=2345 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

Pooling mode. With TLS on, pgagroal uses its session pipeline: a client keeps its PostgreSQL connection for the whole session, so SET, temporary tables, advisory locks, LISTEN and prepared statements all behave exactly as on a direct connection. When the client disconnects, the server connection goes back to the pool for the next client.

Step 6: Watch the pool

pgagroal-cli talks to the running pooler over its local Unix socket (no network port is involved). status shows the pool and the backend server:

sudo -u pgagroal pgagroal-cli -c /etc/pgagroal/pgagroal.conf status | sed -n '/^Response:/,$p'

Response:
  ActiveConnections: 0
  MaxConnections: 90
  NumberOfServers: 1
  ServerVersion: 2.1.0
  Servers:
    primary:
      Health: UP
      Host: 127.0.0.1
      MajorVersion: 17
      Port: 5432
      Primary: Yes
      Server: primary
      State: Primary
      Status: Running
      SystemIdentifier: <system-identifier>
  Status: Running
  TotalConnections: 2

TotalConnections is the number of PostgreSQL connections pgagroal holds open; ActiveConnections is how many are lent to clients right now. MaxConnections (90) stays below PostgreSQL's max_connections (100). Use status details for per-connection detail, and ping for a quick liveness check.

Step 7: Connect from your application

pgagroal accepts TLS connections on 2345. Allow your application to reach it:

  • Security group. Add an inbound rule for 2345 from your application subnet or VPC CIDR only (never 0.0.0.0/0). Leave 5432 closed: PostgreSQL listens on localhost behind the pooler.
  • SSH tunnel (no security group change). On your workstation run ssh -N -L 2345:127.0.0.1:2345 ubuntu@<public-ip>, then connect your client to 127.0.0.1:2345.

Clients must use TLS (sslmode=require, or verify-full with this instance's certificate /etc/pgagroal/tls/server.crt). An application connection string through the pooler looks like this:

postgresql://appuser:<POOL_PASSWORD>@<private-ip>:2345/appdb?sslmode=require

pgagroal refuses a client that does not use TLS, drops a client with a wrong password during the scram-sha-256 exchange, and 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=2345 $args" -c "SELECT 1" 2>&1 || true)"
  echo "${OUT#*failed: }"
done

FATAL:  connection refused
SSL connection has been closed unexpectedly
received invalid error message

Step 8: Check the security controls

Every PostgreSQL and pgagroal unit carries the metadata block, only appuser and the health-check login have passwords, pgagroal's access rules require TLS, and its metrics, management and console ports are off:

systemctl show -p IPAddressDeny postgresql@17-main.service pgagroal.service pgagroal-firstboot.service
sudo -u postgres psql -tAc "SELECT rolname, rolsuper, rolpassword IS NOT NULL AS has_password FROM pg_authid WHERE rolcanlogin ORDER BY 1;"
sudo grep -vE '^[[:space:]]*(#|$)' /etc/pgagroal/pgagroal_hba.conf
sudo grep -E '^(host|port|tls|metrics|management|console|allow_unknown_users) ' /etc/pgagroal/pgagroal.conf

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

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

IPAddressDeny=168.63.129.16/32 fd00:ec2::254/128 169.254.169.254/32
appuser|f|t
pgahealth|f|t
postgres|t|f
hostssl   all      pgahealth  all      reject
hostssl   all      all        all      scram-sha-256
host = 0.0.0.0
port = 2345
metrics = 0
management = 0
console = 0
allow_unknown_users = false
tls = on
host = 127.0.0.1
port = 5432

The second host/port pair is the [primary] section: the PostgreSQL server pgagroal pools onto.

Maintenance

  • Add an application user. Create the role in PostgreSQL (sudo -u postgres psql, then CREATE ROLE myapp LOGIN; and \password myapp, which never puts the password on a command line). Add the same user to pgagroal's backend vault and, with the password your application will use, to the frontend vault. pgagroal-admin prompts for each password:

text sudo -u pgagroal -H pgagroal-admin -f /var/lib/pgagroal/pgagroal_users.conf -U myapp user add sudo -u pgagroal -H pgagroal-admin -f /var/lib/pgagroal/pgagroal_frontend_users.conf -U myapp user add sudo systemctl restart pgagroal.service

  • Change the pool password. sudo -u pgagroal -H pgagroal-admin -f /var/lib/pgagroal/pgagroal_frontend_users.conf -U appuser user edit, then restart pgagroal and update /root/pgagroal-credentials.txt and your secrets manager. To change the backend password, run \password appuser in sudo -u postgres psql, then user edit on /var/lib/pgagroal/pgagroal_users.conf with the same password, and restart pgagroal.
  • Pool settings. /etc/pgagroal/pgagroal.conf: max_connections (PostgreSQL connections in the pool; keep it below PostgreSQL's max_connections), idle_timeout, validation. Per-database limits go in /etc/pgagroal/pgagroal_databases.conf (see the pgagroal documentation). Apply with sudo systemctl restart pgagroal.service.
  • Point pgagroal at another PostgreSQL server. Edit the [primary] section of /etc/pgagroal/pgagroal.conf (host and port), make sure the users in the backend vault exist there with the same passwords, and restart pgagroal.
  • Your own certificate. Replace /etc/pgagroal/tls/server.crt and server.key (owner pgagroal, key mode 0600) and restart pgagroal.
  • PostgreSQL configuration. cloudimg's settings are in /etc/postgresql/17/main/conf.d/10-cloudimg.conf (localhost listener, password encryption). 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 from PGDG and Ubuntu security updates. pgagroal is built from source in /usr/local; cloudimg publishes a new version of this AMI when pgagroal releases a fix.
  • Logs. /var/log/pgagroal/pgagroal.log and journalctl -u pgagroal.service, /var/log/postgresql/postgresql-17-main.log; first boot logs to journalctl -u pgagroal-firstboot.service.

What cloudimg changed from the upstream defaults

  • pgagroal is built from the upstream 2.1.0 release (checksum verified) and its licence is shipped in /usr/local/share/doc/pgagroal/LICENSE; the compiler and build tools are removed from the image.
  • TLS is on with a per-instance certificate and every access rule is hostssl with scram-sha-256 (upstream ships TLS off and a sample rule that accepts any method); unknown users are refused (upstream default: allowed).
  • The master key, the two vaults and every password are generated on your instance's first boot instead of during the build.
  • PostgreSQL listens on localhost only and has a fresh cluster with no build-time passwords.
  • The fwupd-refresh timer is masked: EC2 instances have no firmware that the Linux Vendor Firmware Service can update.

Support

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

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