Rollback and recovery

When a privilege synchronization run fails partway — a network blip after nine of fourteen REVOKE statements, a permission error on the tenth, a health check that trips after the batch commits — the target database is left in a state that matches neither the old baseline nor the intended one. Rollback and recovery is the discipline that guarantees a partial apply is never a terminal state: either the change lands whole, or the database is returned to the exact grant set it held before the run began, with an audit record of what happened.

The failure scenario this section prevents is the silent half-apply. A reconciliation job revokes a compromised service account’s write access, then dies before it re-grants the read access that account legitimately needs — and now a nightly export breaks at 03:00 with no obvious cause. On engines where privilege DDL is transactional this is a solved problem; on engines where it is not, safety has to be engineered from compensating actions and snapshots. This guide is the recovery layer of the broader Remediation & Synchronization Pipelines domain, sitting directly beneath the apply stage so that every write the pipeline issues is reversible.

Prerequisites and scope

Recovery logic wraps the apply stage; it does not replace it. Before wiring rollback into a live pipeline, confirm the pieces it depends on are in place.

• Database engines: PostgreSQL 12+ (all privilege DDL — GRANT, REVOKE, ALTER DEFAULT PRIVILEGES — runs inside a transaction and rolls back cleanly) or MySQL 8.0+ (where GRANT and REVOKE trigger an implicit commit and therefore cannot be rolled back by the server). The engine distinction is the single most important design input on this page.
• Python runtime: 3.11+ with psycopg (v3) for PostgreSQL. psycopg’s Connection.transaction() context manager and its nested savepoint support are used directly here; MySQL uses a driver such as mysqlclient or PyMySQL purely to execute compensating statements one at a time.
• A snapshot sink: an append-only store (object storage, a version-controlled repo, or an audit table) that holds the pre-apply grant set as a replayable artifact. This is what a compensating rollback and a post-failure reconciliation both read from.
• Catalog read permissions: the same read-only role used by extraction, so the snapshot and the reconciliation re-diff query the catalog without any write authority of their own. The apply role that issues DDL is separate and least-privileged.

The scope is deliberately narrow. This section decides how to get back to a known-good state after an apply attempt; it assumes the intended target set was already produced upstream by the diff engine and passed through Exception Routing and Whitelisting, and that the statements themselves were rendered by the idempotent apply layer described in Idempotent Privilege Apply.

Core implementation walkthrough

The following steps take one apply attempt from a pre-flight snapshot through either a clean commit or a full recovery. Each block is runnable against a real database or a fixture.

Step 1 — Capture the prior grant state before touching anything

Recovery of any kind needs a truthful record of what the database looked like before the run. On PostgreSQL this snapshot is a safety net you rarely restore from because the transaction already protects you; on MySQL it is the source of truth a compensating rollback is built from. Either way, capture it with the same deterministic, read-only query the extractor uses so the snapshot is byte-stable and comparable:

-- PostgreSQL: pre-apply snapshot of effective object grants, deterministic order.
SELECT
    grantee        AS principal,
    privilege_type AS privilege,
    table_schema || '.' || table_name AS object,
    is_grantable
FROM information_schema.role_table_grants
WHERE grantee NOT IN ('PUBLIC')
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY principal, object, privilege;

Persist the result as a set of tuples plus a content hash, so the reconciliation step can later prove whether the database returned to this exact state:

import hashlib
import json

def snapshot_fingerprint(rows: list[tuple]) -> str:
    """Content hash of a sorted grant set — same input, same digest, every run."""
    canonical = json.dumps(sorted(rows), separators=(",", ":"))
    return hashlib.sha256(canonical.encode("utf-8")).hexdigest()

prior = [("app_svc", "SELECT", "orders.line_items", "NO"),
         ("app_svc", "INSERT", "orders.line_items", "NO")]
print(snapshot_fingerprint(prior))  # deterministic, e.g. '3f9c...'

Step 2 — Apply inside a transaction with a health-check gate (PostgreSQL)

On PostgreSQL the whole run — every GRANT and REVOKE, plus a verification query — lives inside one transaction. If any statement raises, or if the post-apply health check fails, the context manager rolls the entire batch back and the database never observed a partial state. This is the mechanism the grant and revoke chain logic relies on to keep dependent grants consistent.

import psycopg

class HealthCheckFailed(RuntimeError):
    """Raised to force a transaction rollback when post-apply state is wrong."""

def apply_with_gate(dsn: str, statements: list[str], verify_sql: str) -> str:
    """Apply a privilege batch atomically; commit only if the health check passes."""
    with psycopg.connect(dsn) as conn:          # autocommit is False by default
        try:
            with conn.transaction():            # BEGIN ... COMMIT / ROLLBACK
                with conn.cursor() as cur:
                    for stmt in statements:
                        cur.execute(stmt)       # GRANT / REVOKE DDL, all transactional
                    cur.execute(verify_sql)
                    ok = cur.fetchone()[0]
                    if not ok:
                        # Raising inside the block triggers ROLLBACK, not COMMIT.
                        raise HealthCheckFailed(verify_sql)
            return "committed"
        except HealthCheckFailed:
            return "rolled_back:health"
        except psycopg.Error:
            return "rolled_back:error"

The verification query is a real assertion about the intended end state — for example, that a service account retained the read access it is supposed to keep after a write revocation:

-- Returns TRUE only if the invariant the sync must preserve still holds.
SELECT has_table_privilege('app_svc', 'orders.line_items', 'SELECT') AS ok;

Because the raise happens inside conn.transaction(), psycopg emits ROLLBACK and the eight statements that already ran are undone atomically. No compensating logic is needed — the engine is the recovery mechanism.

