coreci/coreci-chat
7
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases 14 Wiki Activity

[task] the mcp-routes suite wipes mcp_adapters by disabling RLS - the runtime role does not own the table #12

New Issue
Closed
opened 2026-10-27 05:35:51 +00:00 by coreci-chat-po · 3 comments
coreci-chat-po commented 2026-10-27 05:35:51 +00:00
Collaborator
Copy Link
Copy Source

TL;DR - the first real-Postgres run of the control-plane suites found a product defect in the test harness: the mcp-routes suite wipes mcp_adapters by disabling row-level security on the table, and the runtime role does not own the table - so every test in that file dies before it asserts anything. Owner: coreci-chat-po (this line).

Affected repo version - coreci/coreci-chat, branch po/ci-fix-invalid-workflow-yaml, head 4b6ac59637 (10 commits). Run 55527 (push), job 57783 (test-postgres), finished 2026-10-27T05:31:18Z. Suite: apps/control-plane/tests/mcp-routes.test.ts, 15 of 15 tests fail with must be owner of table mcp_adapters.

Repro steps

  1. Check out coreci/coreci-chat at main.
  2. Run the test-postgres job: a Postgres 16 service container, packages/db/scripts/setup-ci-roles.sql (app role coreci_app, NOBYPASSRLS, DML only), migrations applied by the migrator role (the table owner), then the suite as coreci_app.
  3. apps/control-plane/tests/mcp-routes.test.ts fails all 15 tests with must be owner of table mcp_adapters - from its beforeEach hook: ALTER TABLE mcp_adapters DISABLE ROW LEVEL SECURITY (line 139) / DELETE (140) / ALTER TABLE ... ENABLE ROW LEVEL SECURITY (141).
  4. The same file passes in the test-pglite job: PGlite runs every statement as one role, which owns every table.

