Databases AWS

PostgreSQL 18.6 with pgAudit on AWS User Guide

Last updated: 2026-10-26 | Product: PostgreSQL 18.6 with pgAudit 18.0 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and verifying PostgreSQL 18.6 with pgAudit 18.0 on Ubuntu 24.04 from cloudimg on Amazon EC2. It is one version of the cloudimg Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace, and the plain PostgreSQL choice in it; you pick it as the software version when you subscribe and launch. The other PostgreSQL builds and extensions in the listing are described on the listing's guide hub.

pgAudit is the PostgreSQL Audit Extension. Plain PostgreSQL logging can tell you that a statement ran; pgAudit writes a structured AUDIT: record of what kind of statement it was (a schema change, a role or privilege change, a read or a write) and on which object, in a fixed format that log tools and auditors can parse. It must be loaded when PostgreSQL starts, so it is something you normally have to plan a restart for. On this image it is already loaded and auditing from the first boot.

The image installs PostgreSQL 18.6 and pgAudit 18.0, both unmodified official PostgreSQL (PGDG) packages from the PGDG apt repository. Unattended upgrades are configured for the PGDG archive as well as Ubuntu, so later PostgreSQL 18 and pgAudit releases keep arriving through apt.

The audit policy this image ships:

pgaudit.log = 'ddl, role': every schema change (CREATE, ALTER, DROP, ...) and every role or privilege change (CREATE ROLE, ALTER ROLE, GRANT, REVOKE) is recorded. These are the high-value, low-volume events of an audit trail.
Reads and writes are opt-in. Per table, by granting privileges to the auditor role (Step 5), or for everything with one setting (Step 7). Globally they are high volume on a busy database, so they are not on by default.
Passwords and bound values stay out of the trail. pgAudit replaces everything after PASSWORD in CREATE ROLE / ALTER ROLE with <REDACTED> (Step 6), and pgaudit.log_parameter = off keeps the values of bound parameters out. Catalog queries made by tools and drivers are not logged (pgaudit.log_catalog = off).
Who and from where. Every log line starts with the time, the process ID, user@database and the client address and port, and connections and disconnections are logged.
The audit records go to the standard PostgreSQL server log, /var/log/postgresql/postgresql-18-main.log, which Ubuntu's PostgreSQL packaging rotates weekly.

What is on the image:

PostgreSQL 18.6 (postgresql@18-main.service) with pgAudit 18.0 preloaded (shared_preload_libraries = 'pgaudit')
An auditdb database owned by the application role auditapp; the pgaudit extension is created in template1, so auditdb and every database you create later have it
A non-superuser application role auditapp and the postgres superuser; both passwords are generated on your instance's first boot
The auditor role, which cannot log in and has no password; it only selects tables for object auditing
TLS with a certificate generated on your instance's first boot (TLS 1.2 minimum)
A credential guard that runs on every boot and refuses to serve if a published or example password ever authenticates

Default user: ubuntu · Architecture: x86_64 · Root volume: 12 GiB gp3

Security posture

No database ships in the image. The image contains no PostgreSQL cluster at all, so it carries no role, password, data, audit log or key from the build. First boot creates a fresh cluster (with data checksums), the auditdb database and two different passwords, one for postgres and one for auditapp, and writes them to /root/postgresql-pgaudit-credentials.txt, readable by root only. Locally, postgres is also reachable as the postgres system user (peer authentication).
Nothing is served before first boot finishes. PostgreSQL will not start until first boot has created the cluster, the certificate and the passwords. While it sets them, the server listens on its local socket only, with no TCP port and with auditing and statement logging switched off, so the password-setting statements are not logged anywhere.
Per-instance TLS certificate. First boot creates this instance's own private key and self-signed certificate under /etc/postgresql/18/main/ and turns TLS on, so no two instances share a key.
Localhost only. PostgreSQL listens on 127.0.0.1:5432, and the listing's security group opens SSH (22) only. Reach the database through an SSH tunnel, or open it to your VPC as described in Step 9.
Remote connections require TLS. pg_hba.conf accepts non-loopback clients only over hostssl with scram-sha-256 and rejects plain-text remote connections; there is no trust rule.
No metadata access for the database. A database superuser can make PostgreSQL open outbound connections (for example COPY ... FROM PROGRAM), so every PostgreSQL unit and both first-boot services are blocked from reaching the instance metadata service (169.254.169.254 and fd00:ec2::254). Amazon Time Sync (169.254.169.123) still works. If you add backups to S3 from inside the database, give them explicit credentials; they cannot use the instance role.
No core dumps. The database and first-boot services may not write core files, so process memory holding a password never lands on disk.
IMDSv2 required. The AMI is registered with ImdsSupport v2.0, so instances launched from it refuse token-less (IMDSv1) metadata requests.
Fails closed. If first boot cannot finish, PostgreSQL stays stopped and first boot tries again on the next boot.
Per-instance identity. SSH host keys and the machine ID are generated fresh on every instance. SSH allows key login only, root cannot log in over SSH, and root has no password.
What we changed in Ubuntu: the fwupd-refresh timer is masked (EC2 has no firmware that fwupd can update), the C header packages the base image carried are removed (no compiler is installed), and only the running kernel is kept.

