Writing idempotent GRANT and REVOKE DDL in Python

This guide shows how to turn a drift delta into GRANT and REVOKE statements that are safe to run twice — quoted correctly, verb-checked, and wrapped the way each engine actually behaves, so a re-run is either a clean no-op or a harmless warning rather than an error.

When to use this — and when not to

Use this technique when:

• You are building the write half of a drift pipeline and need to emit real DDL from computed deltas rather than hand-written migration files.
• Your role and object names come from a catalog and could contain mixed case, reserved words, or characters that must be quoted — so string-formatting DDL is unsafe.
• You run remediation on a schedule and need re-execution of the same plan to converge, not to raise.

Do not reach for this when:

• You have not yet computed a minimal plan. Rendering the whole desired set as GRANT statements every run is wasteful and noisy; compute desired − current first, as in idempotent privilege apply.
• You need multi-statement atomic rollback on MySQL. MySQL commits DDL implicitly and cannot give it to you; design for convergence or use rollback and recovery instead.
• The change is a one-off manual fix. A reviewed ad-hoc statement is fine; the generator earns its keep only under repetition.

Step-by-step implementation

Step 1 — Understand why REVOKE is naturally idempotent and GRANT usually is

Idempotent DDL leans on a quirk of both engines: re-issuing a privilege change that is already in effect is not an error. On PostgreSQL, GRANT SELECT on a table the role already holds SELECT on succeeds silently, and REVOKE SELECT for a privilege the role never had raises a NOTICE (“no privileges could be revoked”) but the statement succeeds. That means a plain PostgreSQL REVOKE is inherently safe to re-run.

MySQL is asymmetric. A repeated GRANT is fine, but a plain REVOKE of a privilege that was never granted raises ERROR 1141 (there is no such grant). MySQL 8.0.16 added REVOKE IF EXISTS, which downgrades that error to a warning — this is the single most important clause for idempotent revocation on MySQL. Confirm the asymmetry before you rely on it:

# Behaviour matrix the renderer depends on. No client can change these;
# they are engine semantics, documented here so the render choices below are legible.
BEHAVIOUR = {
    ("postgresql", "GRANT"):  "re-grant is a silent no-op",
    ("postgresql", "REVOKE"): "revoking an absent grant warns, does not error",
    ("mysql",      "GRANT"):  "re-grant is a silent no-op",
    ("mysql",      "REVOKE"): "errors unless REVOKE IF EXISTS (8.0.16+)",
}
assert BEHAVIOUR[("mysql", "REVOKE")].startswith("errors")

See the PostgreSQL REVOKE reference and the MySQL REVOKE reference for the exact wording.

Step 2 — Quote identifiers instead of formatting strings

Role and object names come from the catalog and cannot be trusted to be bare lowercase words. Compose every statement with psycopg.sql, which quotes identifiers correctly and refuses injection. Privilege verbs are keywords, not identifiers, so guard them with a whitelist.

from dataclasses import dataclass
from psycopg import sql

_PRIVILEGES = {"SELECT", "INSERT", "UPDATE", "DELETE",
               "TRUNCATE", "REFERENCES", "TRIGGER"}

@dataclass(frozen=True, order=True)
class Grant:
    role: str
    privilege: str
    obj: str            # "schema.table"

def _object(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"unknown privilege verb: {privilege!r}")
    return sql.SQL(privilege)

def render_pg(revokes: list[Grant], grants: list[Grant]) -> list[sql.Composed]:
    """PostgreSQL: revokes first, then grants. Both are naturally idempotent."""
    out: list[sql.Composed] = []
    for g in revokes:
        out.append(sql.SQL("REVOKE {v} ON TABLE {o} FROM {r}").format(
            v=_verb(g.privilege), o=_object(g.obj), r=sql.Identifier(g.role)))
    for g in grants:
        out.append(sql.SQL("GRANT {v} ON TABLE {o} TO {r}").format(
            v=_verb(g.privilege), o=_object(g.obj), r=sql.Identifier(g.role)))
    return out

Render and inspect a statement against a live connection to confirm the quoting. A mixed-case role name must come out double-quoted:

import psycopg

with psycopg.connect("dbname=app") as conn:
    stmt = render_pg([], [Grant("Reporting_RO", "SELECT", "sales.line_items")])[0]
    print(stmt.as_string(conn))
    # -> GRANT SELECT ON TABLE "sales"."line_items" TO "Reporting_RO"

Step 3 — Wrap for PostgreSQL, or compensate for MySQL

On PostgreSQL, apply the whole rendered batch inside one transaction so it is atomic. psycopg v3’s connection context manager commits on a clean exit and rolls back on any exception, which is exactly the transaction guard you want.

def apply_pg(conninfo: str, statements: list[sql.Composed]) -> int:
    if not statements:
        return 0                                  # empty plan is a valid no-op
    with psycopg.connect(conninfo) as conn:       # commit on success, rollback on error
        with conn.cursor() as cur:
            for stmt in statements:
                cur.execute(stmt)
    return len(statements)

On MySQL you cannot wrap the batch: each GRANT/REVOKE triggers an implicit commit, so START TRANSACTION around DDL does nothing. Instead, make each statement individually idempotent — REVOKE IF EXISTS for revocations, plain GRANT for additions — and let the pipeline’s convergence property finish any batch that dies partway. Render MySQL statements with backtick-quoted identifiers:

def render_mysql(revokes: list[Grant], grants: list[Grant]) -> list[str]:
    """MySQL 8: REVOKE IF EXISTS makes revocation re-runnable; GRANT is already safe.
    Each statement auto-commits, so there is no batch to roll back."""
    def ident(name: str) -> str:
        return "`" + name.replace("`", "``") + "`"

    def obj(o: str) -> str:
        schema, _, name = o.partition(".")
        return f"{ident(schema)}.{ident(name)}"

    stmts: list[str] = []
    for g in revokes:
        if g.privilege not in _PRIVILEGES:
            raise ValueError(f"unknown privilege verb: {g.privilege!r}")
        stmts.append(f"REVOKE IF EXISTS {g.privilege} ON {obj(g.obj)} FROM {ident(g.role)}")
    for g in grants:
        if g.privilege not in _PRIVILEGES:
            raise ValueError(f"unknown privilege verb: {g.privilege!r}")
        stmts.append(f"GRANT {g.privilege} ON {obj(g.obj)} TO {ident(g.role)}")
    return stmts

Expected output for a one-revoke, one-grant delta:

REVOKE IF EXISTS DELETE ON `sales`.`orders` FROM `app_reader`
GRANT SELECT ON `sales`.`orders` TO `app_reader`

Because both statements are individually safe, re-running the list is a no-op even after a partial failure — MySQL’s answer to the transaction it will not give you.

Worked example: reconciling a reporting role on PostgreSQL 15

Scenario: PostgreSQL 15. A reporting role analytics_ro should hold SELECT on sales.orders and sales.line_items and nothing else, but a past incident left it with DELETE on sales.orders. The delta is one revoke and (say) one missing grant.

current = {Grant("analytics_ro", "SELECT", "sales.orders"),
           Grant("analytics_ro", "DELETE", "sales.orders")}
desired = {Grant("analytics_ro", "SELECT", "sales.orders"),
           Grant("analytics_ro", "SELECT", "sales.line_items")}

revokes = sorted(current - desired)   # [Grant('analytics_ro','DELETE','sales.orders')]
grants  = sorted(desired - current)   # [Grant('analytics_ro','SELECT','sales.line_items')]

applied = apply_pg("dbname=warehouse", render_pg(revokes, grants))
print(applied)                        # -> 2  (one REVOKE, one GRANT, one transaction)

Run it a second time after re-extracting current: the sets now match, both differences are empty, render_pg returns [], and apply_pg returns 0. Even if you re-ran the original two statements by mistake, PostgreSQL would treat the GRANT as a no-op and the REVOKE as a warning — the render is safe either way.

Gotchas and engine-specific notes

Transactional DDL is a PostgreSQL luxury, not a given. PostgreSQL wraps GRANT/REVOKE in the surrounding transaction, so a batch is atomic. MySQL issues an implicit commit before and after each account-management statement — there is nothing to roll back. Never write MySQL remediation that assumes a failed third statement undoes the first two.

REVOKE IF EXISTS needs MySQL 8.0.16. On older MySQL, a revoke of an absent grant hard-errors. If you must support 8.0.0–8.0.15, pre-check information_schema.table_privileges and only emit the REVOKE when a matching row exists.

GRANT OPTION is a privilege too. Revoking a base privilege does not necessarily remove a separately granted WITH GRANT OPTION; on PostgreSQL, dependent grants may require REVOKE ... CASCADE. Model the grant option as its own delta element rather than assuming it rides along, and consult grant and revoke chain logic for the cascade rules.

Object type must match the verb. ON TABLE is right for tables and views; sequences, schemas, and functions take ON SEQUENCE, ON SCHEMA, ON FUNCTION. Carry the object type in the delta so the renderer picks the correct clause instead of defaulting to TABLE.

Never string-format identifiers. A role literally named "; DROP OWNED BY victim; -- is a valid catalog identifier. sql.Identifier neutralises it; an f-string does not.

Compliance note

Rendering DDL from a versioned delta gives auditors a reproducible change record: for any remediation run you can show the exact statements issued, quoted and ordered, and demonstrate that re-running them changes nothing. This supports SOC 2 CC6.3 (access changes are authorized and controlled) and PCI-DSS Requirement 7.2 by proving that privilege corrections are deterministic rather than typed by hand at a prompt. The artifact is the rendered statement list plus the connection-resolved SQL text, attached to the apply run’s evidence record — a form auditors can read without a database.

Frequently asked questions

Why is REVOKE idempotent but GRANT sometimes needs care? On PostgreSQL both are safe to re-run, but the asymmetry bites on MySQL, where a plain REVOKE of a privilege that was never granted raises an error. Rendering REVOKE IF EXISTS on MySQL 8.0.16 or newer makes revocation a no-op when there is nothing to revoke, matching the natural idempotency GRANT already has.

Can I wrap a batch of GRANT and REVOKE in a transaction on MySQL? No. MySQL performs an implicit commit before and after each account-management statement, so a surrounding transaction has no effect and a failed statement cannot roll back the ones before it. Design MySQL remediation around per-statement idempotency and let the next converging run finish a partial apply.

How do I stop a role name from injecting SQL into my DDL? Compose statements with psycopg sql.Identifier for PostgreSQL, or backtick-escape and double any embedded backticks for MySQL. Never build DDL with f-strings or concatenation, because catalog identifiers can contain quotes, semicolons, and whitespace that break out of an unquoted statement.

Do I need WITH GRANT OPTION handled separately? Yes. The grant option is a distinct privilege that is not automatically removed when you revoke the base privilege, and on PostgreSQL dependent grants can force a CASCADE. Model it as its own element in the delta so the renderer emits the right statement rather than assuming it travels with the base grant.

Related

• Idempotent privilege apply — the plan-and-apply stage this renderer feeds.
• Dry-run mode for privilege reconciliation — preview the rendered statements before executing them.
• Grant and Revoke Chain Logic — cascade and dependency rules that shape statement ordering.

Up: Idempotent Privilege Apply