Support composed policies and scoped ownership in the operator

[hardbyte]

Summary

Support multi-document policy composition and first-class scope ownership in the Kubernetes operator so pgroles can work cleanly in environments where platform/SRE teams and application teams share responsibility for PostgreSQL access control.

Today the operator reconciles a single PostgresPolicy into one core PolicyManifest. That works well for smaller setups, but it becomes awkward in larger environments where:

• platform/SRE wants to own connection settings, shared profiles, schema ownership, and privileged group roles
• application teams want to own their service roles and memberships
• multiple teams need independent review and GitOps workflows without editing one shared object

The operator already has same-database conflict detection between separate policies, but it does not yet support composing multiple fragments into one desired policy with explicit in-policy ownership boundaries.

Goals

• Support policy composition from multiple scoped inputs
• Make ownership boundaries explicit and validated before inspect/diff/apply
• Keep one reconcile loop, one lock, one plan, and one apply transaction per database target
• Reuse the same core composition semantics in the CLI and operator
• Preserve the current single-policy path for simpler users

Non-goals

• Reading arbitrary files from Git repos or ConfigMaps at reconcile time
• Letting independently reconciled fragments race on the same database
• Adding implicit "last writer wins" merge semantics

Proposed model

Introduce composition in pgroles-core first, then expose it through the operator.

Core concepts:

• PolicyBundle
  • shared profiles and auth provider metadata
  • list of policy fragments to compose
• PolicyFragment
  • one team's scoped contribution
  • declares resources plus an explicit ownership scope
• ComposedPolicy
  • the validated result of bundle + fragments
• OwnershipIndex
  • exact ownership of generated and declared objects
• ManagedScope
  • the set of database objects pgroles is allowed to inspect/diff/manage

For the operator API, the cleanest shape is:

• PostgresPolicyBundle
  • connection settings
  • interval / mode / approval
  • shared profiles
  • selector or refs for fragments
• PostgresPolicyFragment
  • team-owned policy content
  • explicit scope ownership

Only the bundle should reconcile. Fragments are inputs, not independently applying resources.

Scope ownership

Ownership should be explicit, not inferred after the fact.

Examples:

• schema facets such as owner and bindings
• named roles
• membership edges
• exact grant keys
• exact default-privilege keys
• retirements by role name

If a fragment defines content outside its declared scope, the composed policy should fail validation.
If two fragments claim the same managed key, the bundle should go Conflict before any SQL is planned or applied.

Operator reconciliation semantics

The intended reconcile flow becomes:

1. Reconcile PostgresPolicyBundle
2. Load matching PostgresPolicyFragment resources
3. Compose bundle + fragments through pgroles-core
4. Validate duplicate/conflicting claims and out-of-scope declarations
5. Build one desired graph and one managed scope
6. Inspect the live database only within that managed scope
7. Diff and either apply or publish a plan

This keeps the existing lower-half operator behavior intact:

• same per-database locking
• same advisory locking
• same PostgresPolicyPlan style plan lifecycle
• same status / events / OTLP observability model

Backward compatibility

• Keep PostgresPolicy as the current single-document path
• Add composition as a new API path rather than changing existing semantics underneath users
• Reuse the same core composition library from CLI and operator so behavior stays aligned

Suggested phases

1. Add composition and ownership primitives to pgroles-core
2. Add CLI support for validating/composing bundles and fragments
3. Add PostgresPolicyBundle / PostgresPolicyFragment CRDs
4. Reconcile only bundles, using fragments as inputs
5. Reuse existing plan/apply/status machinery at bundle level
6. Document migration from single-policy to bundle/fragment workflows

Acceptance criteria

• Conflicting fragment ownership is rejected before database inspection
• Out-of-scope fragment declarations are rejected during validation
• Generated roles/grants/default privileges from schema/profile expansion participate in ownership checks
• One bundle reconcile produces one composed plan and one apply transaction
• Existing PostgresPolicy users remain unaffected

Open questions

• Should fragments be selected by label selector, explicit refs, or both?
• Should PostgresPolicyPlan stay bundle-scoped only, or also reference the fragment set digest used to compose it?
• Do we want bundle-local shared profiles only, or separately reusable cluster-scoped profile libraries later?

[hardbyte]

Use case data from running pgroles across multiple environments — both CLI-managed (local compose) and operator-managed (multiple Kubernetes clusters).

Cross-environment composition, not just cross-team

One database, four targets: local compose (CLI), and three Kubernetes envs the operator manages with different connection settings and (currently) different modes — one in apply, one in plan, one not yet pgroles-managed.

Env-invariant content (profile definitions, schema list, role-naming conventions, ownership directives) is the same across all four. Env-specific differences are confined to the connection block, mode / reconciliation_mode, which app_* login roles exist locally, and a handful of IAM-identity memberships.

CLI --bundle handles the local side cleanly. Operator side has the same duplication in N PostgresPolicy YAMLs, hand-maintained. That drift produced two concrete incidents — both of which the bundle/fragment design in this issue would prevent. Worth recording as motivating evidence.

Problem A: same logical change has to land in two separate files

Adding a new CDC source schema needed three coordinated edits:

1. pgroles policy — add the schema with the cdc_source profile binding and the SECURITY-DEFINER-owner membership. Covers the env where the operator runs apply.
2. Application's relay-list config — applies in every env.
3. A migration granting the same access directly — needed only in envs where the operator doesn't manage that DB in apply mode.

Step 3 was authored, then dropped in review under "pgroles handles it now". True in the apply env; false everywhere else. The application crashlooped on startup with permission denied for schema <X> in the non-apply env.

If step 1 lived in a shared fragment that every env's bundle referenced, the "where does this take effect" question would be structural rather than tribal — and step 3 would be visibly redundant rather than ambiguously redundant.

Problem B: silent per-env drift masked Problem A's pre-prod signal

Two of the operator-managed envs should have ~identical policies. They didn't: the lagging env trailed the leading env by ~140 lines because somebody updated one without the other.

The lagging env was in mode: plan so the divergence was invisible at reconcile time. But that env also retained over-grants from an earlier apply run, which happened to cover the gap Problem A introduced. The env that should have failed first went silent. The non-pgroles-managed env (production) failed instead — late and customer-visible.

A shared fragment between the two envs would have collapsed the 140-line drift to zero. The over-grant masking is a separate concern (reconciliation_mode: adopt mitigates it), but the cause of the drift between the envs would be eliminated.

Two emphases for the design

1. Cross-environment composition matters as much as cross-team composition. The fragment model handles both — a "shared spine" fragment is just a fragment that multiple bundles reference across envs. Worth an explicit goals-section sentence.
2. Admission-time validation is the load-bearing benefit over a YAML render layer. Kustomize overlays can DRY the YAML, but conflict detection then happens at reconcile or runtime — too late. The pgroles-core bundle validator catching scope/ownership conflicts at kubectl apply is what stops the analogous next bug.