Build an audit trail that survives scrutiny: immutable logs, NTP, retention

TL;DR — A defensible audit trail needs three things working together: append-only writes, clocks that stay in sync, and retention that is enforced outside the app. The most common failure is relying on app code alone; fix it by writing audit events to a dedicated table with no UPDATE/DELETE privileges, syncing time with chrony/systemd-timesyncd, and rotating/exporting logs with explicit retention. Reading time: ~5 min

Goal

When you finish, your app will write audit events to a dedicated append-only store, your hosts will have verifiable time sync, and your system will retain audit data for a defined period without app developers being able to silently edit or delete past entries.

Prerequisites

• Shell access on the app host or container image build pipeline
• PostgreSQL 14+ with a role that can create schema, tables, triggers, and roles — check with psql --version
• Linux host using either chrony or systemd-timesyncd — check with chronyc --version or timedatectl --version
• logrotate installed if writing local files — check with logrotate --version
• Your retention requirement in days/months/years, and the timezone policy you will use (UTC recommended)
• A service account name for the application, for examples below: app_user
• A database admin role for setup, for examples below: postgres

Steps

Step 1: Create a dedicated append-only audit table

Run this as a database admin.

psql -U postgres -d appdb <<'SQL'
CREATE SCHEMA IF NOT EXISTS audit;

CREATE TABLE IF NOT EXISTS audit.event_log (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_time TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  actor_id TEXT NOT NULL,
  action TEXT NOT NULL,
  target_type TEXT NOT NULL,
  target_id TEXT NOT NULL,
  request_id TEXT,
  source_ip INET,
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  prev_hash TEXT,
  event_hash TEXT NOT NULL
);

REVOKE ALL ON SCHEMA audit FROM PUBLIC;
REVOKE ALL ON TABLE audit.event_log FROM PUBLIC;
GRANT USAGE ON SCHEMA audit TO app_user;
GRANT INSERT, SELECT ON audit.event_log TO app_user;
REVOKE UPDATE, DELETE, TRUNCATE ON audit.event_log FROM app_user;
SQL

You should see CREATE SCHEMA, CREATE TABLE, GRANT, and REVOKE with no ERROR: lines.

Step 2: Block UPDATE and DELETE at the database layer

This prevents accidental or intentional edits even if someone gets SQL access through the app role.

psql -U postgres -d appdb <<'SQL'
CREATE OR REPLACE FUNCTION audit.block_mutation()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE EXCEPTION 'audit.event_log is append-only';
END;
$$;

DROP TRIGGER IF EXISTS event_log_no_update ON audit.event_log;
CREATE TRIGGER event_log_no_update
BEFORE UPDATE ON audit.event_log
FOR EACH ROW EXECUTE FUNCTION audit.block_mutation();

DROP TRIGGER IF EXISTS event_log_no_delete ON audit.event_log;
CREATE TRIGGER event_log_no_delete
BEFORE DELETE ON audit.event_log
FOR EACH ROW EXECUTE FUNCTION audit.block_mutation();
SQL

You should see CREATE FUNCTION, DROP TRIGGER, and CREATE TRIGGER.

Step 3: Add tamper-evident hashes on insert

This does not make the table immutable by itself; it makes changes detectable. The hash chains each row to the previous row.

psql -U postgres -d appdb <<'SQL'
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION audit.set_event_hash()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  last_hash TEXT;
BEGIN
  SELECT event_hash INTO last_hash
  FROM audit.event_log
  ORDER BY id DESC
  LIMIT 1;

  NEW.prev_hash := last_hash;
  NEW.event_hash := encode(
    digest(
      coalesce(NEW.event_time::text, '') || '|' ||
      coalesce(NEW.actor_id, '') || '|' ||
      coalesce(NEW.action, '') || '|' ||
      coalesce(NEW.target_type, '') || '|' ||
      coalesce(NEW.target_id, '') || '|' ||
      coalesce(NEW.request_id, '') || '|' ||
      coalesce(NEW.source_ip::text, '') || '|' ||
      coalesce(NEW.payload::text, '') || '|' ||
      coalesce(last_hash, ''),
      'sha256'
    ),
    'hex'
  );
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS event_log_set_hash ON audit.event_log;
CREATE TRIGGER event_log_set_hash
BEFORE INSERT ON audit.event_log
FOR EACH ROW EXECUTE FUNCTION audit.set_event_hash();
SQL

You should see CREATE EXTENSION, CREATE FUNCTION, and CREATE TRIGGER.

Step 4: Write audit events from the app using a single INSERT path

Use a dedicated SQL statement; do not let app code write arbitrary SQL to the audit table.

INSERT INTO audit.event_log (
  actor_id, action, target_type, target_id, request_id, source_ip, payload
) VALUES (
  $1, $2, $3, $4, $5, $6, $7::jsonb
);

For a quick manual test:

psql -U app_user -d appdb -c "INSERT INTO audit.event_log (actor_id, action, target_type, target_id, request_id, source_ip, payload) VALUES ('u_123', 'user.login', 'session', 's_456', 'req-abc', '203.0.113.10', '{\"method\":\"password\"}');"

You should see INSERT 0 1.

Step 5: Enable and verify time sync on the host

Use one of the following, depending on your distro.

For hosts with chrony:

