Grant and Revoke Chain Logic

A drift reconciler that knows what to change still fails if it applies the changes in the wrong order. Revoke a parent role before stripping the object grants a child holds through it and you orphan privileges that survive the run; grant a member role before the ancestor that carries the shared read access and the member connects with less access than policy promises. This is the specific sub-problem this section solves: turning an unordered drift delta into a dependency-safe, idempotent execution chain that transitions a live catalog from its observed state to the policy target without ever passing through a state that widens access or breaks a dependent. Neglect this ordering and the failure mode is not a loud error — it is a silent residual grant that passes the next dry run, survives an audit, and becomes the lateral-movement path in a breach report. The delta itself is defined upstream in Core RBAC Architecture & Privilege Fundamentals; this section owns the sequencing, transactional apply, and conflict handling that drive that delta to empty safely.

Prerequisites and Scope

The chain engine described here assumes the following environment. The ordering rules apply to both engines, but the catalog reads and cascade quirks differ, so the code blocks name the engine explicitly.

• PostgreSQL 14+ (tested through 16) or MySQL 8.0.16+ for role support. On MySQL, roles below 8.0 do not exist and the chain degrades to user-level grants only.
• Python 3.11+, for tomllib, StrEnum, and graphlib.TopologicalSorter from the standard library. The reconciler uses psycopg (v3) for PostgreSQL and mysql-connector-python 8.x (or PyMySQL) for MySQL. pydantic v2 validates the desired-state manifest.
• Catalog read permission for the extraction role: SELECT on information_schema.role_table_grants, pg_roles, and pg_auth_members (PostgreSQL), or on mysql.role_edges, mysql.default_roles, and information_schema.role_table_grants (MySQL). The read role does not need WITH GRANT OPTION — extraction is strictly read-only.
• Apply permission for the remediation role: it must hold each privilege it grants WITH GRANT OPTION, or be a member of the owning role. A reconciler that cannot re-grant what it revokes will strand a dependent mid-chain.

Extraction and normalization are owned by the readers in Cross-Environment Privilege Extraction & Parsing; this section consumes the normalized (grantee, privilege, object, grantor, admin_option) tuples they produce and never re-parses raw catalog syntax itself. The inheritance edges that drive the ordering come from the model in Role Hierarchy Design, including the engine-specific mechanics of RBAC inheritance in PostgreSQL vs MySQL.

Core Implementation Walkthrough

The chain runs in four ordered stages. Each stage is a pure transform whose output is the next stage’s input, so the whole pipeline is testable stage by stage against fixtures.

1. Extract the observed grant set as a hashed manifest

The reliability of every downstream decision hinges on a consistent snapshot. Read the relevant system catalogs inside a single REPEATABLE READ transaction so that concurrent DDL cannot tear the snapshot, then sort deterministically and hash the result. The digest is what lets a scheduled job detect “nothing changed” without re-diffing.

import hashlib
import json
import psycopg  # psycopg 3

OBSERVED_SQL = """
SELECT grantee,
       privilege_type                        AS privilege,
       table_schema || '.' || table_name     AS object_name,
       grantor,
       is_grantable = 'YES'                   AS admin_option
FROM information_schema.role_table_grants
WHERE grantee = ANY(%(roles)s)
ORDER BY grantee, object_name, privilege;
"""


def extract_observed(dsn: str, roles: list[str]) -> tuple[list[dict], str]:
    """Return the sorted observed grant rows and a stable manifest digest."""
    with psycopg.connect(dsn, autocommit=False) as conn:
        conn.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY")
        with conn.cursor(row_factory=psycopg.rows.dict_row) as cur:
            cur.execute(OBSERVED_SQL, {"roles": roles})
            rows = cur.fetchall()
        conn.rollback()  # read-only: never leave a transaction open

    payload = json.dumps(rows, sort_keys=True, default=str).encode()
    digest = hashlib.sha256(payload).hexdigest()
    return rows, digest

