Building a custom diff engine for PostgreSQL vs Redshift

This page shows how to build a deterministic engine that compares RBAC state between a PostgreSQL source and an Amazon Redshift target, reconciles their two very different privilege models into one canonical grant set, and reports the delta as scored, auditable evidence — without off-the-shelf schema-migration tooling that ignores grants entirely.

The hard part is not diffing two lists. It is that PostgreSQL and Redshift describe the same intended access policy in incompatible catalogs: PostgreSQL folds users and groups into a single pg_roles graph, while Redshift still exposes legacy users (pg_user) and groups (pg_group) alongside its newer native roles (svv_roles). A naive string comparison of two GRANT dumps reports drift on every line. This engine is the concrete, Redshift-specific implementation of the primitives owned by environment comparison workflows; the general set arithmetic it depends on belongs to the wider Drift Detection Engines & Diff Logic.

When to build this engine — and when not to

Build a custom PostgreSQL-vs-Redshift diff engine when:

• The same analytics access policy is materialized in both an OLTP PostgreSQL primary and an OLAP Redshift warehouse, and the two drift independently through hotfix grants, dbt service accounts, or IAM-injected roles.
• You need a repeatable, timestamped artifact that proves the two estates enforce the same least-privilege boundary, not a one-off manual SHOW GRANTS audit.
• Redshift native RBAC roles are in play (Redshift ≥ 1.0.44903, GA late 2022) and legacy GROUP grants coexist with them, so no single catalog view is authoritative.

Do not build this engine when:

• You only need to compare schema objects (tables, dist keys, sort keys) — that is DDL migration, not RBAC drift, and a schema-migration tool already covers it.
• Both endpoints are PostgreSQL. Then you have one catalog model and the cross-dialect normalization layer below is pure overhead — use a same-engine comparator instead.

Step 1: Extract the PostgreSQL grant graph

Start from ground truth in the source catalog. Membership edges live in pg_auth_members joined to pg_roles; object privileges live in information_schema.role_table_grants. Read both so inheritance and direct grants are captured:

-- PostgreSQL: object-level privileges, one row per (grantee, object, privilege)
SELECT grantee        AS principal,
       table_schema   AS object_schema,
       table_name     AS object_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;

-- PostgreSQL: role-to-role membership (inheritance edges)
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 returns one row per (principal, object, privilege) triple. Spot-check a known service account — svc_dbt_loader should appear with SELECT/INSERT on the staging schema and nowhere else.

Step 2: Extract the Redshift grant graph

Redshift needs three reads, because its principals are split across users, groups, and native roles. The modern, role-aware source of truth is the svv_* privilege views — information_schema on Redshift does not reflect native ROLE grants:

-- Redshift: relation privileges (native roles, users, and groups all appear here)
SELECT identity_name AS principal,
       identity_type,          -- 'role' | 'user' | 'group' | 'public'
       namespace_name AS object_schema,
       relation_name  AS object_name,
       privilege_type,
       admin_option
FROM svv_relation_privileges
WHERE identity_name NOT LIKE 'sys:%'   -- exclude managed system roles
ORDER BY principal, object_schema, relation_name, privilege_type;

-- Redshift: role-to-role and user-to-role membership
SELECT role_name, granted_by, admin_option FROM svv_role_grants;
SELECT user_name, role_name, admin_option   FROM svv_user_grants;

Verification: every row in svv_relation_privileges carries an explicit identity_type. If a principal you expect as a role shows up as group, it is a legacy GROUP grant that predates your RBAC migration — that is real drift, not a read error.

Step 3: Normalize both sides into one canonical grant set

Direct comparison is impossible until both catalogs collapse into the same shape. The canonical unit is a frozen tuple (principal, principal_kind, object_schema, object_name, privilege, grantable). The catalog sources differ as follows:

Fold Redshift’s three principal kinds into the same namespace PostgreSQL uses, tagging each with its origin so the diff never confuses a Redshift group with a PostgreSQL role of the same name:

from dataclasses import dataclass

@dataclass(frozen=True, slots=True)
class Grant:
    principal: str
    principal_kind: str   # 'role' | 'group' | 'user' | 'public'
    object_schema: str
    object_name: str
    privilege: str        # normalized: SELECT | INSERT | UPDATE | DELETE | ...
    grantable: bool

def canonical(rows, kind_field=None, default_kind="role") -> set[Grant]:
    out = set()
    for r in rows:
        out.add(Grant(
            principal=r["principal"].lower(),
            principal_kind=(r.get(kind_field) or default_kind).lower(),
            object_schema=r["object_schema"].lower(),
            object_name=r["object_name"].lower(),
            privilege=r["privilege_type"].upper().strip(),
            grantable=bool(r.get("admin_option") or r.get("is_grantable")),
        ))
    return out

pg_set = canonical(pg_rows)                                  # PostgreSQL side
rs_set = canonical(rs_rows, kind_field="identity_type")      # Redshift side

Verification: len(pg_set) and len(rs_set) should be stable across consecutive extractions of an unchanged catalog. A fluctuating count means non-deterministic ordering leaked in — freeze it here, not in the diff. This canonical step is the same normalization contract described in privilege scope mapping and reused across every cross-environment privilege extraction target.

Step 4: Compute and score the delta

With two comparable sets, the diff is set arithmetic, then severity scoring so not every delta pages someone at 3am:

import json

