Paliadin: visibility-gate returns 0 rows — Supabase-MCP runs as supabase_admin without JWT #72

New Issue

mAi · 2026-05-22T12:59:03Z

mAi commented

2026-05-22 12:59:03 +00:00

Symptom

Paliadin (in-app AI assistant) sees 0 projects, 0 deadlines, 0 appointments for every visibility-gated query, even when m (global_role = 'global_admin') is the session user. Concrete incident from 2026-05-22:

12:40 turn: "Welche Fristen sind diese Woche?" → Paliadin answered "Keine Fristen" with a confident show-your-work block.
14:54 turn: same session, dashboard snapshot attempt — same 0 rows everywhere.
Truth: there is 1 deadline due today (Replik in C-UPC-0001, Siemens ./. Huawei EP3456789, deadline id fb838b5d-d91a-4b30-bbdc-9b20cd25e2a6).
Raw counts in DB at that moment: 12 projects, 30 deadlines, 5 appointments, 47 users.

This is not "empty week" — this is a structural visibility outage.

Root cause

paliad.can_see_project(_project_id) evaluates auth.uid() — the user from request.jwt.claims.sub. The Supabase-MCP that Paliadin uses to reach the DB connects as supabase_admin with no JWT context (request.jwt.claims = NULL). Therefore auth.uid() = NULL, all three EXISTS branches in can_see_project fail, and the function returns false for every project.

Reproducer (any paliadin tmux pane):

SELECT current_user,
       current_setting('request.jwt.claims', true) AS jwt_claims,
       (SELECT COUNT(*) FROM paliad.projects)   AS projects_raw,
       (SELECT COUNT(*) FROM paliad.deadlines)  AS deadlines_raw;
-- → db_user=supabase_admin, jwt_claims=NULL, projects_raw=12, deadlines_raw=30

SELECT COUNT(*) FROM paliad.deadlines d
 WHERE paliad.can_see_project(d.project_id);
-- → 0

The empty-result protocol in the Paliadin skill catches the typical filter bugs but cannot catch this one — the broader sanity-check query still goes through can_see_project(), which is exactly the function that's silently returning false for everything.

Workaround (used live in the 14:54 turn)

SET LOCAL request.jwt.claim.sub = '<user uuid>';
-- … then run the visibility-gated query

m's UUID is 1afbfbe9-45f1-42b2-a9d7-c785d8d7785f. Because m is global_admin, the first EXISTS branch in can_see_project succeeds and all data becomes visible. This is a hot patch — not a real fix — and it only works for users whose UUID Paliadin has somehow learned out-of-band.

Proposed fixes (ascending order of cleanness)

Skill-side pflaster (today, no infra change): the [ctx …] block from the frontend already carries session_id. Extend it with paliad_user_id=<uuid> (server-side enriched from the authenticated session). The Paliadin skill prefixes every visibility-gated query with SET LOCAL request.jwt.claim.sub = '<that uuid>'. The frontend never trusts the value — it comes from the Go server. Pros: zero infra work. Cons: auth is now in the prompt context; non-admin users still have to traverse the real visibility logic via this synthetic auth.uid(), which works as long as can_see_project only ever reads auth.uid() (it does today).
DB-side audit-friendly path (cleanest if audit is a goal): a paliadin.fetch_deadlines(_user_id uuid, …) SECURITY DEFINER function per recipe. It internally checks visibility for _user_id and writes to a paliadin_audit_log row per call. The MCP loses arbitrary SQL access; recipes become callable RPCs. Pros: every Paliadin read is auditable; no JWT plumbing. Cons: locks Paliadin into a finite set of recipes — concept-Q&A and ad-hoc analyses get harder.
MCP-side proper JWT (structurally correct): the paliadin pane spawn injects a per-session JWT (signed with the same key as the Paliad backend) into the Supabase-MCP env. The MCP starts with Authorization: Bearer <jwt>, the JWT carries sub = <user uuid>, and auth.uid() resolves correctly. Pros: the visibility gate works the way it was designed, no per-query plumbing. Cons: non-trivial pane-spawn change in m/mAi#207 (aichat backend on mRiver — same backend that runs PALIADIN_BACKEND=aichat per t-paliad-194); needs a JWT-issuer route in paliad to mint a short-lived per-session token.

Recommend (1) now as a fast unblock, plus (3) as the proper fix once aichat owns the pane spawn end-to-end.

Impact on past data answers

Every datenbezogene Paliadin-Antwort in the current production deploy that used can_see_project() is unreliable — until a fix lands, m must assume Paliadin's "keine Daten" answers are false negatives. Concept answers (Kostenrechner tables, UPC fee schedules, glossary, navigation) are unaffected.

Test plan

After fix: run "Welche Fristen sind diese Woche?" → must surface the Replik (or whichever deadline is current at the time).
Smoke-test the empty-result protocol: pick a project m can see and one he cannot (impersonate a non-admin user); both must return the correct row counts.
Audit log (if option 2): every Paliadin tool call appears with requester=paliadin, user_id, turn_id, classifier_tag.