Prerequisites

An AWS account and permission to launch EC2 instances and create security groups
An EC2 key pair in the Region you launch in
A VPC subnet, with a public IP on the instance if you connect over the internet

Recommended instance type: m5.large. Size up for larger databases or more concurrent connections; with write or read auditing on, also allow for the extra log volume.

Connecting to your instance

Version	Operating system	SSH user
PostgreSQL 18.6 with pgAudit 18.0 on Ubuntu 24.04	Ubuntu 24.04 LTS	`ubuntu`

Step 1: Launch the instance

Option A: AWS Marketplace console

Open the cloudimg Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace and choose View purchase options, then Subscribe. One subscription covers every version in the listing.
Once 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 PostgreSQL 18.6 with pgAudit 18.0 on Ubuntu 24.04, then choose your Region.
Pick an instance type (m5.large recommended; the software charge is $0.04 per vCPU per hour) and your key pair.
Security group: the listing's default group allows SSH (22) only. Restrict it to your own IP address. Do not open 5432 to the internet.
Choose your VPC and subnet, leave the metadata defaults (the image already requires IMDSv2), then choose Launch.

Option B: AWS CLI

Run this on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page, and the VPC, subnet, key pair and CIDR with your own:

SG=$(aws ec2 create-security-group --group-name postgresql-pgaudit \
  --description "PostgreSQL with pgAudit" --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 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=postgresql-pgaudit}]'

You do not need --metadata-options: the AMI already makes IMDSv2 required. To give the database more room, add a --block-device-mappings entry for /dev/sda1 with a bigger VolumeSize.

Step 2: Connect and read your credentials

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. Show the connection details (the two password lines are left out here; read them with sudo cat /root/postgresql-pgaudit-credentials.txt and keep them in your secrets manager):

sudo grep -v -e '^POSTGRES_SUPERUSER_PASSWORD=' -e '^AUDITAPP_PASSWORD=' /root/postgresql-pgaudit-credentials.txt | head -14

# PostgreSQL 18.6 with pgAudit 18.0 (cloudimg AWS Marketplace image)
# Generated on THIS instance's first boot by postgresql-pgaudit-firstboot.service; these passwords exist
# nowhere else. Rotate a password (never on a command line) with:  sudo -u postgres psql
# then  \password auditapp  and update this file to match (postgresql-pgaudit-credguard reads it at boot).


# Database 'auditdb' (owner auditapp) on 127.0.0.1:5432. Connect locally with TLS:
#   psql "host=127.0.0.1 dbname=auditdb user=auditapp sslmode=require"
# Private address of this instance: 172.31.83.91
#
# AUDITING: pgAudit is preloaded with pgaudit.log = 'ddl, role'. Audit entries ("AUDIT: SESSION,..." and
# "AUDIT: OBJECT,...") are written to /var/log/postgresql/postgresql-18-main.log. Per-table auditing: GRANT the privileges
# to audit on a table to the NOLOGIN role 'auditor'. Policy file: /etc/postgresql/18/main/conf.d/30-cloudimg-pgaudit.conf
#

The commands below read the auditapp password 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) as 127.0.0.1:5432:auditdb:auditapp:<AUDITAPP_PASSWORD>.

Step 3: Confirm PostgreSQL and pgAudit are running

psql --version
systemctl is-active postgresql@18-main.service postgresql-pgaudit-firstboot.service postgresql-pgaudit-credguard.service
sudo ss -Hltn

