Staged adoption

Guide to rolling out pgroles against existing databases without disruption.

Brownfield vs greenfield

If your database already has roles, grants, and schemas, you are in a brownfield scenario. pgroles is designed for this — use additive mode to layer managed roles on top of existing state without revoking anything.

For new databases where pgroles owns everything from the start, authoritative mode is appropriate.

Recommended rollout

1. Generate a baseline manifest

Start by capturing what already exists:

pgroles generate --database-url $DATABASE_URL > pgroles.yaml

This produces a flat manifest you can refine into profiles and schema bindings.

2. Plan mode first

Deploy with mode: plan to see what pgroles would do without executing any SQL:

spec:
  mode: plan
  reconciliation_mode: additive

The operator will report planned changes in the CRD status, including the full SQL.

3. Validate with diff

Run pgroles diff locally to review changes before enabling apply:

pgroles diff --database-url $DATABASE_URL -f pgroles.yaml --mode additive

If the output is -- No changes needed, the manifest matches the database and apply will be a no-op.

4. Enable additive apply

Switch to mode: apply with reconciliation_mode: additive. This applies all non-destructive changes — creating roles, adding grants and memberships, setting default privileges — but never revokes existing privileges, removes memberships, or drops roles.

spec:
  mode: apply
  reconciliation_mode: additive

5. Progress to authoritative (optional)

Once the manifest covers all roles and grants you want managed, switch to reconciliation_mode: authoritative to enable full convergence. Review the planned revocations carefully before switching — in a typical brownfield database, this may include thousands of existing grants to roles not yet in the manifest.

App-owned schemas

Applications that create their own schemas via migrations (e.g. awa, analytics) require a two-stage manifest:

1. Bootstrap — create login roles and database-level grants before migrations run
2. Full — add schema-level grants, object grants, and default privileges after migrations have created the schema

This is necessary because GRANT USAGE ON SCHEMA foo fails if foo does not exist yet.

# Stage 1: bootstrap (pre-migration)
roles:
  - name: app_runtime
    login: true
grants:
  - role: app_runtime
    privileges: [CONNECT]
    object:
      type: database
      name: myapp

# Stage 2: full (post-migration)
schemas:
  - name: app_schema
    profiles: [editor, viewer]

PUBLIC privilege caveats

PostgreSQL grants certain default privileges to the PUBLIC pseudo-role on every database (e.g. CONNECT, TEMPORARY). pgroles does not inspect or manage PUBLIC grants.

This means:

• A role may have effective privileges not visible in pgroles inspect output
• A manifest that omits TEMPORARY does not guarantee the role lacks TEMPORARY — it may still inherit it from PUBLIC
• additive mode showing "no changes needed" does not mean effective privileges match the manifest exactly

If least-privilege enforcement is important, you may need to manually revoke unwanted PUBLIC grants:

REVOKE TEMPORARY ON DATABASE mydb FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;