GitOps privilege synchronization

GitOps privilege synchronization makes a version-controlled manifest the single authoritative source of who may touch which database object, and reduces every live grant change to a reviewed, mergeable, replayable git operation. Without it, privilege edits happen out of band — a hotfix GRANT at 2am, a console click in the cloud IAM panel — and the database’s real access surface silently diverges from anything a human ever approved. When that gap is discovered during an audit rather than at merge time, there is no ticket, no reviewer, and no diff to explain it, and the finding lands on the platform team that owns the estate. This section shows how to close that gap by binding the privilege state to git the same way infrastructure teams bind cluster state: declare intent in a file, review the change as a pull request, plan it in CI, and apply it only after merge.

Prerequisites and scope

This model assumes you already produce a canonical privilege manifest and can read live catalog state cheaply. Concretely, it expects PostgreSQL 13+ or MySQL 8.0+, psycopg v3 for catalog reads and apply, and pydantic v2 to load and validate the manifest as typed data rather than free-form YAML. The CI runner needs two credentials: a read-only role holding SELECT on the system catalogs for the plan stage, and a separately-scoped role that can GRANT and REVOKE for the apply stage — never the same secret, so a compromised pull-request build cannot mutate access. Applying the plan requires the idempotent DDL contract from Idempotent Privilege Apply; this section assumes those grant and revoke statements already converge on repeat runs and focuses on the git-driven control flow that decides when they run.

The manifest itself is the deliverable of the extraction side of the estate. How its schema is validated on the way in — column drift, renamed objects, engine-specific privilege names — is the job of Schema Validation Pipelines; GitOps synchronization consumes a manifest those pipelines have already declared well-formed.

How the loop works

The pipeline is four cooperating stages bound to git lifecycle events. A change to the manifest opens a pull request; CI computes a plan and posts it as the reviewable artifact; a human approves; merge to the protected branch is the only event that authorizes an apply.

1. Load the manifest as typed, validated data

The manifest is the contract, so it is parsed into pydantic models before anything reads the database. Validation failures fail the pull request build, which means a malformed grant never reaches a plan, let alone an apply.

from datetime import datetime
from pydantic import BaseModel, Field, field_validator

class GrantSpec(BaseModel):
    principal: str
    obj: str                       # schema-qualified, e.g. "sales.orders"
    privilege: str                 # SELECT, INSERT, EXECUTE, ...
    grantable: bool = False

    @field_validator("privilege")
    @classmethod
    def known_privilege(cls, v: str) -> str:
        allowed = {"SELECT", "INSERT", "UPDATE", "DELETE", "EXECUTE", "USAGE"}
        u = v.strip().upper()
        if u not in allowed:
            raise ValueError(f"unsupported privilege: {v!r}")
        return u

class PrivilegeManifest(BaseModel):
    environment: str = Field(pattern=r"^(dev|staging|prod)$")
    generated_at: datetime
    grants: list[GrantSpec]

    def desired(self) -> set[tuple[str, str, str, bool]]:
        return {(g.principal, g.obj, g.privilege, g.grantable) for g in self.grants}

def load_manifest(path: str) -> PrivilegeManifest:
    import yaml
    with open(path) as fh:
        return PrivilegeManifest.model_validate(yaml.safe_load(fh))

Because desired() returns a set of hashable tuples, the plan stage can compute a difference in near-linear time and the result is order-independent — two runs over an unchanged manifest produce identical output, which is what makes the CI plan diffable across pull requests.

2. Compute the plan against live catalogs

The plan stage reads the live grant set with psycopg v3 and diffs it against the manifest. It is strictly read-only — it holds a connection whose role has only SELECT on the catalogs — and it never writes. Its whole product is a structured plan describing what an apply would do.

import psycopg

OBSERVED_SQL = """
SELECT grantee, table_schema || '.' || table_name AS obj,
       privilege_type, is_grantable::bool
FROM information_schema.role_table_grants
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND grantee <> 'PUBLIC'
ORDER BY grantee, obj, privilege_type;
"""

