Idempotent privilege apply

Idempotent privilege apply is the stage that turns a detected drift delta into the smallest possible set of GRANT and REVOKE statements and applies them so that running the same remediation twice — or ten times — leaves the database in exactly one state, with no errors, no duplicate work, and no oscillation between two configurations. It is the write half of a drift pipeline, and it is where an otherwise read-only compliance tool finally touches production.

The failure scenario this stage exists to prevent is a remediation loop that never settles. A naive “apply the desired grants” script re-issues every GRANT on every run, races a second runner into duplicate work, or — worse — flaps: one job grants a privilege the baseline wants, a stale job revokes it, and the two fight forever while the audit log fills with contradictory events. The cure is to treat apply as a pure function of the gap between current and desired state: compute the minimal delta, order it so intermediate states are always safe, wrap it in a transaction guard where the engine allows one, and prove that a second run produces an empty plan. This is the remediation core of the broader Remediation & Synchronization Pipelines work, sitting immediately downstream of the Drift Detection Engines & Diff Logic that produce the delta.

Prerequisites and scope

This stage consumes a normalized set of desired grants and a freshly extracted set of current grants, and it emits statements against a live database. Before wiring it in, confirm the following.

• Database engines. PostgreSQL 12+ (for transactional DDL, so a whole apply batch commits or rolls back as a unit) or MySQL 8.0.16+ (for REVOKE IF EXISTS, which makes revocation safe to re-run). The two engines behave differently at the point of failure, and the walkthrough below treats each explicitly.
• Python runtime. 3.11 or newer. The examples use psycopg (v3) and its psycopg.sql composition helpers so that role and object identifiers are always safely quoted — never string-formatted into DDL.
• A privileged apply role. Unlike extraction, apply must hold enough authority to grant and revoke on the target objects (WITH GRANT OPTION on the relevant tables, or ownership). Keep this role distinct from the read-only extraction role; the two halves of the pipeline should never share credentials.
• A trustworthy desired state. Apply is only as safe as the baseline it converges toward. The desired set is the output of extraction, parsing, and diffing; if that upstream is wrong, apply will faithfully enforce the wrong thing. Run this behind the dry-run planning mode until the plans are boring.

The scope is narrow on purpose: this stage does not decide whether to remediate (that is a policy gate) and it does not extract state. It takes two sets and makes the database match one of them, idempotently.

Core implementation walkthrough

The following three steps take a pair of grant sets to a reconciled database. Every block runs as-is against a real PostgreSQL instance or a fixture.

Step 1 — Compute the minimal plan from the delta

The unit of work is a single grant: a role, a privilege verb, and a schema-qualified object. Model it as a frozen, orderable dataclass so the whole pipeline can use set algebra and deterministic sorting.

from dataclasses import dataclass

@dataclass(frozen=True, order=True)
class Grant:
    role: str          # canonical role name, e.g. "app_reader"
    privilege: str     # SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
    obj: str           # schema-qualified object, e.g. "sales.orders"

@dataclass(frozen=True)
class Plan:
    revokes: tuple[Grant, ...]   # excess present now, not wanted
    grants: tuple[Grant, ...]    # wanted, not present now

def build_plan(current: set[Grant], desired: set[Grant]) -> Plan:
    """The plan is the symmetric difference, split by direction and sorted.
    Empty input on either side is fine; a matched pair yields an empty plan."""
    return Plan(
        revokes=tuple(sorted(current - desired)),
        grants=tuple(sorted(desired - current)),
    )

The plan is minimal by construction: only privileges that are genuinely excess get revoked, only genuinely missing ones get granted, and anything already correct is left untouched. That untouched middle is what makes repeated runs cheap. Verify the two properties that matter — a matched state produces nothing to do, and each side is deterministically ordered:

desired = {Grant("app_reader", "SELECT", "sales.orders")}
current = {Grant("app_reader", "SELECT", "sales.orders"),
           Grant("app_reader", "DELETE", "sales.orders")}   # one excess grant

plan = build_plan(current, desired)
print([(g.privilege, g.obj) for g in plan.revokes])   # -> [('DELETE', 'sales.orders')]
print([(g.privilege, g.obj) for g in plan.grants])    # -> []
print(build_plan(desired, desired))                   # -> Plan(revokes=(), grants=())

The set difference reads directly from a normalized current snapshot. In PostgreSQL, that snapshot comes from a read-only catalog query — the same shape the detection engines already produce:

-- PostgreSQL: current effective table grants, deterministically ordered.
SELECT grantee AS role,
       privilege_type AS privilege,
       table_schema || '.' || table_name AS obj
FROM information_schema.role_table_grants
WHERE grantee NOT IN ('postgres', 'PUBLIC')
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY role, obj, privilege;

Step 2 — Order the plan so every intermediate state is safe

Rendering statements in the wrong order can leave the database in a briefly unsafe state, or fail outright on a dependency. The house rule is revoke excess before granting missing, and within each direction, order so that dependency edges are respected. Revoking first means that when a role is simultaneously being narrowed on one object and widened on another, the narrowing lands first and the role is never transiently over-privileged. This is the same enabling-then-dependent discipline described in grant and revoke chain logic, applied to a batch.

from psycopg import sql

# Whitelist of privilege verbs; anything else is a programming error, not DDL.
_PRIVILEGES = {"SELECT", "INSERT", "UPDATE", "DELETE",
               "TRUNCATE", "REFERENCES", "TRIGGER"}

def _object_ident(obj: str) -> sql.Composed:
    schema, _, name = obj.partition(".")
    return sql.SQL("{}.{}").format(sql.Identifier(schema), sql.Identifier(name))

