Transaction-safe batch revocation in PostgreSQL

This page shows how to revoke a large set of PostgreSQL grants as a single atomic unit — ordered leaf-first so no dependent grant is silently cascaded away, guarded by SET LOCAL timeouts, isolated with per-statement savepoints, and proven converged before the transaction commits.

Revoking one grant is trivial. Revoking forty of them, some granted WITH GRANT OPTION to other roles, is where pipelines corrupt access: a naive loop that issues REVOKE in arbitrary order either aborts halfway and leaves a partial revocation, or reaches for CASCADE and tears down dependent grants nobody enumerated. PostgreSQL makes the safe version possible because all privilege DDL is transactional — the entire batch commits or none of it does.

When to use this — and when not to

Use transaction-safe batch revocation when:

• You are removing a departing principal, decommissioning a role, or converging a large drift set, and the revokes must land as an all-or-nothing unit.
• Some of the grants were re-granted onward (WITH GRANT OPTION), so revoke order matters and a stray CASCADE would remove access you did not intend to touch.
• You need the operation to be bounded — never blocking indefinitely behind a long-running transaction that holds a conflicting lock.

Do not reach for this when:

• You are on MySQL. Its GRANT/REVOKE statements implicitly commit and cannot participate in an atomic batch — you need the compensating-action approach in rollback and recovery instead.
• You are revoking a single grant with no dependents. One REVOKE needs no savepoints or topological ordering.
• The grants belong to an object the role owns. Ownership is not a grant; revoking privileges leaves ownership intact — use REASSIGN OWNED / DROP OWNED for that, and treat it as a separate change.

Step-by-step implementation

Step 1 — Derive the revoke order from the grant dependency graph

When a role grants a privilege onward with WITH GRANT OPTION, the onward grant depends on the original. Revoking the original first with the default RESTRICT behaviour raises dependent_objects_still_exist (SQLSTATE 2BP01); reaching for CASCADE instead would silently remove the onward grant you never listed. The safe path is to revoke leaf-first — dependents before the grants they hang off — so every REVOKE is explicit and CASCADE is never needed. Read the dependency edges straight from the catalog with aclexplode, which expands each object’s ACL into grantor/grantee rows:

-- Who granted what to whom, and whether it was grantable onward.
SELECT
    n.nspname || '.' || c.relname AS object,
    g.rolname  AS grantor,
    e.rolname  AS grantee,
    acl.privilege_type,
    acl.is_grantable
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL aclexplode(c.relacl) AS acl
JOIN pg_roles g ON g.oid = acl.grantor
JOIN pg_roles e ON e.oid = acl.grantee
WHERE n.nspname = 'app'
ORDER BY object, grantee;

Build a graph whose edges point grantor → grantee, then revoke in reverse topological order so leaves (grantees who granted nothing onward) go first:

import networkx as nx

def revoke_order(edges: list[tuple[str, str]], targets: set[str]) -> list[str]:
    """edges: (grantor, grantee) pairs. Return target roles leaf-first."""
    g = nx.DiGraph()
    g.add_edges_from(edges)
    g.add_nodes_from(targets)
    ordered = list(nx.topological_sort(g))     # grantors before grantees
    return [r for r in reversed(ordered) if r in targets]  # grantees first

edges = [("app_owner", "contractor"), ("contractor", "teammate")]
print(revoke_order(edges, {"contractor", "teammate"}))

['teammate', 'contractor']

teammate — whose grant depends on contractor’s grant option — is revoked first, so the later REVOKE against contractor has no dependents and needs no CASCADE. This is the same dependency reasoning developed in grant and revoke chain logic.

Step 2 — Open one transaction and set local guards

Everything runs inside a single psycopg (v3) transaction. Before the first REVOKE, SET LOCAL bounds the run: lock_timeout stops a revoke from blocking forever behind a conflicting lock, and statement_timeout caps any single statement. SET LOCAL scopes both settings to this transaction only — they revert automatically at commit or rollback, so no session state leaks.

import psycopg

