Role Hierarchy Design

A role hierarchy is the desired-state contract that every drift detection run is measured against. When that contract is implicit — a pile of ad-hoc GRANT statements accumulated across incidents, migrations, and personnel changes — there is no deterministic baseline to diff the live catalog against, and “drift” becomes unmeasurable. The specific failure scenario this page prevents is baseline ambiguity: a scheduled reconciliation job flags a grant as unauthorized, an on-call engineer cannot tell whether the manifest or the database is wrong, and the team disables the alert. Once that happens, silent privilege escalation goes undetected until an audit or a breach surfaces it. A rigorously designed hierarchy makes the desired state a single version-controlled artifact, reduces the comparison surface to a small set of composable roles, and gives the reconciliation planner an unambiguous target. This discipline is the structural foundation for everything in Core RBAC Architecture & Privilege Fundamentals; the rest of that section — scope mapping, chain logic, boundary enforcement — assumes the hierarchy defined here already resolves deterministically.

The Tiered Composition Model and Its Invariants

Effective hierarchy architecture relies on tiered role composition rather than flat user-to-permission assignments. Three tiers keep the model tractable. Base roles encapsulate foundational object access — a svc_readonly role holding SELECT on a schema, a svc_connect role holding only CONNECT on the database. Functional roles aggregate base roles into domain capabilities — billing_reader inherits several read-only base roles that together cover the billing schemas. Application roles bind service identities to workloads and inherit exactly the functional roles that workload requires. No object grant is ever attached directly to an identity; identities receive only application roles.

This layering is not cosmetic. It enforces the invariants that make automated reconciliation safe:

• Effective permissions are a pure function of the role graph plus object grants. A given identity’s access must be computable from catalog state alone, so the desired state can be diffed against the live catalog deterministically.
• The role graph is a directed acyclic graph. Roles may be granted to roles, but a cycle makes privilege resolution non-terminating and reconciliation planning ambiguous. Both engines reject direct membership cycles, but multi-step cycles can still be introduced across separate statements, so the pipeline must validate acyclicity before it plans.
• Object grants live only on base roles. When a table’s access changes, exactly one base role changes. This shrinks the diff surface: a schema-wide permission shift touches a single row of the manifest instead of every identity that happened to be granted directly.

Inheritance semantics, however, are not uniform across engines. PostgreSQL roles carry an INHERIT attribute (on by default) that cascades privileges at query time without an explicit SET ROLE, whereas MySQL 8.0+ requires role activation via SET DEFAULT ROLE, an explicit SET ROLE, or the activate_all_roles_on_login system variable. A role that “has” a grant on paper may not exercise it in a live session on MySQL. The engine-level session and login-scope mechanics that drive this divergence are documented in Understanding RBAC inheritance in PostgreSQL vs MySQL; the hierarchy design here treats both engines as producing the same normalized privilege graph so the diff engine never has to special-case them.

Prerequisites and Scope

Before implementing the extraction and reconciliation described below, confirm the following. The techniques target PostgreSQL 12+ (role membership and pg_auth_members semantics are stable across these versions) and MySQL 8.0.16+ (roles and the mysql.role_edges / mysql.default_roles tables did not exist before 8.0). On the Python side, use Python 3.11+ for the standard-library graphlib.TopologicalSorter, psycopg 3.1+ for PostgreSQL catalog reads, and pydantic 2.x for typed manifests.

The extraction principal needs read access to the role catalogs and nothing more. On PostgreSQL, a login role with pg_read_all_settings and membership in pg_monitor can read pg_roles and pg_auth_members; reading information_schema.role_table_grants returns only rows the connected role can see, so use a role with broad visibility (or pg_read_all_stats) if you need the full object-grant picture. On MySQL, the principal needs SELECT on the mysql system schema (specifically mysql.role_edges, mysql.default_roles, and mysql.tables_priv) plus the ROLE_ADMIN privilege only if the same pipeline will apply changes rather than just detect them. Detection and remediation should run under separate credentials so a read-only drift scan can never mutate state. The catalog-read performance patterns for large role graphs are covered in System Catalog Query Optimization.

Core Implementation Walkthrough

The pipeline has four stages: declare the desired hierarchy, extract the observed graph, normalize and validate it into a DAG, then compute the minimal reconciliation plan. Each stage below is runnable against a live database.

Step 1 — Declare the desired hierarchy as a typed manifest

The desired state is a version-controlled file, reviewed like any other code. A pydantic model gives it a schema and fails fast on malformed input, so a bad edit is caught in CI rather than in production reconciliation.

from __future__ import annotations
from pydantic import BaseModel, Field, field_validator

class RoleDef(BaseModel):
    name: str
    inherits: list[str] = Field(default_factory=list)   # parent roles
    grants: list[str] = Field(default_factory=list)      # only on base roles

    @field_validator("name")
    @classmethod
    def lower_snake(cls, v: str) -> str:
        if not v.replace("_", "").isalnum():
            raise ValueError(f"role name must be alphanumeric/underscore: {v!r}")
        return v.lower()