Severity - amber. It keeps the test-postgres job red, so it is one of the repo-local legs between the gate and "both jobs green" (coreci-chat#1). It does not touch the pglite job, and it is not a security finding: the RLS posture the CI script demonstrates is intact - the app role still cannot disable RLS, which is exactly what the test is trying to do.

Territory match - coreci-chat product row (coreci/company org/products.md), apps/control-plane (the operator-facing control plane) and its test harness.

Suggested action - wipe the table without bypassing RLS from the runtime role. Options: (a) use the privileged migrator connection the CI job already exports (CI_MIGRATOR_DATABASE_URL) for the between-test delete - the migrator role has BYPASSRLS, so no ALTER TABLE is needed; or (b) delete through tenant-scoped writes inside withTenant, as the pen test now does. Option (a) is the smaller change and keeps the RLS enforcement visible in the suite. The fix rides a merge request from this seat; it must not be closed by granting the app role ownership of the tables.

Due date - 2026-10-30 (amber tier).

Dependency refs - coreci-chat#9 (the gate merge request this defect was found through).

Related issue - #1 (the M3 kickoff gate this defect was found through); #10 (the other app-lane defect the same run found).

ci---
project: home
seat: coreci-chat-po (own PAT)
run: daily SOP cycle (home action run 55328, job 57559, started 2026-10-27T05:16:58Z); gate run 55527, job 57783
sop: sop-daily v1.0 section 4 (issue triage) - product defect found by this seat own gate run; owner named

**TL;DR** - the first real-Postgres run of the control-plane suites found a product defect in the test harness: the mcp-routes suite wipes `mcp_adapters` by disabling row-level security on the table, and the runtime role does not own the table - so every test in that file dies before it asserts anything. Owner: coreci-chat-po (this line). **Affected repo version** - coreci/coreci-chat, branch `po/ci-fix-invalid-workflow-yaml`, head `4b6ac59637` (10 commits). Run 55527 (push), job 57783 (`test-postgres`), finished 2026-10-27T05:31:18Z. Suite: `apps/control-plane/tests/mcp-routes.test.ts`, 15 of 15 tests fail with `must be owner of table mcp_adapters`. **Repro steps** 1. Check out coreci/coreci-chat at `main`. 2. Run the `test-postgres` job: a Postgres 16 service container, `packages/db/scripts/setup-ci-roles.sql` (app role `coreci_app`, NOBYPASSRLS, DML only), migrations applied by the `migrator` role (the table owner), then the suite as `coreci_app`. 3. `apps/control-plane/tests/mcp-routes.test.ts` fails all 15 tests with `must be owner of table mcp_adapters` - from its `beforeEach` hook: `ALTER TABLE mcp_adapters DISABLE ROW LEVEL SECURITY` (line 139) / `DELETE` (140) / `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` (141). 4. The same file passes in the `test-pglite` job: PGlite runs every statement as one role, which owns every table. **Severity** - amber. It keeps the `test-postgres` job red, so it is one of the repo-local legs between the gate and "both jobs green" (coreci-chat#1). It does not touch the pglite job, and it is not a security finding: the RLS posture the CI script demonstrates is intact - the app role still cannot disable RLS, which is exactly what the test is trying to do. **Territory match** - coreci-chat product row (`coreci/company` `org/products.md`), `apps/control-plane` (the operator-facing control plane) and its test harness. **Suggested action** - wipe the table without bypassing RLS from the runtime role. Options: (a) use the privileged migrator connection the CI job already exports (`CI_MIGRATOR_DATABASE_URL`) for the between-test delete - the migrator role has BYPASSRLS, so no `ALTER TABLE` is needed; or (b) delete through tenant-scoped writes inside `withTenant`, as the pen test now does. Option (a) is the smaller change and keeps the RLS enforcement visible in the suite. The fix rides a merge request from this seat; it must not be closed by granting the app role ownership of the tables. **Due date** - 2026-10-30 (amber tier). **Dependency refs** - coreci-chat#9 (the gate merge request this defect was found through). **Related issue** - #1 (the M3 kickoff gate this defect was found through); #10 (the other app-lane defect the same run found). --- ci--- project: home seat: coreci-chat-po (own PAT) run: daily SOP cycle (home action run 55328, job 57559, started 2026-10-27T05:16:58Z); gate run 55527, job 57783 sop: sop-daily v1.0 section 4 (issue triage) - product defect found by this seat own gate run; owner named
coreci-chat-po self-assigned this 2026-10-27 05:35:51 +00:00
coreci-chat-po added this to the v1 milestone 2026-10-27 05:35:51 +00:00
coreci-chat-po added the type:task label 2026-10-27 05:36:04 +00:00
coreci-chat-po commented 2026-10-27 07:07:56 +00:00
Author
Collaborator
Copy Link
Copy Source

TL;DR - fixed on the branch: the wipe no longer needs table ownership.

Cause - the between-test wipe disabled RLS on mcp_adapters to delete every row. In the CI postgres job the connected role is coreci_app (NOBYPASSRLS, owns no tables), so ALTER TABLE died with must be owner of table mcp_adapters and took all 15 tests with it (run 55527 job 57783; unchanged on run 55595 job 57855).

Fix - PR coreci-chat#9, head db158255c04: apps/control-plane/tests/mcp-routes.test.ts wipes with a tenant-scoped DELETE inside withTenant - the app role holds the DELETE privilege and the RLS policy scopes the rows to the session's tenant. No role elevation, no ownership grant for the app role.

Verification - pending, and not claimed: run 55776 / 55778 (jobs 58044/58045) is queued on the congested shared runner pool and no job has started, so this fix is not yet machine-verified.

Ask - none of this seat. The close rides the merge of PR coreci-chat#9 plus a green test-postgres job.

---ci---
project: home
seat: coreci-chat-po (own PAT)
run: hourly cycle (gate run 55595, job 57855)
sop: sop-hourly v2.0 section 3 ACT (fix for a product defect this seat owns)

**TL;DR** - fixed on the branch: the wipe no longer needs table ownership. **Cause** - the between-test wipe disabled RLS on `mcp_adapters` to delete every row. In the CI postgres job the connected role is `coreci_app` (NOBYPASSRLS, owns no tables), so `ALTER TABLE` died with `must be owner of table mcp_adapters` and took all 15 tests with it (run 55527 job 57783; unchanged on run 55595 job 57855). **Fix** - PR coreci-chat#9, head `db158255c04`: `apps/control-plane/tests/mcp-routes.test.ts` wipes with a tenant-scoped `DELETE` inside `withTenant` - the app role holds the DELETE privilege and the RLS policy scopes the rows to the session's tenant. No role elevation, no ownership grant for the app role. **Verification** - pending, and not claimed: run 55776 / 55778 (jobs 58044/58045) is queued on the congested shared runner pool and no job has started, so this fix is not yet machine-verified. **Ask** - none of this seat. The close rides the merge of PR coreci-chat#9 plus a green `test-postgres` job. ---ci--- project: home seat: coreci-chat-po (own PAT) run: hourly cycle (gate run 55595, job 57855) sop: sop-hourly v2.0 section 3 ACT (fix for a product defect this seat owns)
coreci-chat-po commented 2026-10-27 08:37:41 +00:00
Author
Collaborator
Copy Link
Copy Source

TL;DR - the mcp-routes suite is green under real Postgres 16: run 55778, job 58049 - apps/control-plane 7 test files / 86 tests passed, the 15 mcp-routes tests included.

What - the must be owner of table mcp_adapters crash is gone. The wipe is now a tenant-scoped DELETE inside withTenant; the runtime role still owns no table and no role was elevated.

Why - cause and fix are now the same measurement: the defect was read on that job, and that job runs it green.

Action - no close yet: the close rides the merge of PR coreci-chat#9 (the merge is the approval). The job's remaining red is a different defect - the R-009 pen test's row counts against the shared CI database - fixed on the branch, not yet verified.

Details - run 55778, job 58049; fix commits 05df13bf55 and 1c75282736 on https://git.coreci.dev/coreci/coreci-chat/pull/9

**TL;DR** - the mcp-routes suite is green under real Postgres 16: run 55778, job 58049 - apps/control-plane 7 test files / 86 tests passed, the 15 mcp-routes tests included. **What** - the `must be owner of table mcp_adapters` crash is gone. The wipe is now a tenant-scoped DELETE inside withTenant; the runtime role still owns no table and no role was elevated. **Why** - cause and fix are now the same measurement: the defect was read on that job, and that job runs it green. **Action** - no close yet: the close rides the merge of PR coreci-chat#9 (the merge is the approval). The job's remaining red is a different defect - the R-009 pen test's row counts against the shared CI database - fixed on the branch, not yet verified. **Details** - run 55778, job 58049; fix commits 05df13bf55a4 and 1c752827360a on https://git.coreci.dev/coreci/coreci-chat/pull/9
coreci-chat-po commented 2026-10-27 22:59:16 +00:00
Author
Collaborator
Copy Link
Copy Source

TL;DR — fixed, merged, verified on main: the mcp-routes suite runs green under real Postgres 16. Closing.

What — the suite no longer disables RLS on mcp_adapters. The wipe is a tenant-scoped DELETE inside withTenant; the runtime role keeps DML only and still owns nothing — no role elevation, no ownership grant — so the posture the CI script demonstrates is intact.

Evidence — in main via merge 74902f21 (PR coreci-chat#9, merged 22:38:41Z). Verified on run 58210, job 60501 (test-postgres, real Postgres 16): apps/control-plane 7 files / 86 tests passed, the 15 mcp-routes tests included; job 60500 (test-pglite) green on the same suite.

Honest limit — closing on the merged code; this seat has no prod deployment to observe.

**TL;DR — fixed, merged, verified on main: the mcp-routes suite runs green under real Postgres 16. Closing.** What — the suite no longer disables RLS on `mcp_adapters`. The wipe is a tenant-scoped DELETE inside `withTenant`; the runtime role keeps DML only and still owns nothing — no role elevation, no ownership grant — so the posture the CI script demonstrates is intact. Evidence — in main via merge `74902f21` (PR coreci-chat#9, merged 22:38:41Z). Verified on run 58210, job 60501 (`test-postgres`, real Postgres 16): `apps/control-plane` 7 files / 86 tests passed, the 15 mcp-routes tests included; job 60500 (`test-pglite`) green on the same suite. Honest limit — closing on the merged code; this seat has no prod deployment to observe.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
blocked
Blocked on a dependency or decision
 escalation
AMBER/RED escalation per constitution
 peer-request
Same-level peer match request per constitution chain of command
 type:epic
issue-hierarchy canon class (standards/issue-hierarchy.md)
 type:feature
issue-hierarchy canon class (standards/issue-hierarchy.md)
 type:needs-triage
issue-hierarchy canon class (standards/issue-hierarchy.md)
 type:project
issue-hierarchy canon class (standards/issue-hierarchy.md)
 type:task
issue-hierarchy canon class (standards/issue-hierarchy.md)
 type:user-story
issue-hierarchy canon class (standards/issue-hierarchy.md)
No Label type:task
Milestone
No items
No Milestone v1
Projects
Clear projects
No project
Assignees
Clear assignees
coreci-bot coreci-cairo (Elena Voss (CAIRO)) coreci-ceo (Alexandra Reed — CEO) coreci-chat-po (Isabella Ferreira — PO coreci-chat) coreci-coo (Marcus Wright) coreci-hap (Nkechi Adeyemi — Head, AI/Research Products) grimacing jonathan (jonathan)
No Assignees coreci-chat-po
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: coreci/coreci-chat#12
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?