Hash-chaining drift audit logs for tamper evidence

This page shows how to make an RBAC drift audit log tamper-evident by giving every entry a SHA-256 that embeds the previous entry’s hash, so any later edit, deletion, or re-ordering of history breaks the chain at a position you can name.

When to use this — and when not to

Reach for a hash chain when:

• Your drift log is written by the same platform whose access it audits, so “trust the storage” is not an answer an auditor will accept.
• You need to prove not just that each record is intact but that the sequence was not re-ordered or silently truncated — deletion of an inconvenient row is the attack you care about.
• You want detection that is independent of the database’s own audit features, verifiable offline from exported files alone.

Do not reach for it when:

• You need to prevent rather than detect tampering — chaining is tamper-evident, not tamper-proof; pair it with append-only storage and an Ed25519 signature over the chain head for provenance.
• Records are legitimately edited in place after the fact — a chain assumes append-only; if your workflow mutates history, fix the workflow first.
• You only need to prove one record’s membership in a large batch without revealing the rest — that is a Merkle inclusion proof, a variation noted at the end.

Step-by-step implementation

Step 1 — Define the entry and a canonical hash

An entry carries its sequence number, the previous entry’s hash, and the record payload. The entry hash is SHA-256 over a canonical serialization of exactly those three fields — canonical so that re-hashing the same logical entry always yields the same digest.

import hashlib
import json
from pydantic import BaseModel

GENESIS = "0" * 64   # fixed anchor: the "previous hash" of the first entry

class ChainEntry(BaseModel):
    seq: int
    prev_hash: str
    record: dict          # the drift evidence payload for this run

def entry_hash(entry: ChainEntry) -> str:
    body = {"seq": entry.seq, "prev_hash": entry.prev_hash, "record": entry.record}
    raw = json.dumps(body, sort_keys=True, separators=(",", ":")).encode("utf-8")
    return hashlib.sha256(raw).hexdigest()

Verify the digest is deterministic and 64 hex chars:

e = ChainEntry(seq=0, prev_hash=GENESIS, record={"run": 1, "drift": "grant SELECT"})
h = entry_hash(e)
assert entry_hash(e) == h          # deterministic
assert len(h) == 64 and int(h, 16) >= 0   # valid SHA-256 hex digest

Step 2 — Append entries, each linking to the last

Appending computes the new entry’s prev_hash from the current chain head, builds the entry, and stores the entry’s own hash alongside it. Keeping the computed hash next to the entry is what lets verification later detect a mismatch without re-deriving trust from anywhere else.

def append_record(chain: list[dict], record: dict) -> dict:
    prev = chain[-1]["entry_hash"] if chain else GENESIS
    entry = ChainEntry(seq=len(chain), prev_hash=prev, record=record)
    stored = entry.model_dump()
    stored["entry_hash"] = entry_hash(entry)
    chain.append(stored)
    return stored

chain: list[dict] = []
append_record(chain, {"run": 1, "drift": "grant SELECT on cust.cards"})
append_record(chain, {"run": 2, "drift": "revoke INSERT on orders"})
append_record(chain, {"run": 3, "drift": "grant ALL on staging.tmp"})

Confirm the links actually point back correctly:

assert chain[0]["prev_hash"] == GENESIS
assert chain[1]["prev_hash"] == chain[0]["entry_hash"]
assert chain[2]["prev_hash"] == chain[1]["entry_hash"]

Step 3 — Verify the chain and locate any break

Verification walks the chain from genesis, re-deriving each entry’s hash and checking three things: the sequence increments, the stored prev_hash matches the running head, and the recomputed hash matches the stored one. The first failure returns the offending index — auditors want the position, not just a boolean.

def verify_chain(chain: list[dict]) -> tuple[bool, int | None, str]:
    expected_prev = GENESIS
    for i, e in enumerate(chain):
        if e["seq"] != i:
            return (False, i, "sequence gap — an entry was inserted or removed")
        if e["prev_hash"] != expected_prev:
            return (False, i, "broken link — previous hash does not match")
        recomputed = entry_hash(ChainEntry(seq=e["seq"], prev_hash=e["prev_hash"],
                                           record=e["record"]))
        if recomputed != e["entry_hash"]:
            return (False, i, "content tampered — record no longer matches its hash")
        expected_prev = e["entry_hash"]
    return (True, None, "intact")

Verify against both an intact and a tampered chain:

print(verify_chain(chain))            # (True, None, 'intact')
chain[1]["record"]["drift"] = "revoke NONE"   # falsify the middle entry
print(verify_chain(chain))            # (False, 1, 'content tampered — record no longer matches its hash')

