Deployment guide

PostgreSQL 18 pgAudit

Overview

pgAudit is the PostgreSQL Audit Extension. It is the control named in PCI-DSS, SOC 2 and HIPAA guidance for PostgreSQL. You cannot simply switch it on in a running database. It must be loaded into the server process through shared_preload_libraries, which means editing the configuration and restarting the server. On a production database, that is a change window. On this image, it is already done.

This guide covers the deployment and use of PostgreSQL 18 with pgAudit on Ubuntu 24.04 on Azure using SecureAnyCloud Azure Marketplace images. The image is a PostgreSQL database that arrives already auditing.

pgAudit gives you a trail that PostgreSQL's own log_statement cannot produce. Entries are structured and classified. They name the class of the statement, the command, the object type and the fully qualified object, in a format you can parse and ship to a SIEM.

What is audited as shipped. The image ships a deliberate policy rather than "everything" or "nothing":

Session auditing is on for ddl and role. This covers every schema change, and every GRANT, REVOKE, CREATE ROLE and ALTER ROLE. It is the high-value, low-volume half of an audit trail. It answers "who changed the shape of this database, and who was given access to it".

Read and write auditing is off globally and opt-in per object. On a busy transactional database, auditing every SELECT and INSERT is the flood, not the trail. Instead, the image ships a NOLOGIN role called auditor, named in pgaudit.role. Grant a privilege on a table to that role, and pgAudit records OBJECT class entries for exactly that table and exactly those privileges. Nothing else changes.

Bound parameter values are never written. pgaudit.log_parameter is off, so a statement is recorded but the values passed to it are not. An audit trail that copies the data it is auditing into a log file is a second place for that data to leak from.

Catalog lookups are not audited. pgaudit.log_catalog is off, so your driver's introspection queries and psql tab completion do not drown the trail.

Every VM gets its own database. The captured image contains the PostgreSQL binaries, pgAudit and this appliance's configuration, but no database cluster at all. On first boot, each VM runs its own initdb, so no two deployments share a cluster identity, a certificate or a password. There is no default, blank or shared credential at any point, not even for a moment.

What you get:

PostgreSQL 18 from the official PGDG repository, running under systemd as postgresql@18-main.service
pgAudit 18.0, preloaded through shared_preload_libraries and enabled with CREATE EXTENSION in the default auditdb database
A shipped audit policy of ddl, role session auditing plus per-object read and write auditing through the auditor role, with an audited_ledger example table already granted so the mechanism is live on first boot
A dedicated audit log directory at /var/log/postgresql/audit, mode 0700, with files in mode 0600, rotated by PostgreSQL every 64 MB or every day
An hourly retention sweep that enforces both a 30-day age cap and a hard 2 GiB total size cap, so a long-running VM cannot fill its OS disk with audit data
A per-VM database cluster, a per-VM TLS certificate and two per-VM passwords (the postgres superuser and the auditapp application role), generated on first boot into a credentials file that only root can read
Loopback-only networking by default, so the only port reachable from the network is TCP 22
pg_hba.conf requiring TLS and scram-sha-256 on every TCP connection, so opening the database later is a single safe step
Unattended security upgrades left enabled so the appliance keeps receiving patches

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 from your administration network. Nothing else needs to be opened, because PostgreSQL is bound to loopback in the shipped image.

Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) for evaluation and light workloads. Auditing costs write bandwidth on the log volume rather than memory. For production, choose a size that matches your query load, such as Standard_D4s_v5, and give /var/log room for the retention window you configure.

Deploy the virtual machine

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

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:postgresql-pgaudit-ubuntu-24-04:default:latest
az vm create \
  --resource-group my-rg \
  --name pgaudit-1 \
  --image sac1647283583153:postgresql-pgaudit-ubuntu-24-04:default:latest \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard

First boot creates the database cluster, mints a TLS certificate and generates the passwords. Give it a minute before connecting. You can watch it complete with systemctl status postgresql-pgaudit-firstboot.service.

Retrieve your per-VM credentials

On the first boot, the VM initialises its own cluster and generates its own passwords and TLS certificate. Connect over SSH and read the credentials file, which only root can read. The command below prints everything except the two passwords, so it is safe to paste into a ticket:

sudo stat -c '%a %U %G %n' /root/postgresql-pgaudit-credentials.txt
sudo grep -E '^(postgres\.(host|port|role|database|sslmode)|auditapp\.role|pgaudit\.)' /root/postgresql-pgaudit-credentials.txt

The file is mode 600 and owned by root. The keys it reports look like this:

600 root root /root/postgresql-pgaudit-credentials.txt postgres.host=127.0.0.1 postgres.port=5432 postgres.role=postgres postgres.database=auditdb postgres.sslmode=require auditapp.role=auditapp pgaudit.version=18.0 pgaudit.log=ddl,role pgaudit.role=auditor pgaudit.logdir=/var/log/postgresql/audit

