RBAC Drift Detection & Compliance Sync

How to map database roles to least-privilege access

This page shows how to convert an existing set of over-broad database grants into an auditable, least-privilege role map, then keep that map from drifting — using catalog queries, a version-controlled desired-state manifest, and a transactional dry-run before any GRANT or REVOKE reaches production.

Least-privilege mapping is the point where an abstract compliance requirement (“analysts read, they do not write”) becomes a concrete, machine-verifiable set of grants. It sits directly on top of your role hierarchy design: a clean hierarchy makes the mapping tractable, and a well-defined mapping is what a drift pipeline compares the live catalog against on every run.

When to apply this mapping — and when not to

Use the workflow on this page when:

  • You already have a defined role hierarchy (base roles inheriting from scoped capability roles) and need to bind concrete object privileges to each role deterministically.
  • Grants are provisioned or changed by more than one actor — CI pipelines, on-call DBAs, migration tools — so the live state routinely diverges from what anyone intended.
  • An auditor or a control owner needs every privilege assignment to trace back to a specific requirement, and you need a repeatable artifact to prove it.

Do not reach for this workflow when:

  • The database has a single application role and no service accounts — a hand-written GRANT script under version control is enough, and a diff pipeline is overhead.
  • You have not yet fixed circular or overlapping role membership. Resolve those in the hierarchy first; mapping privileges onto a broken graph only encodes the breakage.

Step 1: Inventory the live grant graph

Least-privilege work starts from ground truth, not from the manifest you think is deployed. Query the catalog directly so the current state is materialized as data you can diff. In PostgreSQL, information_schema.role_table_grants exposes every object-level privilege as one row:

SELECT
    grantee        AS role_name,
    table_schema,
    table_name,
    privilege_type,
    is_grantable
FROM information_schema.role_table_grants
WHERE grantee NOT IN ('postgres', 'PUBLIC')
ORDER BY grantee, table_schema, table_name, privilege_type;

Pair it with the membership edges from pg_auth_members joined to pg_roles, so you capture inheritance as well as direct object grants:

SELECT
    m.rolname   AS member,
    r.rolname   AS granted_role,
    a.admin_option
FROM pg_auth_members a
JOIN pg_roles m ON m.oid = a.member
JOIN pg_roles r ON r.oid = a.roleid
ORDER BY member, granted_role;

Verification: the first query should return one row per (role, object, privilege) triple. Spot-check a known service account — for example svc_etl_loader should appear only with INSERT/UPDATE on the ETL schema and nowhere near schema_admin objects. Any row that surprises you is drift you are about to formalize away.

Step 2: Author the desired-state manifest

Express intent as a version-controlled manifest — YAML or JSON — where each role lists exactly the privileges it is allowed to hold. This is the artifact that binds a role to specific objects, actions, and scope, and it is the practical expression of privilege scope mapping:

# roles.desired.yaml — checked into version control, reviewed via PR
roles:
  app_read:
    grants:
      - {schema: analytics, table: events,   privileges: [SELECT]}
      - {schema: analytics, table: sessions, privileges: [SELECT]}
  svc_etl_loader:
    inherits: [app_read]
    grants:
      - {schema: staging, table: events_raw, privileges: [INSERT, UPDATE]}

Load and normalize it into the same shape the catalog query produces — a flat set of (role, schema, table, privilege) tuples — so the two states are directly comparable:

import yaml

def load_desired(path: str) -> set[tuple[str, str, str, str]]:
    """Flatten the desired-state manifest into comparable grant tuples."""
    doc = yaml.safe_load(open(path, encoding="utf-8"))
    desired: set[tuple[str, str, str, str]] = set()
    for role, spec in doc["roles"].items():
        for g in spec.get("grants", []):
            for priv in g["privileges"]:
                desired.add((role, g["schema"], g["table"], priv))
    return desired

Verification: len(load_desired("roles.desired.yaml")) should equal the number of privilege lines you authored (4 for the sample above). A mismatch means a duplicated or malformed grant entry.

