Comparing role snapshots across AWS RDS and on-prem

This page shows how to take a role snapshot from an AWS RDS PostgreSQL instance and from an on-prem PostgreSQL database running the same application, reconcile the managed-service roles that only exist on RDS, and report the privilege delta as a scored, auditable JSON artifact.

The trap is that RDS and a self-hosted engine describe the same intended access policy through catalogs that are almost identical — but not quite. RDS injects a fixed set of service roles (rds_superuser, rdsadmin, rds_replication, rds_password) that have no on-prem counterpart, blocks direct SUPERUSER, and hides some pg_catalog internals behind the managed control plane. A naive GRANT dump comparison flags every one of those as drift and buries the one hotfix GRANT that actually matters. The fix is to normalize both sides into one canonical grant set before diffing — the same primitive owned by the broader environment comparison workflows, specialized here for the managed-vs-self-hosted split. The set arithmetic and severity model belong to the wider Drift Detection Engines & Diff Logic.

When to use this technique — and when not to

Apply this snapshot-and-diff method when:

• You run the same schema and application roles on an RDS PostgreSQL primary and an on-prem PostgreSQL replica (or a lift-and-shift target) and need continuous proof they enforce identical privileges.
• Your change-management is asynchronous — hotfix GRANT/REVOKE statements land on one side and are expected to be mirrored to the other, and you need to catch the ones that were not.
• An auditor needs a point-in-time, signable comparison showing that production access matches the documented baseline across both hosting models.

Skip it — or reach for a different tool — when:

• You are comparing schema objects (tables, indexes, extensions) rather than RBAC. That is DDL migration, and a schema-migration tool already covers it.
• The two environments run different engine families (RDS PostgreSQL vs on-prem Oracle/SQL Server). Cross-family privilege semantics need the canonical-mapping layer from Privilege Scope Mapping, not the straight PostgreSQL-to-PostgreSQL comparison below.

Step 1 — Extract object grants and membership edges from each endpoint

Read from ground truth in each catalog. Object-level privileges live in information_schema.role_table_grants; role membership edges live in pg_auth_members joined to pg_roles. Run the same two queries against both the RDS and the on-prem DSN.

-- Object privileges (same query on RDS and on-prem)
SELECT grantee        AS role_name,
       table_schema   AS object_schema,
       table_name     AS object_name,
       privilege_type,
       is_grantable
FROM information_schema.role_table_grants
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY grantee, table_schema, table_name, privilege_type;

-- Role membership edges (same query on RDS and on-prem)
SELECT member.rolname AS role_name,
       parent.rolname AS granted_role,
       a.admin_option
FROM pg_auth_members a
JOIN pg_roles member ON member.oid = a.member
JOIN pg_roles parent ON parent.oid = a.roleid
ORDER BY member.rolname, parent.rolname;

Verification: each query returns one row per privilege or membership fact. Spot-check a known service account — svc_app_web should hold SELECT/INSERT/UPDATE on the app schema and be a member of nothing surprising. If role_table_grants returns zero rows on RDS, your connecting principal cannot see grants it is not party to; connect as the role that owns the schema or as a member of rds_superuser.

Step 2 — Fold each snapshot into one canonical grant set

Normalize both result sets into frozen tuples so RDS and on-prem become directly comparable, and drop the RDS-managed roles at this stage so they never reach the diff. Membership admin_option and object is_grantable both collapse to a single grantable boolean.

import psycopg  # psycopg 3.x

# RDS injects these; they have no on-prem equivalent and must not read as drift.
RDS_MANAGED = frozenset({
    "rds_superuser", "rdsadmin", "rds_replication",
    "rds_password", "rds_iam", "rdsrepladmin",
})

OBJECT_SQL = """
SELECT grantee, table_schema, table_name, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
"""

MEMBERSHIP_SQL = """
SELECT member.rolname, parent.rolname, a.admin_option
FROM pg_auth_members a
JOIN pg_roles member ON member.oid = a.member
JOIN pg_roles parent ON parent.oid = a.roleid
"""

