Rollback trigger patterns for failed privilege sync

This page shows how to decide — deterministically and with an audit trail — whether a privilege synchronization run has failed badly enough to roll back, and which kind of rollback to fire: a bounded retry, an automatic undo, or a human-gated recovery.

The recovery machinery itself is only half the problem. The other half is the trigger: a run that raises a transient lock timeout should be retried, not rolled back; a run that raises a permission error should be rolled back immediately; a run that commits but then fails its health check must be undone even though nothing errored; and a run whose state can no longer be proven should stop and wait for a human. Getting this classification wrong is how pipelines either thrash on retries or tear down healthy access on a blip.

When to use this — and when not to

Use the trigger patterns here when:

• Your apply stage runs unattended and needs to distinguish a retryable database condition (deadlock, lock timeout, serialization failure) from a fatal one (permission denied, syntax error, missing object).
• You need every rollback to be explainable after the fact — which signal fired it, whether it was automatic or approved, and exactly which statements were compensated.
• You run against mixed engines and need the same decision logic to sit above both a transactional rollback (PostgreSQL) and a compensating one (MySQL).

Do not reach for this when:

• Your apply is fully transactional and single-statement — a lone GRANT inside one transaction needs no trigger taxonomy; commit or don’t.
• You have no verification query. Without a health check there is nothing to gate on, and you are only catching raised exceptions — fix the verification contract first.
• The “failures” are actually approved deviations. Route those through exception routing and whitelisting upstream so they never reach the apply stage as errors.

Step-by-step implementation

Step 1 — Classify the outcome from its SQLSTATE

The first job is to turn a raised exception into a decision. PostgreSQL’s psycopg (v3) exposes the five-character SQLSTATE on every error, and the SQLSTATE — not the message string — is the stable signal to classify on. Deadlocks and serialization failures are transient by definition; a permission error or an undefined object is fatal.

import psycopg

# SQLSTATE classes that are safe to retry — the transaction saw contention, not a policy error.
RETRYABLE = {
    "40001",  # serialization_failure
    "40P01",  # deadlock_detected
    "55P03",  # lock_not_available (statement lock_timeout tripped)
}
# Classes that must never be retried — the statement is wrong, not unlucky.
FATAL = {
    "42501",  # insufficient_privilege
    "42P01",  # undefined_table
    "42704",  # undefined_object (role/privilege target missing)
}

def classify(err: psycopg.Error) -> str:
    state = err.sqlstate or ""
    if state in RETRYABLE:
        return "retryable"
    if state in FATAL:
        return "fatal"
    return "fatal"   # unknown errors fail closed: treat as fatal, roll back

Verify the taxonomy resolves the two anchor cases correctly:

class _E(psycopg.Error):
    def __init__(self, s): self._s = s
    @property
    def sqlstate(self): return self._s

assert classify(_E("40P01")) == "retryable"   # deadlock -> retry
assert classify(_E("42501")) == "fatal"       # permission denied -> rollback
assert classify(_E("XX000")) == "fatal"       # unknown -> fail closed

Step 2 — Gate on a post-apply health check

Not every failure raises. A batch can commit every statement and still leave the wrong end state — for example, a revoke that stripped a read grant the service still needs. The health-check gate is a boolean assertion evaluated before commit; a False result is a rollback trigger even though no exception occurred.

-- Health check: the invariant the sync must preserve. Runs inside the apply transaction.
SELECT
    has_table_privilege('app_svc', 'orders.line_items', 'SELECT')
    AND NOT has_table_privilege('temp_intern', 'orders.line_items', 'DELETE')
    AS ok;

def health_gate(cur, verify_sql: str) -> str:
    cur.execute(verify_sql)
    return "healthy" if cur.fetchone()[0] else "health_fail"

A health_fail outcome routes to the same automatic rollback path as a fatal error — the difference is only in the audit record, which distinguishes “the database rejected our statement” from “our statement was accepted but produced the wrong access”.