class HierarchyManifest(BaseModel):
    engine: str                      # "postgresql" | "mysql"
    roles: list[RoleDef]

    def edges(self) -> set[tuple[str, str]]:
        """Directed edges (parent, child): parent is granted TO child."""
        return {
            (parent.lower(), r.name)
            for r in self.roles
            for parent in r.inherits
        }

Load and validate it as the first step of every run; a ValidationError here means the baseline itself is broken and reconciliation must not proceed.

import tomllib
from pathlib import Path

def load_manifest(path: str) -> HierarchyManifest:
    data = tomllib.loads(Path(path).read_text())
    return HierarchyManifest.model_validate(data)

Step 2 — Extract the observed role graph from the catalog

Extraction uses parameterized, read-only queries against the real catalog views. On PostgreSQL, pg_auth_members holds the membership edges and pg_roles resolves OIDs to names; filter out the built-in pg_* roles so they never appear as spurious drift.

-- PostgreSQL: observed role-to-role edges
SELECT g.rolname AS parent,
       m2.rolname AS child,
       a.admin_option
FROM   pg_auth_members a
JOIN   pg_roles g  ON g.oid  = a.roleid
JOIN   pg_roles m2 ON m2.oid = a.member
WHERE  g.rolname  NOT LIKE 'pg\_%'
  AND  m2.rolname NOT LIKE 'pg\_%'
ORDER  BY parent, child;

The MySQL equivalent reads mysql.role_edges directly; roles there are user@host pairs, so carry the host component to avoid collapsing distinct grantees.

-- MySQL 8.0+: observed role-to-role edges
SELECT CONCAT(FROM_USER, '@', FROM_HOST) AS parent,
       CONCAT(TO_USER,   '@', TO_HOST)   AS child,
       WITH_ADMIN_OPTION                 AS admin_option
FROM   mysql.role_edges
ORDER  BY parent, child;

The PostgreSQL reader forces a read-only transaction so a detection scan can never mutate state, and returns plain tuples the rest of the pipeline treats identically regardless of engine:

import psycopg

PG_EDGE_SQL = """
SELECT g.rolname, m2.rolname, a.admin_option
FROM   pg_auth_members a
JOIN   pg_roles g  ON g.oid  = a.roleid
JOIN   pg_roles m2 ON m2.oid = a.member
WHERE  g.rolname NOT LIKE 'pg\\_%' AND m2.rolname NOT LIKE 'pg\\_%'
"""

def fetch_pg_edges(dsn: str) -> set[tuple[str, str]]:
    with psycopg.connect(dsn, autocommit=True) as conn:
        with conn.cursor() as cur:
            cur.execute("SET default_transaction_read_only = on")
            cur.execute(PG_EDGE_SQL)
            return {(parent, child) for parent, child, _admin in cur.fetchall()}

Normalizing these engine-specific grant matrices into one comparable shape is the concern of Privilege Scope Mapping, and the broader multi-engine extraction and parsing machinery lives under Cross-Environment Privilege Extraction & Parsing.

Step 3 — Normalize into a DAG and validate acyclicity

Before any diff, the observed edge set must be proven acyclic. graphlib.TopologicalSorter raises CycleError if a cycle exists, which converts a subtle privilege-resolution hazard into a loud, actionable failure. The same function also yields a deterministic apply order — parents before children — which Step 4 relies on.

from graphlib import TopologicalSorter, CycleError

def resolve_order(edges: set[tuple[str, str]]) -> list[str]:
    """Return roles in dependency order (parents first); raise on cycles."""
    ts: TopologicalSorter[str] = TopologicalSorter()
    for parent, child in edges:
        ts.add(child, parent)          # child depends on its parent role
    try:
        return list(ts.static_order())
    except CycleError as exc:
        cycle = " -> ".join(exc.args[1])
        raise ValueError(f"role graph contains a cycle: {cycle}") from exc

With acyclicity guaranteed, effective membership is the transitive closure of the edge set — every role an identity ultimately resolves to. Computing it explicitly lets the pipeline reason about effective privilege rather than only direct grants:

def transitive_closure(edges: set[tuple[str, str]]) -> dict[str, set[str]]:
    """Map each role to every ancestor role it effectively inherits."""
    parents: dict[str, set[str]] = {}
    for parent, child in edges:
        parents.setdefault(child, set()).add(parent)
    effective: dict[str, set[str]] = {}
    for role in resolve_order(edges):
        acc: set[str] = set()
        for p in parents.get(role, ()):
            acc.add(p)
            acc |= effective.get(p, set())
        effective[role] = acc
    return effective

Step 4 — Compute the minimal reconciliation plan

The plan is the symmetric difference between desired and observed edges: grants to add, memberships to revoke. Computing it as pure set arithmetic keeps it deterministic and trivial to test.