Step 3 — Compensating rollback where DDL auto-commits (MySQL)

MySQL does not offer this luxury. Every GRANT and REVOKE performs an implicit commit, so by the time statement ten fails, statements one through nine are already durable and ROLLBACK does nothing. Recovery here means computing and executing the inverse of each statement that actually succeeded — a compensating action — in reverse order.

def inverse_statement(stmt: str) -> str:
    """Map a privilege statement to the compensating action that undoes it.
    GRANT -> REVOKE the same privilege; REVOKE -> re-GRANT from the snapshot."""
    head, tail = stmt.split(" ", 1)
    verb = head.upper()
    if verb == "GRANT":
        return "REVOKE " + tail.replace(" TO ", " FROM ", 1)
    if verb == "REVOKE":
        return "GRANT " + tail.replace(" FROM ", " TO ", 1)
    raise ValueError(f"no inverse for: {stmt!r}")

def compensate(cursor, applied: list[str]) -> list[str]:
    """Undo the statements that already committed, newest first."""
    undone = []
    for stmt in reversed(applied):
        cursor.execute(inverse_statement(stmt))  # each is its own implicit commit
        undone.append(stmt)
    return undone

Two properties make this trustworthy. First, only statements that succeeded are recorded in applied, so the compensation targets exactly the committed subset. Second, a REVOKE that is compensated by re-granting must restore the privilege with its original grant option and scope, which is why the snapshot from Step 1 — not the statement text alone — is the authoritative source for what to put back. When in doubt, replay the snapshot rows for the affected principal rather than inferring the inverse.

Step 4 — Reconcile after the dust settles

Whether the rollback was a transactional undo or a compensating replay, the run ends with a reconciliation pass: re-extract the live grant set and compare its fingerprint against the pre-apply snapshot (for a rollback) or the intended target (for a commit). This is what turns “we think it rolled back” into “the database provably matches state X”.

def reconcile(live_rows: list[tuple], expected_fp: str) -> dict:
    """Confirm the database converged to the expected grant set."""
    live_fp = snapshot_fingerprint(live_rows)
    return {
        "converged": live_fp == expected_fp,
        "expected": expected_fp,
        "observed": live_fp,
    }

If converged is False after a rollback, the recovery itself is incomplete — the correct response is to halt automated apply, freeze the affected principals, and page a human, never to retry blindly on top of an unknown state.

Idempotency and safety contract

Recovery is only trustworthy if re-running it cannot make things worse. The contract this section holds to:

• A partial apply is never a resting state. Every apply attempt terminates in exactly one of two committed states — the full new target set, or the exact pre-apply snapshot. There is no third outcome the pipeline is allowed to stop on.
• Rollback is convergent, not additive. On PostgreSQL, rollback is the engine’s own ROLLBACK and leaves zero residue. On MySQL, compensation restores privileges from the snapshot rather than from guessed inverses, so re-running a compensation that already completed is a no-op — it re-asserts a state the database already holds.
• Read-only recovery inputs. The snapshot and the reconciliation re-diff both use the read-only catalog role. The only component with write authority is the apply role, and it is invoked exclusively inside the gated transaction (PostgreSQL) or the compensation loop (MySQL).
• Fail closed on ambiguity. If reconciliation cannot prove convergence, automated apply stops. A pipeline that retries against an unverified state is how a half-apply becomes a full outage. Aligning the freeze with a dry-run preview — the mechanism described in the sibling apply guide — lets an operator inspect the intended change before re-enabling writes.

Compliance alignment and evidence artifacts

Rollback and recovery produce exactly the evidence an auditor asks for after any privileged change: proof that a failed modification did not leave residual access. Each run emits a recovery record — the pre-apply snapshot fingerprint, the statements attempted, the outcome (committed, rolled_back:health, rolled_back:error), the compensation list where applicable, and the post-recovery reconciliation result.

• SOC 2 CC7.4 / CC8.1 — the recovery record is the change-management evidence that a failed apply was detected and remediated to a known state rather than left partially applied.
• PCI-DSS Req. 7.2 — proving that a revoked privilege was either fully removed or fully restored (never half-removed) demonstrates least-privilege was continuously enforced across the failure.
• HIPAA §164.312(a)(1) — the append-only recovery log records every access-control decision, including the ones that were undone.
• NIST SP 800-53 AC-2 — the reconciliation fingerprint is machine-verifiable evidence that account privileges returned to their authorized baseline.

The canonical artifact is one JSON recovery record per apply attempt, carrying both fingerprints so an auditor can independently confirm the database converged.

Troubleshooting matrix

Detecting the failure in the first place

A recovery path is only as good as the trigger that fires it. Classifying an error as retryable versus fatal, deciding between an automatic and a human-gated rollback, and keeping a compensating-statement ledger are covered in rollback trigger patterns for failed privilege sync. The atomic-revocation mechanics that make a PostgreSQL rollback clean — savepoints, SET LOCAL guards, and correct revoke ordering — are the subject of transaction-safe batch revocation in PostgreSQL.

Related

• Rollback trigger patterns for failed privilege sync — how to detect a partial apply and decide when to fire recovery.
• Transaction-safe batch revocation in PostgreSQL — revoking many grants atomically with savepoints and correct ordering.
• Idempotent Privilege Apply — the apply stage this recovery layer wraps.
• Grant and Revoke Chain Logic — why revoke ordering and dependency chains matter to a clean rollback.
• Exception Routing and Whitelisting — filtering approved deltas before they ever reach the apply stage.

Up: Remediation & Synchronization Pipelines