RBAC Drift Detection & Compliance Sync

PostgreSQL pg_auth_members vs MySQL role_edges extraction

This page shows how to read role-to-role membership from PostgreSQL and MySQL, which store it in completely different catalog shapes, and fold both into a single canonical edge set that a drift engine can diff without engine-specific special-casing.

Role membership is the backbone of any RBAC model: dba_group grants read_write grants app_svc, and effective privilege flows along those edges. But PostgreSQL keeps membership in pg_auth_members as OID pairs, while MySQL 8 keeps it in mysql.role_edges as user@host pairs — and, critically, a MySQL edge grants nothing in a session until the role is activated. If your extractor treats a MySQL role edge the way it treats a PostgreSQL one, you will report privileges that no session actually holds. The adapter here is one of the cross-DB parser adapters that make heterogeneous fleets diffable.

When to use this — and when not to

Use this two-engine extraction and normalization when:

  • You run both PostgreSQL and MySQL 8 and need a single drift baseline that compares role hierarchies across them without maintaining two divergent diff paths.
  • Your model depends on effective membership — you care whether a grant is actually active in a session, not merely recorded in a catalog.
  • You already ingest object grants and want membership edges in the same canonical shape so inheritance can be resolved consistently.

Do not reach for this when:

  • You run a single engine. A direct query against that engine’s catalog is simpler than a normalization layer you do not need.
  • You only care about direct object grants and never resolve transitive inheritance — then role edges are noise for your use case.
  • You are on MySQL 5.7 or MariaDB, which have no role_edges catalog at all; the activation model and storage differ and this adapter does not apply unmodified.
Two engines, two storage shapes, one canonical role-edge set On the left, PostgreSQL pg_auth_members supplies roleid and member OIDs that are resolved against pg_roles into role names, always active. On the right, MySQL role_edges supplies user-at-host pairs that are joined to default_roles to determine whether each edge is activated in a session. Both normalized streams converge on a single canonical edge tuple of grantee, role, admin option, and active flag that the drift engine diffs. PostgreSQL · pg_auth_members roleid OID · member OID · admin_option MySQL 8 · mysql.role_edges FROM_USER@HOST · TO_USER@HOST join pg_roles ×2 OID → rolname (roleid & member) join mysql.default_roles resolve activation gap edges always active Postgres membership applies on login active = is a default role else inert until SET ROLE canonical edge (grantee, role, admin, active) drift engine diffs one shape
Figure 1 — Divergent catalog shapes converge on a single canonical edge, with MySQL's activation state folded into a single active flag.

Step-by-step implementation

Step 1 — Extract PostgreSQL membership from pg_auth_members

pg_auth_members stores only OIDs, so each row must be joined to pg_roles twice — once to name the group role (roleid) and once to name the member (member). Membership in PostgreSQL takes effect according to the member role’s INHERIT attribute and is available on login, so every extracted edge is effectively active. See the PostgreSQL role membership docs for the inheritance rules.

SELECT grp.rolname   AS role_name,
       mbr.rolname   AS grantee,
       am.admin_option,
       mbr.rolinherit AS member_inherits
FROM pg_auth_members am
JOIN pg_roles grp ON grp.oid = am.roleid
JOIN pg_roles mbr ON mbr.oid = am.member
WHERE grp.rolname NOT LIKE 'pg\_%'
ORDER BY grantee, role_name;

Verification — a member should appear once per role it holds:

 role_name  | grantee     | admin_option | member_inherits
------------+-------------+--------------+-----------------
 read_write | app_svc     | f            | t
 read_only  | reporting   | f            | t

Step 2 — Extract MySQL membership and resolve the activation gap

mysql.role_edges records the grant, but a granted role contributes zero privilege in a session unless it is one of the account’s default roles (or is activated with SET ROLE, or the server has activate_all_roles_on_login enabled). Left-join mysql.default_roles to compute an auto_activated flag per edge. The MySQL roles docs describe activation precisely.

SELECT re.FROM_USER            AS role_name,
       re.TO_USER              AS grantee,
       re.WITH_ADMIN_OPTION    AS admin_option,
       (dr.DEFAULT_ROLE_USER IS NOT NULL) AS auto_activated
FROM mysql.role_edges re
LEFT JOIN mysql.default_roles dr
  ON  dr.USER = re.TO_USER   AND dr.HOST = re.TO_HOST
  AND dr.DEFAULT_ROLE_USER = re.FROM_USER
  AND dr.DEFAULT_ROLE_HOST = re.FROM_HOST
ORDER BY grantee, role_name;

Verification — an inert edge is visible but flagged inactive:

 role_name  | grantee | admin_option | auto_activated
------------+---------+--------------+---------------
 app_write  | app_svc | N            | 1
 audit_read | app_svc | N            | 0

Step 3 — Normalize both into one canonical edge set

Fold both result sets into a single frozen tuple so the diff engine never branches on engine. The active flag is the load-bearing normalization: True for every PostgreSQL edge, and the auto_activated value for MySQL. This mirrors the normalization contract in understanding RBAC inheritance in PostgreSQL vs MySQL.

