ARCH-01 — PostgreSQL actor context, roles, privileges, and connection-pool safety #827

New Issue

2026-07-22T19:44:38-05:00

jcwalker3 commented

2026-07-22 19:44:38 -05:00

Candidate architecture requiring executable validation; not yet approved, production-ready, or certified.

Parents: #820, #821. Depends on: #822, #823, #824, #825, #826 (SQLite semantics to mirror).

1. Summary / objective

Bring the ARCH-01 authority/identity/evidence kernel to PostgreSQL parity with an accurate trust boundary: table-owner/service-role separation, restricted privileges, transaction-scoped actor context, and pool-reset safety.

2. Security/correctness problem

SET LOCAL app.actor is service-mediated, not independently DB-authenticated: PG cannot know app.actor matches the transport-authenticated caller. Misstating this, or letting the table owner be the connection role, silently voids the boundary.

3. Threat model / failure modes

cp_service writing base tables directly (bypassing triggers); a leaked/stale app.actor on a pooled connection; PUBLIC EXECUTE/CREATE enabling shadow objects; search_path hijack; row-security/ownership bypass; SECURITY DEFINER owned by a writable role.

4. In-scope behavior

State clearly: transport authentication + actor selection are [TRUSTED-SERVICE]; DB functions verify consistent use of service-supplied context; possession of cp_service is privileged.
Roles: cp_owner (NOLOGIN, owns all cp.*), cp_service (LOGIN, EXECUTE on SECURITY DEFINER mutation functions, no table DML), cp_migration, cp_backup, cp_readonly.
REVOKE ALL ON ALL TABLES … FROM PUBLIC, cp_service; REVOKE EXECUTE ON ALL FUNCTIONS … FROM PUBLIC; REVOKE CREATE ON SCHEMA cp FROM PUBLIC, cp_service.
Define who may SET each context GUC (app.actor, app.mode, app.service_session, app.epoch) — only inside definer functions.
Safe search_path=pg_catalog,cp pinned on every definer function; schema-qualify every object.
Prevent cp_service creating shadow objects (no CREATE).
Row-security posture + ownership-bypass behavior documented; definer functions own the boundary, not cp_service.
Prove base-table writes unavailable to cp_service.
Pool lifecycle: checkout → request binding → commit/rollback/cancel/error → DISCARD ALL (or verified app.* reset) before return.

5. Exclusions

No new authority semantics — mirrors #822–#826. No ARCH-02/04.

6. Schema/operation contracts

PG migrations creating roles/privileges + SECURITY DEFINER wrappers for every ARCH-01 mutation; triggers read current_setting('app.*', true), NULL ⇒ fail closed.

7. SQLite behavior

Reference only (parity target); no SQLite change.

8. PostgreSQL behavior

As §4; SET LOCAL transaction-scoped; NULL context aborts.

9. Concurrency / transaction requirements

Pool-return reset prevents context leakage across requests; readiness test asserts no residual app.* after DISCARD ALL.

10. Structured results

INVALID_ACTOR_CONTEXT, AUTHORIZATION_DENIED, READINESS_FAILED, plus parity results from #822–#826.

11. Enforcement classification

Role/privilege separation, definer ownership [SCHEMA]/DB-privilege; actor authenticity [TRUSTED-SERVICE].

12. Acceptance criteria

cp_service cannot INSERT/UPDATE/DELETE base tables directly (permission denied).
NULL app.actor fails closed on every mutation.
PUBLIC has no EXECUTE on mutation functions; no CREATE on schema cp.
Definer functions pin search_path and are owned by cp_owner.
Pool return clears app.*; a leaked context is not observable on the next checkout.
All #822–#826 ACs hold on PostgreSQL with backend-specific expected errors.

13. Named tests

t_pg_direct_write(−), t_pg_null_context(−), t_pg_public_execute(−), t_pg_search_path(+), t_pg_pool_leak(readiness), t_pg_parity_install/t_pg_parity_grant/t_pg_parity_merge/t_pg_parity_evidence(+).

14. Audit events / evidence

Same as mirrored issues; plus a readiness report asserting the privilege posture.

15. Dependencies / parent

Parents #820, #821; depends #822–#826.

16. Definition of done

PG migration executes; privilege posture proven; parity tests pass; readiness assertion required before enablement; bounded PR; no self-review/merge.

17. Known limitations / deferred

This is service-mediated authorization, not independent DB authentication — stated, not worked around. ARCH-02/04 PostgreSQL parity are separate.