def _verb(privilege: str) -> sql.SQL:
    if privilege not in _PRIVILEGES:
        raise ValueError(f"refusing to render unknown privilege: {privilege!r}")
    return sql.SQL(privilege)   # a keyword, never an identifier

def render(plan: Plan) -> list[sql.Composed]:
    """Revokes first, then grants; each side already sorted by build_plan."""
    statements: list[sql.Composed] = []
    for g in plan.revokes:
        statements.append(sql.SQL("REVOKE {v} ON TABLE {o} FROM {r}").format(
            v=_verb(g.privilege), o=_object_ident(g.obj), r=sql.Identifier(g.role)))
    for g in plan.grants:
        statements.append(sql.SQL("GRANT {v} ON TABLE {o} TO {r}").format(
            v=_verb(g.privilege), o=_object_ident(g.obj), r=sql.Identifier(g.role)))
    return statements

Identifiers pass through sql.Identifier, which quotes the role and object names correctly and closes off SQL injection; privilege verbs pass through a whitelist, because a keyword cannot be a bound identifier. The full mechanics of safe DDL generation — and why REVOKE is naturally idempotent while GRANT needs care on some engines — are covered in writing idempotent GRANT and REVOKE DDL in Python.

Step 3 — Apply inside a transaction guard, then prove convergence

On PostgreSQL, GRANT and REVOKE are transactional DDL, so the entire batch can be wrapped in one transaction: either every statement lands or none does. psycopg v3 gives this to you through the connection context manager, which commits on a clean exit and rolls back on any exception.

import psycopg

def apply(conninfo: str, statements: list[psycopg.sql.Composed]) -> int:
    """Apply the whole batch atomically on PostgreSQL. Returns the count applied.
    An empty batch is a valid no-op — the convergence case."""
    if not statements:
        return 0
    with psycopg.connect(conninfo) as conn:     # commits on success, rolls back on error
        with conn.cursor() as cur:
            for stmt in statements:
                cur.execute(stmt)
    return len(statements)

Convergence is the property that makes this safe to run on a schedule. After a successful apply, the database matches the desired set, so re-extracting and rebuilding the plan yields empty revokes and grants, render returns [], and apply returns 0 without opening a write transaction at all:

# First run remediates; second run is a verified no-op.
first = apply(conninfo, render(build_plan(current, desired)))    # -> 1  (one REVOKE)
# ...re-extract `current` from the catalog; it now equals `desired`...
second = apply(conninfo, render(build_plan(desired, desired)))   # -> 0  (nothing to do)
print(first, second)   # -> 1 0

That “0” is the whole point: it is machine-checkable proof that the estate is reconciled, and it is the signal a scheduler uses to stay quiet.

Idempotency and safety contract

This stage is safe to run continuously because it satisfies four explicit guarantees.

• Minimal writes. Because the plan is a set difference, apply only ever issues statements for genuine discrepancies. A reconciled database receives zero DDL, so scheduling apply every few minutes costs a read snapshot and nothing more.
• Order-independence of outcome, order-dependence of safety. The final reconciled state does not depend on statement order, but intermediate safety does — hence revoke-before-grant and dependency-respecting order within each side. The sorted plan makes the exact statement sequence reproducible run to run, which matters for audit diffs.
• Convergence to a fixed point. Repeated application drives the system to current = desired and holds it there. The empty plan is the fixed point; reaching it and staying is the definition of “no oscillation”.
• Atomic failure on PostgreSQL, compensating failure on MySQL. On PostgreSQL a mid-batch error rolls the whole transaction back, so the database is never left half-reconciled. MySQL commits each GRANT/REVOKE implicitly and cannot roll a batch back; there, idempotency itself is the recovery mechanism — a failed run leaves a partial state that the next run’s fresh delta simply finishes. When even that is not enough, hand off to explicit rollback and recovery.

Treat the desired set as an immutable, versioned input for the duration of a run. If it can change mid-apply, you reintroduce the flapping this stage exists to eliminate.

Compliance alignment and evidence artifacts

Idempotent apply produces exactly the evidence auditors ask for about remediation: proof that a detected access discrepancy was corrected, by a named automated actor, with the precise statements recorded. The stage supports SOC 2 CC6.1 and CC6.3 (logical access is restricted and changes are controlled) and PCI-DSS Requirement 7.2 (least privilege enforced by need-to-know), because every apply run emits the ordered plan it executed, the count applied, and the before/after snapshot hashes. Because a reconciled run applies nothing, the audit trail also documents stability — a long run of zero-statement applies is evidence that no unauthorized change occurred between checks, which maps to NIST SP 800-53 AC-2(4), automated audit of account management actions.

The canonical evidence artifact is a per-run JSON record:

{
  "run_id": "apply-2026-07-18T09:14:02Z",
  "engine": "postgresql",
  "revokes": ["REVOKE DELETE ON TABLE sales.orders FROM app_reader"],
  "grants": [],
  "applied": 1,
  "converged_after": true,
  "current_hash_before": "9f2c…",
  "current_hash_after": "1a7e…"
}

Troubleshooting matrix

The failures here are quiet: the pipeline keeps running, but it either does too much, too little, or the same thing forever. Each row pairs a signature with a fix.

Related

• Writing idempotent GRANT and REVOKE DDL in Python — how each statement is rendered and made safe to re-run on both engines.
• Dry-run mode for privilege reconciliation — preview and gate the exact statements before any of them touch production.
• Rollback and Recovery — what to do when an apply must be undone rather than re-converged.
• GitOps Privilege Synchronization — driving the desired set from version control so apply enforces a reviewed baseline.
• Grant and Revoke Chain Logic — the dependency rules that decide safe ordering within a plan.

Up: Remediation & Synchronization Pipelines