## Symptom Paliadin (in-app AI assistant) sees **0 projects, 0 deadlines, 0 appointments** for every visibility-gated query, even when m (`global_role = 'global_admin'`) is the session user. Concrete incident from 2026-05-22: - 12:40 turn: "Welche Fristen sind diese Woche?" → Paliadin answered **"Keine Fristen"** with a confident show-your-work block. - 14:54 turn: same session, dashboard snapshot attempt — same 0 rows everywhere. - Truth: there is 1 deadline due **today** (Replik in C-UPC-0001, Siemens ./. Huawei EP3456789, deadline id `fb838b5d-d91a-4b30-bbdc-9b20cd25e2a6`). - Raw counts in DB at that moment: 12 projects, 30 deadlines, 5 appointments, 47 users. This is not "empty week" — this is a structural visibility outage. ## Root cause `paliad.can_see_project(_project_id)` evaluates `auth.uid()` — the user from `request.jwt.claims.sub`. The Supabase-MCP that Paliadin uses to reach the DB connects as `supabase_admin` with **no JWT context** (`request.jwt.claims = NULL`). Therefore `auth.uid() = NULL`, all three EXISTS branches in `can_see_project` fail, and the function returns `false` for every project. Reproducer (any paliadin tmux pane): ```sql SELECT current_user, current_setting('request.jwt.claims', true) AS jwt_claims, (SELECT COUNT(*) FROM paliad.projects) AS projects_raw, (SELECT COUNT(*) FROM paliad.deadlines) AS deadlines_raw; -- → db_user=supabase_admin, jwt_claims=NULL, projects_raw=12, deadlines_raw=30 SELECT COUNT(*) FROM paliad.deadlines d WHERE paliad.can_see_project(d.project_id); -- → 0 ``` The empty-result protocol in the Paliadin skill catches the typical filter bugs but **cannot** catch this one — the broader sanity-check query still goes through `can_see_project()`, which is exactly the function that's silently returning false for everything. ## Workaround (used live in the 14:54 turn) ```sql SET LOCAL request.jwt.claim.sub = '<user uuid>'; -- … then run the visibility-gated query ``` m's UUID is `1afbfbe9-45f1-42b2-a9d7-c785d8d7785f`. Because m is `global_admin`, the first EXISTS branch in `can_see_project` succeeds and all data becomes visible. This is a hot patch — not a real fix — and it only works for users whose UUID Paliadin has somehow learned out-of-band. ## Proposed fixes (ascending order of cleanness) 1. **Skill-side pflaster** *(today, no infra change)*: the `[ctx …]` block from the frontend already carries `session_id`. Extend it with `paliad_user_id=<uuid>` (server-side enriched from the authenticated session). The Paliadin skill prefixes every visibility-gated query with `SET LOCAL request.jwt.claim.sub = '<that uuid>'`. The frontend never trusts the value — it comes from the Go server. **Pros:** zero infra work. **Cons:** auth is now in the prompt context; non-admin users still have to traverse the real visibility logic via this synthetic `auth.uid()`, which works as long as `can_see_project` only ever reads `auth.uid()` (it does today). 2. **DB-side audit-friendly path** *(cleanest if audit is a goal)*: a `paliadin.fetch_deadlines(_user_id uuid, …)` SECURITY DEFINER function per recipe. It internally checks visibility for `_user_id` and writes to a `paliadin_audit_log` row per call. The MCP loses arbitrary SQL access; recipes become callable RPCs. **Pros:** every Paliadin read is auditable; no JWT plumbing. **Cons:** locks Paliadin into a finite set of recipes — concept-Q&A and ad-hoc analyses get harder. 3. **MCP-side proper JWT** *(structurally correct)*: the paliadin pane spawn injects a per-session JWT (signed with the same key as the Paliad backend) into the Supabase-MCP env. The MCP starts with `Authorization: Bearer <jwt>`, the JWT carries `sub = <user uuid>`, and `auth.uid()` resolves correctly. **Pros:** the visibility gate works the way it was designed, no per-query plumbing. **Cons:** non-trivial pane-spawn change in `m/mAi#207` (aichat backend on mRiver — same backend that runs `PALIADIN_BACKEND=aichat` per t-paliad-194); needs a JWT-issuer route in paliad to mint a short-lived per-session token. Recommend (1) **now** as a fast unblock, plus (3) as the proper fix once aichat owns the pane spawn end-to-end. ## Impact on past data answers Every datenbezogene Paliadin-Antwort in the current production deploy that used `can_see_project()` is unreliable — until a fix lands, m must assume Paliadin's "keine Daten" answers are false negatives. Concept answers (Kostenrechner tables, UPC fee schedules, glossary, navigation) are unaffected. ## Test plan - [ ] After fix: run "Welche Fristen sind diese Woche?" → must surface the Replik (or whichever deadline is current at the time). - [ ] Smoke-test the empty-result protocol: pick a project m can see and one he cannot (impersonate a non-admin user); both must return the correct row counts. - [ ] Audit log (if option 2): every Paliadin tool call appears with `requester=paliadin`, `user_id`, turn_id, classifier_tag.

mAi referenced this issue

2026-05-22 13:03:24 +00:00

Paliadin: replace tmux-relay PoC with API-mediated tool layer before opening to more users #73