Auditing Permission Changes With an Append-Only Log

A SOC 2 auditor asks who granted the billing:refund permission to a contractor six weeks ago — and your UPDATE-in-place user_roles table has no answer because the row was overwritten the moment the grant was revoked. This page is part of the Designing Role-Based Access Control Systems guide, and it solves that exact gap: it shows how to capture every permission and role mutation in an immutable, hash-chained event log that survives revocation, resists tampering, and can be replayed to reconstruct the authorization state at any past instant.

The normalized RBAC schema you store in PostgreSQL is mutable by design — it answers “what can this user do right now.” An audit log answers a different, compliance-critical question: “what changed, who changed it, when, and can you prove the record was not altered after the fact.” These are separate concerns and they want separate stores.

Root Cause: Mutable State Has No Memory

The default RBAC tables — roles, permissions, role_permissions, user_role_assignments — are current-state projections. An UPDATE or DELETE destroys the prior value. Even an updated_at column only tells you when the last change happened, not what it changed from, who triggered it, or whether anything was skipped. Three properties are missing:

• Attribution. The acting principal (a human admin, a provisioning job, a SCIM sync) is not bound to the change.
• Immutability. Anyone with UPDATE/DELETE on the audit table — including a compromised app credential — can rewrite history.
• Verifiability. Even an append-only table proves nothing if a privileged attacker can DELETE a row and renumber the rest. You need cryptographic evidence that the sequence is intact.

The fix is an event-sourced log: instead of mutating state, you append a permanent, ordered record of each intent — permission_granted, role_revoked, role_permission_added. The current-state tables become a derived read model you can rebuild from the log.

The Hash Chain

Tamper-evidence comes from chaining: each event stores a hash computed over its own payload plus the hash of the previous event. Altering or deleting any historical row breaks every hash downstream, so a single periodic verification pass detects tampering anywhere in the chain. This is the same Merkle-style linkage that backs certificate transparency logs and Git commit history.

flowchart LR
    E0["event 0\nseed\nhash h0"]:::store --> E1["event 1\ngrant billing:refund\nhash h1 = H(h0 + p1)"]:::store
    E1 --> E2["event 2\nrevoke billing:refund\nhash h2 = H(h1 + p2)"]:::store
    E2 --> E3["event 3\nadd role admin\nhash h3 = H(h2 + p3)"]:::store
    E3 -.tamper here.-> X["edit p2\nbreaks h2..h3"]:::threat
    classDef store  fill:#fffbec,stroke:#d4840a,stroke-width:2px,color:#1a1614
    classDef threat fill:#fff0ee,stroke:#c0392b,stroke-width:2px,color:#1a1614

Step 1: The Immutable Event Table

Model the log as an append-only table with a monotonic sequence, a structured event payload, the acting principal, and the chain hash. Store it in a dedicated schema (or a separate database) so application credentials never receive write access beyond INSERT.

CREATE SCHEMA IF NOT EXISTS audit;

CREATE TABLE audit.permission_events (
  seq            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_id       uuid        NOT NULL DEFAULT gen_random_uuid(),
  tenant_id      uuid        NOT NULL,
  event_type     text        NOT NULL,          -- permission_granted | role_revoked | ...
  subject_id     uuid        NOT NULL,          -- the user/role being changed
  payload        jsonb       NOT NULL,          -- full before/after detail
  actor_id       uuid        NOT NULL,          -- WHO made the change
  actor_kind     text        NOT NULL,          -- human | service | scim_sync
  actor_ip       inet,
  occurred_at    timestamptz NOT NULL DEFAULT now(),
  prev_hash      bytea       NOT NULL,
  entry_hash     bytea       NOT NULL,
  CONSTRAINT event_type_known CHECK (event_type IN (
    'permission_granted','permission_revoked',
    'role_assigned','role_revoked',
    'role_permission_added','role_permission_removed'
  ))
);

-- Compliance query support
CREATE INDEX idx_pe_subject ON audit.permission_events (tenant_id, subject_id, occurred_at);
CREATE INDEX idx_pe_actor   ON audit.permission_events (tenant_id, actor_id, occurred_at);

Enforce immutability at the database layer, not just in application code. A trigger that rejects UPDATE and DELETE means even a SQL-injection foothold on the app role cannot rewrite history.

