Syncing RBAC manifests from Git in CI pipelines

Q: What stops the apply job from running on a feature branch?

Two independent gates: the workflow only triggers apply on push to main, and the entrypoint itself refuses unless GITHUB_REF is refs/heads/main. Even a misconfigured trigger cannot apply off the protected branch.

Q: How do I know the apply actually converged?

The final step re-runs plan with the read-only credential and fails the job unless the grant and revoke lists are both empty. An empty post-apply plan is the machine-checked proof that the live database matches the manifest.

This page gives you a working two-job CI pipeline that turns a version-controlled RBAC manifest into live database grants: a plan job runs on every pull request and posts the diff, and a gated apply job runs on merge to the main branch.

When to use this — and when not to

Use this workflow when:

Your privilege manifest already lives in git and you want every grant change to arrive through a reviewed pull request rather than a console click or an ad-hoc psql session.
You run CI (GitHub Actions here, but the shape ports to GitLab or Jenkins) and can attach two separately-scoped database credentials — one read-only, one write.
You need the exact diff a reviewer approved to be the exact diff that applies, with no human retyping DDL between review and production.

Do not reach for this when:

Your manifest is not yet trustworthy — if extraction still misses grants, a merge will revoke privileges the database legitimately needs. Stabilize the manifest through Schema Validation Pipelines first.
You have a single database and two engineers. A protected branch and a nightly reconcile script may be all the ceremony you need; a full plan/apply split is overhead.
The apply DDL is not yet idempotent. Gating a non-convergent apply behind CI just automates a script that errors on the second run — fix that with Idempotent Privilege Apply before wiring it to merge.

Figure 1 — Two triggers, two jobs: the pull-request event plans read-only and comments; a push to main applies write-scoped behind a review-and-dry-run gate.

Step-by-step implementation

Step 1 — Model the manifest and load it with pydantic v2

The manifest is a YAML file checked into the repository. Loading it through pydantic v2 means an invalid grant fails the pull-request build before any database is touched.

# sync/manifest.py
from datetime import datetime
import yaml
from pydantic import BaseModel, Field, field_validator

ALLOWED = {"SELECT", "INSERT", "UPDATE", "DELETE", "EXECUTE", "USAGE"}

class GrantSpec(BaseModel):
    principal: str
    obj: str                        # "sales.orders"
    privilege: str
    grantable: bool = False

    @field_validator("privilege")
    @classmethod
    def known(cls, v: str) -> str:
        u = v.strip().upper()
        if u not in ALLOWED:
            raise ValueError(f"unsupported privilege {v!r}")
        return u

