Plan: A8b — reporting least-privilege restricted DB role + credential cutover (#1456, epic &73)

REVOKE ALL ON ALL TABLES/FUNCTIONS IN SCHEMA … FROM PUBLIC; GRANT CONNECT ON DATABASE; GRANT USAGE, CREATE ON SCHEMA … TO canopy_reporting_owner (transfer prereq) + GRANT USAGE ON SCHEMA … TO canopy_reporting_app; pin ALTER ROLE canopy_reporting_app SET search_path. No sequence grants exist to make — every PK is a UUID (uuidv7()/gen_random_uuid()); no serial/sequence/nextval anywhere in the reporting migrations.

GRANT EXECUTE on the 7 report_run_* plpgsql fns (enqueue/claim/checkpoint/heartbeat/abandon/finalize/reap). Load-bearing: all seven are SECURITY INVOKER — their bodies run as the caller, so EXECUTE alone is insufficient; the app also needs the underlying report_runs/report_generations DML enumerated above (which it has). Built-ins (uuidv7, gen_random_uuid, pg_advisory_xact_lock) are PUBLIC-executable by default. The canopy_redaction_keys_one_way_guard() trigger fn needs no app EXECUTE (trigger firing bypasses EXECUTE checks) but must transfer ownership with everything else.

Today the generation janitor is a Rust loop of unguarded direct DELETEs (worker/mod.rs:324-337 over JANITOR_TABLES — the 5 output tables + report_run_universe) trusting a candidate list from a separate SELECT (janitor_candidates, mod.rs:498-529). Under least privilege the runtime holds no direct DELETE on those six tables; the delete moves behind a SECURITY DEFINER fn owned by canopy_reporting_owner:

No runtime DB-role guard exists anywhere in the fleet (the rejection logic ships only migration-side, in the chain-v2 reconcile). Clone the #1006 guard structure from services/canopy-applications/src/guard.rs:

Files: none (GitLab + this plan)

Done (2026-08-12, this MR): #1463 (P1) + #1464 (P3) filed with full ACs; blocks links to #1456 set; parent Step-2b row amended; #1456 ACs refreshed.

Files: crates/canopy-api/src/idempotency.rs, crates/canopy-api/src/bootstrap.rs, crates/canopy-api/src/lib.rs, xtask/src/cmd/migrate.rs

Delivered under #1463 (see the issue for full ACs). Extract the DDL (CREATE_TABLE_DDL + EXPAND_MIGRATION_DDL, idempotency.rs:337-409) into a standalone fn; split IdempotencyCache::with_pool (:550-644) into that DDL part + a runtime part (metrics + cleanup task + renewal pool) that assumes the table exists; lib.rs:173 becomes DDL-free. The DDL fn is invoked from both migration arms: (a) bootstrap.rs migration_phase on the migration pool before it closes (:296-311), and (b) xtask/src/cmd/migrate.rs::apply after migrator.run (:200-205) — arm (b) is load-bearing because under SKIP_MIGRATIONS/JobOwned, migration_phase no-ops (bootstrap.rs:289-295); the chain-migration-split compose profile (dormant until the #1279 cutover, docker-compose.yml:1676-1728) models canopy-security/tanf/medicaid exactly that way whenever invoked, and reporting joins them at Step 8. The idempotency.rs:544-549 "no sqlx::migrate!`" constraint is respected — the DDL runs on the migration pool/job, never inside `_sqlx_migrations.

Files: crates/canopy-common/src/settings.rs, crates/canopy-api/src/bootstrap.rs

Add migration_database_url: Option<String> beside database_url (settings.rs:19), read through the secret provider with the optional-secret pattern (settings.rs:222-229). bootstrap.rs:119 runs migration_phase against migration_database_url.as_deref().unwrap_or(&database_url); the runtime pool (:121) stays on database_url. Run validate_database_name + the TLS gate on the effective migration URL as well. Fallback-to-runtime-URL is the correct default for the ~18 services that do not split credentials; a mis-set reporting deployment fails loud (permission denied on the first DDL), not silent.

Files: xtask/src/cmd/migrate.rs

Delivered under #1464 (see the issue for full ACs). In migrate.rs::apply, before any pool opens: canopy_crypto_shred::require_kek("CANOPY_ENCRYPTION_KEY")? gated on the sealing-service set {reporting, persons, snap, tanf, medicaid, caps, wic}. Closes the JobOwned hole the sealing migration’s 20261110000001:46-48 comment wrongly assumed shut. (Deviation from the original step: that migration comment itself is NOT byte-edited — sqlx checksums applied migrations, so any edit breaks every existing database’s version validation. Post-#1464 the comment’s conclusion — "this reset cannot commit and then strand an unsealable service" — is actually true via both gates; the corrected two-gate claim lives in the preflight’s own comment in migrate.rs.)

Files: services/canopy-reporting/migrations/<ts>_least_privilege_roles.sql

DO-guarded CREATE ROLE canopy_reporting_owner NOLOGIN + CREATE ROLE canopy_reporting_app LOGIN (no password in source); the fail-closed attribute reconcile (clone 20260910000000_chain_v2_substrate.sql:59-73); GRANT canopy_reporting_owner TO current_user (the migrator’s SET-membership for the transfer).

Files: same migration as Step 4, or a sibling <ts>_least_privilege_grants.sql (ordering: roles → schema grants → transfer → object grants)

GRANT USAGE, CREATE ON SCHEMA … TO canopy_reporting_owner; the one-time catalog-driven ownership-transfer loop (pg_class relkinds r/p/v/m/S + pg_proc, schema-scoped, ALTER … OWNER TO canopy_reporting_owner — the ratified REASSIGN OWNED fails on the pinned devstack superuser, see Design); REVOKE ALL … FROM PUBLIC; the per-object grant matrix + fn EXECUTEs from the Design table; GRANT USAGE ON SCHEMA … TO canopy_reporting_app; ALTER ROLE canopy_reporting_app SET search_path.

Files: services/canopy-reporting/migrations/<ts>_janitor_security_definer.sql, services/canopy-reporting/src/worker/mod.rs

Create reporting_janitor_reap per the Design; refactor reap_and_janitor (worker/mod.rs:310-339) to call it per candidate; the app role receives no direct DELETE on the six tables (grant omission in Step 5 + this fn is the only path).

Files: services/canopy-reporting/src/guard.rs (new), services/canopy-reporting/src/config.rs, services/canopy-reporting/src/main.rs

Clone services/canopy-applications/src/guard.rs per the Design. Call site: after bootstrap() returns the runtime pool, before serving.

Files: devstack/postgres/init.sql, docker-compose.yml

Roles + dev app password in init.sql behind DO-block guards (created as devstack superuser canopy). Init scripts run only on empty PGDATA, so adoption requires a one-time cargo xtask dev destructive volume reset (already blessed pre-1.0 by the parent’s fresh-start reset) — documented; no new post-start provisioning path. Flip docker-compose.yml:919 to postgres://canopy_reporting_app:…@postgres:5432/canopy_reporting; migrations run as owner via CANOPY_REPORTING__MIGRATION_DATABASE_URL (Step 2) or a canopy-reporting-migrate one-shot modeled on the chain-migration-split services (docker-compose.yml:1690-1704; note that profile is dormant until #1279 and its rehearsal carries the #1372 depends_on caveat — verify both when wiring the one-shot).

Files: services/canopy-reporting/tests/least_privilege_role_test.rs (new), e2e config

Grant-matrix proofs over a dedicated canopy_reporting_app connection (no pooled SET ROLE): the app runs every service flow; cannot UPDATE extracts; cannot DELETE outputs directly; cannot DDL; cannot reap a staged/published generation through reporting_janitor_reap. Guard rejection: superuser and broad-membership sessions refused outside development without the override; the override WARNs and proceeds. The full battery + e2e run reporting AS the restricted login (Step 8 makes this the devstack default). Mirror services/canopy-tanf/tests/chain_v2_substrate_test.rs:1220-1242.

Files: docs/modules/ROOT/pages/security-operations.adoc, configuration-reference.adoc, deployment-guide.adoc, coding-conventions.adoc (overlay), the parent plan, CHANGELOG.adoc

Credential-cutover + rollback runbook (the one-time transfer-as-current-owner/superuser note; the ongoing CREATEROLE non-superuser migrator; app-password provisioning). The standing convention: every future reporting migration must ALTER … OWNER TO canopy_reporting_owner + grant the app its new objects — recorded in the coding-conventions overlay, not just prose, else the first unaware migration silently regresses privileges. Configuration reference: MIGRATION_DATABASE_URL, ALLOW_BROAD_DB_ROLE. Parent Step-2b row → Done; this plan → Archive.