# Audit logs append-only: no deletion, modification, or truncation

- **Pattern:** `ab-001380` (`finserv-audit-trail.transaction-logging.append-only-enforcement`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-001380
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-001380)

## Why it matters

An audit log that can be edited or deleted is not an audit log — it is a mutable ledger that any database administrator, compromised application role, or insider threat can silently rewrite. SOX §404 requires that internal controls over financial reporting be reliable; a log table with UPDATE or DELETE grants on the application role fails that test on its face. PCI-DSS 4.0 Req-10.3 requires protection of audit logs from destruction and unauthorized modification. FINRA Rule 4511 (book-and-records) requires that records be preserved in a non-rewriteable, non-erasable format. NIST 800-53 AU-9 mandates protection of audit information from unauthorized access, modification, and deletion. CWE-284 (Improper Access Control) is the vulnerability class. An attacker who gains write access to a role that can UPDATE `transaction_logs` can backdate or erase evidence of fund transfers with no trace.

## Severity rationale

Critical because a single UPDATE or DELETE on an audit row can permanently destroy the evidentiary chain of custody that regulators, courts, and forensic investigators rely on.

## Remediation

Explicitly REVOKE modification permissions in a migration under `db/migrations/` or `supabase/migrations/` — implicit defaults are not sufficient and do not count as pass:

```sql
REVOKE ALL ON transaction_logs FROM app_role;
GRANT INSERT, SELECT ON transaction_logs TO app_role;
-- Only a superuser/DBA role may ever touch rows
GRANT ALL ON transaction_logs TO dba_role;
```

For PostgreSQL with row-level security, add an INSERT-only policy and intentionally leave out UPDATE and DELETE policies so those operations are denied by default:

```sql
ALTER TABLE transaction_logs ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_insert_only ON transaction_logs
  FOR INSERT TO app_role WITH CHECK (true);
-- No UPDATE/DELETE/TRUNCATE policy means those paths are blocked.
```

## Detection

- **ID:** `append-only-enforcement`
- **Severity:** `critical`
- **What to look for:** Enumerate all database roles with access to the audit log table(s) and list all permissions granted. Count every role that has UPDATE, DELETE, or TRUNCATE permissions — quote the actual GRANT/REVOKE statements or RLS policies found. Check for column-level and row-level constraints that prevent modification. A role with UPDATE permission on even one column of the audit table must not pass — do not pass if any application role can modify rows.
- **Pass criteria:** The audit log table(s) have explicit permissions that prevent UPDATE, DELETE, TRUNCATE operations from all application roles — at least 1 explicit REVOKE or RLS policy must be documented. Only INSERT and SELECT are permitted. Report the ratio of restricted vs. unrestricted roles (e.g., "3 of 3 app roles restricted to INSERT/SELECT only").
- **Fail criteria:** The audit log table allows UPDATE or DELETE operations for any application role, or TRUNCATE is permitted without administrative override, or no explicit permission restrictions are documented (implicit defaults do not count as pass).
- **Skip (N/A) when:** Never — append-only is fundamental to audit trails.
- **Detail on fail:** Specify which operations are allowed on the audit log table. Example: `"audit_logs table has UPDATE and DELETE permissions for app_role — 2 of 3 roles unrestricted"` or `"Database allows TRUNCATE on audit_logs without requiring superuser role"`.
- **Cross-reference:** Check `finserv-audit-trail.balance-reconciliation.access-control` for role-based read restrictions, and `finserv-audit-trail.retention-compliance.tamper-evidence` for cryptographic tamper detection.
- **Remediation:** Explicitly revoke modification permissions at the database level (in `db/migrations/` or `supabase/migrations/`):

  ```sql
  REVOKE ALL ON transaction_logs FROM app_role;
  GRANT INSERT, SELECT ON transaction_logs TO app_role;
  GRANT UPDATE, DELETE, TRUNCATE ON transaction_logs TO superuser_role ONLY;
  ```

  For PostgreSQL with row-level security:

  ```sql
  ALTER TABLE transaction_logs ENABLE ROW LEVEL SECURITY;
  CREATE POLICY rls_append_only ON transaction_logs
    FOR INSERT WITH CHECK (true);
  -- No UPDATE, DELETE, or TRUNCATE policies
  ```

  If using a document database (DynamoDB, Firestore), use similar immutability patterns: immutable document IDs, TTL-based archival only (no delete API), signed transaction records.

---

## External references

- cwe CWE-284
- nist AU-9
- pci-dss Req-10.3
- sox Section 404
- external FINRA-Rule-4511 — https://www.finra.org/rules-guidance/rulebook/finra-rules/4511

Taxons: regulatory-conformance, data-integrity

HTML version: https://auditbuffet.com/patterns/ab-001380