P2
Databases Azure

pgagroal 2.1.0 on Ubuntu 24.04 on Azure User Guide

| Product: pgagroal 2.1.0 on Ubuntu 24.04 on Azure

Overview

This guide covers the deployment and use of pgagroal 2.1.0 on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images.

pgagroal is a high performance connection pool for PostgreSQL, written in C. It sits between your applications and a PostgreSQL server and multiplexes many short lived client connections onto a small set of long lived backend connections that it keeps warm and reuses.

This matters because a PostgreSQL connection is not cheap. Every client that connects causes the server to fork a backend process and build its own catalogue caches, and every one of those backends holds memory for as long as the client stays connected. Applications that open a connection per request, run many workers, or scale out horizontally, spend a large share of their database time paying that cost over and over. pgagroal pays it once and hands out ready connections in microseconds.

This image is a working pooler, not a bare binary. A pooler on its own is useless without a database behind it, so the image also installs and configures PostgreSQL 17 as the pooled backend, creates an empty application database, and puts pgagroal in front of it. You can deploy the VM and connect an application to it immediately. You can equally repoint pgagroal at your own PostgreSQL server or at a managed PostgreSQL service by editing one section of one file, which is documented below.

The shape of the deployment

Port Listens on What it is
2345 the VM's address pgagroal. This is the only database port on the network, and the one your applications use.
5432 127.0.0.1 only PostgreSQL 17, the pooled backend. Not reachable from outside the VM.
2346 / 2347 nothing Prometheus metrics and remote management. Both ship disabled.

Why metrics and remote management ship disabled. pgagroal binds its metrics, remote management and console listeners to the same address as the pool itself. There is no configuration in which the pool is reachable by your applications while those three are confined to loopback, so shipping them switched on would mean shipping extra unauthenticated surface on a public port. Local administration with pgagroal-cli goes over a Unix domain socket and needs none of them, so nothing is lost by default. Turning metrics on deliberately, behind a Network Security Group rule, is documented later in this guide.

Security by design

Five secrets, all generated on your VM's first boot, none baked into the image:

  • the PostgreSQL postgres superuser password

  • the PostgreSQL appuser role password, which is what pgagroal uses to reach PostgreSQL

  • the pgagroal frontend password for appuser, which is what your application uses to reach pgagroal

  • the password for pgahealth, a role with no privileges beyond its own empty database, used only by the pool's periodic backend health check

  • the pgagroal master key, which encrypts the credential vaults at rest

The third of those is the one worth pausing on. The password your clients use is deliberately not the password pgagroal uses to reach PostgreSQL. pgagroal authenticates the client against its frontend vault, then opens or reuses a backend connection using an entirely separate credential from its backend vault. The database password never leaves the machine, and rotating what your applications use does not mean touching PostgreSQL at all.

The pool refuses to start until those secrets exist. pgagroal.service carries a condition on a bootstrap marker that first boot only writes once every credential is on disk, so there is no window in which the pool is listening with a half configured vault.

PostgreSQL is bound to loopback and has no trust rule. The pg_hba.conf shipped in the image uses peer for the local socket and scram-sha-256 for loopback TCP. The passwordless trust line that stock PostgreSQL installations so often keep does not exist here, and there is no 0.0.0.0/0 rule.

Unknown users are rejected at the pool. allow_unknown_users is set to false, which is not the upstream default. A client whose user is not in the credential vault is refused by pgagroal instead of being passed through to PostgreSQL to fail there.

The database ships empty. appdb contains no tables, and there are exactly three login roles and three databases, none of them carrying sample data.

What is included:

  • pgagroal 2.1.0, built from the pinned upstream release tarball with its SHA-256 verified, installed under /usr/local and running under systemd as pgagroal.service

  • PostgreSQL 17 from the official PostgreSQL APT repository as the pooled backend, bound to loopback

  • An empty application database appdb owned by a non superuser role appuser

  • Periodic backend health checking, so pgagroal-cli status reports the truth about your database rather than Down

  • Two audit scripts shipped in the image, pgagroal-surface-audit.sh and pgagroal-account-audit.sh, so you can re-prove the security posture yourself at any time

  • No C compiler in the image. The toolchain used to build pgagroal is purged before capture and every shipped binary is checked to still resolve its libraries

  • Unattended security upgrades left enabled so the appliance keeps receiving patches

