PostgreSQL audit logging that a superuser can't quietly rewrite

Database audits rarely fail on "do you have audit logging?" Most teams can point to pgaudit in shared_preload_libraries. The trouble starts with the next question. The auditor picks a DBA account and a date and asks for everything that account did. Then they ask how you know the DBA couldn't edit that record afterwards.

Community PostgreSQL handles the first question well. The second is harder. By default the audit trail ends up in files written by the same operating system account that runs the database, and a database superuser can act as that account. Below: where the trail can be switched off or edited, what stock PostgreSQL and pgAudit let you harden, and what the auditing in CYBERTEC PostgreSQL Enterprise Edition (PGEE) changes.

What the regulator actually asks for

For Saudi entities under the NCA Essential Cybersecurity Controls, control 2-12-3 in ECC-2:2024 asks for event logs on critical assets and on privileged accounts, collection through a SIEM, continuous monitoring, and retention of at least 12 months. NCA's event logs and monitoring standard template goes further. It lists activity by privileged accounts, changes to system settings, and changes to audit log parameters as events that must be logged, and it names database events as a required source.

Put those two items side by side. Turning off the audit log is itself an event you must log. A setup where a DBA can disable pgAudit for an hour without leaving a trace fails the control, even if pgAudit runs perfectly the rest of the year.

What pgAudit records

pgAudit writes structured lines through PostgreSQL's normal logging. Each line carries the audit type (SESSION or OBJECT), a statement ID and sub-statement ID, the class, the command, the object type and fully qualified name, and the statement text. log_line_prefix adds who, where, and when.

Session auditing logs by class. The classes are READ, WRITE, FUNCTION, ROLE, DDL, MISC, and MISC_SET, and you can subtract classes, as in 'all, -misc'. Object auditing works through a role. You set pgaudit.role = 'auditor' and grant that role the privileges you want logged on specific tables. A SELECT on payments.card gets logged because auditor holds SELECT on it, and a SELECT on a lookup table doesn't.

The split keeps volume under control. Logging READ for every session on a busy OLTP system produces a lot of log. Object auditing lets you log reads only on the tables that hold personal or payment data.

A sensible starting point:

# postgresql.conf
shared_preload_libraries = 'pgaudit'

pgaudit.log = 'ddl, role, misc_set' # every session
pgaudit.log_catalog = off # skip psql/driver catalog noise
pgaudit.log_relation = on # one line per table touched
pgaudit.log_parameter = off # see the PDPL note below
pgaudit.role = 'auditor' # object auditing for sensitive tables

log_line_prefix = '%m [%p] %q%u@%d %r app=%a sess=%c '
log_connections = 'authentication,authorization' # PostgreSQL 18; use 'on' on 17 and older
log_disconnections = on

CREATE EXTENSION pgaudit;
CREATE ROLE auditor NOLOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON payments.card TO auditor;

-- Privileged roles get full session auditing
ALTER ROLE dba_ops SET pgaudit.log = 'all, -misc';

PostgreSQL 18 turned log_connections from a boolean into a list of stages. Failed authentication attempts are always logged regardless of the setting.

One caution on pgaudit.log_parameter. With it on, bind parameters go into the log in clear text. On a table of national ID numbers that turns your audit log into a second copy of the personal data, and under PDPL that copy needs the same protection as the table. Leave it off unless an investigation needs it, and treat the log store as sensitive either way.

Five ways the trail goes quiet

None of these is a bug. Each is documented PostgreSQL behaviour. Run them in staging and check what your current setup records.

1. Session-level SET. pgAudit settings are superuser-only, which stops ordinary users. A superuser can still run SET pgaudit.log = 'none' and work unlogged for the rest of the session. Include MISC_SET in the global classes so the SET itself leaves a line, and confirm it does on your pgAudit version.

2. Role-level overrides. ALTER ROLE dba_ops SET pgaudit.log = 'none' disables auditing for every future session of that role. It never touches postgresql.conf, so a config file review won't find it. Look in the catalog:

SELECT coalesce(r.rolname, '(all roles)') AS role,
coalesce(d.datname, '(all databases)') AS db,
s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase
WHERE array_to_string(s.setconfig, ',') LIKE '%pgaudit%';

3. ALTER SYSTEM and a reload. A superuser can write pgaudit.log = 'none' to postgresql.auto.conf and call pg_reload_conf(). On reload the server logs parameter "pgaudit.log" changed to "none". That line is your evidence, as long as it reaches somewhere the DBA can't reach.

4. Acting as the OS account. COPY ... TO PROGRAM runs a shell command as the operating system user that runs PostgreSQL. It needs superuser or membership in pg_execute_server_program. Untrusted procedural languages such as plpython3u run code as that same OS user. With either, a superuser can truncate or edit the log files in log_directory, because that OS user owns them. List who holds these powers:

SELECT rolname, 'superuser' AS why FROM pg_roles WHERE rolsuper
UNION ALL
SELECT r.rolname, g.rolname
FROM pg_roles r
JOIN pg_auth_members m ON m.member = r.oid
JOIN pg_roles g ON g.oid = m.roleid
WHERE g.rolname IN ('pg_execute_server_program',
'pg_write_server_files',
'pg_read_server_files');