Step 3: Compute the privilege delta

With both states as tuple sets, the delta is plain set arithmetic. Privileges present live but absent from the manifest are REVOKE operations; privileges in the manifest but missing live are GRANT operations. Emit revokes first — collapsing a broad privilege before adding the narrow one prevents a momentary window of excess access, which is the ordering that grant and revoke chain logic depends on:

def plan(current: set[tuple[str, str, str, str]],
         desired: set[tuple[str, str, str, str]]) -> list[str]:
    """Return idempotent DDL: revokes before grants, deterministic order."""
    to_revoke = sorted(current - desired)
    to_grant  = sorted(desired - current)
    stmts: list[str] = []
    for role, schema, table, priv in to_revoke:
        stmts.append(f'REVOKE {priv} ON {schema}.{table} FROM "{role}";')
    for role, schema, table, priv in to_grant:
        stmts.append(f'GRANT {priv} ON {schema}.{table} TO "{role}";')
    return stmts

Verification: running plan(current, desired) on a database that already matches the manifest returns []. That empty result is the idempotency contract — a converged system produces no operations, so the same pipeline can run on every commit without churning grants.

Step 4: Dry-run inside a transaction before committing

Never let generated DDL touch production unvalidated. Execute the plan inside an explicit transaction and roll back unconditionally: the engine parses and evaluates every statement — catching typos, missing objects, and privilege-check failures — while the rollback discards all side effects.

import psycopg

def dry_run_remediation(dsn: str, statements: list[str]) -> list[str]:
    """
    Execute remediation SQL inside a transaction, then roll back
    unconditionally. Returns the log of statements that parsed and ran.
    Validation only — never commit from this function.
    """
    executed: list[str] = []
    with psycopg.connect(dsn, autocommit=False) as conn:
        try:
            for sql in statements:
                conn.execute(sql)
                executed.append(sql)
        except Exception as exc:
            conn.rollback()
            raise RuntimeError(f"Dry-run failed on: {sql!r}") from exc
        finally:
            conn.rollback()   # always roll back — this is a dry run
    return executed

Verification: a clean dry-run returns the full statement list with no exception. Only after that promote the identical plan to a committed transaction (autocommit=False → conn.commit()), so the reviewed plan and the applied plan are byte-for-byte the same.

From two states to a committed least-privilege plan Two inputs on the left feed a set-difference stage. The top input is the live catalog query against role_table_grants, normalized into a current-state set of tuples. The bottom input is the desired-state manifest roles.desired.yaml, normalized into a desired set of tuples. Both arrows enter a center node labelled set-difference delta, which holds two ordered rows: first REVOKE, computed as current minus desired, then GRANT, computed as desired minus current, with a note that revokes are emitted first. The delta feeds a transactional dry-run node that parses and validates every statement and always rolls back, shown by a loop arrow labelled roll back and discard. Only if the dry-run is clean does an arrow labelled if clean lead to the final node, a committed apply of the identical plan. Live catalog query role_table_grants → current set {tuples} Desired-state manifest roles.desired.yaml → desired set {tuples} Set-difference delta 1 REVOKE current − desired 2 GRANT desired − current revokes emitted first Transactional dry-run parse + validate roll back — discard if clean Committed apply identical plan
Figure — Both states are normalized to comparable grant tuples; the set-difference emits revokes (current − desired) before grants (desired − current), and only a clean transactional dry-run promotes the byte-for-byte identical plan to a committed apply.

Worked example: PostgreSQL 15, a CI account scoped to one schema

Scenario: a CI service account ci_migrator was granted ALL on the analytics schema during an incident and never walked back. Policy says it should hold only SELECT and INSERT on analytics.events.

Live state from Step 1:

ci_migrator | analytics | events   | SELECT  | NO
ci_migrator | analytics | events   | INSERT  | NO
ci_migrator | analytics | events   | UPDATE  | NO
ci_migrator | analytics | events   | DELETE  | NO
ci_migrator | analytics | sessions | SELECT  | NO