Prerequisites

  • Active Azure subscription, an SSH public key, and a VNet plus subnet in the target region

  • Subscription to this listing on Azure Marketplace

  • A Network Security Group allowing TCP 22 for administration and TCP 2345 from the systems that will use the pool. You do not need to open 5432; PostgreSQL is bound to loopback and is reached through the pool.

Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) for development, evaluation and light workloads. pgagroal itself is extremely light; the sizing driver is the PostgreSQL backend, so for production workloads choose a size to suit your database, such as Standard_D4s_v5 or larger, and raise max_connections in /etc/pgagroal/pgagroal.conf to match.

Deploy the virtual machine

Create the VM from the image, opening SSH and the pool port to your own network only:

az vm create \
  --resource-group my-resource-group \
  --name my-pgagroal-vm \
  --image <marketplace-image-urn> \
  --size Standard_B2s \
  --storage-sku StandardSSD_LRS \
  --admin-username azureuser \
  --ssh-key-values ~/.ssh/id_rsa.pub \
  --public-ip-sku Standard \
  --location eastus

# Allow SSH and the pool port from your own address range only
az vm open-port --resource-group my-resource-group --name my-pgagroal-vm --port 22 --priority 1001
az vm open-port --resource-group my-resource-group --name my-pgagroal-vm --port 2345 --priority 1002

First boot generates this VM's five secrets and then releases the pool. It completes in a few seconds; the checks below will show you when it is done.

Connect and confirm the service is healthy

SSH to the VM as azureuser, then confirm the version pin, the running services, and that first boot completed:

/usr/local/bin/pgagroal --version
systemctl is-active postgresql.service
systemctl is-active pgagroal.service

Confirm that first boot finished and released the pool:

test -f /var/lib/cloudimg/pgagroal-firstboot.done  && echo "first boot: complete"
test -f /var/lib/cloudimg/pgagroal-bootstrap-ready && echo "pool released: yes"

Now look at what is actually listening. The pool is on the VM's address; PostgreSQL is on loopback only:

ss -Hltn | awk '{print $4}' | sort -u

And confirm that metrics, remote management and the console are all switched off in the shipped configuration:

grep -E '^(metrics|management|console)[[:space:]]*=' /etc/pgagroal/pgagroal.conf

Terminal showing pgagroal reporting version 2.1.0, postgresql.service and pgagroal.service both active, first boot done yes and pooler released yes, then the listening socket table with pgagroal on port 2345 on both the VM address 10.0.0.12 and loopback while postgres on port 5432 is marked LOOPBACK ONLY, and finally the shipped configuration showing metrics equals 0, management equals 0 and console equals 0

Retrieve the per VM credentials

Every secret on this machine was generated during its own first boot and written to a file only root can read:

sudo stat -c '%n perms=%a owner=%U:%G' /root/pgagroal-credentials.txt

Read the file to get the values. POOL_USER and POOL_PASSWORD are what your applications use:

sudo grep -E '^(pool_port|backend_port|database|POOL_USER|POSTGRES_USER|BACKEND_USER|HEALTH_USER)=' /root/pgagroal-credentials.txt

To see the passwords themselves, read the whole file with sudo cat /root/pgagroal-credentials.txt. Store the values in your secret manager; this file is the only place they appear in plain text.

Connect an application through the pool

Connect with psql, or with any PostgreSQL driver, to port 2345. Nothing about your client changes; pgagroal speaks the native PostgreSQL wire protocol, so pgjdbc, Npgsql, psycopg, pq and every other driver work unmodified.

POOL_PW="$(sudo awk -F= '$1=="POOL_PASSWORD"{print substr($0, index($0,"=")+1); exit}' /root/pgagroal-credentials.txt)"
PGPASSWORD="$POOL_PW" psql -w -h 127.0.0.1 -p 2345 -U appuser -d appdb </dev/null \
  -c "SELECT version();"

The query ran on PostgreSQL and the result came back through the pool. Confirm which database and role you are connected as, and that PostgreSQL served it on its own port 5432:

