Remediation & Synchronization Pipelines

Detecting and scoring drift only tells you that live database privileges have diverged from the access policy you approved; it does not close the gap. Remediation and synchronization pipelines own the stage that comes next — safely converging the runtime grant set of every PostgreSQL and MySQL instance back to the version-controlled manifest without ever compromising availability. This is the highest-consequence stage in the whole control loop, because it is the only one that issues writes against production catalogs. A scoring bug produces a bad report; an apply bug revokes the wrong service account and takes down a payment path. The engineering standard here is therefore uncompromising: every apply is idempotent, every batch is transactional or compensatable, every plan is dry-runnable, and every action is recoverable and evidenced. This section is owned by database reliability engineers and platform operators, but its evidence output is consumed directly by the compliance officers who sign SOC 2, HIPAA, and PCI DSS attestations.

The Convergence and Idempotency Contract

A remediation pipeline is, formally, a convergent function. Given the current observed grant set O and the desired manifest set D, an apply run must move the database toward the state O = D and, critically, must be a no-op when that state is already reached. This is the idempotency contract: applying the same plan against a fully converged database changes nothing, errors on nothing, and produces the same evidence as the run before it. Idempotency is not a nicety here — it is the property that makes retries, partial-failure recovery, and continuous reconciliation safe. A pipeline you cannot re-run without fear is a pipeline you cannot operate.

Convergence is computed from the drift delta, never from a wholesale re-grant. The delta arrives from Drift Detection Engines & Diff Logic already split into missing = D − O (grants the policy requires but the database lacks) and excess = O − D (grants present but unauthorized). The remediation plan applies only this minimal symmetric difference. Re-granting the entire manifest on every run would technically converge, but it would obscure intent, bloat the audit trail, and multiply the blast radius of a bad manifest. The minimal-delta rule keeps every apply small, reviewable, and attributable.

Three invariants make convergence trustworthy. First, deterministic ordering: revokes are always planned and executed before grants, and within each phase statements are sorted by a stable key, so two runs over the same delta emit byte-identical plans and dependency conflicts resolve the same way every time. Second, dependency-aware sequencing: because privileges cascade through role membership, a revoke that would orphan a dependent grant must be ordered after the dependents are handled, and a grant that depends on a role’s existence must follow that role’s creation — the cascade semantics are grounded in Grant and Revoke Chain Logic. Third, verify-then-trust: an apply is not considered complete when the driver returns success; it is complete only when a post-apply reconcile re-diffs live state against the manifest and confirms the delta is empty. A zero exit code is a claim; an empty re-diff is proof.

The unit that flows through the pipeline is the remediation plan — an ordered, hash-identified list of statements, each tagged with the drift record it resolves, the compliance controls that record touches, and its phase (revoke or grant). That plan is the artifact you dry-run, apply, roll back, and archive as evidence, and keeping its shape stable is what lets the same code drive PostgreSQL and MySQL from one path.

Cross-Engine Scope: Transactional DDL Versus Implicit Commit

The single most important engine difference in this entire domain is how each database treats DDL inside a transaction, and getting it wrong is how a “safe” remediation script corrupts a privilege set. PostgreSQL supports transactional DDL: GRANT and REVOKE are transactional statements, so an entire remediation batch can be wrapped in BEGIN … COMMIT, and if any statement fails or a verification check inside the transaction fails, a single ROLLBACK atomically undoes every grant and revoke in the batch as though none had run. This is the foundation of transaction-safe batch remediation on PostgreSQL and is documented in the PostgreSQL transaction and GRANT reference.

MySQL 8 behaves fundamentally differently. Most DDL and access-control statements — including GRANT and REVOKE — cause an implicit commit: MySQL commits the current transaction before executing the statement and cannot roll it back, a behavior spelled out in the MySQL statements that cause an implicit commit reference. The practical consequence is decisive: you cannot wrap a MySQL GRANT/REVOKE batch in a BEGIN … ROLLBACK and expect it to undo cleanly, because each statement has already committed the moment it ran. A mid-batch failure on MySQL therefore leaves a genuinely half-applied state that no transaction rollback can reverse.

