PostgreSQL 18 with pgAudit on Ubuntu 24.04 on Azure User Guide

Overview

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

pgAudit is the PostgreSQL Audit Extension. It is the control named in PCI-DSS, SOC 2 and HIPAA guidance for PostgreSQL, and it is the one extension you cannot simply switch on in a running database: it has to 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.

What that buys you is a trail that PostgreSQL's own log_statement cannot produce: structured, classified entries that 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 — every schema change, and every GRANT, REVOKE, CREATE ROLE and ALTER ROLE. This 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 deliberately 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 is included:

• 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, files 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 root only credentials file

• Loopback only networking by default: 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

• 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 from your administration network. Nothing else needs to be opened: 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, so 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:

az vm create \
  --resource-group my-rg \
  --name pgaudit-1 \
  --image <this-marketplace-image> \
  --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, so 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. SSH in and read the root only credentials file. 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

You will see the file is mode 600 owned by root, and 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, together with a written summary of what is audited and where the trail is kept. Nothing is baked into the image: every deployed VM generates its own.

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 is the answer to that question:

sudo systemctl is-active postgresql@18-main.service
sudo systemctl is-active cloudimg-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

You should see both units active, shared_preload_libraries reporting pgaudit, the extension listed at version 18.0, and a listening set in which 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

Note that 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 just makes the example repeatable; it is itself a DDL event, so it appears in the trail too on the second run.

One CREATE TABLE produces three entries, because it really is 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>

Reading an entry left to right: the class is SESSION, then the statement and substatement identifiers, then the class again (DDL), the command (CREATE TABLE), the object type (TABLE), the fully qualified object (public.demo_customers), the statement text, and finally the parameter field. That last field reads <not logged> because pgaudit.log_parameter is off, which is the setting that 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, classified OBJECT rather than SESSION, with the class of access (WRITE, READ) and the table named:

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, so the mechanism is live and demonstrable before you create anything of your own.

Turn on global write or read auditing

If your policy really does require every write to be recorded, it is a one line change plus a reload. No restart is needed, because pgaudit.log can be changed 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 it took, then resets it to the shipped value so 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, because 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, so exactly one thing rotates these files and your trail is never interleaved with unrelated noise:

sudo cat /etc/default/cloudimg-pgaudit-logretention
sudo /usr/local/sbin/cloudimg-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, doing two different jobs:

• PostgreSQL's logging collector rotates. A new file is started every 64 MB or every day, whichever comes first, and 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 one reaches 64 MB or when the day rolls over, so the listing grows to one file per rotation until the sweep below starts retiring the oldest.

• cloudimg-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, and if the directory total still exceeds PGAUDIT_MAX_BYTES the oldest files are removed until it does not. The file PostgreSQL is currently writing is never removed.

Edit /etc/default/cloudimg-pgaudit-logretention to match your own retention policy and run sudo systemctl restart cloudimg-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) and leave the caps where they are, rather than raising the caps 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, is required to 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 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 it is a deliberate two step change, and 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 one of those remote connections is recorded in the trail as well.

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, set through psql standard input so they never appear on a command line or in the journal, and written to /root/postgresql-pgaudit-credentials.txt at mode 0600. Neither 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, so two VMs from this image have different cluster system identifiers, different TLS certificates, different machine IDs, different SSH host keys and different passwords.

• TLS on every TCP connection. pg_hba.conf carries only hostssl ... scram-sha-256 rules. Local administration stays password less 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 it 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) and 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. Note that postgresql.service on Debian and Ubuntu is a wrapper: the unit that actually runs your database is postgresql@18-main.service, and that is the one to check:

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/cloudimg-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, so they parse as CSV with the log line prefix stripped.

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. cloudimg is not affiliated with, endorsed by or sponsored by any of them.

Support

Every cloudimg image includes 24/7 support. If you have any questions about this PostgreSQL with pgAudit image, contact us at support@cloudimg.co.uk.