The ORDER BY is load-bearing: two runs against an unchanged catalog must serialize to byte-identical JSON so the SHA-256 digest is stable. If the digest matches the last recorded manifest, the chain short-circuits and emits no DDL. This normalized shape is exactly what Privilege Scope Mapping defines, so object-class normalization (schema USAGE, function EXECUTE) happens once, upstream, rather than being re-derived here.

2. Diff into ADD / REMOVE / MODIFY operations

With the observed set and the desired manifest both expressed as frozen, hashable tuples, the diff is a set operation. Classify each element of the symmetric difference into an operation carrying the dependency edge it must respect.

from dataclasses import dataclass
from enum import StrEnum


class Op(StrEnum):
    ADD = "ADD"        # grant a missing privilege
    REMOVE = "REMOVE"  # revoke an excess privilege
    MODIFY = "MODIFY"  # change grant option / expiry in place


@dataclass(frozen=True, slots=True)
class ChainOp:
    op: Op
    grantee: str
    privilege: str
    object_name: str
    depends_on: str | None  # role that must be settled before this op


def diff(desired: frozenset, observed: frozenset,
         edges: dict[str, str]) -> list[ChainOp]:
    """Compute the unordered operation set from two grant-tuple sets."""
    ops: list[ChainOp] = []
    for t in observed - desired:            # excess -> REMOVE
        ops.append(ChainOp(Op.REMOVE, t.grantee, t.privilege,
                           t.object_name, edges.get(t.grantee)))
    for t in desired - observed:            # missing -> ADD
        ops.append(ChainOp(Op.ADD, t.grantee, t.privilege,
                           t.object_name, edges.get(t.grantee)))
    return ops

The edges map is the role-membership graph: member -> role_it_inherits_from. It is what turns a flat set difference into an ordered chain in the next step.

3. Topologically sort the operations into a dependency-safe chain

Ordering follows two rules that are exact inverses of each other, and getting the direction wrong is the single most common chain bug:

• Grants flow parent-first. Grant a privilege to an ancestor role before any member that should inherit it, so the member never connects underprivileged.
• Revokes flow child-first. Revoke direct object grants from a child role before revoking the parent-role membership that would otherwise cascade, so no privilege is orphaned or left dangling on a role that is about to disappear.

graphlib.TopologicalSorter produces the parent-first order; revokes consume it reversed. All revokes are emitted before all grants, so convergence never transits a state that is simultaneously broader than both the start and the end.

from graphlib import TopologicalSorter, CycleError


def order_chain(ops: list[ChainOp], edges: dict[str, str]) -> list[ChainOp]:
    """Return revokes (child-first) then grants (parent-first)."""
    ts: TopologicalSorter[str] = TopologicalSorter()
    for member, parent in edges.items():
        ts.add(member, parent)  # parent must precede member
    try:
        parent_first = list(ts.static_order())
    except CycleError as exc:
        raise RuntimeError(f"role graph has a cycle, refusing to plan: {exc}")

    rank = {role: i for i, role in enumerate(parent_first)}
    revokes = sorted((o for o in ops if o.op is Op.REMOVE),
                     key=lambda o: -rank.get(o.grantee, 0))   # child-first
    grants = sorted((o for o in ops if o.op is Op.ADD),
                    key=lambda o: rank.get(o.grantee, 0))      # parent-first
    return revokes + grants

A CycleError is treated as fatal rather than something to reconcile through — a live catalog that has drifted into a multi-step membership cycle is a defect to alarm on, consistent with the DAG invariant asserted by the parent section.

4. Apply the chain transactionally and idempotently

The ordered chain is applied inside one explicit transaction per grantee, with each statement guarded so a repeated run is a no-op. In PostgreSQL, wrap the guard in a DO block that checks current catalog state before acting; REVOKE on an already-absent object privilege is not an error, but the guard still avoids emitting a redundant audit event.