def open_guarded(conn: psycopg.Connection) -> None:
    """Bound the transaction so a batch revocation can never hang the pipeline."""
    with conn.cursor() as cur:
        cur.execute("SET LOCAL lock_timeout = '3s'")
        cur.execute("SET LOCAL statement_timeout = '30s'")
        cur.execute("SET LOCAL idle_in_transaction_session_timeout = '15s'")

Using SET LOCAL rather than a bare SET is the crucial detail — a plain SET lock_timeout would persist on the pooled connection and silently alter every later query on it.

Step 3 — Revoke each grant under its own savepoint

A savepoint lets one failed REVOKE roll back to a known point without discarding the whole batch, so you can decide per statement whether the failure is tolerable. In psycopg v3 a nested conn.transaction() is a savepoint: exiting it normally releases the savepoint, and an exception inside rolls back to it.

def revoke_batch(conn: psycopg.Connection, statements: list[str]) -> list[str]:
    """Run REVOKEs inside the open transaction, each isolated by a savepoint.
    Returns the statements that a dependent-objects error blocked."""
    blocked: list[str] = []
    with conn.cursor() as cur:
        for stmt in statements:
            try:
                with conn.transaction():           # SAVEPOINT
                    cur.execute(stmt)              # e.g. REVOKE ... RESTRICT
            except psycopg.errors.DependentObjectsStillExist:
                # A dependent grant still exists — ordering was wrong for this row.
                blocked.append(stmt)                # rolled back to the savepoint
    return blocked

If blocked is non-empty after the loop, the leaf-first ordering missed a dependency; the correct response is to raise and roll the outer transaction back, not to switch to CASCADE. Every REVOKE uses the explicit RESTRICT keyword so the intent — “fail rather than cascade” — is in the SQL itself:

statements = [
    "REVOKE SELECT ON app.orders FROM teammate RESTRICT",
    "REVOKE SELECT ON app.orders FROM contractor RESTRICT",
]

Step 4 — Verify convergence, then commit

The batch is only correct if the target grants are actually gone. Re-query the catalog for the principals you revoked and assert the result is empty before the transaction commits — a non-empty result raises, and the outer conn.transaction() rolls the entire batch back atomically.

CONVERGENCE_SQL = """
SELECT count(*) AS residual
FROM information_schema.role_table_grants
WHERE grantee = ANY(%s)
  AND table_schema = 'app';
"""

def run_revocation(dsn: str, principals: list[str], statements: list[str]) -> str:
    with psycopg.connect(dsn) as conn:
        with conn.transaction():                   # outer: BEGIN ... COMMIT/ROLLBACK
            open_guarded(conn)
            blocked = revoke_batch(conn, statements)
            if blocked:
                raise RuntimeError(f"dependent grants remain: {blocked}")
            with conn.cursor() as cur:
                cur.execute(CONVERGENCE_SQL, (principals,))
                if cur.fetchone()[0] != 0:
                    raise RuntimeError("revocation did not converge")
        return "committed"

Expected on success:

committed        # residual == 0, transaction committed atomically

If either guard trips or convergence fails, psycopg emits ROLLBACK and the database is byte-for-byte what it was before BEGIN.

Worked example: offboarding a contractor on PostgreSQL 15

Scenario: PostgreSQL 15. A departing contractor holds nine object grants across the app schema, and one of them — SELECT ON app.orders — was re-granted WITH GRANT OPTION to teammate, who used it to grant SELECT onward. The offboarding job must remove every one of the contractor’s grants atomically without accidentally stripping teammate’s independent access.

The dependency query returns the edge contractor → teammate on app.orders. revoke_order sequences teammate’s dependent grant first, then the contractor’s. The batch runs inside one transaction:

SET LOCAL lock_timeout = '3s'
REVOKE SELECT ON app.orders FROM teammate RESTRICT      -- leaf first
REVOKE SELECT ON app.orders FROM contractor RESTRICT    -- now dependency-free
... 7 more contractor revokes ...
convergence check: residual == 0
COMMIT