CREATE OR REPLACE FUNCTION audit.reject_mutation() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'audit.permission_events is append-only (%-attempt blocked)', TG_OP;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER no_update BEFORE UPDATE ON audit.permission_events
  FOR EACH ROW EXECUTE FUNCTION audit.reject_mutation();
CREATE TRIGGER no_delete BEFORE DELETE ON audit.permission_events
  FOR EACH ROW EXECUTE FUNCTION audit.reject_mutation();

-- The application role can append but never mutate.
GRANT INSERT, SELECT ON audit.permission_events TO app_writer;
REVOKE UPDATE, DELETE ON audit.permission_events FROM app_writer;

Step 2: Appending Chained Events in TypeScript

Every permission mutation in your service must funnel through a single appendEvent function so nothing bypasses the log. Compute the entry hash over a canonical (stable-key-order) serialization — otherwise re-serialization during verification produces a different digest and false-positive tamper alarms.

import { createHash } from "node:crypto";
import type { Pool } from "pg";

type EventType =
  | "permission_granted" | "permission_revoked"
  | "role_assigned"      | "role_revoked"
  | "role_permission_added" | "role_permission_removed";

interface PermissionEvent {
  tenantId: string;
  eventType: EventType;
  subjectId: string;
  payload: Record<string, unknown>;
  actorId: string;
  actorKind: "human" | "service" | "scim_sync";
  actorIp?: string;
}

// Canonical JSON: sorted keys so the hash is reproducible.
function canonical(value: unknown): string {
  if (Array.isArray(value)) return `[${value.map(canonical).join(",")}]`;
  if (value && typeof value === "object") {
    const obj = value as Record<string, unknown>;
    return `{${Object.keys(obj).sort()
      .map((k) => `${JSON.stringify(k)}:${canonical(obj[k])}`)
      .join(",")}}`;
  }
  return JSON.stringify(value ?? null);
}

const GENESIS = Buffer.alloc(32, 0); // 32 zero bytes seed the chain

