RBAC Drift Detection & Compliance Sync

Dry-run mode for privilege reconciliation

Dry-run mode is a read-only planning pass that renders the exact GRANT and REVOKE statements a reconciliation would execute, diffs them against the last applied plan, and stops at a plan/apply gate — so a human or a policy check approves the change before any of it touches the target database.

When to use this — and when not to

Use a dry run when:

  • You are about to let automation write to a production database for the first time, and you need to see the blast radius before granting apply authority.
  • Your desired baseline changed (a new manifest, a new role) and you want to confirm the resulting statements match intent before they land.
  • Compliance requires a reviewable, pre-execution record of every privilege change — a plan artifact an approver signs off on.

Do not lean on it when:

  • Nothing about the plan is surprising and you already run behind a tested policy gate — a mature pipeline dry-runs continuously but auto-approves empty or known-safe plans rather than pausing for a human every time.
  • You are treating it as a substitute for a real apply. A dry run predicts; it cannot prove the statements succeed against live locks and dependencies — that is what the shadow replica check below adds.
  • The delta computation itself is untrusted. A dry run of a wrong plan just previews the wrong change; fix extraction and diffing first.
Dry-run planning with a shadow-replica check and a plan/apply gate Left to right: a drift delta is rendered into exact statements with no execution, the statements are applied to a shadow replica to verify they run cleanly, and the outcome reaches a plan slash apply gate. An approved path leads to production apply on the right. A blocked path leads down from the gate to an abort-and-report box when the shadow diff is not clean, so production is never touched on a failed plan. Drift delta revokes + grants Render statements exact SQL · no exec Apply to shadow replica · verify + diff plan / apply gate Apply prod commit Abort · report no prod change approved diff dirty
Figure 1 — The plan is rendered and validated against a shadow replica before the gate decides; production is only ever touched on an approved, clean plan.

Step-by-step implementation

Step 1 — Render the plan without a database connection

The core of dry-run is a single flag that swaps a real cursor for a recorder. The planner renders exactly the statements the idempotent privilege apply stage would issue, but in dry-run it returns them instead of executing them. Model the outcome as a typed result so plan and apply share one code path.

from dataclasses import dataclass
import psycopg
from psycopg import sql

@dataclass(frozen=True)
class ReconcileResult:
    mode: str                    # "plan" | "apply"
    statements: tuple[str, ...]  # rendered SQL text, connection-resolved
    applied: int                 # 0 in plan mode, len(statements) in apply mode

def reconcile(conninfo: str, rendered: list[sql.Composed], *, apply: bool) -> ReconcileResult:
    """One code path for plan and apply. In plan mode we open a connection only to
    resolve identifier quoting into text, then roll back without executing DDL."""
    with psycopg.connect(conninfo) as conn:
        text = tuple(s.as_string(conn) for s in rendered)
        if not apply:
            conn.rollback()                       # touch nothing; discard the transaction
            return ReconcileResult("plan", text, 0)
        with conn.cursor() as cur:
            for stmt in rendered:
                cur.execute(stmt)
        return ReconcileResult("apply", text, len(rendered))

Because plan and apply differ only by the apply flag, the statements you preview are byte-for-byte the statements you later run — there is no second renderer to drift out of sync. Verify the read-only guarantee: a plan run reports statements but applies zero.

rendered = render_pg(revokes, grants)             # from the DDL guide
plan = reconcile("dbname=warehouse", rendered, apply=False)
print(plan.mode, plan.applied, len(plan.statements))   # -> plan 0 2

Step 2 — Diff the plan against the last applied plan and gate

A plan is only actionable in context. Persist each applied plan, and on the next run diff the new statements against the last approved set. An empty diff means the estate is reconciled and the gate can auto-approve a no-op; a non-empty diff is exactly what an approver — human or policy — should review.

def plan_diff(previous: tuple[str, ...], current: tuple[str, ...]) -> dict:
    prev, cur = set(previous), set(current)
    return {
        "new_statements": sorted(cur - prev),      # would run now, did not last time
        "dropped_statements": sorted(prev - cur),  # ran last time, no longer needed
        "unchanged": sorted(cur & prev),
    }

def gate(result: ReconcileResult, diff: dict, *, auto_approve_empty: bool = True) -> str:
    if not result.statements:
        return "approved-noop"                     # nothing to do; converged
    if auto_approve_empty and not diff["new_statements"]:
        return "approved-known"                    # only re-applies previously reviewed work
    return "hold-for-review"                        # genuinely new change; require sign-off

Expected behaviour on a freshly changed baseline that introduces one new revoke:

diff["new_statements"]     -> ['REVOKE DELETE ON TABLE "sales"."orders" FROM "analytics_ro"']
gate(...)                  -> 'hold-for-review'

The gate never executes; it only classifies. Wiring its hold-for-review verdict to a review queue or a signed approval is where dry-run meets change management, the same control surface GitOps privilege synchronization formalizes with pull requests.

Step 3 — Verify the plan on a shadow replica

A dry run predicts statement text; it does not prove the statements succeed against real objects, locks, and dependencies. Close that gap by applying the plan to a short-lived shadow replica — a clone of production’s catalog state — inside a transaction you always roll back. If the batch fails on the shadow, it would fail on production; if it succeeds and the shadow’s post-state matches the desired set, the plan is verified.

