RBAC Drift Detection & Compliance Sync

Understanding RBAC inheritance in PostgreSQL vs MySQL

This page shows how to compute the effective privilege set of a database principal on PostgreSQL and on MySQL when the two engines resolve role inheritance by fundamentally different rules — so a drift check compares real capabilities instead of raw grant rows.

The trap is that both engines expose a role-membership graph that looks comparable, but PostgreSQL resolves inheritance recursively at privilege-check time while MySQL resolves it from per-session activation state. A naive pipeline that diffs pg_auth_members against role_edges will emit false positives on MySQL (roles granted but never activated read as “extra”) and false negatives on PostgreSQL (a NOINHERIT edge reads as “active” when it is not). Getting this right is a prerequisite for the deterministic baseline described in Role Hierarchy Design, and it underpins the normalization work in Privilege Scope Mapping.

PostgreSQL check-time recursion versus MySQL session-start activation Two panels. The left panel, labelled PostgreSQL and resolved at privilege-check time, shows a recursive walk up pg_auth_members: svc_etl cascades into app_write and then writer_base because each member has rolinherit true, while a second chain shows svc_analytics whose edge to reporting_ro is blocked because it is NOINHERIT, so the privileges are only reachable through an explicit SET ROLE. The right panel, labelled MySQL and resolved at session start, shows svc_analytics granted two static role_edges to reporting_ro and reporting_rw; both pass through a session-start activation gate keyed on default_roles and activate_all_roles_on_login, from which reporting_ro emerges active and effective while reporting_rw stays dormant because it was never activated. The two panels illustrate that PostgreSQL recomputes cascade on every check whereas MySQL fixes the effective set once at login. PostgreSQL resolved recursively at privilege-check time walk pg_auth_members, cascade while rolinherit = t svc_etl rolinherit = t · cascade app_write writer_base svc_analytics NOINHERIT · blocked reachable only via SET ROLE reporting_ro cascade recomputed on every check MySQL resolved once at session start static role_edges, activated at login svc_analytics reporting_ro reporting_rw session-start activation gate in default_roles? or activate_all_roles_on_login = ON? yes no active effective this session dormant granted, not activated effective set fixed at login until SET ROLE

When to compute effective privileges this way

Use the two-model resolution in this page when:

  • You run drift detection across a mixed fleet that includes both PostgreSQL (9.5+) and MySQL (8.0+) and need one comparable “effective privilege” record per principal.
  • Your baseline manifest expresses intended capability (“this service account may SELECT on analytics.*”) rather than raw membership edges.
  • You have service accounts whose granted roles may be inactive — MySQL default-role gaps or PostgreSQL NOINHERIT edges.

Skip it (a plain membership diff is enough) when:

  • You only target a single engine and only care that the grant graph matches, not the resolved capability.
  • All principals activate every role at login (activate_all_roles_on_login = ON fleet-wide) and no PostgreSQL role uses NOINHERIT — in that case membership and effective privilege coincide.

Step-by-step: resolving inheritance per engine

Step 1 — Extract the PostgreSQL membership graph honoring INHERIT

PostgreSQL treats a role as both a principal and a privilege container. Whether a granted role’s privileges flow automatically depends on the member’s rolinherit flag (set from INHERIT/NOINHERIT) — a NOINHERIT member must run SET ROLE to use the privileges. Extract the edges together with the flags that gate them:

-- PostgreSQL 9.5+ : membership edges plus the flags that decide cascade
SELECT m.roleid::regrole::text  AS granted_role,
       m.member::regrole::text  AS member_role,
       r.rolinherit             AS member_inherits,
       r.rolsuper               AS member_is_superuser,
       m.admin_option           AS can_grant_onward
FROM   pg_auth_members m
JOIN   pg_roles r ON r.oid = m.member
ORDER  BY member_role, granted_role;

Expected output (one row per direct edge):

 granted_role  | member_role   | member_inherits | member_is_superuser | can_grant_onward
---------------+---------------+-----------------+---------------------+------------------
 app_write     | svc_etl       | t               | f                   | f
 reporting_ro  | svc_analytics | f               | f                   | f