POOL_PW="$(sudo awk -F= '$1=="POOL_PASSWORD"{print substr($0, index($0,"=")+1); exit}' /root/pgagroal-credentials.txt)"
PGPASSWORD="$POOL_PW" psql -w -h 127.0.0.1 -p 2345 -U appuser -d appdb </dev/null \
  -c "SELECT current_database() AS database, current_user AS role, inet_server_port() AS served_by_postgres_on;"

A wrong password is refused by the pool before it ever reaches PostgreSQL:

PGPASSWORD='not-the-right-password' psql -w -h 127.0.0.1 -p 2345 -U appuser -d appdb </dev/null \
  -c "SELECT 1" 2>/dev/null || echo "rejected at the pool, as expected"

From another machine, use the VM's address instead of 127.0.0.1, with port 2345 open in your Network Security Group. Your application's connection string is simply:

postgresql://appuser:<POOL_PASSWORD>@<vm-address>:2345/appdb

Terminal showing a psql session connected to the pooler on 10.0.0.12 port 2345 as appuser, the SELECT version banner returning PostgreSQL 17.10 on Ubuntu, a result row confirming database appdb and role appuser served by postgres on port 5432, a backend process id proving the query executed on PostgreSQL itself, and a final line showing a wrong password was rejected as expected

Inspect the pool

pgagroal-cli talks to the running daemon over a Unix domain socket. It declines to run as root, so run it as the pgagroal service account:

sudo -u pgagroal env HOME=/var/lib/pgagroal \
  /usr/local/bin/pgagroal-cli -c /etc/pgagroal/pgagroal.conf ping

status shows the pool and the health of the backend behind it:

sudo -u pgagroal env HOME=/var/lib/pgagroal \
  /usr/local/bin/pgagroal-cli -c /etc/pgagroal/pgagroal.conf status

Health: UP and Status: Running come from the periodic health check, which probes the backend every 30 seconds using the unprivileged pgahealth role.

Now watch the pooling actually happen. Open a series of separate client connections, then look at how many backend connections were needed to serve them:

POOL_PW="$(sudo awk -F= '$1=="POOL_PASSWORD"{print substr($0, index($0,"=")+1); exit}' /root/pgagroal-credentials.txt)"
for i in $(seq 1 12); do
  PGPASSWORD="$POOL_PW" psql -w -h 127.0.0.1 -p 2345 -U appuser -d appdb -tAc "SELECT $i;" </dev/null >/dev/null
done
echo "12 client connections served"
sudo -u pgagroal env HOME=/var/lib/pgagroal \
  /usr/local/bin/pgagroal-cli -c /etc/pgagroal/pgagroal.conf status | grep -E 'TotalConnections|ActiveConnections'

TotalConnections is the number of backend connections pgagroal actually opened against PostgreSQL. Twelve clients served by one pooled connection is the whole point of the product.

Terminal showing twelve client connections served through the pooler, pgagroal-cli ping returning Status true, pgagroal-cli status reporting MaxConnections 100 with the primary server at 127.0.0.1 port 5432 showing Health UP, Primary Yes and Status Running, then the pooled backend connection for database appdb and user appuser in state Free, and a closing line stating that twelve client connections were served by one pooled backend connection

Verify the security posture

Two audit scripts ship in the image so you can prove the posture on your own VM rather than take this guide's word for it.

The first checks what is reachable. It confirms the pool answers on the VM's address, that PostgreSQL is loopback only, that the metrics and management ports have no listener, and that live connections to those ports on the routable address are refused:

sudo /usr/local/sbin/pgagroal-surface-audit.sh

The second enumerates every login role in PostgreSQL and every user in both pgagroal vaults at run time, rather than from a fixed list, then probes each of them with an empty password, its own name, and the passwords attackers try first. Any success is a failure:

sudo /usr/local/sbin/pgagroal-account-audit.sh

Both end in an _OK line and exit non-zero if anything is wrong, so they can be wired into your own monitoring. You can also confirm directly that no trust rule exists in the backend's client authentication file:

grep -vE '^[[:space:]]*(#|$)' /etc/postgresql/17/main/pg_hba.conf