psql (PostgreSQL) 18.6 (Ubuntu 18.6-1.pgdg24.04+2)
active
active
active
LISTEN 0      4096   127.0.0.53%lo:53   0.0.0.0:*
LISTEN 0      4096         0.0.0.0:22   0.0.0.0:*
LISTEN 0      4096      127.0.0.54:53   0.0.0.0:*
LISTEN 0      200        127.0.0.1:5432 0.0.0.0:*
LISTEN 0      4096            [::]:22      [::]:*

PostgreSQL listens on 127.0.0.1:5432 only; port 53 on 127.0.0.53/127.0.0.54 is the operating system's local DNS stub. Now confirm pgAudit is preloaded, then connect as auditapp over TLS and show the extension and the audit settings:

sudo -u postgres psql -tAc "SHOW shared_preload_libraries;"
export PGPASSWORD="$(sudo sed -n 's/^AUDITAPP_PASSWORD=//p' /root/postgresql-pgaudit-credentials.txt)"
psql "host=127.0.0.1 dbname=auditdb user=auditapp sslmode=require" \
  -c "\dx" \
  -c "SELECT name, setting FROM pg_settings WHERE name IN ('pgaudit.log', 'pgaudit.role', 'pgaudit.log_catalog', 'pgaudit.log_parameter', 'log_line_prefix') ORDER BY name;" \
  -c "SELECT ssl, version AS tls_version FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

pgaudit
                            List of installed extensions
  Name   | Version | Default version |   Schema   |           Description
---------+---------+-----------------+------------+---------------------------------
 pgaudit | 18.0    | 18.0            | public     | provides auditing functionality
 plpgsql | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
(2 rows)

         name          |       setting
-----------------------+---------------------
 log_line_prefix       | %m [%p] %q%u@%d %r
 pgaudit.log           | ddl, role
 pgaudit.log_catalog   | off
 pgaudit.log_parameter | off
 pgaudit.role          | auditor
(5 rows)

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

Step 4: See a schema change in the audit trail

Create a table, use it and drop it again as auditapp, then look for the audit records. The CREATE TABLE and the DROP TABLE are recorded as SESSION / DDL entries; the INSERT and SELECT are not, because reads and writes are outside the default policy:

export PGPASSWORD="$(sudo sed -n 's/^AUDITAPP_PASSWORD=//p' /root/postgresql-pgaudit-credentials.txt)"
psql "host=127.0.0.1 dbname=auditdb user=auditapp sslmode=require" -v ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE customers_demo (id int PRIMARY KEY, name text, card_last4 text);
INSERT INTO customers_demo VALUES (1, 'Ada', '4242');
SELECT count(*) AS customers FROM customers_demo;
DROP TABLE customers_demo;
SQL
sudo grep -a 'AUDIT: SESSION,.*,TABLE,public.customers_demo,' /var/log/postgresql/postgresql-18-main.log | tail -n 2 | cut -d' ' -f4-

CREATE TABLE
INSERT 0 1
 customers
-----------
         1
(1 row)

DROP TABLE
[11160] auditapp@auditdb 127.0.0.1(37084) LOG:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.customers_demo,"CREATE TABLE customers_demo (id int PRIMARY KEY, name text, card_last4 text)",<not logged>
[11160] auditapp@auditdb 127.0.0.1(37084) LOG:  AUDIT: SESSION,2,1,DDL,DROP TABLE,TABLE,public.customers_demo,DROP TABLE customers_demo,<not logged>

(cut only drops the date and time at the start of each line.) Each record names the process, the user and database (auditapp@auditdb), the client address and port, then the pgAudit fields: audit type, statement ID, substatement ID, class, command, object type, object name, the statement, and the parameters (<not logged>, because pgaudit.log_parameter is off). The full format is described in the pgAudit documentation.

Step 5: Audit reads and writes of one table (object auditing)

To audit access to a particular table, grant the privileges you want recorded on it to the auditor role. auditor cannot log in; it only tells pgAudit which tables and which kinds of access to record. The table owner can grant them:

export PGPASSWORD="$(sudo sed -n 's/^AUDITAPP_PASSWORD=//p' /root/postgresql-pgaudit-credentials.txt)"
psql "host=127.0.0.1 dbname=auditdb user=auditapp sslmode=require" -v ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE payroll_demo (id int PRIMARY KEY, employee text, salary numeric);
INSERT INTO payroll_demo VALUES (1, 'Grace', 52000);
-- Audit reads and updates of this one table: grant those privileges to the NOLOGIN role 'auditor'
GRANT SELECT, UPDATE ON payroll_demo TO auditor;
SELECT employee, salary FROM payroll_demo WHERE id = 1;
UPDATE payroll_demo SET salary = salary * 1.05 WHERE id = 1;
DROP TABLE payroll_demo;
SQL
sudo grep -a 'AUDIT: OBJECT,.*payroll_demo' /var/log/postgresql/postgresql-18-main.log | tail -n 2 | cut -d" " -f4-