Had the job revoked the contractor first with RESTRICT, PostgreSQL would have raised 2BP01 on statement one and the whole transaction would have rolled back — no harm, but no progress. Had it used CASCADE, teammate’s grant would have vanished as collateral. Leaf-first ordering with RESTRICT is the only sequence that removes exactly the contractor’s nine grants and nothing else, and because it is one transaction, a failure at any point leaves the contractor’s access fully intact for a clean retry.

Gotchas and engine-specific notes

RESTRICT is the default, but say it anyway. PostgreSQL’s REVOKE defaults to RESTRICT, so omitting the keyword still fails on dependents — but writing RESTRICT explicitly documents that a CASCADE was a deliberate choice never made. Reviewers should be able to see the safety decision in the SQL.

SET LOCAL vs SET on pooled connections. With a connection pool, a bare SET lock_timeout persists on the physical connection and corrupts unrelated later queries. SET LOCAL reverts at transaction end. This is the single most common self-inflicted bug in batch DDL.

MySQL cannot do this at all. MySQL 8 implicitly commits each GRANT/REVOKE, so there is no atomic batch and no savepoint-protected retry — a mid-batch failure leaves a durable partial revocation. On MySQL you must build the inverse statements and compensate, as covered in rollback and recovery. Do not port this pattern across engines unchanged.

Savepoints are not free. Each savepoint has a small cost and holding thousands in one transaction bloats memory. For very large batches, group revokes into savepoint-protected chunks rather than one savepoint per statement, or drop savepoints entirely once the ordering is proven and let the outer transaction be the only rollback boundary.

Privileges are not ownership. Revoking every privilege from a role does not remove objects it owns, and a role that owns objects cannot be dropped. Pair batch revocation with REASSIGN OWNED BY contractor TO app_owner and DROP OWNED BY contractor as a distinct, separately reviewed step. See the PostgreSQL REVOKE reference for the dependency semantics.

Lock ordering still matters. lock_timeout bounds how long you wait, but revoking against a heavily written table can still repeatedly time out. Schedule large revocations in a low-traffic window and let the timeout fail fast rather than queueing behind long transactions.

Compliance note

Transaction-safe batch revocation produces auditor-grade evidence that access removal was atomic and complete. Because the run either commits with a zero-residual convergence check or rolls back entirely, the emitted record — the ordered statement list, the SET LOCAL guards, and the convergence count — proves there was never an interval where a departing principal held a partial, undefined privilege set. This directly supports SOC 2 CC6.2/CC6.3 (access is removed when no longer required) and PCI-DSS Req. 7.2 (least privilege), and the atomic guarantee satisfies NIST SP 800-53 AC-2(3)'s expectation that disabled or removed accounts leave no residual authorization.

Frequently asked questions

Why revoke leaf-first instead of just using CASCADE? CASCADE removes dependent grants you may not have enumerated — for example a colleague’s access that a departing user granted onward. Revoking leaf-first with RESTRICT means every removal is explicit and listed, so you take away exactly the grants you intended and nothing else. CASCADE is a blast radius; leaf-first is a scalpel.

Do PostgreSQL GRANT and REVOKE really roll back? Yes. All privilege DDL in PostgreSQL is transactional, so a batch of REVOKEs inside a single transaction commits or rolls back as one unit. This is the property that makes an atomic batch revocation possible, and it is exactly what MySQL lacks, because MySQL implicitly commits each grant statement.

What does SET LOCAL do that SET does not? SET LOCAL scopes a setting to the current transaction, so lock_timeout and statement_timeout revert automatically at commit or rollback. A bare SET persists on the connection, which on a pooled connection silently changes the behaviour of later, unrelated queries. Always use SET LOCAL for per-batch guards.

How do savepoints help a batch revocation? A savepoint lets one failed REVOKE roll back to a known point without discarding the whole batch, so you can classify the failure per statement and decide whether to tolerate it or abort. In psycopg v3, a nested connection.transaction() block is a savepoint that releases on success and rolls back on an exception.

Related

• Rollback and Recovery — the recovery layer this atomic revoke sits inside.
• Rollback trigger patterns for failed privilege sync — classifying the failures that abort a batch.
• Idempotent Privilege Apply — rendering the grant and revoke statements this batch executes.

Up: Rollback and Recovery