export async function appendEvent(pool: Pool, e: PermissionEvent): Promise<void> {
  // SERIALIZABLE prevents two concurrent appends from chaining off the same prev_hash.
  const client = await pool.connect();
  try {
    await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");

    const { rows } = await client.query<{ entry_hash: Buffer }>(
      "SELECT entry_hash FROM audit.permission_events ORDER BY seq DESC LIMIT 1"
    );
    const prevHash = rows[0]?.entry_hash ?? GENESIS;

    const body = canonical({
      tenantId: e.tenantId, eventType: e.eventType, subjectId: e.subjectId,
      payload: e.payload, actorId: e.actorId, actorKind: e.actorKind,
      occurredAt: new Date().toISOString(),
    });
    const entryHash = createHash("sha256")
      .update(prevHash)
      .update(Buffer.from(body, "utf8"))
      .digest();

    await client.query(
      `INSERT INTO audit.permission_events
         (tenant_id, event_type, subject_id, payload,
          actor_id, actor_kind, actor_ip, prev_hash, entry_hash)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)`,
      [e.tenantId, e.eventType, e.subjectId, e.payload,
       e.actorId, e.actorKind, e.actorIp ?? null, prevHash, entryHash]
    );
    await client.query("COMMIT");
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

Call it from the same transaction boundary as the mutation it records. When you grant a permission, write the current-state row and append the event together; a partial failure must roll back both so the log never disagrees with the read model.

Step 3: Separating the Audit Store

Keeping the log in the same database as your mutable RBAC tables is the convenient default, but it shares a blast radius: a credential that can drop your app schema can also drop the evidence. For regulated workloads, separate the store along one of these axes:

The cheapest high-assurance upgrade is the last row: every hour, write the latest entry_hash to an append-only external sink (an object-lock S3 bucket, a transparency log, or even a signed Slack/email). An attacker who rewrites the entire table still cannot reproduce a head hash you already published off-system.

Step 4: Verifying the Chain

Verification recomputes each hash from the genesis seed forward and stops at the first mismatch. Run it as a scheduled job and on demand before any compliance export.

import { createHash } from "node:crypto";
import type { Pool } from "pg";

export async function verifyChain(pool: Pool): Promise<
  { ok: true } | { ok: false; brokenAtSeq: string }
> {
  const { rows } = await pool.query(
    `SELECT seq, tenant_id, event_type, subject_id, payload,
            actor_id, actor_kind, occurred_at, prev_hash, entry_hash
       FROM audit.permission_events ORDER BY seq ASC`
  );

  let expectedPrev = Buffer.alloc(32, 0);
  for (const r of rows) {
    const body = canonical({
      tenantId: r.tenant_id, eventType: r.event_type, subjectId: r.subject_id,
      payload: r.payload, actorId: r.actor_id, actorKind: r.actor_kind,
      occurredAt: new Date(r.occurred_at).toISOString(),
    });
    const recomputed = createHash("sha256")
      .update(expectedPrev).update(Buffer.from(body, "utf8")).digest();

    if (!recomputed.equals(r.entry_hash) || !expectedPrev.equals(r.prev_hash)) {
      return { ok: false, brokenAtSeq: String(r.seq) };
    }
    expectedPrev = r.entry_hash;
  }
  return { ok: true };
}

Step 5: Querying for Compliance and Least-Privilege Reviews

Because every change is preserved with its actor, the queries auditors actually ask become trivial SQL — no log-aggregation pipeline required.

-- Who granted/revoked anything for a given user, ever?
SELECT occurred_at, event_type, actor_id, actor_kind, actor_ip,
       payload->>'permission' AS permission
FROM audit.permission_events
WHERE tenant_id = $1 AND subject_id = $2
ORDER BY occurred_at DESC;

-- Quarterly least-privilege review: every grant still in effect that was
-- issued by a now-deactivated admin (a classic privilege-creep finding).
SELECT pe.subject_id, pe.payload->>'permission' AS permission, pe.occurred_at
FROM audit.permission_events pe
WHERE pe.tenant_id = $1
  AND pe.event_type = 'permission_granted'
  AND pe.actor_id = ANY($2::uuid[])      -- deactivated admin ids
  AND NOT EXISTS (
    SELECT 1 FROM audit.permission_events later
    WHERE later.tenant_id = pe.tenant_id
      AND later.subject_id = pe.subject_id
      AND later.event_type = 'permission_revoked'
      AND later.payload->>'permission' = pe.payload->>'permission'
      AND later.seq > pe.seq
  );

This directly supports the SOC 2 CC6.1/CC6.3 evidence of restricting and reviewing access, and the OWASP ASVS V4 requirement for an audit trail of authorization changes.

Step 6: Replaying to Reconstruct State

Because the log is the source of truth, you can fold events forward to rebuild the exact permission set as of any timestamp — for incident forensics (“what could this account do when the breach happened?”) or to rebuild a corrupted read model.

interface AuthzState {
  // subjectId -> set of permission strings
  perms: Map<string, Set<string>>;
}

export function replayUntil(
  events: Array<{ event_type: string; subject_id: string; payload: any }>,
): AuthzState {
  const state: AuthzState = { perms: new Map() };
  const setFor = (id: string) =>
    state.perms.get(id) ?? state.perms.set(id, new Set()).get(id)!;

  for (const ev of events) {
    const perm = ev.payload?.permission as string | undefined;
    switch (ev.event_type) {
      case "permission_granted":
        if (perm) setFor(ev.subject_id).add(perm);
        break;
      case "permission_revoked":
        if (perm) setFor(ev.subject_id).delete(perm);
        break;
      case "role_revoked":
        state.perms.delete(ev.subject_id);
        break;
    }
  }
  return state;
}

Fetch events with WHERE occurred_at <= $asOf ORDER BY seq ASC and the fold yields the historical state deterministically.

Prevention & Monitoring Hooks

• Scheduled chain verification. Run verifyChain hourly; page on any ok: false and capture brokenAtSeq for forensics.
• External anchoring. Publish the head entry_hash to an out-of-band, object-locked sink on a fixed cadence.
• Write-path coverage tests. Assert in CI that every RBAC mutation path calls appendEvent — an unaudited code path is the real failure mode.
• Sequence-gap alerts. Alert if seq is non-contiguous; combined with the DELETE trigger this surfaces tampering attempts.
• Actor sanity. Flag actor_kind = 'human' grants of high-risk permissions outside business hours for review, and pair this with middleware that blocks privilege escalation at the API edge.

Frequently Asked Questions

Related

• How to Structure RBAC Tables in PostgreSQL — the mutable current-state schema this log feeds and rebuilds.
• Designing Role-Based Access Control Systems — the broader RBAC architecture, including where audit trails fit.
• Preventing Privilege Escalation in API Endpoints — blocking the unauthorized grants your log is designed to surface after the fact.