Step 3 — Choose the trigger: automatic or human-gated

Classification decides what happened; the trigger policy decides who acts. Retryable conditions retry up to a bound, then fall through to rollback. Fatal errors and health failures roll back automatically. An outcome that cannot be verified at all — a lost connection mid-batch on MySQL, where you no longer know which statements committed — is the one case that must wait for a human rather than guess.

def choose_trigger(outcome: str, attempt: int, *, max_retries: int = 3) -> str:
    if outcome == "retryable":
        return "retry" if attempt < max_retries else "auto_rollback"
    if outcome in ("fatal", "health_fail"):
        return "auto_rollback"
    if outcome == "ambiguous":
        return "manual_gate"       # freeze and page; never retry an unknown state
    return "commit"

assert choose_trigger("retryable", attempt=0) == "retry"
assert choose_trigger("retryable", attempt=3) == "auto_rollback"   # bound exhausted
assert choose_trigger("ambiguous", attempt=0) == "manual_gate"

Step 4 — Append to the compensating-statement ledger

Every statement that actually executed is recorded together with the statement that would undo it, at the moment it succeeds. This ledger is what a rollback replays — on PostgreSQL it is a diagnostic; on MySQL it is the rollback itself, since the engine cannot undo committed DDL.

from dataclasses import dataclass, field

@dataclass
class Ledger:
    entries: list[tuple[str, str]] = field(default_factory=list)

    def record(self, applied: str, inverse: str) -> None:
        """Called immediately after `applied` succeeds, before the next statement."""
        self.entries.append((applied, inverse))

    def compensation(self) -> list[str]:
        """Inverses in reverse order — the exact rollback sequence."""
        return [inverse for _applied, inverse in reversed(self.entries)]

led = Ledger()
led.record("REVOKE INSERT ON orders.line_items FROM temp_intern",
           "GRANT INSERT ON orders.line_items TO temp_intern")
led.record("GRANT SELECT ON orders.line_items TO app_svc",
           "REVOKE SELECT ON orders.line_items FROM app_svc")
print(led.compensation())

Expected output:

['REVOKE SELECT ON orders.line_items FROM app_svc', 'GRANT INSERT ON orders.line_items TO temp_intern']

The ledger records inverses newest-first, so replaying it exactly reverses the order of application — the correct sequence for undoing a dependency chain.

Worked example: PostgreSQL 15, a nightly reconciliation with a mid-batch deadlock

Scenario: PostgreSQL 15, a nightly job applies a six-statement revoke/grant batch to converge a service account. On the third statement the run raises SQLSTATE 40P01 (deadlock) because a concurrent migration held a conflicting lock. On the retry, the batch reaches the fifth statement and raises 42501 — the apply role lacks grant authority on a newly added schema.

def run_batch(dsn, statements_with_inverses, verify_sql, max_retries=3):
    led = Ledger()
    for attempt in range(max_retries + 1):
        led = Ledger()
        try:
            with psycopg.connect(dsn) as conn:
                with conn.transaction():
                    with conn.cursor() as cur:
                        for applied, inverse in statements_with_inverses:
                            cur.execute(applied)
                            led.record(applied, inverse)
                        if health_gate(cur, verify_sql) == "health_fail":
                            raise psycopg.errors.RaiseException("health gate failed")
            return {"result": "committed", "attempts": attempt + 1}
        except psycopg.Error as err:
            outcome = classify(err)
            trigger = choose_trigger(outcome, attempt)
            if trigger == "retry":
                continue                          # transaction already rolled back
            return {"result": trigger, "sqlstate": err.sqlstate,
                    "compensation": led.compensation()}

Execution trace:

attempt 0: statement 3 raises 40P01  -> classify=retryable -> trigger=retry
attempt 1: statement 5 raises 42501  -> classify=fatal     -> trigger=auto_rollback
result: {'result': 'auto_rollback', 'sqlstate': '42501', 'compensation': [...]}

