mosaicstack/stack
1
0
Fork 0
Code Issues 48 Pull Requests Actions Packages Projects Releases 15 Wiki Activity

KBN-101: separate PostgreSQL migration-owner and runtime application identities #771

New Issue
Open
opened 2026-07-15 04:42:13 +00:00 by jason.woltje · 0 comments
jason.woltje commented 2026-07-15 04:42:13 +00:00
Owner
Copy Link

Objective

Introduce a fail-closed PostgreSQL identity split so schema migrations run as a migration owner while Mosaic runtime connections use a non-owner application role with only the privileges required by the Native Kanban contract.

Blocker origin

KBN-100 issue #769 cannot certify immutable artifact/event/evidence records while the Gateway/storage bootstrap runs migrations and runtime queries through the same DATABASE_URL table-owner identity. REVOKE FROM PUBLIC and triggers do not prove the deployed application identity is non-owner.

Requirements

  • DATABASE_URL is the non-owner runtime identity.
  • A separate explicit migration credential (name to freeze before implementation, proposed DATABASE_MIGRATION_URL) is used only by migration commands/bootstrap.
  • Production/federated startup fails closed when migration is required but role separation is missing or identities collapse to the same effective owner.
  • Runtime application role has no ownership, role-management, schema-DDL, UPDATE/DELETE on immutable KBN evidence/event/checkpoint/artifact tables, or secret-bearing audit visibility beyond contract.
  • Migration owner is not accepted as runtime authority.
  • Credentials remain external/Vault-managed; no values in source, logs, errors, artifacts, or generated config.
  • PGlite/local developer compatibility is explicit and cannot be mistaken for production PostgreSQL certification.

Scope to determine before coding

DB client/migration API, Gateway/storage bootstrap, strict configuration DTOs, installer/compose/CI examples, and operator/security documentation. Deployment-specific secret wiring is separately enumerated; no manual production mutation.

Mandatory evidence

TDD RED/GREEN; owner/non-owner real-PostgreSQL integration; startup same-role/missing-role denials; migration success then runtime CRUD; immutable UPDATE/DELETE denial; credential redaction; N-1 configuration behavior; independent code/security review; Ultron; PR/main CI and issue closure.

Dependencies

  • Depends on merged #757 migration 0016 and current main.
  • Blocks #769 security approval and KBN-105.
  • Does not authorize KBN consumers, deployment, or production credential changes.
## Objective Introduce a fail-closed PostgreSQL identity split so schema migrations run as a migration owner while Mosaic runtime connections use a non-owner application role with only the privileges required by the Native Kanban contract. ## Blocker origin KBN-100 issue #769 cannot certify immutable artifact/event/evidence records while the Gateway/storage bootstrap runs migrations and runtime queries through the same `DATABASE_URL` table-owner identity. `REVOKE FROM PUBLIC` and triggers do not prove the deployed application identity is non-owner. ## Requirements - `DATABASE_URL` is the non-owner runtime identity. - A separate explicit migration credential (name to freeze before implementation, proposed `DATABASE_MIGRATION_URL`) is used only by migration commands/bootstrap. - Production/federated startup fails closed when migration is required but role separation is missing or identities collapse to the same effective owner. - Runtime application role has no ownership, role-management, schema-DDL, UPDATE/DELETE on immutable KBN evidence/event/checkpoint/artifact tables, or secret-bearing audit visibility beyond contract. - Migration owner is not accepted as runtime authority. - Credentials remain external/Vault-managed; no values in source, logs, errors, artifacts, or generated config. - PGlite/local developer compatibility is explicit and cannot be mistaken for production PostgreSQL certification. ## Scope to determine before coding DB client/migration API, Gateway/storage bootstrap, strict configuration DTOs, installer/compose/CI examples, and operator/security documentation. Deployment-specific secret wiring is separately enumerated; no manual production mutation. ## Mandatory evidence TDD RED/GREEN; owner/non-owner real-PostgreSQL integration; startup same-role/missing-role denials; migration success then runtime CRUD; immutable UPDATE/DELETE denial; credential redaction; N-1 configuration behavior; independent code/security review; Ultron; PR/main CI and issue closure. ## Dependencies - Depends on merged #757 migration `0016` and current main. - Blocks #769 security approval and KBN-105. - Does not authorize KBN consumers, deployment, or production credential changes.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
fleet-enhancement
Fleet-wide reliability/speed/cost improvements surfaced by the nightly enhance pass
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
jason.woltje
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: mosaicstack/stack#771
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?