Here svc_analytics is a member of reporting_ro but member_inherits = f, so it does not get reporting_ro’s privileges automatically — a detail a raw edge diff would miss.

Step 2 — Extract the MySQL grant graph and its activation state

MySQL 8.0 stores the static grant graph in mysql.role_edges (exposed as information_schema.applicable_roles) and the per-user activation defaults in mysql.default_roles. A granted role does nothing until it is activated for the session, so you must pull both tables:

-- MySQL 8.0+ : static grant edges
SELECT CONCAT(from_user, '@', from_host) AS granted_role,
       CONCAT(to_user,   '@', to_host)   AS member_user,
       with_admin_option                 AS can_grant_onward
FROM   mysql.role_edges
ORDER  BY member_user, granted_role;

-- MySQL 8.0+ : which of those roles activate by default
SELECT CONCAT(user, '@', host)                       AS member_user,
       CONCAT(default_role_user, '@', default_role_host) AS active_by_default
FROM   mysql.default_roles
ORDER  BY member_user;

Also read the global that overrides per-user defaults:

SELECT @@GLOBAL.activate_all_roles_on_login AS activate_all;

If activate_all = 1, every granted role is effectively active regardless of mysql.default_roles; if 0, only rows present in default_roles are active at connect time.

Step 3 — Normalize both engines to one effective-privilege record

Collapse the engine differences into a single Python shape. For PostgreSQL, walk the edges recursively but stop descending through a member whose rolinherit is false. For MySQL, treat an edge as effective only when the target role is active (per default_roles or activate_all).

from dataclasses import dataclass, field

@dataclass
class Edge:
    granted_role: str
    member: str
    active: bool          # PG: member_inherits ; MySQL: role is activated

@dataclass
class EffectiveRoles:
    principal: str
    roles: set[str] = field(default_factory=set)

def resolve(principal: str, edges: list[Edge]) -> EffectiveRoles:
    """Return the set of roles whose privileges the principal actually holds.

    An edge only propagates when it is `active`, which encodes
    PostgreSQL INHERIT and MySQL activation with one flag.
    """
    by_member: dict[str, list[Edge]] = {}
    for e in edges:
        by_member.setdefault(e.member, []).append(e)

    result, stack, seen = EffectiveRoles(principal), [principal], set()
    while stack:
        current = stack.pop()
        for e in by_member.get(current, []):
            if e.active and e.granted_role not in seen:
                seen.add(e.granted_role)
                result.roles.add(e.granted_role)
                stack.append(e.granted_role)   # descend only through active edges
    return result

Verify the resolver on the Step 1 data — svc_analytics should resolve to an empty set because its only edge is inactive:

edges = [
    Edge("app_write",    "svc_etl",       active=True),
    Edge("reporting_ro", "svc_analytics", active=False),  # NOINHERIT
]
assert resolve("svc_etl", edges).roles == {"app_write"}
assert resolve("svc_analytics", edges).roles == set()

Step 4 — Diff effective privileges against the baseline

Feed both engines’ EffectiveRoles into the same comparison. Anything the principal effectively holds but the baseline does not authorize is drift; anything the baseline requires but the principal cannot reach (a dormant grant) is also drift, and this is exactly the case a membership-only diff hides.

def diff(effective: set[str], baseline: set[str]) -> dict[str, list[str]]:
    return {
        "unauthorized": sorted(effective - baseline),  # holds more than allowed
        "dormant":      sorted(baseline - effective),  # granted but not effective
    }

The dormant bucket is what surfaces the MySQL “granted but never activated” gap and the PostgreSQL NOINHERIT gap in one place. Scoring and severity for these deltas are handled downstream by Rule-Based Drift Scoring; remediation ordering is handled by Grant and Revoke Chain Logic.

Worked example: an analytics service account on two engines

Scenario: svc_analytics must have read-only access via a reporting_ro role. The baseline authorizes {"reporting_ro"}. The same intent is provisioned on a PostgreSQL 15 primary and a MySQL 8.0 replica-of-record.