Because the deadlock rolled the whole transaction back automatically, attempt 1 started from the clean pre-apply state — no compensation was needed for the transient failure. The fatal error on attempt 1 also rolled back transactionally, so the returned compensation list is diagnostic evidence of what would have needed undoing on a non-transactional engine, not a set of statements the pipeline had to run. The run ends in the exact grant set it started with, and the record names 42501 as the fatal trigger.

Gotchas and engine-specific notes

PostgreSQL rollback is free; MySQL rollback is the ledger. On PostgreSQL the deadlock and the permission error both rolled back via the transaction — the ledger was never replayed. On MySQL 8, where each GRANT/REVOKE implicitly commits, the ledger’s compensation() output is the rollback and must be executed statement by statement. Classify identically, but wire the trigger to different executors per engine. The full mechanism is in rollback and recovery.

Classify on SQLSTATE, never on message text. Error strings are localized and version-dependent; err.sqlstate is stable across PostgreSQL versions and locales. Matching on "permission denied" breaks the first time the server runs in a non-English locale.

Retry bounds must be finite and jittered. An unbounded retry on 55P03 turns a lock storm into a livelock. Cap attempts (Step 3) and back off with jitter, so competing sync runs do not synchronize their retries into the same contention window.

Ambiguity is not fatal — it is worse. A connection dropped mid-batch on MySQL leaves you unable to prove which statements committed. Do not classify that as fatal and auto-compensate blindly; re-extract the live grant set, diff it against the ledger, and only compensate the statements the catalog confirms landed. When the catalog itself is unreachable, route to the manual gate.

Health checks belong inside the transaction. On PostgreSQL, evaluate the gate before the conn.transaction() block exits, so a False result raises and rolls back atomically. Checking after commit means the bad state is already durable and you are back to compensating.

Compliance note

This trigger layer supports SOC 2 CC7.4 (the entity responds to identified failures) and CC8.1 (change management) by proving that every failed privilege change was classified against a documented taxonomy and remediated by a named trigger rather than by ad-hoc judgment. Each run emits a trigger record — the SQLSTATE or health result, the classification, whether the rollback was automatic or human-approved, and the compensating statements — which is the evidence an auditor needs to confirm a partial apply never became residual access, satisfying NIST SP 800-53 AC-2’s requirement to detect and correct unauthorized account states.

Frequently asked questions

How do I tell a retryable database error from a fatal one? Classify on the five-character SQLSTATE, not the message. Serialization failures (40001), deadlocks (40P01), and lock timeouts (55P03) are transient and safe to retry within a bound. Permission denied (42501) and undefined object (42P01, 42704) are fatal and must roll back. Treat any unknown SQLSTATE as fatal so the pipeline fails closed.

Should a rollback ever be automatic, or always human-approved? Fatal errors and failed health checks should roll back automatically, because the correct state is known — return to the pre-apply snapshot. Only an unverifiable outcome, where you cannot prove which statements committed, should wait for a human, because there guessing could make the state worse.

What is a compensating-statement ledger and why keep one on PostgreSQL? It is an ordered list pairing each executed statement with the statement that undoes it, appended the moment each one succeeds. On MySQL it is the rollback mechanism itself. On PostgreSQL the transaction handles rollback, but the ledger is still valuable as tamper-evident audit evidence of exactly what the run did and could reverse.

A health check passed every statement but the access is still wrong — what triggers? That is the health_fail outcome: the statements committed but the end state violates an invariant. It routes to automatic rollback, exactly like a fatal error, but the audit record distinguishes the two so you can tell a rejected statement from an accepted-but-wrong one.

Related

• Rollback and Recovery — the recovery mechanism these triggers fire.
• Transaction-safe batch revocation in PostgreSQL — the atomic apply a clean rollback depends on.
• Idempotent Privilege Apply — rendering the statements the ledger records.

Up: Rollback and Recovery