BEGIN;
-- Guard: only grant if the privilege is actually missing.
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM information_schema.role_table_grants
    WHERE grantee        = 'app_readonly'
      AND table_schema   = 'public'
      AND table_name     = 'orders'
      AND privilege_type = 'SELECT'
  ) THEN
    EXECUTE 'GRANT SELECT ON TABLE public.orders TO app_readonly';
  END IF;
END
$$;
COMMIT;

The Python driver applies the whole ordered chain for a grantee in a single transaction so a mid-chain failure rolls back cleanly and the next run recomputes the identical delta:

def apply_chain(dsn: str, grantee: str, chain: list[ChainOp],
                correlation_id: str) -> None:
    with psycopg.connect(dsn, autocommit=False) as conn:
        try:
            for op in chain:
                verb = "REVOKE" if op.op is Op.REMOVE else "GRANT"
                prep = "FROM" if op.op is Op.REMOVE else "TO"
                conn.execute(
                    f"{verb} {op.privilege} ON {op.object_name} "
                    f"{prep} {psycopg.sql.Identifier(op.grantee).as_string(conn)}"
                )
            conn.commit()
        except psycopg.errors.LockNotAvailable:
            conn.rollback()
            raise  # caller re-queues with backoff (see safety contract)

Statement construction must parameterize or Identifier-quote the role name; a grantee value pulled from a manifest is still untrusted input. The cascade semantics of the emitted verbs — which grants persist after a membership revoke, and which do not — are the engine quirks the RBAC inheritance comparison documents, and they are why the chain revokes direct object grants explicitly rather than trusting a membership revoke to cascade.

Idempotency and Safety Contract

The chain guarantees convergence: applying it against a catalog already in the desired state produces no DDL, no audit event, and no alert. Three properties enforce that contract.

Recompute, never replay. The chain is never cached and replayed. Every run re-extracts the observed state (Step 1) and recomputes the delta (Step 2), so a partially-applied previous run simply produces a smaller delta on the next pass. There is no stored “pending operations” queue that could drift from reality.

One transaction per grantee. Each grantee’s ordered chain commits atomically. A network partition or LockNotAvailable mid-chain rolls the grantee back to its pre-run state; the orchestrator re-queues that grantee with exponential backoff and jitter, and the retried run recomputes the same delta from a clean snapshot. Other grantees, already committed, are unaffected.

Dry-run is the default posture. Running with --plan-only executes Steps 1–3 and renders the ordered chain as SQL text plus a JSON diff report, but opens no write transaction. Because extraction runs READ ONLY REPEATABLE READ, a plan can be produced against production continuously with zero risk. Promotion from plan to apply is a separate, gated action. This same read-only planning surface is what Environment Comparison Workflows consume when they compare two environments’ chains side by side.

A minimum-access floor sits between planning and apply: any REMOVE that would drop a role below a declared least-privilege floor (for example, a service account losing its last SELECT on a table it must read) is held out of the chain and routed for review rather than applied. This is the safety valve that keeps a bad manifest from taking down production, and the held operation becomes an exception record handled by Exception Routing & Whitelisting.

Compliance Alignment and Evidence Artifacts

Every chain run emits a structured, timestamped evidence artifact regardless of whether it applied any DDL — an empty chain is itself proof that the catalog matches policy. The artifact is a JSON diff report keyed by correlation ID:

{
  "correlation_id": "grc-2026-07-04T09:15:22Z-a1b2",
  "engine": "postgresql",
  "observed_digest": "sha256:9f2c…",
  "desired_digest": "sha256:9f2c…",
  "ops_planned": 0,
  "ops_applied": 0,
  "held_for_review": [],
  "converged": true,
  "controls": ["SOC2-CC6.1", "HIPAA-164.312(a)(1)", "PCI-DSS-7.2"]
}

That artifact maps directly onto the controls the parent section enumerates:

