pgagroal 2.1.0

Option A: AWS Marketplace console

• Open the SecureAnyCloud Hardened Adminer & Database Admin and Backup Tools AMIs listing on AWS Marketplace. Choose View purchase options, then Subscribe. One subscription covers every version in the listing.
• When the subscription is active, choose Launch your software.
• 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.
• Pick an instance type and your key pair. m5.large is recommended, and the software charge is $0.04 per vCPU per hour.
• Review the 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.
• Choose your VPC and subnet. Leave the metadata defaults, because the image already requires IMDSv2. Then choose Launch.

Option B: AWS CLI

Run this command on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page. Replace 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, because the AMI already requires IMDSv2.

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. It sets the passwords, starts pgagroal, checks that it can log in through pgagroal 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). ...

Your application uses POOL_PASSWORD. 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>.

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 and 127.0.0.54 is the operating system's local DNS stub.

Connect as appuser through pgagroal over TLS. Then check which user 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)

Create a table, write to it in a transaction, read it back and drop it. 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.

pgagroal-cli talks to the running pooler over its local Unix socket, so no network port is involved. The status command 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.

pgagroal accepts TLS connections on 2345. Use one of these two methods to let your application 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, because 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. Set 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. It drops a client with a wrong password during the scram-sha-256 exchange. 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=<set-at-first-boot> 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

Run these commands to confirm four things. Every PostgreSQL and pgagroal unit carries the metadata block. Only appuser and the health-check login have passwords. pgagroal's access rules require TLS. 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 and port pair is the [primary] section, which is the PostgreSQL server that pgagroal pools onto.