Terminal showing the surface audit confirming the pooler on port 2345 reachable, PostgreSQL on port 5432 loopback only, ports 2346 and 2347 with no listener, live connections to 2346, 2347 and 5432 on the routable address all refused while 2345 is open, ending SURFACE_AUDIT_OK, then the account audit reporting roles enumerated from pg_authid and vault users enumerated from both vaults with unknown users rejected, ending ACCOUNT_AUDIT_OK with 51 probes and no weak credentials, and finally the credentials file at permissions 600 owned by root with five secrets generated on first boot and zero trust rules in pg_hba.conf

Point pgagroal at your own PostgreSQL server

The bundled PostgreSQL exists so the image works standalone. To pool a database you already run, whether on another VM, in Azure Database for PostgreSQL, or anywhere else, change the [primary] section of the pool configuration and give pgagroal a credential for that server.

The [primary] section is the last two lines of /etc/pgagroal/pgagroal.conf:

sed -n '/^\[primary\]/,$p' /etc/pgagroal/pgagroal.conf

Point it at your server, add the backend credential to the vault, and restart. Replace the placeholders with your own values:

# Point the pool at your own PostgreSQL server
sudo sed -i 's@^host = 127.0.0.1@host = <your-domain>@' /etc/pgagroal/pgagroal.conf

# Give pgagroal a credential for that server, and your clients a separate one
sudo -u pgagroal env HOME=/var/lib/pgagroal /usr/local/bin/pgagroal-admin \
  -f /var/lib/pgagroal/pgagroal_users.conf user add -U myappuser -P '<new-password>'
sudo -u pgagroal env HOME=/var/lib/pgagroal /usr/local/bin/pgagroal-admin \
  -f /var/lib/pgagroal/pgagroal_frontend_users.conf user add -U myappuser -P '<new-password>'

sudo systemctl restart pgagroal.service

Two things to keep in mind when you do this. The health check role pgahealth will not exist on your server, so either create a role and a database of that name on it, or set health_check = off in /etc/pgagroal/pgagroal.conf. And if you no longer need the bundled database, you can stop and disable it with sudo systemctl disable --now postgresql.service.

Add another application user

Every user your clients connect as needs an entry in both vaults: the backend vault holds the password pgagroal uses against PostgreSQL, and the frontend vault holds the password the client presents to pgagroal. Because allow_unknown_users is false, a user missing from the vault is refused at the pool.

# 1. Create the role in PostgreSQL
sudo -u postgres psql -w -d postgres -c \
  "CREATE ROLE reportsvc LOGIN NOSUPERUSER PASSWORD '<new-password>';"

# 2. Tell pgagroal how to reach PostgreSQL as that role
sudo -u pgagroal env HOME=/var/lib/pgagroal /usr/local/bin/pgagroal-admin \
  -f /var/lib/pgagroal/pgagroal_users.conf user add -U reportsvc -P '<new-password>'

# 3. Set the separate password your clients will present to pgagroal
sudo -u pgagroal env HOME=/var/lib/pgagroal /usr/local/bin/pgagroal-admin \
  -f /var/lib/pgagroal/pgagroal_frontend_users.conf user add -U reportsvc -P '<new-password>'

sudo systemctl reload pgagroal.service

List who is currently known to the pool at any time:

sudo -u pgagroal env HOME=/var/lib/pgagroal /usr/local/bin/pgagroal-admin \
  -f /var/lib/pgagroal/pgagroal_frontend_users.conf user ls

Tune the pool

The settings you are most likely to change live in the [pgagroal] section of /etc/pgagroal/pgagroal.conf:

grep -E '^(max_connections|idle_timeout|validation|background_interval|pipeline|allow_unknown_users|authentication_timeout)[[:space:]]*=' /etc/pgagroal/pgagroal.conf
Setting Shipped What it does
max_connections 100 Ceiling on backend connections pgagroal will open. Keep it below the backend's own max_connections.
idle_timeout 600 Seconds an unused pooled connection is kept before being closed.
validation background Idle connections are checked periodically, so a stale one is not handed to a client.
background_interval 300 Seconds between those background validation scans.
pipeline auto Connection handling strategy. auto selects the performance pipeline.
allow_unknown_users false Users absent from the vault are refused at the pool.

Changes to most settings take effect with sudo systemctl reload pgagroal.service; changing ports or max_connections needs a restart.

Enable Prometheus metrics