class Manifest(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(path: str) -> Manifest:
    with open(path) as fh:
        return Manifest.model_validate(yaml.safe_load(fh))

Verify the model rejects a bad privilege and accepts a good manifest:

from sync.manifest import load
m = load("manifests/prod.yaml")
print(m.environment, len(m.grants))       # -> prod 42

prod 42

Step 2 — Diff the manifest against live catalogs with psycopg v3

The plan reads effective grants with psycopg v3 over a read-only connection, then diffs. The result is a plain dict any CI step can serialize.

# sync/plan.py
import psycopg
from sync.manifest import Manifest

OBSERVED = """
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)
            return {(g, o, p, bool(gr)) for g, o, p, gr in cur.fetchall()}

def build_plan(m: Manifest, dsn: str) -> dict[str, list]:
    live = observed(dsn)
    want = m.desired()
    return {"grant": sorted(want - live), "revoke": sorted(live - want)}

Verify against a database that is missing one grant and has one extra:

from sync.plan import build_plan
from sync.manifest import load
plan = build_plan(load("manifests/prod.yaml"), DSN_RO)
print({k: len(v) for k, v in plan.items()})

{'grant': 1, 'revoke': 1}

Step 3 — Wrap both stages in one CLI entrypoint

A single script exposes plan and apply. The apply path re-derives the plan from the same code and refuses to run off the main branch, so what applies is exactly what was reviewed.

# sync/__main__.py
import argparse, json, os, sys
import psycopg
from sync.manifest import load
from sync.plan import build_plan

def render_apply(plan: dict) -> list[str]:
    stmts = []
    for grantee, obj, priv, _ in plan["revoke"]:
        stmts.append(f'REVOKE {priv} ON {obj} FROM "{grantee}"')
    for grantee, obj, priv, grantable in plan["grant"]:
        opt = " WITH GRANT OPTION" if grantable else ""
        stmts.append(f'GRANT {priv} ON {obj} TO "{grantee}"{opt}')
    return stmts

def main() -> int:
    ap = argparse.ArgumentParser(prog="sync")
    ap.add_argument("cmd", choices=["plan", "apply"])
    ap.add_argument("--manifest", required=True)
    args = ap.parse_args()

    plan = build_plan(load(args.manifest), os.environ["DB_DSN"])

    if args.cmd == "plan":
        print(json.dumps(plan, indent=2))
        return 0

    # apply: gate on branch, then run idempotent DDL in one transaction
    if os.environ.get("GITHUB_REF") != "refs/heads/main":
        print("apply refused: not on main", file=sys.stderr)
        return 2
    stmts = render_apply(plan)
    with psycopg.connect(os.environ["DB_DSN"]) as conn:
        with conn.cursor() as cur:
            for s in stmts:
                cur.execute(s)
        conn.commit()
    print(f"applied {len(stmts)} statements")
    return 0

if __name__ == "__main__":
    raise SystemExit(main())

Step 4 — Bind it to git with a GitHub Actions workflow

Two jobs, two triggers. The plan job runs on pull_request with the read-only DSN and comments the plan. The apply job runs on push to main, uses a protected environment (which enforces required reviewers), and asserts the plan is empty after applying.

name: rbac-sync
on:
  pull_request:
    paths: ["manifests/**"]
  push:
    branches: [main]
    paths: ["manifests/**"]

jobs:
  plan:
    if: github.event_name == 'pull_request'
    runs-on: ubuntu-latest
    env:
      DB_DSN: $
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: "3.11" }
      - run: pip install -r requirements.txt
      - name: Compute plan
        run: python -m sync plan --manifest manifests/prod.yaml | tee plan.json
      - name: Comment plan on PR
        uses: actions/github-script@v7
        with:
          script: |
            const fs = require('fs');
            const body = "```json\n" + fs.readFileSync('plan.json','utf8') + "\n```";
            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner, repo: context.repo.repo, body });

  apply:
    if: github.event_name == 'push'
    runs-on: ubuntu-latest
    environment: production        # required reviewers gate this job
    env:
      DB_DSN: $
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: "3.11" }
      - run: pip install -r requirements.txt
      - name: Apply
        run: python -m sync apply --manifest manifests/prod.yaml
      - name: Dry-run assert convergence
        env:
          DB_DSN: $
        run: |
          test "$(python -m sync plan --manifest manifests/prod.yaml \
            | python -c 'import sys,json; p=json.load(sys.stdin); print(len(p["grant"])+len(p["revoke"]))')" = "0"

The final step re-plans with the read-only credential and fails the job unless the plan is empty, proving the live database now matches the manifest.

Worked example: PostgreSQL 15, three environments, a CI service account

A team runs PostgreSQL 15 across dev, staging, and prod. An engineer opens a pull request adding SELECT on analytics.daily_revenue for the bi_reader role in manifests/prod.yaml. The plan job runs and comments:

{
  "grant": [["bi_reader", "analytics.daily_revenue", "SELECT", false]],
  "revoke": [["legacy_etl", "analytics.daily_revenue", "SELECT", false]]
}

The reviewer sees two things: the intended grant, and — importantly — a revoke of a stale legacy_etl grant that had drifted in out of band and is not in the manifest. They approve. On merge, the apply job (gated by the production environment’s required reviewer) runs REVOKE ... FROM "legacy_etl" then GRANT ... TO "bi_reader" in one transaction. The dry-run assert re-plans and finds an empty diff, so the job passes. Git now records who requested the grant, who approved it, and the exact statements applied — the same continuous evidence stream that Continuous Control Monitoring consumes between merges.

Gotchas and engine-specific notes

PostgreSQL PUBLIC grants. information_schema.role_table_grants surfaces implicit PUBLIC grants that your manifest will never list, so the plan reports them as revoke. Either filter grantee <> 'PUBLIC' (as above) or model PUBLIC explicitly. Revoking a real PUBLIC grant you did not intend to touch is a genuine outage risk.

MySQL 8 role activation. Porting the read to MySQL means querying information_schema.schema_privileges and mysql.role_edges, but a granted role does nothing until it is a default role. A manifest that only issues GRANT role TO user leaves the user inert; the apply must also SET DEFAULT ROLE from mysql.default_roles, or the plan will look converged while the session exercises no privileges.

Version-specific catalog columns. is_grantable is a yes/no text column in information_schema; the cast to bool matters so the tuple compares equal to the manifest’s grantable: false. On MySQL the equivalent lives in information_schema.table_privileges.is_grantable with the same text encoding — normalize both to Python bool before diffing or every row reports spurious drift.

Fork pull requests and secrets. GitHub does not expose repository secrets to workflows triggered by fork pull requests, so the plan job’s DB_DSN_READONLY is empty for forks. Run fork plans against a sanitized replica, and never let the write secret reach any pull_request-triggered job — the branch check in apply is a second line of defense, not the first.

Compliance note

This workflow satisfies SOC 2 CC6.3 (access changes are authorized) and CC8.1 (change management) by making the pull request the authorization record and branch protection the enforcement point: no grant reaches production without a named approver. The artifact it produces for auditors is a joined trail — the git commit and its approving reviewer, the plan comment showing the exact before/after grant diff, and the apply job log showing the statements that ran and the post-apply dry-run that proved convergence. Because that evidence is generated per change rather than reconstructed during an audit window, it also feeds the least-privilege attestation expected under PCI DSS Requirement 7.

Frequently asked questions

Why run the plan read-only instead of just previewing the apply? A read-only credential makes it structurally impossible for a pull-request build — including one from a fork — to mutate access. Previewing with a write credential relies on the code behaving; a role that lacks GRANT relies on the database refusing, which is a far stronger guarantee.

What stops the apply job from running on a feature branch? Two independent gates: the workflow only triggers apply on push to main, and the entrypoint itself refuses unless GITHUB_REF is refs/heads/main. Even a misconfigured trigger cannot apply off the protected branch.

How do I know the apply actually converged? The final step re-runs plan with the read-only credential and fails the job unless the grant and revoke lists are both empty. An empty post-apply plan is the machine-checked proof that the live database matches the manifest.

What happens if someone changes a grant directly in the database? The next scheduled reconcile or the next pull-request plan will show that change as revoke (it is not in the manifest). Reviewing and merging pulls the database back to the manifest, so out-of-band edits are surfaced and corrected through the same reviewed path rather than silently kept.

GitOps Privilege Synchronization — the loop and safety model this workflow implements.
Idempotent Privilege Apply — the convergent GRANT/REVOKE DDL the apply job depends on.
Rollback and Recovery — unwinding a failed apply without a half-synchronized state.

Up: GitOps Privilege Synchronization