Run sudo cat /root/postgresql-pgaudit-credentials.txt to see the whole file, including the two passwords and a written summary of what is audited and where the trail is kept. Nothing is baked into the image, because every deployed VM generates its own credentials.

Confirm the service is healthy and pgAudit is loaded

The check that matters is not whether the pgAudit package is installed, but whether the library is actually loaded into the server. SHOW shared_preload_libraries answers that question:

sudo systemctl is-active postgresql@18-main.service
sudo systemctl is-active sac-pgaudit-logretention.timer
sudo -u postgres psql -tAc 'SHOW shared_preload_libraries;'
sudo -u postgres psql -d auditdb -c "SELECT extname, extversion FROM pg_extension ORDER BY extname;"
sudo ss -tlnH | awk '{print $4}' | sort -u

Both units report active. shared_preload_libraries reports pgaudit, and the extension is listed at version 18.0. In the listening set, PostgreSQL appears only on 127.0.0.1:5432:

active active pgaudit extname | extversion ---------+------------ pgaudit | 18.0 plpgsql | 1.0 (2 rows) 0.0.0.0:22 127.0.0.1:5432 127.0.0.53%lo:53 127.0.0.54:53 [::]:22

PostgreSQL binds 127.0.0.1 only. The addresses on port 53 are the local systemd resolver stub, also on loopback. The only entries reachable from outside the VM are the two on port 22.

Read the audit policy the image ships

Every setting that governs the trail is readable from pg_settings:

sudo -u postgres psql -x -c "SELECT name, setting FROM pg_settings WHERE name IN ('pgaudit.log','pgaudit.role','pgaudit.log_catalog','pgaudit.log_parameter','log_connections','log_directory','log_rotation_size','log_rotation_age') ORDER BY name;"

-[ RECORD 5 ]---------------------- name | pgaudit.log setting | ddl, role -[ RECORD 6 ]---------------------- name | pgaudit.log_catalog setting | off -[ RECORD 7 ]---------------------- name | pgaudit.log_parameter setting | off -[ RECORD 8 ]---------------------- name | pgaudit.role setting | auditor

log_rotation_size is reported in kilobytes (65536 is 64 MB) and log_rotation_age in minutes (1440 is one day).

Make an audited change and read the trail

This is the whole product in one exchange. Create a table, then look at what the trail recorded:

sudo -u postgres psql -d auditdb -c "DROP TABLE IF EXISTS demo_customers;"
sudo -u postgres psql -d auditdb -c "CREATE TABLE demo_customers (id bigserial PRIMARY KEY, name text NOT NULL);" sleep 2
sudo grep 'AUDIT: SESSION' /var/log/postgresql/audit/pgaudit-*.log | tail -3

The DROP TABLE IF EXISTS on the first line makes the example repeatable. It is itself a DDL event, so it also appears in the trail on the second run.

One CREATE TABLE produces three entries, because it makes three catalogue changes:

2026-09-20 19:03:26.118 UTC [6846] postgres@auditdb/psql LOG: AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.demo_customers,"CREATE TABLE demo_customers (id bigserial PRIMARY KEY, name text NOT NULL)",<not logged> 2026-09-20 19:03:26.118 UTC [6846] postgres@auditdb/psql LOG: AUDIT: SESSION,1,1,DDL,CREATE INDEX,INDEX,public.demo_customers_pkey,"CREATE TABLE demo_customers (id bigserial PRIMARY KEY, name text NOT NULL)",<not logged> 2026-09-20 19:03:26.118 UTC [6846] postgres@auditdb/psql LOG: AUDIT: SESSION,1,1,DDL,ALTER SEQUENCE,SEQUENCE,public.demo_customers_id_seq,"CREATE TABLE demo_customers (id bigserial PRIMARY KEY, name text NOT NULL)",<not logged>

Read an entry from left to right. First comes the class, SESSION, then the statement and substatement identifiers. Next come the class again (DDL), the command (CREATE TABLE), the object type (TABLE), the fully qualified object (public.demo_customers) and the statement text. The last field is the parameter field.

The parameter field reads <not logged> because pgaudit.log_parameter is off. That setting keeps your data out of your logs.

Audit reads and writes on a table you choose

Global read and write auditing is off, because on a real workload it produces far more log than anyone reads. Instead, grant the privileges you care about, on the specific table you care about, to the auditor role:

sudo -u postgres psql -d auditdb <<'SQL' GRANT SELECT, INSERT ON demo_customers TO auditor; INSERT INTO demo_customers (name) VALUES ('Example Ltd'); SELECT count(*) FROM demo_customers; SQL sleep 2
sudo grep 'AUDIT: OBJECT' /var/log/postgresql/audit/pgaudit-*.log | tail -2