Remediation on MySQL must instead be built on compensation. Before the batch runs, the pipeline captures the exact prior grant state for every principal it will touch. If a statement fails partway through, recovery is not a rollback but the emission of the inverse statements — a REVOKE to undo each GRANT that committed, and a GRANT to restore each privilege that a committed REVOKE removed — driving the database back to the captured pre-batch state. This makes the compensation set itself a first-class artifact that must be computed and stored before the first write, not improvised after a failure. The two models are reconciled behind one interface: the planner emits an engine-neutral ordered plan, and the executor chooses a transactional strategy on PostgreSQL or a capture-and-compensate strategy on MySQL. The detailed patterns live in Rollback and Recovery, which treats PostgreSQL’s ROLLBACK and MySQL’s compensating statements as two implementations of the same recovery guarantee. Beyond DDL semantics, the engines diverge in privilege granularity — PostgreSQL’s ACL arrays and row-level policies versus MySQL’s global, database, table, and routine scopes stored across mysql.db and the information_schema privilege views — a normalization concern handled upstream in extraction.

The Python Automation Surface

Remediation is implemented as typed, testable Python that treats the manifest as infrastructure-as-code and the apply step as a convergent function of the drift delta. The planner sorts the delta into a deterministic, dependency-aware statement sequence; the executor applies it under the engine-appropriate safety model. A pydantic model gives the plan validation and a schema auditors can read, and networkx resolves the role-dependency order that governs safe revocation.

import networkx as nx
from pydantic import BaseModel

class PlannedStatement(BaseModel):
    phase: str          # "revoke" or "grant"
    principal: str
    obj: str            # schema-qualified object, e.g. "sales.orders"
    privilege: str
    sql: str
    drift_record_id: str

def order_dependencies(edges: list[tuple[str, str]]) -> list[str]:
    """Topologically sort roles so a role is revoked only after its dependents.

    edges: (member, granted_role) pairs from pg_auth_members / mysql.role_edges.
    Returns dependents-first order for the revoke phase; reverse it for grants.
    """
    g = nx.DiGraph()
    g.add_edges_from(edges)
    if not nx.is_directed_acyclic_graph(g):
        raise ValueError("role graph has a cycle; refusing to plan a revoke order")
    return list(nx.topological_sort(g))

def build_plan(delta: dict[str, set], role_edges: list[tuple[str, str]]) -> list[PlannedStatement]:
    """Revokes before grants; each phase sorted by the dependency order."""
    order = {role: i for i, role in enumerate(order_dependencies(role_edges))}
    plan: list[PlannedStatement] = []
    for g in sorted(delta["excess"], key=lambda x: (-order.get(x.principal, 0), x.obj, x.privilege)):
        plan.append(PlannedStatement(
            phase="revoke", principal=g.principal, obj=g.obj, privilege=g.privilege,
            sql=f'REVOKE {g.privilege} ON {g.obj} FROM "{g.principal}";',
            drift_record_id=g.record_id))
    for g in sorted(delta["missing"], key=lambda x: (order.get(x.principal, 0), x.obj, x.privilege)):
        opt = " WITH GRANT OPTION" if g.grantable else ""
        plan.append(PlannedStatement(
            phase="grant", principal=g.principal, obj=g.obj, privilege=g.privilege,
            sql=f'GRANT {g.privilege} ON {g.obj} TO "{g.principal}"{opt};',
            drift_record_id=g.record_id))
    return plan

The executor is where the two engine strategies diverge. On PostgreSQL, psycopg (v3) runs the whole plan inside one transaction so a failure or a failed in-transaction verification triggers an atomic ROLLBACK:

import psycopg

def apply_postgres(dsn: str, plan: list[PlannedStatement], *, dry_run: bool) -> None:
    """Transactional apply: the with-block commits on success, rolls back on any error."""
    with psycopg.connect(dsn) as conn:  # opens a transaction
        with conn.cursor() as cur:
            for stmt in plan:
                if dry_run:
                    print(stmt.sql)      # render only; no write
                    continue
                cur.execute(stmt.sql)
            if dry_run:
                conn.rollback()          # guarantee zero side effects
                return
        # leaving the with-block COMMITs; any exception above rolls the batch back

On MySQL, where each GRANT/REVOKE implicitly commits, the executor first captures the compensation set, then applies statement by statement, emitting inverse statements if any step fails. High-throughput catalog reads that feed the pre-batch capture use asyncpg or a pooled MySQL driver, and the driver trade-offs matter enough to weigh deliberately. The dry-run path — rendering the exact plan without writing — is the operator’s approval gate and is covered in depth by Idempotent Privilege Apply, which specifies how repeated runs converge and why a REVOKE of an absent grant and a GRANT of an existing one must both be no-ops rather than errors.

Compliance Control Mapping