def observed(dsn: str) -> set[tuple[str, str, str, bool]]:
    with psycopg.connect(dsn, autocommit=True) as conn:
        conn.execute("SET transaction_read_only = on")
        with conn.cursor() as cur:
            cur.execute(OBSERVED_SQL)
            return {(g, o, p, bool(gr)) for g, o, p, gr in cur.fetchall()}

def plan(desired: set, live: set) -> dict[str, list]:
    """missing = grant it; excess = revoke it. Deterministic ordering."""
    return {
        "grant": sorted(desired - live),
        "revoke": sorted(live - desired),
    }

The plan dictionary is serialized to JSON and posted as a pull-request comment (and uploaded as a build artifact), so the diff a reviewer approves is byte-for-byte the diff CI will apply. This is the same read-side discipline used by Continuous Control Monitoring, which polls the identical catalog views on a schedule to detect drift between merges rather than only at merge time.

3. Gate the apply on merge

Apply runs only on the main branch, only after the plan has been approved, and only with the write-scoped credential. The apply reuses the same plan() function so it cannot drift from what was reviewed, then delegates each statement to the idempotent DDL layer inside one transaction.

import os
import psycopg

def apply(dsn: str, planned: dict[str, list]) -> int:
    if os.environ.get("GITHUB_REF") != "refs/heads/main":
        raise SystemExit("apply is only permitted on main")
    applied = 0
    with psycopg.connect(dsn) as conn:          # transactional: all-or-nothing
        with conn.cursor() as cur:
            for grantee, obj, priv, grantable in planned["revoke"]:
                cur.execute(f'REVOKE {priv} ON {obj} FROM "{grantee}"')
                applied += 1
            for grantee, obj, priv, grantable in planned["grant"]:
                opt = " WITH GRANT OPTION" if grantable else ""
                cur.execute(f'GRANT {priv} ON {obj} TO "{grantee}"{opt}')
                applied += 1
        conn.commit()
    return applied

Revokes run before grants so a tightened privilege set never has a transient window where both the old broad grant and the new narrow one coexist. Because the block is one transaction, a mid-batch failure rolls the whole apply back rather than leaving a half-synchronized state — the recovery semantics of that failure path belong to Rollback and Recovery.

Idempotency and safety contract

The loop is safe to run repeatedly because each stage is convergent. The plan is a pure function of (manifest, live state): given the same two inputs it always emits the same grant and revoke lists, so re-running the pull-request build never changes what a reviewer sees. The apply is idempotent at the statement level — REVOKE of an already-absent grant and GRANT of an already-present one are both no-ops in PostgreSQL — so replaying a merged plan against a database that already converged produces zero net change. Together these mean the reconcile step can run on a timer without risk: it recomputes the plan, and if the plan is empty the system is synchronized and nothing happens. When the plan is not empty after a merge — someone changed a grant out of band — reconcile opens a fresh pull request carrying that delta, so even manual changes are pulled back through review rather than silently overwritten.

The one hard rule that makes the whole model trustworthy is credential separation: the plan stage physically cannot write (its role lacks GRANT), and the apply stage runs only on the protected branch. A pull request from a fork therefore can compute a plan but can never touch production access.

Compliance alignment

Binding privilege changes to git turns everyday version control into a continuous evidence stream. Each control below maps to an artifact the loop produces without extra work.

The evidence set is git history (who changed what, who approved it, when it merged) joined to the pipeline logs (the plan that was reviewed and the apply that ran). Because both are immutable and timestamped, an auditor can replay any historical access decision end to end — the continuous, per-change version of the periodic access review that Continuous Control Monitoring formalizes.

Troubleshooting

Related

• Idempotent Privilege Apply — the convergent GRANT/REVOKE DDL the gated apply stage executes.
• Rollback and Recovery — how a failed apply unwinds cleanly without leaving a half-synchronized privilege set.
• Continuous Control Monitoring — scheduled catalog polling that detects drift between merges and feeds it back as evidence.
• Schema Validation Pipelines — declaring the manifest well-formed before it ever drives a plan.
• Syncing RBAC manifests from Git in CI pipelines — a complete GitHub Actions workflow and Python entrypoint that implement the loop above.

Up: Remediation & Synchronization Pipelines