def extract_snapshot(dsn: str) -> frozenset[tuple]:
    grants: set[tuple] = set()
    with psycopg.connect(dsn) as conn, conn.cursor() as cur:
        cur.execute(OBJECT_SQL)
        for role, schema, obj, priv, grantable in cur:
            if role in RDS_MANAGED:
                continue
            grants.add((role, "object", schema, obj, priv, grantable))
        cur.execute(MEMBERSHIP_SQL)
        for role, parent, admin in cur:
            if role in RDS_MANAGED or parent in RDS_MANAGED:
                continue
            grants.add((role, "member", "", parent, "MEMBER", admin))
    return frozenset(grants)

Verification: len(extract_snapshot(dsn)) should be stable across two consecutive runs against an unchanged database — this determinism is what lets the diff be trusted. A fluctuating count means a transient session grant is leaking in; strip it before committing the snapshot, exactly as the sibling custom diff engine for PostgreSQL vs Redshift does for its own catalog.

Step 3 — Diff the two canonical sets

With both sides reduced to frozen tuples, the delta is plain set arithmetic. Pick one comparison axis per run and record it: for a policy authored on the RDS primary and expected to be mirrored on-prem, treat RDS as source, so only_on_rds reads as privilege that never made it to on-prem and only_on_prem reads as un-mirrored local drift.

rds     = extract_snapshot(RDS_DSN)
on_prem = extract_snapshot(ON_PREM_DSN)

only_on_rds     = sorted(rds - on_prem)
only_on_prem    = sorted(on_prem - rds)
in_both         = rds & on_prem

Verification: an environment that is fully in sync yields empty only_on_rds and only_on_prem lists. Seed a deliberate one-line difference — GRANT SELECT ON app.invoices TO reporting_ro; on RDS only — and confirm exactly one tuple appears in only_on_rds and none elsewhere.

Step 4 — Score the delta and emit an auditable report

Raw membership in a delta list is not yet a decision. Weight each tuple by privilege sensitivity so a missing SELECT on a report view does not page anyone while an unmirrored GRANT ... WITH GRANT OPTION on a production schema does. These weights are the local application of Rule-Based Drift Scoring; the fire/suppress cutoff comes from Threshold Tuning for Alerts.

import hashlib
import json
from datetime import datetime, timezone

WEIGHTS = {"MEMBER": 5, "SELECT": 1, "INSERT": 3, "UPDATE": 3,
           "DELETE": 4, "TRUNCATE": 6, "REFERENCES": 2, "TRIGGER": 4}

def score(tuples: list[tuple]) -> int:
    total = 0
    for role, kind, schema, obj, priv, grantable in tuples:
        w = WEIGHTS.get(priv, 2)
        if grantable:          # WITH GRANT / ADMIN OPTION escalates
            w *= 3
        total += w
    return total

def build_report(only_on_rds, only_on_prem) -> dict:
    payload = {
        "generated_at": datetime.now(timezone.utc).isoformat(),
        "source": "aws-rds", "target": "on-prem",
        "only_on_rds": [list(t) for t in only_on_rds],
        "only_on_prem": [list(t) for t in only_on_prem],
        "drift_index": score(only_on_rds) + score(only_on_prem),
    }
    body = json.dumps(payload, sort_keys=True).encode()
    payload["snapshot_sha256"] = hashlib.sha256(body).hexdigest()
    return payload

report = build_report(only_on_rds, only_on_prem)
print(json.dumps(report, indent=2))

Verification: re-running Step 4 on the same two snapshots must produce an identical snapshot_sha256. A changing hash on unchanged inputs means a non-deterministic ordering leaked in — confirm both delta lists were sorted() before scoring.

Worked example: PostgreSQL 15, RDS primary mirrored to an on-prem replica

Two databases run the app schema. The RDS primary (db.r6g.large, engine 15.4) is the write master; the on-prem PostgreSQL 15 replica serves internal reporting. Last week an engineer granted the reporting role read on a new table directly on RDS during an incident and never opened the mirror PR.

Snapshots yield 214 grant tuples on RDS and 213 on-prem after RDS_MANAGED filtering. The diff returns:

{
  "source": "aws-rds",
  "target": "on-prem",
  "only_on_rds": [
    ["reporting_ro", "object", "app", "invoices_2026", "SELECT", false]
  ],
  "only_on_prem": [],
  "drift_index": 1,
  "snapshot_sha256": "e3b0c442…"
}

One tuple, drift index 1 — a low-severity read that is real drift but not an emergency. It routes to the mirror-PR queue rather than paging. Had the same engineer instead run GRANT SELECT ON app.invoices_2026 TO reporting_ro WITH GRANT OPTION, the grantable flag would flip to true and the weighted score would jump to 3, crossing the production alert floor. The RDS-managed roles never appear in either list because they were dropped in Step 2 — which is the entire point of filtering at normalization time rather than after the diff.

Gotchas and engine-specific notes

• RDS hides SUPERUSER and some catalog internals. You cannot snapshot as a real superuser on RDS; connect as a member of rds_superuser (still filtered from the output) so role_table_grants is complete. On-prem, connect as a role that can see all schemas — a non-superuser sees only grants it participates in.
• information_schema.role_table_grants omits schema-level and default privileges. It covers table/view object grants only. Schema USAGE, sequence, and ALTER DEFAULT PRIVILEGES rules live in pg_namespace.nspacl and pg_default_acl; extend Step 1 with those catalogs if your policy depends on them.
• RDS minor-version differences. A newer RDS engine may expose predefined roles (pg_read_all_data, pg_monitor) that an older on-prem 15.x build lacks. Treat those like the managed roles — add them to a version-controlled allowlist rather than the code constant, so the list is auditable.
• MySQL instead of PostgreSQL? The shape is identical but the catalog is not: membership edges come from mysql.role_edges (FROM_USER/FROM_HOST/TO_USER/TO_HOST) and object privileges from information_schema.schema_privileges and table_privileges. Swap the two SQL constants and keep Steps 2–4 unchanged.

Compliance note

This comparison is direct evidence for SOC 2 CC6.1 and CC6.3 (logical access is provisioned consistently and reviewed) and PCI-DSS Requirement 7 (access restricted and identical across the cardholder-data footprint regardless of hosting model). The signable artifact is the Step 4 JSON report: it names the source and target environments, enumerates every privilege that differs, carries a weighted drift index, and is bound by a snapshot_sha256 so an auditor can confirm the evidence was not edited after generation. Store each report immutably alongside the mirror PRs that resolved its deltas — that pair (detected drift → reconciling change) is the access-review trail attestations ask for.

Frequently asked questions

Which RDS roles must I filter, and why not just ignore them in the diff? Filter rds_superuser, rdsadmin, rds_replication, rds_password, and any rds_* / pg_* predefined roles your engine version injects. Dropping them at normalization (Step 2) rather than post-diff keeps the delta lists clean and keeps the drift index honest — a role that cannot exist on-prem should never contribute a point.

Should RDS or on-prem be the source of truth? Neither by default. Choose per run and record it in the report metadata. If policy is authored on the RDS write primary and mirrored outward, make RDS the source so only_on_rds means “not yet mirrored” and only_on_prem means “local drift.”

How do I compare RDS PostgreSQL against on-prem Oracle or SQL Server? Not with this page. Different engine families need the canonical privilege translation from Privilege Scope Mapping before any set arithmetic; the PostgreSQL-to-PostgreSQL diff here assumes both sides speak the same privilege vocabulary.

Why does my drift index change between runs on an unchanged database? A transient session grant or unsorted delta list is leaking in. Confirm Step 2 produces a stable tuple count on repeat runs and that both delta lists are sorted() before Step 4 — the snapshot_sha256 must be reproducible for the evidence to hold.

Related

• Building a custom diff engine for PostgreSQL vs Redshift — the same normalize-then-diff method across two different catalogs.
• Rule-Based Drift Scoring — the severity weighting that turns raw deltas into an actionable drift index.
• Threshold Tuning for Alerts — deciding which drift index values fire and which are suppressed.
• Exception Routing and Whitelisting — codifying the managed roles and sanctioned deviations that must never page.

Up: Environment Comparison Workflows