Deployment guide

pgagroal

Overview

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 guide covers how to deploy and use pgagroal 2.1.0 on Ubuntu 24.04 on Azure, using the SecureAnyCloud Azure Marketplace image. The image is a working pooler, not a bare binary. It also installs and configures PostgreSQL 17 as the pooled backend, creates an empty application database, and puts pgagroal in front of it, so you can deploy the VM and connect an application immediately. You can also repoint pgagroal at your own PostgreSQL server or at a managed PostgreSQL service by editing one section of one file, as described below.

What you get:

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 true state of your database rather than Down
Two audit scripts shipped in the image, pgagroal-surface-audit.sh and pgagroal-account-audit.sh, so you can verify 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

Why use a pooler. 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 each backend 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.

The shape of the deployment

Port 2345, on the VM's address: pgagroal. This is the only database port on the network, and the one your applications use.
Port 5432, on 127.0.0.1 only: PostgreSQL 17, the pooled backend. It is not reachable from outside the VM.
Ports 2346 and 2347, with no listener: 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 your applications can reach the pool while those three listeners stay confined to loopback. Shipping them switched on would therefore add 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. This guide later explains how to turn metrics on deliberately, behind a Network Security Group rule.

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 pgagroal uses to reach PostgreSQL
the pgagroal frontend password for appuser, which 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 secret deserves a closer look. 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 with an entirely separate credential from its backend vault. The database password never leaves the machine, and rotating what your applications use does not touch PostgreSQL at all.

The pool refuses to start until those secrets exist. pgagroal.service carries a condition on a bootstrap marker that first boot writes only once every credential is on disk. The pool therefore never listens 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 differs from the upstream default. pgagroal refuses a client whose user is not in the credential vault instead of passing it through to PostgreSQL to fail there.

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

Prerequisites

An active Azure subscription, an SSH public key, and a VNet with a subnet in the target region
A subscription to this listing on Azure Marketplace
A Network Security Group that allows 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 very light, and the PostgreSQL backend drives sizing. 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. Open SSH and the pool port to your own network only:

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:pgagroal-ubuntu-24-04:default:latest
az vm create \
  --resource-group my-resource-group \
  --name my-pgagroal-vm \
  --image sac1647283583153:pgagroal-ubuntu-24-04:default:latest \
  --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 show when it is done.

Connect and confirm the service is healthy

SSH to the VM as azureuser. Confirm the pgagroal version, 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/sac/data/pgagroal-firstboot.done && echo "first boot: complete" test -f /var/lib/sac/data/pgagroal-bootstrap-ready && echo "pool released: yes"

Check what is listening. The pool listens on the VM's address, and PostgreSQL listens on loopback only:

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

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

Retrieve the per-VM credentials

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

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

Read the file to get the values. Your applications use POOL_USER and POOL_PASSWORD:

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 where they appear in plain text.

Connect an application through the pool

Connect with psql, or with any PostgreSQL driver, to port 2345. Your client does not change. 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 the query 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;"

The pool refuses a wrong password 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:

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

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

The status command 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. It probes the backend every 30 seconds with the unprivileged pgahealth role.

Now watch the pooling happen. Open a series of separate client connections, then check 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 that pgagroal actually opened against PostgreSQL. Twelve clients served by one pooled connection is the whole point of the product.

Verify the security posture

Two audit scripts ship in the image, so you can verify the security posture on your own VM.

The first script checks what is reachable. It confirms that 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 script enumerates every login role in PostgreSQL and every user in both pgagroal vaults at run time, rather than from a fixed list. It then probes each account with an empty password, its own name, and the passwords attackers try first. Any successful login counts as a failure:

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

Both scripts end in an _OK line and exit non-zero if anything is wrong, so you can wire them 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

Point pgagroal at your own PostgreSQL server

The bundled PostgreSQL lets the image work 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 the section at your server, add the backend credential to the vault, and restart the service. Replace the values in angle brackets with your own:

# 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

Keep two points in mind. First, the health check role pgahealth does not exist on your server. Either create a role and a database of that name on it, or set health_check = off in /etc/pgagroal/pgagroal.conf. Second, if you no longer need the bundled database, stop and disable it with sudo systemctl disable --now postgresql.service.

Add another application user

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

# 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 the users that the pool currently knows 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

Each entry below lists the setting, its shipped value and what it does:

max_connections, shipped as 100: the ceiling on backend connections that pgagroal opens. Keep it below the backend's own max_connections.
idle_timeout, shipped as 600: the number of seconds an unused pooled connection is kept before it is closed.
validation, shipped as background: idle connections are checked periodically, so a stale connection is not handed to a client.
background_interval, shipped as 300: the number of seconds between those background validation scans.
pipeline, shipped as auto: the connection handling strategy. The auto value selects the performance pipeline.
allow_unknown_users, shipped as false: the pool refuses users that are absent from the vault.

Most settings take effect after 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. To use them, open the port only to your monitoring subnet, then enable the listener:

# 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 that your applications use to reach the pool.

Managing the services

Check that the services are enabled and active:

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

To flush pooled connections without a restart, for example after you change a role's password on the backend, run:

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, so use it with care. Stop, start and restart the pool with 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. Run:

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

To rotate the backend password, change it in PostgreSQL and in the backend vault together. Then restart the pool so that no pooled connection keeps using the old password:

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

Update /root/pgagroal-credentials.txt so that the file stays accurate.

Troubleshooting

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

test -f /var/lib/sac/data/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 service that stays active once it has succeeded. If it reports inactive and the marker is missing, 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. This is expected. Run it as the service account, as every example in this guide does.

The pool reports the backend as down. Confirm that 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 edit the configuration. pgagroal rejects any configuration file that contains a non-ASCII byte and reports that the file contains binary data. If you pasted text from a document, a typographic dash or quote is the usual cause:

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

Support

SecureAnyCloud provides 24/7 support for this image. Contact sales@secureanycloud.com with your Azure subscription ID and the VM name.

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

Deploy on Azure

Launch pgagroal 2.1.0 on Ubuntu 24.04 on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com

Deploy on Azure