from dataclasses import dataclass

@dataclass(frozen=True)
class RoleEdge:
    grantee: str      # role that receives membership
    role: str         # role being granted
    admin: bool       # WITH ADMIN / admin_option
    active: bool      # effective in a session right now

def from_postgres(row) -> RoleEdge:
    return RoleEdge(row["grantee"], row["role_name"],
                    bool(row["admin_option"]), active=True)

def from_mysql(row) -> RoleEdge:
    return RoleEdge(row["grantee"], row["role_name"],
                    row["admin_option"] == "Y", active=bool(row["auto_activated"]))

Verification — both engines yield comparable tuples, and the inert MySQL edge is preserved but marked inactive:

pg = from_postgres({"grantee": "app_svc", "role_name": "read_write",
                    "admin_option": False})
my = from_mysql({"grantee": "app_svc", "role_name": "audit_read",
                 "admin_option": "N", "auto_activated": 0})
assert pg == RoleEdge("app_svc", "read_write", False, True)
assert my.active is False        # recorded, not effective

Worked example: PostgreSQL 15 and MySQL 8.0 side by side

Scenario: the account app_svc exists on both engines. On PostgreSQL 15 it is a member of read_write. On MySQL 8.0 it has been granted both app_write (set as a default role) and audit_read (granted but never made default). A naive extractor would report three effective memberships; the correct effective set is two.

canonical = {from_postgres({"grantee": "app_svc", "role_name": "read_write",
                            "admin_option": False})}
canonical |= {
    from_mysql({"grantee": "app_svc", "role_name": "app_write",
                "admin_option": "N", "auto_activated": 1}),
    from_mysql({"grantee": "app_svc", "role_name": "audit_read",
                "admin_option": "N", "auto_activated": 0}),
}
effective = {e for e in canonical if e.active}
print(sorted((e.role for e in effective)))
print(sorted((e.role for e in canonical if not e.active)))

Expected output:

['app_write', 'read_write']
['audit_read']

The audit_read edge is retained in the canonical set — you want it in your baseline so that a later change activating it registers as drift — but it is excluded from the effective privilege graph. Diffing on the effective set stops the pipeline from alerting on a grant that grants nothing.

Gotchas and engine-specific notes

OIDs are cluster-local. A pg_auth_members OID is meaningless outside its database cluster and can be reused after DROP ROLE. Always resolve to rolname at extraction time — never cache raw OIDs across snapshots or environments.

MySQL user@host is a two-part identity. role_edges keys on both TO_USER and TO_HOST. app_svc@'%' and app_svc@'10.0.%' are different grantees, and default-role activation is matched per host. Collapsing to the user part alone will mismatch activation and produce phantom active/inactive flips.

activate_all_roles_on_login overrides default_roles. If the server variable is ON, every granted role activates on login regardless of mysql.default_roles. Read the variable and, when set, treat all MySQL edges as active — otherwise you will mark genuinely effective roles as inert.

PostgreSQL 16 split the admin/set/inherit options. From PostgreSQL 16, pg_auth_members carries separate inherit_option and set_option columns, and admin_option no longer implies inheritance. On 16+, read inherit_option to decide whether membership actually confers privilege, rather than assuming it from membership alone.

Reserved roles are not drift. Filter pg_% bootstrap roles in PostgreSQL and the internal mysql.session/mysql.sys accounts, or every baseline reports built-in noise that is identical on every host anyway.

Compliance note

A normalized, engine-neutral membership graph is the evidence backbone for least-privilege review under SOC 2 CC6.3 (role-based access is provisioned and reviewed). Because the canonical edge set records both recorded and effective membership, an auditor can see not just who was granted a role but whether that grant confers privilege today — the exact distinction MySQL’s activation model otherwise obscures. Exported as a per-host edge list with the active flag, it produces a defensible artifact showing that access flowing through role inheritance was enumerated identically across a heterogeneous fleet, which feeds directly into the drift detection engines baseline.

Frequently asked questions

Why join pg_roles twice instead of once? Because pg_auth_members stores two OIDs per row — the group role in roleid and the member in member — and each must be resolved to a name independently. A single join names only one side and leaves the other as an integer.

Does a role in mysql.role_edges always grant its privileges? No. That is the activation gap. A role edge confers privilege only when the role is a default role for the account, is activated via SET ROLE, or the server has activate_all_roles_on_login enabled. Otherwise the edge is recorded but inert in a session.

Should inactive MySQL edges be dropped from the baseline? Keep them in the canonical set with active=False, but exclude them from the effective privilege graph. Retaining them means a later change that activates the role registers as drift instead of appearing from nowhere.

How do I compare admin/grant options across engines? Normalize both to a boolean admin field: PostgreSQL’s admin_option and MySQL’s WITH_ADMIN_OPTION (‘Y’/‘N’) map cleanly. On PostgreSQL 16+, read inherit_option separately since admin no longer implies inheritance.

Up: Cross-DB Parser Adapters