def reconcile(desired: set[tuple[str, str]],
              observed: set[tuple[str, str]]) -> dict[str, list[tuple[str, str]]]:
    return {
        "grant":  sorted(desired - observed),   # present in manifest, missing live
        "revoke": sorted(observed - desired),   # present live, absent from manifest
    }

The plan is emitted as ordered DDL. On PostgreSQL, GRANT role_a TO role_b and its REVOKE are naturally convergent, so no existence guard is required for the membership edges themselves; the cascade behavior of the object grants those roles carry is handled by Grant and Revoke Chain Logic, which orders dependent statements so no revoke strands a privilege mid-plan.

def emit_pg_ddl(plan: dict[str, list[tuple[str, str]]]) -> list[str]:
    stmts = [f'GRANT "{p}" TO "{c}";'  for p, c in plan["grant"]]
    stmts += [f'REVOKE "{p}" FROM "{c}";' for p, c in plan["revoke"]]
    return stmts

Translating those role structures into the narrowest possible object grants — so a compromised application role has the smallest blast radius — is the methodology in How to map database roles to least-privilege access, and keeping those boundaries intact under change is the concern of Security Boundary Enforcement.

Idempotency and the Dry-Run Safety Contract

Reconciliation must be safe to run every fifteen minutes forever. The contract has three clauses. First, convergence: applying the plan drives the observed edge set to equal the desired set, and a second immediate run produces an empty plan (grant == [] and revoke == []). Because the plan is computed as desired - observed and observed - desired, an already-reconciled catalog yields empty sets by construction — there is no counter to increment, no timestamp to bump, nothing that makes the second run differ from the first.

Second, read-only by default. The pipeline computes and serializes a plan without ever holding write credentials. Detection connects with the read-only principal from the prerequisites and sets default_transaction_read_only = on; only a separate, explicitly invoked apply stage connects with a role that holds ROLE_ADMIN (MySQL) or membership WITH ADMIN OPTION (PostgreSQL). A drift scan therefore cannot mutate the database even if the manifest is wrong.

Third, plan-before-apply. The serialized plan is the review gate. In CI it is rendered as a diff artifact and, in dry-run mode, asserted empty so a pull request that would silently change live privileges fails the build:

def assert_no_drift(desired, observed) -> None:
    plan = reconcile(desired, observed)
    if plan["grant"] or plan["revoke"]:
        raise SystemExit(f"drift detected:\n{plan}")   # non-zero exit fails CI

Because both the desired and observed sets are engine-normalized, the same convergence guarantee holds whether the target is PostgreSQL’s implicit INHERIT cascade or MySQL’s explicit activation model — the diff never sees the difference.

Compliance Alignment and Evidence Artifacts

A well-formed hierarchy is directly auditable, and the reconciliation plan doubles as the evidence artifact. Each run produces a signed JSON report — desired edge count, observed edge count, the exact grant/revoke deltas applied, and a hash of the manifest that was the authority — which maps cleanly onto the controls auditors ask about:

• SOC 2 CC6.1 / CC6.3 — logical access is provisioned from an approved baseline and least privilege is enforced. The version-controlled manifest is the “approved” record; the empty-plan assertion is continuous evidence that live state matches it.
• PCI-DSS Req 7 — access is restricted to least privilege by role. Tiered composition with object grants only on base roles is the mechanism; the transitive-closure output enumerates each identity’s effective access for the assessor.
• NIST SP 800-53 AC-2 / AC-6 and HIPAA §164.312(a)(1) — account management and least-privilege enforcement with an audit trail. Every reconciliation delta, with actor, timestamp, and manifest hash, is an immutable record of privilege change. Separation-of-duties requirements trace to NIST SP 800-53 Access Control.

A minimal, auditor-ready report shape:

import hashlib, json, datetime as dt

def evidence(manifest_text: str, plan: dict) -> str:
    return json.dumps({
        "generated_at": dt.datetime.now(dt.UTC).isoformat(),
        "manifest_sha256": hashlib.sha256(manifest_text.encode()).hexdigest(),
        "granted": plan["grant"],
        "revoked": plan["revoke"],
        "converged": not (plan["grant"] or plan["revoke"]),
    }, indent=2, sort_keys=True)

How much a given deviation should weigh on an overall compliance score — a wildcard admin membership versus a single read-only edge — is governed by Rule-Based Drift Scoring inside Drift Detection Engines & Diff Logic.

Troubleshooting Matrix

Related

• Understanding RBAC inheritance in PostgreSQL vs MySQL — engine-specific session and login-scope resolution that this hierarchy normalizes over.
• How to map database roles to least-privilege access — decomposing broad privileges into narrowly scoped, workload-specific roles.
• Privilege Scope Mapping — turning engine-specific grant matrices into a normalized, comparable dataset.
• Grant and Revoke Chain Logic — ordering dependent privilege changes so no revoke strands an access mid-plan.
• Security Boundary Enforcement — keeping the boundaries this hierarchy defines intact under change.

Up: Core RBAC Architecture & Privilege Fundamentals