sudo systemctl enable --now chronyd
chronyc tracking
chronyc sources -v

Typical healthy output shape:

Reference ID    : C0A80101 (ntp1.example.net)
Stratum         : 3
Ref time (UTC)  : Thu Oct 30 10:12:41 2026
System time     : 0.000184321 seconds fast of NTP time
Last offset     : -0.000092311 seconds
RMS offset      : 0.000201882 seconds

For hosts with systemd-timesyncd:

sudo timedatectl set-ntp true
timedatectl status

Typical healthy output shape:

System clock synchronized: yes
NTP service: active
RTC in local TZ: no
Time zone: Etc/UTC (UTC, +0000)

You should see either a small offset in chronyc tracking or System clock synchronized: yes in timedatectl status.

Step 6: Set explicit retention for local audit log files

If your app also writes structured audit logs to disk, rotate them separately from general app logs.

⚠️ If you set copytruncate, a busy process can lose a few log lines during rotation. Prefer reopening logs with a signal if your app supports it.

Create a dedicated logrotate policy:

sudo tee /etc/logrotate.d/app-audit >/dev/null <<'CONF'
/var/log/myapp/audit.log {
    daily
    rotate 365
    missingok
    notifempty
    compress
    delaycompress
    dateext
    create 0640 app app
    su app app
    sharedscripts
    postrotate
        /bin/kill -HUP $(cat /run/myapp.pid 2>/dev/null) 2>/dev/null || true
    endscript
}
CONF

sudo logrotate -d /etc/logrotate.d/app-audit

You should see reading config file /etc/logrotate.d/app-audit and no syntax errors.

Step 7: Export or back up audit data on a retention schedule

Retention that exists only on the live database is weak. Take regular dumps of the audit schema.

pg_dump -U postgres -d appdb -n audit -Fc -f /var/backups/appdb-audit-$(date +%F).dump
ls -lh /var/backups/appdb-audit-$(date +%F).dump

You should see a non-zero-size dump file.

[App] ---- 1. INSERT audit event ----> [Postgres audit.event_log]
  |                                         |
  | 2. host time from NTP                   | 3. hash chain per row
  v                                         v
[chrony/timesyncd]                    [Backup/Retention]

Verify it works

Run all of these checks.

1. Insert an event and confirm hashes were generated:

psql -U app_user -d appdb -c "INSERT INTO audit.event_log (actor_id, action, target_type, target_id, request_id, source_ip, payload) VALUES ('u_999', 'invoice.approve', 'invoice', 'inv_42', 'req-verify', '203.0.113.11', '{\"amount\":1250}');"
psql -U app_user -d appdb -x -c "SELECT id, event_time, actor_id, action, prev_hash, event_hash FROM audit.event_log ORDER BY id DESC LIMIT 2;"

Expected shape:

INSERT 0 1
-[ RECORD 1 ]-------------------------------
id         | 2
event_time | 2026-10-30 10:15:22.123456+00
actor_id   | u_999
action     | invoice.approve
prev_hash  | 4c1d...
event_hash | b9a7...

1. Confirm UPDATE is blocked:

psql -U app_user -d appdb -c "UPDATE audit.event_log SET action='tampered' WHERE id=1;"

Expected error:

ERROR:  audit.event_log is append-only

1. Confirm DELETE is blocked:

psql -U app_user -d appdb -c "DELETE FROM audit.event_log WHERE id=1;"

Expected error:

ERROR:  audit.event_log is append-only

1. Confirm host time sync:

chronyc tracking || timedatectl status

Expected: offset in milliseconds or System clock synchronized: yes.

1. Confirm retention config parses:

sudo logrotate -d /etc/logrotate.d/app-audit | tail -n 5

Expected: no error: lines.

Common pitfalls

App role still has DELETE through table ownership

Mistake: the application connects as the table owner or a superuser-like role.

Symptom: DELETE 1 succeeds even though you revoked privileges.

Fix: run the app as app_user, and keep table ownership with a separate admin role.

Using application server time instead of database time

Mistake: the app sends its own timestamp from a skewed container or client.

Symptom: audit rows appear out of order across hosts, often by seconds or minutes.

Fix: keep event_time TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp() and do not pass event_time from the app.

Hash chain breaks under concurrent inserts assumptions

Mistake: relying on ORDER BY id DESC LIMIT 1 as a globally ordered chain in a high-write system without thinking about concurrency.

Symptom: verification scripts report unexpected predecessor relationships during bursts.

Fix: if you need strict serial chaining, insert through a single writer queue or lock around the hash-chain write path.

Retention only exists in logrotate, not for database rows

Mistake: rotating /var/log/myapp/audit.log but leaving audit.event_log to grow forever.

Symptom: disk usage rises on the database host; compliance asks for a retention policy and you only have one for files.

Fix: schedule schema dumps and define a database retention/archive job explicitly; do not assume file rotation covers database audit data.

Local timezone leaks into logs

Mistake: host or app runs in local time instead of UTC.

Symptom: mixed timestamps like -0700 and +0000 in the same incident timeline.

Fix: set the host timezone to UTC and keep TIMESTAMPTZ; verify with timedatectl status showing Time zone: Etc/UTC.

This article was written by an AI system and published pending human review. Verify anything you intend to act on.