Metrics ship disabled because pgagroal binds them to the same address as the pool. If you want them, open the port only to your monitoring subnet and then enable it:

# Restrict scraping to your monitoring network FIRST
az network nsg rule create \
  --resource-group my-resource-group --nsg-name my-pgagroal-nsg \
  --name allow-pgagroal-metrics --priority 1003 \
  --source-address-prefixes <your-mgmt-cidr> \
  --destination-port-ranges 2346 --access Allow --protocol Tcp

# Then enable the metrics listener
sudo sed -i 's/^metrics = 0/metrics = 2346/' /etc/pgagroal/pgagroal.conf
sudo systemctl restart pgagroal.service
curl -s http://127.0.0.1:2346/metrics | head -20

Do not enable metrics without the Network Security Group rule. The listener is unauthenticated and it binds to the same address your applications reach the pool on.

Managing the services

systemctl is-enabled postgresql.service pgagroal.service pgagroal-firstboot.service
systemctl is-active postgresql.service pgagroal.service

To flush pooled connections without restarting, for example after changing a role's password on the backend:

sudo -u pgagroal env HOME=/var/lib/pgagroal \
  /usr/local/bin/pgagroal-cli -c /etc/pgagroal/pgagroal.conf flush idle

flush gracefully waits for active connections to finish; flush all closes everything immediately and should be used with care. Stopping, starting and restarting are the usual sudo systemctl stop|start|restart pgagroal.service.

Rotating credentials

The password your clients use lives only in pgagroal's frontend vault, so rotating it does not touch PostgreSQL at all:

sudo -u pgagroal env HOME=/var/lib/pgagroal /usr/local/bin/pgagroal-admin \
  -f /var/lib/pgagroal/pgagroal_frontend_users.conf user edit -U appuser -P '<new-password>'
sudo systemctl reload pgagroal.service

Rotating the backend password means changing it in PostgreSQL and in the backend vault together, then flushing pooled connections so none are left using the old one:

sudo -u postgres psql -w -d postgres -c "ALTER ROLE appuser PASSWORD '<new-password>';"
sudo -u pgagroal env HOME=/var/lib/pgagroal /usr/local/bin/pgagroal-admin \
  -f /var/lib/pgagroal/pgagroal_users.conf user edit -U appuser -P '<new-password>'
sudo systemctl restart pgagroal.service

Remember to update /root/pgagroal-credentials.txt so the file continues to reflect reality.

Troubleshooting

The pool is not listening on 2345. Check whether first boot completed. pgagroal.service deliberately will not start until the bootstrap marker exists:

test -f /var/lib/cloudimg/pgagroal-bootstrap-ready && echo "marker present" || echo "marker MISSING, first boot has not finished"
printf 'pgagroal-firstboot.service: %s\n' "$(systemctl is-active pgagroal-firstboot.service || true)"

A healthy VM reports marker present and active. The unit is a one shot that stays reported as active once it has succeeded, so inactive together with a missing marker means first boot did not complete. Its output is in the journal under pgagroal-firstboot.

Clients are refused even with the right password. The user must exist in the frontend vault, because allow_unknown_users is false:

sudo -u pgagroal env HOME=/var/lib/pgagroal /usr/local/bin/pgagroal-admin \
  -f /var/lib/pgagroal/pgagroal_frontend_users.conf user ls

pgagroal-cli says the root account is not allowed. That is expected. Run it as the service account, as every example in this guide does.

The pool reports the backend as down. Confirm PostgreSQL is running and check the pool's own log:

systemctl is-active postgresql.service
sudo tail -20 /var/log/pgagroal/pgagroal.log

pgagroal refuses to start after you edited the configuration. pgagroal rejects any configuration file containing a non ASCII byte, reporting that it contains binary data. If you pasted text from a document, a typographic dash or quote is the usual culprit:

LC_ALL=C grep -nP '[^\x00-\x7F]' /etc/pgagroal/pgagroal.conf || echo "configuration is clean ASCII"

Support

cloudimg provides 24/7 support for this image. Contact support@cloudimg.co.uk with your Azure subscription ID and the VM name.

Upstream project documentation is at https://pgagroal.github.io/. pgagroal is licensed under the BSD 3-Clause licence.