Expected output:

(True, None, 'intact')
(False, 1, 'content tampered — record no longer matches its hash')

Worked example: a quarterly drift log on PostgreSQL 15

Scenario: PostgreSQL 15, one drift run per night for a quarter, each run’s evidence record derived from information_schema.role_table_grants. An operator with write access to the log table later edits run 47 to erase a GRANT ALL PRIVILEGES they issued during an incident and never revoked. The nightly extraction itself is a read-only catalog query, so the log is the only mutable surface:

SELECT grantee, privilege_type,
       table_schema || '.' || table_name AS object_scope
FROM information_schema.role_table_grants
WHERE grantee NOT IN ('postgres', 'PUBLIC')
ORDER BY grantee, table_schema, table_name;

Each night’s rows become a record and are appended with append_record. When the auditor runs verify_chain over the exported log, the edited run 47 fails: its recomputed hash no longer equals the hash entry 48 recorded as its prev_hash, so verification returns (False, 47, 'content tampered …'). The operator would have had to re-hash and re-store every entry from 47 to the end to hide the edit — and even that is defeated once the chain head is signed, because they cannot forge a signature over the new head. The edit that looked invisible in a plain table is now a named, positioned finding.

Gotchas and engine-specific notes

Canonicalization is the whole game. If the bytes you hash on append differ from the bytes you hash on verify — a re-ordered key, a float rendered 1.0 versus 1, a timezone normalized differently — every entry fails verification for a reason that has nothing to do with tampering. Route every hash through one serializer with sort_keys=True and fixed separators, and store timestamps as canonical strings, not native datetimes.

PostgreSQL vs MySQL record sourcing. The chain does not care where records come from, but the record content must be deterministic across engines. PostgreSQL grants come from information_schema.role_table_grants and role edges from pg_auth_members; MySQL 8 exposes role edges in mysql.role_edges and does not treat a granted role as active until SET ROLE or a default role activates it. Normalize both to the same record schema before hashing, or a MySQL log and a PostgreSQL log of the “same” state will chain to different hashes.

Append-only is a storage property, not a code property. The chain detects tampering; it cannot stop an attacker who rewrites the entire log and recomputes every hash. Store entries where in-place edits are hard — S3 Object Lock, an append-only table with revoked UPDATE/DELETE, or WORM storage — and cover the durable retention side with querying immutable drift history for auditors.

A whole-log rewrite still forges cleanly — until you sign. Because anyone can recompute a self-consistent chain, integrity without provenance is incomplete. Sign the chain head so a rewrite is detectable even when internally consistent; that is the job of signing compliance manifests with Ed25519.

Compliance note

A verifiable hash chain over drift evidence directly supports NIST SP 800-53 AU-9 (protection of audit information from unauthorized modification) and the audit-trail integrity intent of PCI-DSS Requirement 10.5, which expects that logs cannot be altered without detection. The artifact you hand an auditor is the exported chain — each entry’s seq, prev_hash, record, and entry_hash — together with the verify_chain result. Because verification is offline and independent of your database, it satisfies SOC 2 CC7.x expectations that monitoring evidence be reliable rather than self-attested, and it slots into the broader control mapping in evidence integrity and signing.

Frequently asked questions

Does hash-chaining prevent tampering or just detect it? It detects, it does not prevent. A chain makes any edit, deletion, or re-order evident at a specific position, but a determined attacker with full write access can rewrite every entry into a new self-consistent chain. Pair chaining with append-only storage and a signature over the chain head to close that gap.

Why embed the previous hash instead of just hashing each record? Hashing each record independently proves the record’s content but says nothing about order or completeness — an attacker could delete a whole record and nothing would notice. Embedding the prior hash binds the sequence together, so removing or re-ordering any entry breaks the link at a detectable point.

What should the genesis previous-hash be? Any fixed, well-known constant works; sixty-four zeros is conventional and unambiguous. The only requirement is that verifiers agree on the same anchor, since the first entry’s prev_hash is what the whole chain is validated against.

How do I prove one record without exposing the whole log? Use a Merkle tree instead of a linear chain: hash the records into a tree, chain only the root, and hand an auditor a short inclusion proof — the sibling hashes on the path from a leaf to the root. That proves one record’s membership without revealing the others.

Related

• Signing Compliance Manifests with Ed25519 — add provenance so a whole-log rewrite is also caught
• Querying Immutable Drift History for Auditors — the append-only storage the chain relies on
• Audit Report Artifact Formats — the canonical record shapes each entry wraps

Up: Evidence Integrity and Signing