Remediation is not just an operational fix; each converging action is a control activity that produces defensible evidence. Every applied plan maps to the regulatory controls the resolved drift touched, so an auditor can trace a live-state change back to an approved manifest, an authorized actor, and a signed record. The mapping is explicit and version-controlled.

Because every plan carries the drift record IDs it resolves, the evidence record links each committed statement to the control it enforces. Producing that signed, tamper-evident record continuously — rather than reconstructing it during an audit window — is the compliance payoff of the pipeline, and its format, hashing, and signing are owned by Compliance Evidence & Audit Reporting.

Failure Modes and Edge Cases

A remediation pipeline is judged on how it degrades, because it is the one stage that writes to production. Each of these modes must be designed for, not discovered.

• Half-applied MySQL batches. Because MySQL commits each GRANT/REVOKE implicitly, a mid-batch failure leaves a genuinely partial state that no ROLLBACK can reverse. The compensation set must be captured before the first write so recovery can emit the exact inverse statements; a pipeline that computes compensation only after a failure has already lost the information it needs.
• Revoking a still-depended-on privilege. Revoking a role or grant that dependent service accounts inherit can cascade into an outage. Dependency ordering must place revokes after their dependents are handled, and a revoke that would orphan a live production principal is held for explicit review rather than auto-applied.
• Availability-threatening applies. A plan that would revoke the connection path of an active application must never run blind. The dry-run diff and a shadow-replica verification gate the apply, and a plan touching a critical principal requires human approval before it reaches production.
• Manifest is itself wrong. GitOps makes version control the source of truth, so a bad commit can instruct the pipeline to revoke legitimate access estate-wide. Sync applies the minimal delta and stages high-severity revokes behind approval, so a mistaken manifest cannot silently strip production privileges in one run.
• Legitimate exceptions treated as drift. Break-glass access and time-bound maintenance grants are real deviations that must not be auto-reverted. They are reconciled against the exception register before planning, using the routing and expiry rules in Exception Routing and Whitelisting, so remediation converges toward policy without clobbering an approved temporary grant.
• Non-convergent oscillation. A manifest that conflicts with a database-enforced default (an implicit PUBLIC grant, a MySQL default role) can make each run undo the last, flapping a privilege forever. The verify step detects a non-empty re-diff after a “successful” apply and halts the loop for operator handoff instead of oscillating.

When an apply fails, the pipeline falls back through a defined sequence: dry-run confirmation, transactional rollback on PostgreSQL or compensating statements on MySQL, a post-recovery reconcile to confirm the pre-batch state was restored, and finally manual handoff with full evidence. This chain guarantees that synchronization never compromises database availability or leaves privileges in an undefined state.

How the Remediation Subsystems Fit Together

The pipeline is built from three cooperating subsystems, each documented in depth on its own page.

• Idempotent Privilege Apply — the convergence core that renders and executes the minimal GRANT/REVOKE delta so that repeated runs are no-ops, plus the dry-run mode that lets operators preview and approve the exact statement set before any write reaches production. Start here if your remediation script errors on a second run or applies more than it should.
• Rollback and Recovery — the recovery guarantee behind every apply, implemented as PostgreSQL transactional ROLLBACK where DDL is transactional and as pre-captured compensating statements on MySQL where DDL implicitly commits. This is the section that keeps a failed sync from becoming an outage, and it is where transaction-safe batch revocation and rollback-trigger patterns are specified.
• GitOps Privilege Synchronization — the delivery model that makes a version-controlled manifest the single source of truth for database privileges, reconciling live state to the committed manifest inside CI pipelines so every access change is a reviewed, merged, and evidenced commit rather than an ad-hoc production edit.

Read together, these subsystems turn drift remediation from a risky manual chore into a continuous, idempotent, recoverable control: a scored delta becomes an ordered plan, is previewed, applied under the right engine safety model, verified, and — on the rare failure — cleanly recovered, with every step producing the signed evidence that closes the compliance loop.

Related Guides

• Drift Detection Engines & Diff Logic — the section that produces the scored delta this pipeline consumes and converges.
• Grant and Revoke Chain Logic — the cascade and dependency semantics that dictate safe revoke ordering.
• Compliance Evidence & Audit Reporting — how the signed evidence each remediation emits is formatted, hashed, and retained for auditors.
• Exception Routing and Whitelisting — the register of legitimate deviations that remediation must reconcile against before planning a revoke.
• Core RBAC Architecture & Privilege Fundamentals — the role model and least-privilege foundations the manifest and its convergence assume.

↑ Back to all RBAC drift topics