CREATE TABLE
INSERT 0 1
GRANT
 employee | salary
----------+--------
 Grace    |  52000
(1 row)

UPDATE 1
DROP TABLE
[11078] auditapp@auditdb 127.0.0.1(35082) LOG:  AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.payroll_demo,"SELECT employee, salary FROM payroll_demo WHERE id = 1",<not logged>
[11078] auditapp@auditdb 127.0.0.1(35082) LOG:  AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.payroll_demo,UPDATE payroll_demo SET salary = salary * 1.05 WHERE id = 1,<not logged>

The INSERT before the grant was not recorded (auditor was not granted INSERT). Only the privileges you grant to auditor are audited, and only on the tables you grant them on. The GRANT itself is recorded as a ROLE entry.

Step 6: Passwords are redacted

Role statements are audited, but pgAudit replaces everything after the PASSWORD keyword with <REDACTED>, so a password set with SQL does not end up in the audit trail. Try it with a throwaway role (the statements are fed on standard input, so the example passwords are not on a command line either):

sudo -u postgres psql -v ON_ERROR_STOP=1 -q <<'SQL'
CREATE ROLE redaction_demo LOGIN PASSWORD 'example-password-not-for-use';
ALTER ROLE redaction_demo WITH PASSWORD 'another-example-password';
DROP ROLE redaction_demo;
SQL
sudo grep -a 'AUDIT: .*redaction_demo' /var/log/postgresql/postgresql-18-main.log | tail -n 3 | cut -d' ' -f4-
sudo grep -c -a -e 'example-password-not-for-use' -e 'another-example-password' /var/log/postgresql/postgresql-18-main.log || true

[10764] postgres@postgres [local] LOG:  AUDIT: SESSION,1,1,ROLE,CREATE ROLE,,,CREATE ROLE redaction_demo LOGIN PASSWORD <REDACTED>,<not logged>
[10764] postgres@postgres [local] LOG:  AUDIT: SESSION,2,1,ROLE,ALTER ROLE,,,ALTER ROLE redaction_demo WITH PASSWORD <REDACTED>,<not logged>
[10764] postgres@postgres [local] LOG:  AUDIT: SESSION,3,1,ROLE,DROP ROLE,,,DROP ROLE redaction_demo,<not logged>
0

One caution: PostgreSQL itself writes the full text of a statement that fails to the log (log_min_error_statement = error, the PostgreSQL default), and that is outside pgAudit's redaction. The safest way to change a password is psql's \password command (see Maintenance), which sends only a salted SCRAM verifier, never the password.

Step 7: Turn on more auditing

The policy lives in /etc/postgresql/18/main/conf.d/30-cloudimg-pgaudit.conf. Every pgaudit.* setting takes effect on a reload; no restart is needed. To audit every write (INSERT, UPDATE, DELETE, TRUNCATE, COPY ... FROM) on every table:

sudo sed -i "s/^pgaudit.log = 'ddl, role'/pgaudit.log = 'ddl, role, write'/" /etc/postgresql/18/main/conf.d/30-cloudimg-pgaudit.conf
sudo systemctl reload postgresql@18-main.service
sudo -u postgres psql -tAc "SHOW pgaudit.log;"

Add read the same way to record every SELECT as well; on a busy database that is a large volume of log. You can also set the policy for one database or one role only, as postgres:

ALTER DATABASE auditdb SET pgaudit.log = 'ddl, role, write';
ALTER ROLE reporting_user SET pgaudit.log = 'read';

The other classes are function, misc and misc_set; see the pgAudit settings.

Keeping the trail. The audit records are part of the PostgreSQL server log, which Ubuntu's /etc/logrotate.d/postgresql-common rotates weekly and keeps for ten weeks. If your retention rules need more, or need the trail off the instance, ship /var/log/postgresql/postgresql-18-main.log to a central store, for example with the Amazon CloudWatch agent. The file is readable by members of the adm group (the ubuntu user is one). Expect a few failed logins for postgres and auditapp from 127.0.0.1 in the log at every boot: those are the credential guard checking that no published password works.

Step 8: Check the security controls