• SOC 2 — CC6.1 / CC6.3. The version-controlled manifest is the documented control; a converged: true report on every scheduled run is the enforcement evidence; the diff report is the periodic-review artifact. The ordered chain demonstrates that changes were applied deliberately, not ad hoc.
• HIPAA — §164.312(a)(1). Deterministic, dependency-ordered REVOKE on deprovisioning satisfies the requirement that access be removed for terminated principals, and the child-first revoke ordering proves no residual inherited access survived.
• PCI DSS — Requirement 7.2. The chain only ever grants privileges present in the manifest, and the minimum-access floor documents the business need for each retained grant, giving auditors the machine-checkable “documented need” the requirement demands.

The digest fields matter for auditors: identical observed_digest and desired_digest across a run window is cryptographic evidence that no un-manifested change occurred between runs. How each drifted tuple is weighted before it pages anyone — so that a low-risk chain does not generate the same signal as a privilege escalation — is handled by Rule-Based Drift Scoring, and the techniques for suppressing benign chains are covered in reducing false positives in RBAC drift alerts.

Troubleshooting Matrix

Named failure scenarios, the signature that identifies each, and the remediation the chain engine applies.

Orphaned privilege after membership revoke

Signature: a role still has effective object access after the run that revoked its parent-role membership; PostgreSQL left the directly-granted object privilege in place. Root cause: the chain trusted a membership REVOKE to cascade to object grants — it does not. Remediation: ensure the diff emits explicit REMOVE operations for the child’s direct object grants and that the topological sort places them before the membership revoke (child-first).

Underprivileged member mid-chain

Signature: an application connects during the run and is denied access it should have. Root cause: a member role’s grant was applied before its ancestor’s, violating parent-first ordering, so for a window the member lacked inherited access. Remediation: verify all ADD operations are sorted by ascending topological rank; never interleave grants and revokes.

Chain stalls on CycleError

Signature: RuntimeError: role graph has a cycle, refusing to plan. Root cause: separate GRANT statements introduced a multi-step membership cycle the engine did not reject at DDL time. Remediation: do not attempt to reconcile through the cycle; alarm on it, break the cycle manually or via a targeted exception, then re-run. Validate manifest acyclicity in CI before it can be promoted.

Repeated LockNotAvailable on apply

Signature: the same grantee’s chain rolls back run after run with a lock error. Root cause: a long-running manual session or migration holds a conflicting lock on the object. Remediation: the orchestrator re-queues with exponential backoff and jitter; if the retry budget is exhausted, the grantee is flagged for manual review rather than force-applied. Reads always target a replica; writes wait for the primary lock.

False large-revoke from a torn snapshot

Signature: a plan proposes revoking most of a role’s grants after a catalog read timed out. Root cause: extraction returned a truncated grant set, which the differ read as excess to remove. Remediation: treat any non-clean read as fatal — abort the run, do not plan on a partial snapshot, and retry the extraction. The REPEATABLE READ snapshot plus digest check in Step 1 is the primary guard.

Escalation across a security boundary

Signature: a planned ADD would grant access spanning schemas or tenants that policy isolates. Root cause: a manifest error or a cross-schema grant that slipped past review. Remediation: the boundary checks in Security Boundary Enforcement reject the operation before apply; the chain holds it for review and records it as an exception.

Where These Techniques Are Implemented

The chain logic above is exercised by several concrete techniques documented elsewhere on this site:

• Time-bound grants that must be added and later revoked on a schedule are provisioned through automating exception routing for temporary access grants, which feeds ADD and expiry-driven REMOVE operations into this chain.
• Cross-instance chains — where the observed state comes from one environment and the target from another — build on comparing role snapshots across AWS RDS and on-prem.
• The read patterns that make Step 1 cheap enough to run continuously are tuned in System Catalog Query Optimization.

Related

• Role Hierarchy Design — the inheritance graph whose edges drive the topological ordering of the chain
• Privilege Scope Mapping — the normalized (grantee, privilege, object) shape the chain consumes
• Security Boundary Enforcement — cross-schema and tenant checks that gate every ADD before apply
• Exception Routing & Whitelisting — where held operations and approved deviations are recorded
• Rule-Based Drift Scoring — weighting each chain operation before it pages an on-call engineer

Up: Core RBAC Architecture & Privilege Fundamentals