On PostgreSQL the role was created NOINHERIT (a common hardening default), so the grant exists but never propagates:

GRANT reporting_ro TO svc_analytics;   -- svc_analytics is NOINHERIT

On MySQL the role was granted but svc_analytics has no matching row in mysql.default_roles and activate_all_roles_on_login = 0:

GRANT reporting_ro TO 'svc_analytics'@'%';   -- never set as a default role

Running the resolver + diff on each:

pg_effective    = resolve("svc_analytics", pg_edges).roles     # -> set()
mysql_effective = resolve("svc_analytics", mysql_edges).roles  # -> set()
baseline = {"reporting_ro"}

diff(pg_effective, baseline)
# {'unauthorized': [], 'dormant': ['reporting_ro']}
diff(mysql_effective, baseline)
# {'unauthorized': [], 'dormant': ['reporting_ro']}

Both engines report the same logical fault — a dormant, non-functional grant — even though the underlying cause differs (NOINHERIT vs missing default-role activation). The remediation differs per engine: ALTER ROLE svc_analytics INHERIT; on PostgreSQL, SET DEFAULT ROLE reporting_ro TO 'svc_analytics'@'%'; on MySQL. Wrap either in the dry-run pattern used across the reconciliation planner before committing.

Gotchas and engine-specific notes

  • PostgreSQL NOINHERIT still allows SET ROLE. A NOINHERIT member cannot use the privileges automatically but can switch into the role explicitly. If your application init runs SET ROLE, the grant is effective at runtime even though rolinherit = f — decide whether your baseline models login-scope or session-scope capability and keep it consistent.
  • rolsuper bypasses every check. A superuser member resolves to all privileges regardless of edges. Detect rolsuper = true and short-circuit the walk, or superuser accounts will look under-privileged in a pure edge diff.
  • MySQL SHOW GRANTS is activation-sensitive. Plain SHOW GRANTS FOR 'u'@'h' omits privileges reachable only through unactivated roles; use SHOW GRANTS FOR 'u'@'h' USING 'reporting_ro' to force the activation context when you audit interactively.
  • activate_all_roles_on_login overrides default_roles. Read the global first — if it is ON, the default_roles table is irrelevant and every granted role is active.
  • Catalog names differ by version. MySQL role support begins at 8.0 (mysql.role_edges does not exist earlier); PostgreSQL exposed pg_auth_members.grantor/inheritance option columns differently before 16, where per-membership INHERIT/SET options were added — target the columns your minimum version actually has.

Compliance note

Computing effective (not merely granted) privileges is the evidence a SOC 2 CC6.1 / CC6.3 or PCI-DSS Requirement 7 assessor expects for “access is limited to what is required.” A membership dump proves a role was granted; it does not prove the principal can or cannot exercise it. The artifact this technique produces — a per-principal JSON record of {engine, principal, effective_roles, dormant, unauthorized} resolved by the rules above — is directly attestable: it demonstrates least-privilege was verified against real capability on each engine, and the dormant/unauthorized buckets give the auditor a dated, reproducible drift ledger.

Frequently asked questions

Does a MySQL role granted but not in default_roles count as access for compliance? No. Until it is activated (via default_roles, SET ROLE, or activate_all_roles_on_login) the privileges are unreachable in a normal session. Report it as a dormant grant, not as effective access — but flag it, because a later config change can silently activate it.

Why does PostgreSQL SHOW GRANTS-equivalent output differ from my edge diff? Because NOINHERIT members do not inherit automatically. Check pg_roles.rolinherit for the member; if it is false, the membership exists but the privileges are only reachable through explicit SET ROLE.

How do I model a superuser in the normalized record? Detect pg_roles.rolsuper (PostgreSQL) or the SUPER/ALL PRIVILEGES grant (MySQL) and mark the principal as holding the full privilege universe, bypassing the recursive walk entirely.

Can I compare these effective-privilege records across environments directly? Yes — that is the point of normalizing to one shape. Once both engines emit the same EffectiveRoles structure, the same comparison feeds environment-to-environment drift as covered in the diff engines section.

Up: Role Hierarchy Design