Desired manifest for the account:

  ci_migrator:
    grants:
      - {schema: analytics, table: events, privileges: [SELECT, INSERT]}

The plan from Step 3 emits revokes first, then no new grants (both desired privileges already exist):

REVOKE DELETE ON analytics.events   FROM "ci_migrator";
REVOKE UPDATE ON analytics.events   FROM "ci_migrator";
REVOKE SELECT ON analytics.sessions FROM "ci_migrator";

After a clean dry-run and commit, re-running Step 1 returns exactly the two allowed rows, and re-running Step 3 returns [] — the account is now converged and least-privileged, and the next scheduled run will confirm no drift.

PostgreSQL vs MySQL grant-mapping gotchas

The set-difference method is engine-agnostic, but the catalog you read and the way privileges resolve are not. These differences are covered in depth in understanding RBAC inheritance in PostgreSQL vs MySQL; the mapping-specific ones to guard against:

  • Catalog source. PostgreSQL grants live in information_schema.role_table_grants and membership in pg_auth_members. MySQL 8.0+ keeps the static grant graph in information_schema.role_edges (backed by mysql.role_edges) and per-user activation in mysql.default_roles. A manifest normalized from one will not diff correctly against the other.
  • Session vs. static resolution. In MySQL a granted role can be dormant until SET ROLE or activate_all_roles_on_login, so a naive SHOW GRANTS under-reports and produces false-negative “missing privilege” deltas. Reconstruct effective grants from role_edges + default_roles before diffing.
  • WITH GRANT OPTION cascades. In PostgreSQL, REVOKE-ing a privilege that a role re-granted to others fails unless you add CASCADE; stripping delegation is a boundary decision, not a syntax detail. Route those revokes through explicit security boundary enforcement so a cascade never silently removes access something else depends on.
  • Allow-union semantics. Both engines accumulate grants from every role rather than intersecting them, so there is no implicit deny. Least privilege is achieved only by revoking the broader grant, never by adding a narrower one alongside it.

Compliance mapping and the audit artifact this produces

This workflow directly satisfies the least-privilege and access-restriction controls auditors probe: SOC 2 CC6.1 and CC6.3 (logical access is restricted to what each identity requires), PCI-DSS Requirement 7 (access limited to least privilege by role), and HIPAA §164.312(a)(1) (technical access control). The evidence artifact is the pairing of the version-controlled desired-state manifest with the JSON delta report from Step 3: together they show the intended grant set, the live grant set at a point in time, and the exact GRANT/REVOKE operations that reconciled them — a reviewable, timestamped chain from control requirement to applied change. Keeping the manifest under pull-request review also gives you the change-authorization trail those same controls expect.

Frequently asked questions

Does an empty plan mean the mapping is compliant? It means the live grant graph matches the manifest for the objects the manifest covers. Compliance also depends on the manifest itself being correct — an empty delta against a permissive manifest is convergence, not least privilege. Review the manifest as carefully as the diff.

How do I handle a legitimate, temporary grant that the diff wants to revoke? Route it through an exception path rather than widening the manifest. Time-bound grants belong in a whitelist with an expiry, not in the baseline, so they clear automatically — the pattern in reducing false positives in RBAC drift alerts keeps them from firing as violations.

Should REVOKE really run before GRANT? Yes. Applying revokes first guarantees no transient window where a role holds both the broad and the narrow privilege at once. On a converged system the ordering is moot because both sets are empty, but during remediation it is the difference between a clean cutover and a momentary privilege spike.

What if a REVOKE fails because of a dependent grant? That is a WITH GRANT OPTION cascade. Decide explicitly whether the delegation is legitimate; if not, revoke with CASCADE, and if so, correct the manifest to include the delegated grants so the diff stops flagging them.

Up: Core RBAC Architecture & Privilege Fundamentals