Declarative per-schema owner directive

[hardbyte]

What I'd like to be able to express

A way to say "this schema is owned by this role" directly in the policy YAML, analogous to how default_owner sets the global default today:

default_owner: pgloader_pg

schemas:
  - name: cdc
    owner: cdc_owner        # <-- new, per-schema override
    profiles: [editor, viewer, subscriber]
  - name: awa
    owner: awa_owner
    profiles: [editor, viewer]

On apply, pgroles would:

1. Ensure the schema exists with the correct AUTHORIZATION (using CREATE SCHEMA ... AUTHORIZATION on first create, ALTER SCHEMA ... OWNER TO on converge).
2. Set ALTER DEFAULT PRIVILEGES FOR ROLE {owner} per profile, so the editor/viewer grants keep applying to objects the owner creates afterwards.

Out of scope

Bulk reassignment of existing schema-scoped objects (tables, functions, sequences, types) to the new owner. Reasons to leave this out:

• Blast radius. Changing the declared owner would rewrite ownership of potentially hundreds of objects on the next apply. Bigger consequence than any of pgroles' current converge steps.
• Edge cases. Partitioned parents + children, enum array types, function overloads, materialised views, extension-owned objects. Each solvable, each a source of bugs.
• Overlap with migrations. Migrations sometimes intentionally reassign ownership for specific reasons (RLS, replication, FDW). Silently re-overriding that on converge is the kind of trap that's hard to debug.
• Incompleteness. Services that create objects at runtime (e.g. a cdc-relay calling CREATE TABLE IF NOT EXISTS) produce objects owned by the connecting role, not necessarily the schema owner, unless they SET ROLE first. pgroles can't enforce that from outside, so the invariant is never fully pgroles'.

Object-level ownership reassignment stays a one-time migration concern when adopting the pattern. Optionally pgroles could ship a CLI helper (pgroles reassign --schema cdc) that does the walk, but it's not part of the converge loop.

Why

A handful of services at work own a schema that they manage themselves at runtime — their startup code does CREATE OR REPLACE FUNCTION, CREATE TABLE IF NOT EXISTS, dynamic CREATE TRIGGER, etc. Those statements either require ownership or (for replace-style DDL) require the caller to be a member of the owning role.

Concretely, in one of our services:

• A change-data-capture relay owns a cdc schema whose DDL it generates at boot.
• An async-worker framework owns an awa schema and uses awa_owner (NOLOGIN) as the owning role, with service login roles granted membership.

With the current policy shape the only way I've found to express this is:

1. Let pgroles apply create the schema under default_owner.
2. Run a post-apply SQL step that does ALTER SCHEMA … OWNER TO and sets ALTER DEFAULT PRIVILEGES FOR ROLE for the new owner.

That works but moves a small piece of the policy out of the declarative file and into a side-car SQL script, which is awkward to keep consistent.

What I've considered

• Just making the service login role the owner. Works but conflates ownership with the connecting identity, and rules out having multiple login roles (old + new during a rollout) both act as owner via membership — which is the obvious zero-downtime story.
• A dedicated {schema}_owner NOLOGIN role with login roles granted membership. This is a clean pattern and works fine at the SQL level; I'd just like pgroles to know about it so the ownership and its default privileges are part of the converged state.

Shape of the feature

Either:

1. owner: field on each schema entry (as sketched above). Falls back to default_owner if omitted.
2. Ownership expressed on the owning role instead, e.g. owns_schemas: [cdc, awa] on a role entry.

Either would let pgroles also set ALTER DEFAULT PRIVILEGES FOR ROLE {owner} per profile so editor/viewer grants keep applying to future objects without a separate SQL step.

[hardbyte]

High-level plan for this feature:

• Treat declared schemas as first-class managed state, not just as names referenced by grants/default privileges.
• Keep the manifest shape as , resolving per-schema owner first and then falling back to .
• Extend the normalized model / diff / SQL pipeline with schema changes so pgroles can:
  • create missing schemas ( / )
  • converge ownership on existing schemas ()
• Keep object-level ownership reassignment out of scope for converge. Only schema existence + schema owner are managed.
• Do not add schema dropping in this first pass. That keeps the feature useful without introducing a new destructive converge step.
• Extend inspection to read schema owners from so owner drift is visible in / .
• Update preflight validation so:
  • schemas explicitly declared under may be absent and will be created by pgroles
  • schemas only referenced by grants/default privileges still need to exist up front
• Ensure generated profile default privileges continue to use the resolved schema owner, so stays aligned with schema ownership.
• Update / export so inspected schema ownership can round-trip back into YAML.
• Test coverage:
  • manifest/model expansion for per-schema owner resolution
  • diff + SQL rendering for create-schema and alter-schema-owner
  • inspect support for schema owners
  • operator/CLI validation around missing declared vs undeclared schemas
  • live integration tests for create + converge + clean re-diff

I'll implement this in that order unless something surprising comes up in the existing tests/code paths.

[hardbyte]

Implementation plan for the first pass:

• Treat declared schemas as first-class managed state, not just as names referenced by grants/default privileges.
• Keep the manifest shape as schemas[].owner, resolving per-schema owner first and then falling back to default_owner.
• Extend the normalized model, diff, and SQL pipeline with schema changes so pgroles can:
  • create missing schemas (CREATE SCHEMA ... / CREATE SCHEMA ... AUTHORIZATION ...)
  • converge ownership on existing schemas (ALTER SCHEMA ... OWNER TO ...)
• Keep object-level ownership reassignment out of scope for converge. Only schema existence plus schema owner are managed.
• Do not add schema dropping in this first pass. That keeps the feature useful without introducing a new destructive converge step.
• Extend inspection to read schema owners from pg_namespace so owner drift is visible in diff / apply.
• Update preflight validation so:
  • schemas explicitly declared under schemas: may be absent and will be created by pgroles
  • schemas only referenced by grants/default privileges still need to exist up front
• Ensure generated profile default privileges continue to use the resolved schema owner, so ALTER DEFAULT PRIVILEGES FOR ROLE {owner} stays aligned with schema ownership.
• Update generate / export so inspected schema ownership can round-trip back into YAML.
• Test coverage:
  • manifest/model expansion for per-schema owner resolution
  • diff and SQL rendering for create-schema and alter-schema-owner
  • inspect support for schema owners
  • operator/CLI validation around missing declared vs undeclared schemas
  • live integration tests for create + converge + clean re-diff

[hardbyte]

Addressed by #90, which added declarative per-schema support, schema creation/ownership convergence, owner-scoped default privileges, and the corresponding docs/tests. The intentionally out-of-scope part from the issue remains unchanged: pgroles does not automatically reassign ownership of existing objects inside the schema. Closing.

[hardbyte]

Addressed by #90, which added declarative per-schema owner support, schema creation/ownership convergence, owner-scoped default privileges, and the corresponding docs/tests. The intentionally out-of-scope part from the issue remains unchanged: pgroles does not automatically reassign ownership of existing objects inside the schema. Closing.