def diff(source: set[Grant], target: set[Grant]) -> dict:
    added   = target - source     # present in Redshift, absent in PostgreSQL
    removed = source - target     # present in PostgreSQL, absent in Redshift
    return {"added": sorted(map(vars, added), key=lambda g: (g["principal"], g["object_name"])),
            "removed": sorted(map(vars, removed), key=lambda g: (g["principal"], g["object_name"]))}

HIGH_RISK = {"DELETE", "DROP", "UPDATE", "TRUNCATE", "GRANT OPTION"}

def score(entry: dict) -> int:
    w = 5 if entry["privilege"] in HIGH_RISK else 1
    return w + (3 if entry["grantable"] else 0)   # delegated privileges weigh more

report = diff(pg_set, rs_set)
report["drift_index"] = sum(score(e) for e in report["added"] + report["removed"])
print(json.dumps(report, indent=2))

Verification: on two converged catalogs the report is {"added": [], "removed": [], "drift_index": 0}. Re-running is idempotent — the engine is read-only and never mutates either endpoint. The weighting here is the same model formalized in rule-based drift scoring, and the threshold that decides whether drift_index fires an alert is tuned in threshold tuning for alerts.

Worked example: reporting access diverges after a Redshift hotfix

Scenario: policy says the reporting_ro principal reads analytics.events in both engines. During an incident, someone ran GRANT INSERT ON analytics.events TO GROUP reporting on Redshift and never walked it back.

PostgreSQL canonical set:

reporting_ro | role  | analytics | events | SELECT | False

Redshift canonical set:

reporting_ro | role  | analytics | events | SELECT | False
reporting    | group | analytics | events | INSERT | False

The engine emits:

{
  "added": [
    {"principal": "reporting", "principal_kind": "group",
     "object_schema": "analytics", "object_name": "events",
     "privilege": "INSERT", "grantable": false}
  ],
  "removed": [],
  "drift_index": 5
}

The INSERT scores 5 (high-risk write on a read-only reporting path), crosses the alert threshold, and routes to remediation. Note the engine correctly flagged a legacy group grant against a role-based policy — the exact confusion a string diff would have buried.

PostgreSQL vs Redshift gotchas

The set-difference method is engine-agnostic; the catalogs are not. Guard against these Redshift-specific traps:

• information_schema is role-blind on Redshift. information_schema.table_privileges does not surface grants made to native ROLEs. Always read svv_relation_privileges for the authoritative privilege set — mixing the two under-reports role grants and produces false “removed” deltas.
• The tri-model principal split. Redshift keeps users (pg_user), groups (pg_group), and roles (svv_roles) as distinct namespaces; PostgreSQL unifies all three as roles. Always carry principal_kind so a Redshift group reporting never silently matches a PostgreSQL role reporting.
• Managed system roles. Redshift injects sys:secadmin, sys:dbadmin, sys:operator, and sys:superuser, which have no PostgreSQL equivalent — the direct analogue of RDS-managed roles covered when comparing role snapshots across AWS RDS and on-prem. Route them through exception routing and whitelisting rather than the diff.
• Catalog lag. Redshift svv_* views can trail a DDL commit by seconds. Apply a 60–120s grace window before flagging structural change, or you will alert on eventual-consistency artifacts, not drift.
• Late-binding views. svv_relation_privileges omits late-binding views; if your policy grants on them, read svv_* privilege views for those objects explicitly or they read as false “removed” rows.

Compliance mapping and the audit artifact this produces

Comparing the enforced privilege set across two engines directly satisfies the access-restriction controls auditors probe: SOC 2 CC6.1 and CC6.3 (logical access limited to what each identity requires, consistently across systems), PCI-DSS Requirement 7 (least-privilege access by role), and HIPAA §164.312(a)(1) (technical access control). The evidence artifact is the pairing of the two timestamped canonical snapshots with the scored JSON delta report from Step 4: together they prove the intended policy, both live states at a point in time, and the exact grant discrepancy between them — a reviewable chain from control requirement to observed drift.

Frequently asked questions

Does Amazon Redshift have roles like PostgreSQL? Yes, since late 2022 (Redshift ≥ 1.0.44903) Redshift supports native CREATE ROLE / GRANT ROLE, exposed through svv_roles, svv_role_grants, and svv_user_grants. But legacy GROUP-based grants still coexist, so a complete extraction must read groups, users, and roles — not roles alone.

Why not just string-diff two GRANT statement dumps? Because the two engines emit different syntax, principal kinds, and ordering for identical policy, so a text diff reports drift on nearly every line. Normalizing both into canonical (principal, kind, object, privilege, grantable) tuples before diffing is what makes the comparison meaningful.

How do I stop Redshift system roles like sys:dbadmin from showing as drift? They have no PostgreSQL counterpart, so they will always appear as “added”. Filter them at extraction (identity_name NOT LIKE 'sys:%') and codify any that must survive in a version-controlled allowlist evaluated before scoring.

Which engine is the source of truth? Neither by default — pick one comparison axis per run. For a policy authored in OLTP and mirrored to analytics, treat PostgreSQL as source and Redshift as target so added reads as “extra privilege in the warehouse”. Make that choice explicit in the report metadata for the auditor.

Related

• Comparing role snapshots across AWS RDS and on-prem — the same normalize-then-diff method across managed and self-hosted PostgreSQL.
• 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.
• System Catalog Query Optimization — reading svv_* and pg_catalog efficiently for the extraction steps above.

Up: Environment Comparison Workflows