def verify_on_shadow(shadow_conninfo: str, rendered: list[sql.Composed]) -> tuple[bool, str]:
    """Apply the plan to a shadow replica inside an always-rolled-back transaction.
    Returns (ok, detail). Nothing is committed, so the shadow stays reusable."""
    try:
        with psycopg.connect(shadow_conninfo) as conn:
            with conn.cursor() as cur:
                for stmt in rendered:
                    cur.execute(stmt)             # exercise locks, dependencies, ownership
            conn.rollback()                        # discard — verification only
        return True, "plan applies cleanly on shadow"
    except psycopg.Error as exc:
        return False, f"shadow apply failed: {exc.sqlstate} {exc}"

Only a plan that both passes the shadow check and clears the gate is handed to a real apply. Verify the failure path deliberately — point the shadow at a catalog missing the target table and confirm the function returns False with the sqlstate, so the gate blocks and the run aborts with a report instead of touching production.

Worked example: promoting a new baseline on PostgreSQL 15

Scenario: PostgreSQL 15, one production database and one shadow replica refreshed nightly from a catalog-only dump. A new manifest tightens analytics_ro, dropping its stray DELETE on sales.orders. The change is promoted through plan, shadow verify, gate, then apply.

rendered = render_pg(revokes=[Grant("analytics_ro", "DELETE", "sales.orders")], grants=[])

plan = reconcile("dbname=warehouse", rendered, apply=False)      # read-only preview
ok, detail = verify_on_shadow("dbname=warehouse_shadow", rendered)
diff = plan_diff(previous=load_last_plan(), current=plan.statements)
verdict = gate(plan, diff)

print(plan.applied, ok, verdict)
# -> 0 True hold-for-review

if ok and verdict != "hold-for-review":
    reconcile("dbname=warehouse", rendered, apply=True)          # only now does prod change

The plan applies nothing to production, the shadow proves the single REVOKE runs cleanly, and because the statement is new the gate holds for sign-off. After approval, the identical rendered statement applies to production — no re-render, no surprise. A second promotion of the same manifest yields an empty plan, an approved-noop, and no apply at all.

Gotchas and engine-specific notes

A rolled-back plan connection is still a connection. In plan mode the code above opens a connection to resolve identifier quoting into text, then rolls back. Use the read-only apply credentials for this, and confirm no autocommit is set, or a stray execute could leak a real change. Setting the session default_transaction_read_only on the plan role is a belt-and-braces guard.

Shadow freshness decides shadow trust. A shadow replica verifies only against the catalog state it holds. If it drifts from production — a table added yesterday, a role dropped this morning — the shadow check gives false confidence. Refresh the shadow’s catalog close to plan time, and treat a stale shadow as a blocking condition, not a warning.

MySQL cannot roll back the shadow apply. Because MySQL commits DDL implicitly, verify_on_shadow cannot use a rolled-back transaction there — the grants would persist. On MySQL, verify against a genuinely disposable shadow instance you tear down after the check, or snapshot-and-restore around it. This is the same implicit-commit constraint that shapes idempotent apply on MySQL.

Empty plans are the healthy default, not a bug. A mature pipeline produces empty plans almost every run. Do not page on them; page on plans that appear when the baseline did not change, because an unexpected non-empty plan means drift entered from outside the pipeline.

Compliance note

Dry-run mode produces a pre-execution evidence artifact that most access-change controls specifically want: the exact statements a change would make, reviewed and approved before they run. It supports SOC 2 CC6.3 and CC8.1 (changes are authorized, tested, and approved before deployment) and PCI-DSS Requirement 7.2 by demonstrating that privilege changes pass a documented plan/apply gate rather than being applied blind. The artifact is the persisted plan — rendered statements, the diff against the last plan, the shadow-verify result, and the gate verdict — which an approver signs and an auditor can replay to confirm production only ever received a reviewed, verified change.

Frequently asked questions

How is a dry run different from just reading the current grants? Reading grants tells you the current state; a dry run renders the precise statements needed to move from that state to the desired one, without executing them. It previews the action, not the inventory, so an approver reviews exactly what will change rather than inferring it from two snapshots.

Does the plan mode guarantee the apply will succeed? Not on its own. Rendering the statements proves they are well-formed, but only applying them against real objects, locks, and dependencies proves they run. That is why the shadow-replica check exists: it exercises the plan on a clone inside a rolled-back transaction, so a plan that fails there is blocked before it reaches production.

How do I verify a plan without a full production copy? Use a shadow replica that mirrors only the catalog and privilege state, not the data, and refresh it close to plan time. Apply the plan there inside a transaction you always roll back on PostgreSQL, or against a disposable instance on MySQL, then compare the post-state to the desired set before approving.

Should an empty plan be treated as an error? No. An empty plan means the database already matches the desired baseline, which is the converged, healthy state. Auto-approve it as a no-op. What deserves attention is an unexpected non-empty plan when the baseline did not change, because that signals drift introduced outside the pipeline.

Up: Idempotent Privilege Apply