The insert and the select are now both in the trail. They are classified OBJECT rather than SESSION, and each entry names the class of access (WRITE or READ) and the table:

2026-09-20 19:03:39.636 UTC [6925] postgres@auditdb/psql LOG: AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.demo_customers,INSERT INTO demo_customers (name) VALUES ('Example Ltd'),<not logged> 2026-09-20 19:03:39.640 UTC [6925] postgres@auditdb/psql LOG: AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.demo_customers,SELECT count(*) FROM demo_customers,<not logged>

The grant is the switch. REVOKE SELECT, INSERT ON demo_customers FROM auditor; turns that table's read and write auditing off again and leaves everything else alone.

The image already ships one table wired up this way, audited_ledger in auditdb. The mechanism is live and demonstrable before you create anything of your own.

Turn on global write or read auditing

If your policy requires every write to be recorded, make a one-line change and reload. No restart is needed, because you can change pgaudit.log at runtime:

sudo -u postgres psql -c "ALTER SYSTEM SET pgaudit.log = 'ddl, role, write';"
sudo systemctl reload postgresql@18-main.service </dev/null sleep 2
sudo -u postgres psql -tAc 'SHOW pgaudit.log;'
sudo -u postgres psql -c "ALTER SYSTEM RESET pgaudit.log;"
sudo systemctl reload postgresql@18-main.service </dev/null sleep 2
sudo -u postgres psql -tAc 'SHOW pgaudit.log;'

ALTER SYSTEM ddl, role, write ALTER SYSTEM ddl, role

The block above sets the policy, shows that it took effect, then resets it to the shipped value. You can run it as a demonstration without changing your machine. Drop the last three commands to make the change permanent.

Valid classes are read, write, function, role, ddl, misc, misc_set and all.

Before you enable read or all, work out your log volume and raise the retention caps in the next section to match. Those two classes can multiply the size of the trail by orders of magnitude.

Where the trail lives, and why it cannot fill the disk

Audit output goes to its own directory rather than mixing with the ordinary server log. Exactly one thing rotates these files, and your trail is never interleaved with unrelated noise:

sudo cat /etc/default/sac-pgaudit-logretention
sudo /usr/local/sbin/sac-pgaudit-logretention.sh
sudo ls -lh /var/log/postgresql/audit/

PGAUDIT_LOG_DIR=/var/log/postgresql/audit PGAUDIT_RETAIN_DAYS=30 PGAUDIT_MAX_BYTES=2147483648 PGAUDIT_RETENTION_OK dir=/var/log/postgresql/audit removed=0 files=1 bytes=42520 cap=2147483648 retain_days=30 total 48K -rw------- 1 postgres postgres 42K Sep 20 19:55 pgaudit-2026-09-20_193321.log

Two mechanisms do two different jobs:

PostgreSQL's logging collector rotates. A new file is started every 64 MB or every day, whichever comes first. log_truncate_on_rotation is off, so a rotation can never silently overwrite an existing trail.

A brand-new VM has a single file, as above. The collector starts another when that file reaches 64 MB or when the day rolls over. The listing grows to one file per rotation until the sweep below starts retiring the oldest.

sac-pgaudit-logretention.timer deletes. Rotation on its own never frees a byte, which is how audit logging fills disks. The sweep runs hourly and enforces both caps in the file above. Anything older than PGAUDIT_RETAIN_DAYS goes. If the directory total still exceeds PGAUDIT_MAX_BYTES, the oldest files are removed until it does not. The sweep never removes the file PostgreSQL is currently writing.

Edit /etc/default/sac-pgaudit-logretention to match your own retention policy, then run sudo systemctl restart sac-pgaudit-logretention.service to apply it immediately.

If your policy requires a longer window than the disk can hold, ship the files off the machine on a schedule, to Azure Blob Storage or to a syslog or SIEM collector. Leave the caps where they are rather than raising them and hoping.

Connect to the database

On the VM itself, you can connect as the postgres superuser with no password through the local unix socket:

sudo -u postgres psql -d auditdb -c "SELECT version();"

To connect over TCP, use the per-VM password from the credentials file. Every TCP connection, loopback included, must be TLS encrypted, so sslmode=require is not optional. Reading the password out of the file rather than typing it keeps it out of your shell history:

sudo sh -c 'PGPASSWORD=$(sed -n "s/^auditapp\.password=//p" /root/postgresql-pgaudit-credentials.txt) psql "host=127.0.0.1 port=5432 dbname=auditdb user=auditapp sslmode=require" -c "SELECT ssl, version AS tls_version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();"'