**Candidate architecture requiring executable validation; not yet approved, production-ready, or certified.** Parents: #820, #821. Depends on: #822, #823, #824, #825, #826 (SQLite semantics to mirror). ## 1. Summary / objective Bring the ARCH-01 authority/identity/evidence kernel to PostgreSQL parity with an accurate trust boundary: table-owner/service-role separation, restricted privileges, transaction-scoped actor context, and pool-reset safety. ## 2. Security/correctness problem `SET LOCAL app.actor` is service-mediated, not independently DB-authenticated: PG cannot know `app.actor` matches the transport-authenticated caller. Misstating this, or letting the table owner be the connection role, silently voids the boundary. ## 3. Threat model / failure modes `cp_service` writing base tables directly (bypassing triggers); a leaked/stale `app.actor` on a pooled connection; PUBLIC EXECUTE/CREATE enabling shadow objects; search_path hijack; row-security/ownership bypass; SECURITY DEFINER owned by a writable role. ## 4. In-scope behavior - State clearly: transport authentication + actor selection are `[TRUSTED-SERVICE]`; DB functions verify **consistent use** of service-supplied context; possession of `cp_service` is privileged. - Roles: `cp_owner` (NOLOGIN, owns all `cp.*`), `cp_service` (LOGIN, EXECUTE on SECURITY DEFINER mutation functions, no table DML), `cp_migration`, `cp_backup`, `cp_readonly`. - `REVOKE ALL ON ALL TABLES … FROM PUBLIC, cp_service`; `REVOKE EXECUTE ON ALL FUNCTIONS … FROM PUBLIC`; `REVOKE CREATE ON SCHEMA cp FROM PUBLIC, cp_service`. - Define who may `SET` each context GUC (`app.actor`, `app.mode`, `app.service_session`, `app.epoch`) — only inside definer functions. - Safe `search_path=pg_catalog,cp` pinned on every definer function; schema-qualify every object. - Prevent `cp_service` creating shadow objects (no CREATE). - Row-security posture + ownership-bypass behavior documented; definer functions own the boundary, not `cp_service`. - Prove base-table writes unavailable to `cp_service`. - Pool lifecycle: checkout → request binding → commit/rollback/cancel/error → `DISCARD ALL` (or verified `app.*` reset) before return. ## 5. Exclusions No new authority semantics — mirrors #822–#826. No ARCH-02/04. ## 6. Schema/operation contracts PG migrations creating roles/privileges + SECURITY DEFINER wrappers for every ARCH-01 mutation; triggers read `current_setting('app.*', true)`, NULL ⇒ fail closed. ## 7. SQLite behavior Reference only (parity target); no SQLite change. ## 8. PostgreSQL behavior As §4; `SET LOCAL` transaction-scoped; NULL context aborts. ## 9. Concurrency / transaction requirements Pool-return reset prevents context leakage across requests; readiness test asserts no residual `app.*` after `DISCARD ALL`. ## 10. Structured results `INVALID_ACTOR_CONTEXT`, `AUTHORIZATION_DENIED`, `READINESS_FAILED`, plus parity results from #822–#826. ## 11. Enforcement classification Role/privilege separation, definer ownership `[SCHEMA]`/DB-privilege; actor authenticity `[TRUSTED-SERVICE]`. ## 12. Acceptance criteria 1. `cp_service` cannot INSERT/UPDATE/DELETE base tables directly (permission denied). 2. NULL `app.actor` fails closed on every mutation. 3. PUBLIC has no EXECUTE on mutation functions; no CREATE on schema `cp`. 4. Definer functions pin search_path and are owned by `cp_owner`. 5. Pool return clears `app.*`; a leaked context is not observable on the next checkout. 6. All #822–#826 ACs hold on PostgreSQL with backend-specific expected errors. ## 13. Named tests `t_pg_direct_write`(−), `t_pg_null_context`(−), `t_pg_public_execute`(−), `t_pg_search_path`(+), `t_pg_pool_leak`(readiness), `t_pg_parity_install`/`t_pg_parity_grant`/`t_pg_parity_merge`/`t_pg_parity_evidence`(+). ## 14. Audit events / evidence Same as mirrored issues; plus a readiness report asserting the privilege posture. ## 15. Dependencies / parent Parents #820, #821; depends #822–#826. ## 16. Definition of done PG migration executes; privilege posture proven; parity tests pass; readiness assertion required before enablement; bounded PR; no self-review/merge. ## 17. Known limitations / deferred This is service-mediated authorization, not independent DB authentication — stated, not worked around. ARCH-02/04 PostgreSQL parity are separate.

jcwalker3 referenced this issue

2026-07-22 19:51:24 -05:00

Architecture acceptance matrix — invariant, test, evidence, and backend traceability #837

jcwalker3 referenced this issue

2026-07-22 20:04:09 -05:00

Architecture program — role-delivery-reliability control plane #820

jcwalker3 referenced this issue

2026-07-22 20:04:46 -05:00

ARCH-01 — Authority, identity, evidence, and platform-bootstrap architecture #821