The PostgreSQL unit carries the metadata block, auditapp is not a superuser, auditor cannot log in, PostgreSQL listens on loopback and uses this instance's own certificate, and the fwupd refresh timer is masked:

systemctl show -p IPAddressDeny postgresql@18-main.service
sudo -u postgres psql -tAc "SELECT rolname, rolsuper, rolcanlogin FROM pg_roles WHERE rolname IN ('auditapp', 'auditor', 'postgres') ORDER BY 1;"
sudo -u postgres psql -tAc "SHOW listen_addresses;"
sudo -u postgres psql -tAc "SHOW ssl_cert_file;"
systemctl is-enabled fwupd-refresh.timer 2>/dev/null || true

IPAddressDeny=fd00:ec2::254/128 169.254.169.254/32 168.63.129.16/32
auditapp|f|t
auditor|f|f
postgres|t|t
127.0.0.1
/etc/postgresql/18/main/cloudimg-server.crt
masked

Step 9: Connect from your application

PostgreSQL accepts connections on localhost only. Two ways to reach it:

SSH tunnel (no change to the instance). On your workstation run ssh -N -L 5432:127.0.0.1:5432 ubuntu@<public-ip>, then connect your client to 127.0.0.1:5432 as auditapp with sslmode=require.
Open it to your VPC. In /etc/postgresql/18/main/conf.d/10-cloudimg.conf change listen_addresses = '127.0.0.1' to listen_addresses = '*', restart PostgreSQL, then add an inbound rule for 5432 to the instance's security group from your application subnet or VPC CIDR only (never 0.0.0.0/0). Remote clients must use TLS (sslmode=require, or verify-full with this instance's certificate /etc/postgresql/18/main/cloudimg-server.crt) and the role's password. The audit records then show each client's own address:

sudo sed -i "s/^listen_addresses = '127.0.0.1'/listen_addresses = '*'/" /etc/postgresql/18/main/conf.d/10-cloudimg.conf
sudo systemctl restart postgresql@18-main.service

A plain-text (non-TLS) connection from another host is refused by pg_hba.conf, and a wrong password is refused by scram-sha-256:

OUT="$(PGPASSWORD=wrong-password psql "host=127.0.0.1 dbname=auditdb user=auditapp sslmode=require" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"

FATAL:  password authentication failed for user "auditapp"

Maintenance

Change a password. Run sudo -u postgres psql and type \password auditapp (or \password postgres) at the prompt; it asks for the new password twice, never puts it on a command line, and sends the server only a SCRAM verifier. Then update /root/postgresql-pgaudit-credentials.txt (the credential guard reads it on every boot) and your secrets manager.
Configuration. cloudimg's settings are in /etc/postgresql/18/main/conf.d/: 10-cloudimg.conf (listener, port, password hashing, log line prefix, connection logging), 20-cloudimg-tls.conf (this instance's certificate) and 30-cloudimg-pgaudit.conf (pgAudit and the audit policy). pgaudit.* changes need sudo systemctl reload postgresql@18-main.service; listener and shared_preload_libraries changes need a restart.
New databases. pgaudit is created in template1, so CREATE DATABASE gives every new database the extension. For a database created from another template, run CREATE EXTENSION pgaudit; in it as postgres.
Updates. sudo apt update && sudo apt upgrade installs PostgreSQL 18 and pgAudit releases from PGDG and Ubuntu updates; unattended upgrades also apply them automatically. After a pgAudit upgrade, run ALTER EXTENSION pgaudit UPDATE; in each database if the release notes say so.
Backups. Take EBS snapshots of the root volume or pg_dump -Fc backups regularly.
Logs. The server log and the audit trail are in /var/log/postgresql/postgresql-18-main.log; first boot and the credential guard log to journalctl -u postgresql-pgaudit-firstboot.service and journalctl -u postgresql-pgaudit-credguard.service.
Licences. PostgreSQL and pgAudit are both under The PostgreSQL License and are unmodified PGDG packages; /usr/share/doc/cloudimg/LICENCE-AUDIT.txt records the audited licence files.

Support

cloudimg supports this image by email at support@cloudimg.co.uk. Include the instance ID, the version (PostgreSQL 18.6 with pgAudit 18.0 on Ubuntu 24.04) and what you were doing.

PostgreSQL is a trademark of the PostgreSQL Community Association of Canada. Ubuntu is a trademark of Canonical Ltd. pgAudit is the name of its open-source project. cloudimg is not affiliated with or endorsed by these organisations or projects.