ssl | tls_version | cipher -----+-------------+------------------------ t | TLSv1.3 | TLS_AES_256_GCM_SHA384 (1 row)

The pg_stat_ssl view confirms that the connection is encrypted and reports the negotiated TLS version and cipher. Use the auditapp role for your application and keep the postgres superuser for administration.

Open the database to your application network

The shipped image binds PostgreSQL to loopback only, so the database is not reachable from the network until you decide otherwise. That is the right default for an appliance whose job is to hold an audit trail.

Exposing the database is a deliberate two-step change. pg_hba.conf already requires TLS and the per-VM password, so no third step is needed to make it safe.

# 1. on the VM: bind to all interfaces and restart
sudo sed -i "s/^listen_addresses.*/listen_addresses = '*'/" /etc/postgresql/18/main/postgresql.conf
sudo systemctl restart postgresql@18-main.service
# 2. from your workstation: open 5432 to YOUR subnet only, never to the internet az network nsg rule create --resource-group my-rg --nsg-name <your-nsg> \
  --name allow-postgres --priority 900 --access Allow --protocol Tcp \
  --destination-port-ranges 5432 --source-address-prefixes <your-app-subnet-cidr>

Then tighten the 0.0.0.0/0 line in /etc/postgresql/18/main/pg_hba.conf to the same CIDR. Remote clients must still present the per-VM password over TLS. There is no plaintext TCP path on this image, on loopback or off it. Because log_connections is on, every remote connection is also recorded in the trail.

Security posture

No known credential, ever. The image ships no database cluster, so there is no role, no password and no certificate to discover. Both passwords are generated on the customer VM's first boot. They are set through psql standard input, so they never appear on a command line or in the journal. They are written to /root/postgresql-pgaudit-credentials.txt at mode 0600. Neither password carries an expiry, so nothing locks you out on a date you did not choose.

Per-VM cluster identity. Each VM runs its own initdb. Two VMs from this image therefore have different cluster system identifiers, TLS certificates, machine IDs, SSH host keys and passwords.

TLS on every TCP connection. pg_hba.conf carries only hostssl ... scram-sha-256 rules. Local administration stays passwordless through the unix socket.

Loopback only by default. The only port this image exposes to the network is TCP 22. PostgreSQL 5432 is bound to 127.0.0.1 until you change it.

The trail is not world-readable. The audit directory is 0700 postgres and every file in it is 0600. Reading the trail requires root or the postgres account.

The trail cannot copy your data. pgaudit.log_parameter is off, so bound parameter values are recorded as <not logged>.

SSH hardening. Root login is disabled outright (PermitRootLogin no, not prohibit-password, which still permits key-based root login). Password authentication is off.

Kernel module baseline. The image ships a /etc/modprobe.d/dirtyfrag.conf baseline that disables the esp4, esp6, ipcomp, ipcomp4, ipcomp6 and rxrpc modules. If you intend to terminate IPsec on this VM, remove that file.

To rotate a password later, run sudo -u postgres psql -c "ALTER ROLE auditapp PASSWORD 'your-new-password'" and update the credentials file to match. That statement is itself a ROLE class event, so the rotation appears in the audit trail.

Operations

Manage the service through systemd. On Debian and Ubuntu, postgresql.service is a wrapper. The unit that runs your database is postgresql@18-main.service, so check that one:

sudo systemctl show postgresql@18-main.service -p ActiveState,SubState,MainPID
sudo journalctl -u postgresql@18-main.service -p warning --no-pager -n 20

MainPID=1545 ActiveState=active SubState=running -- No entries --

Ordinary server messages go to the audit log directory along with the audit entries, because pgAudit writes through PostgreSQL's own logger. The first-boot log is at /var/log/sac/sac-firstboot.log. The data directory lives under /var/lib/postgresql/18/main, and the audit trail is at /var/log/postgresql/audit.

To ship the trail off the machine, point a log agent at /var/log/postgresql/audit/pgaudit-*.log. The AUDIT: entries are comma-separated after the AUDIT: marker and quote fields that contain commas. They parse as CSV once you strip the log line prefix.

Trademarks

PostgreSQL and the PostgreSQL elephant logo are trademarks of the PostgreSQL Community Association of Canada. pgAudit is the name of the open source project at github.com/pgaudit/pgaudit. Ubuntu is a registered trademark of Canonical Ltd. All other trademarks are the property of their respective owners, and are used here only to identify the software contained in this image. SecureAnyCloud is not affiliated with, endorsed by or sponsored by any of them.

Support

Every SecureAnyCloud image includes 24/7 support. If you have questions about this PostgreSQL with pgAudit image, contact us at sales@secureanycloud.com.

Deploy on Azure

Launch PostgreSQL 18 with pgAudit 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