SELECT lanname FROM pg_language
WHERE NOT lanpltrusted AND lanname NOT IN ('c', 'internal');

The membership query shows direct grants only. Nested role grants need a recursive check.

5. Moving the destination. Changing log_destination or log_directory sends future lines somewhere else. The reload still logs the parameter change, but by then the change may already apply to the new destination.

In all five cases, a superuser in community PostgreSQL controls both what gets logged and the files that hold it. No setting closes that. The fix is to move the record out of the superuser's reach as soon as it's written, and to make sure that turning auditing down leaves its own record.

Hardening the community setup

Use fewer superusers. Daily DBA work doesn't need superuser. Predefined roles such as pg_monitor, pg_signal_backend, pg_read_all_data (PostgreSQL 14 and later), and pg_checkpoint (15 and later) cover most routine tasks. Keep superuser as a break-glass account with a named owner, and audit it with 'all, -misc'. NCA's privileged access guidance already asks that privileged accounts not be used for daily tasks, so one change covers two controls.

Ship lines off the host at write time. Two patterns work.

log_destination = 'syslog', with rsyslog forwarding over TCP and TLS to the SIEM using a disk-backed queue. The PostgreSQL docs warn that syslog may drop messages under heavy load, while the logging collector is designed never to lose them.
Keep logging_collector = on with jsonlog or csvlog, set log_file_mode = 0640, and run a log shipper as a separate OS user that has group read access and no write access. The local files stay complete, and the shipper sends them on within seconds.

Neither pattern closes the window completely. A superuser who edits a local file before the shipper reads it can still change that copy. What you gain: the copy the auditor reads sits in a system where the DBA has no write access.

Alert on the quiet moments. In the SIEM, alert on any parameter "pgaudit. line, on ALTER ROLE ... SET pgaudit and ALTER SYSTEM statements, on COPY ... TO PROGRAM, and on a privileged session that produces no audit lines for a period in which it ran queries. Run the two catalog queries above on a schedule and alert on any change in their output.

Size retention before go-live. Twelve months of 'all' auditing on privileged roles plus object auditing on sensitive tables is a storage line item. Measure a week of real staging traffic and multiply. Don't guess.

Where PGEE auditing fits

CYBERTEC lists enterprise-class auditing as a core PGEE feature, described as "advanced audit support and safeguards to prevent modification". The specific design point, from CYBERTEC's PGEE security material, is that the audit trail is not stored under the PostgreSQL user. PGEE sends it to a different UNIX user context, so DBAs can't modify their own audit records.

That closes bypass 4 directly. In a community setup, the person being audited can reach the audit files through the OS account that runs the server. With PGEE, the trail is written under a separate OS identity. You still need the rest of the controls above: fewer superusers, SIEM forwarding, alerting on setting changes, and 12-month retention. PGEE doesn't replace your SIEM, and no database feature makes you compliant on its own.

Two other PGEE pieces sit next to auditing. pg_permissions shows the permissions that actually exist, compares them with a declared target state, and reports the difference, which helps when an auditor asks who can read a given table. PGEE TDE covers tables, indexes, WAL, temporary files, and backups. CYBERTEC doesn't list server log files on that TDE list, so plan protection for the audit log store separately.

When you evaluate PGEE, ask to see the audit separation on your PostgreSQL major version during the proof of concept. Have a superuser try gaps 1 to 5 above and check what lands in the separated trail and in your SIEM. Your security team gets evidence it can file.

A staging test you can run this week

Enable the configuration above in staging with forwarding to your SIEM or a test collector.
As a superuser, run each of the five bypasses in turn.
For each one, check whether the SIEM has a record that names the account, the time, and the change.
Fix any bypass that left no trace, then repeat.

If your team is moving off Oracle or SQL Server, run this test before go-live, while the role design is still easy to change.

Talk to Worlber

Worlber is the authorised CYBERTEC reseller in Saudi Arabia. We deploy and support PGEE from Riyadh, including audit design, SIEM integration, and the PoC test described above.

Request a PGEE quote: https://worlber.sa/en/contact?subject=pgee-quote&utm_source=blog&utm_medium=cta&utm_campaign=postgresql-audit-logging-tamper-resistant-pgee
Free migration assessment: https://worlber.sa/en/contact?subject=migration&utm_source=blog&utm_medium=cta&utm_campaign=postgresql-audit-logging-tamper-resistant-pgee&utm_content=assessment
Or email contactus@worlber.com

Sources

CYBERTEC PGEE product page: https://www.cybertec-postgresql.com/en/products/cybertec-postgresql-enterprise-edition/
CYBERTEC, Enterprise PostgreSQL Security (PDF): https://www.cybertec-postgresql.com/wp-content/uploads/2025/02/Enterprise-PostgreSQL-Security.pdf
pgAudit documentation: https://github.com/pgaudit/pgaudit
PostgreSQL 18 error reporting and logging: https://www.postgresql.org/docs/18/runtime-config-logging.html
PostgreSQL 18 COPY: https://www.postgresql.org/docs/18/sql-copy.html
NCA Essential Cybersecurity Controls ECC-2:2024: https://cdn.nca.gov.sa/api/files/public/upload/86e09090-44e4-481f-bc28-355673607654_ECC--2024-EN.pdf