[security] Realtime + Storage anon-key surface bypasses route-code authorization (RLS / Realtime-Auth gap)

[Jose-Gael-Cruz-Lopez]

Summary

The backend enforces ownership entirely in route code (require_self + user_id-scoped queries) using the service-role key, so PostgreSQL RLS is disabled project-wide (docs/architecture.md, docs/decisions/0017). But the frontend talks to Supabase directly with the public anon key for realtime and storage — paths that never touch route code and therefore have no authorization. The room_id/message filters used client-side are conveniences, not security boundaries.

Discovered while fixing #124 (realtime chat rendered ciphertext). #124 fixes the display; this issue is the authorization model behind it.

Severity: metadata-only for realtime — room_messages.text / room_reactions content the attacker would receive is column-encrypted (ciphertext), so message bodies stay protected. What leaks is metadata (who posted/reacted, when, in which room, reply/edit/delete activity, presence/typing) for arbitrary rooms the user isn't a member of. Storage is a separate anon-write / public-read concern.

Full anon-key surface audit (every route-code-bypassing path)

Client obtained via getSupabase() (NEXT_PUBLIC_SUPABASE_ANON_KEY).

Realtime (frontend/src/components/screens/Social.tsx):

1. postgres_changes INSERT/UPDATE on room_messages, filtered client-side by room_id=eq.<roomId> — the filter is attacker-controlled; any room's message stream is subscribable.
2. postgres_changes INSERT/DELETE on room_reactions — no room filter at all (only a client-side "is this message loaded" check), so reaction events for all rooms stream to any subscriber. Broadest exposure.
3. Presence channel presence:<roomId> (typing indicators) — also unauthenticated.

Storage (anon-key upload + public getPublicUrl):
4. Social.tsx → chat-images bucket (chat attachments).
5. ReportIssueFlow.tsx → issue-report screenshots bucket.
6. Admin.tsx → cosmetics bucket.

The frontend-audit docs already flag "Verify bucket RLS policy" / "Bucket permissions must allow anon writes." Risk: anon upload abuse + public read of all objects.

No direct anon-key table queries exist — all DB reads/writes go through FastAPI (route-code enforced). The gap is bounded to realtime + storage above.

Membership model

room_members(room_id, user_id) exists (supabase_schema.sql:248) and is the natural scope for policies.

Proposed fix (NOT yet implemented — for architectural review)

1. Enable RLS on room_messages, room_reactions, room_members, and presence-relevant tables, scoped to room membership (EXISTS (SELECT 1 FROM room_members m WHERE m.room_id = <row>.room_id AND m.user_id = auth.uid())). The service-role backend bypasses RLS, so route code is unaffected; only the anon/realtime path is constrained.
2. Realtime Authorization (private channels) instead of public broadcast: gate realtime.messages by the same membership policy so the server only streams events for rooms the user belongs to.
3. Authenticate the realtime client with the user's JWT, not the anon key. This is the crux/decision: the app uses its own HMAC session, not Supabase Auth. Options:
   • (a) Mint a Supabase-compatible JWT per user (signed with the Supabase JWT secret, carrying the user id as sub) at login; hand it to the realtime client and key RLS on auth.uid(). Smallest bridge.
   • (b) Adopt Supabase Auth wholesale (larger change).
   • (c) Proxy realtime through the backend (e.g. backend-driven SSE) and drop the direct anon-key client entirely — eliminates the surface but is a chat rebuild.
4. Storage bucket policies: require authenticated uploads (kill anon write abuse); scope reads (chat-images likely needs public read for <img>; issue/cosmetic reconsidered).

Decision needed before building

• Which realtime-auth bridge: mint Supabase JWTs (a), adopt Supabase Auth (b), or backend-proxy realtime (c)?
• Storage: per-bucket read/write policy intent.

Do not implement until the approach is chosen.

[Jose-Gael-Cruz-Lopez]

Live DB findings (Sapling prod jxqcmjqtjlpuxfrxmrdv, read-only) — severity is worse than "metadata-only"

Ran read-only checks against the real Sapling project. The realtime metadata leak is real, but it's a symptom of a project-wide RLS + grants misconfiguration that is potentially critical, not metadata-only.

Confirmed from DB state (authorized metadata queries)

• RLS is disabled on 38 of 40 public tables (relrowsecurity=false). Only 2 tables have it on.
• The anon role has full DML (SELECT/INSERT/UPDATE/DELETE) on every sensitive table checked: users, user_roles, documents, messages, sessions, assignments, notes, graph_nodes, quiz_attempts, room_messages, room_reactions.
• The anon key is public (NEXT_PUBLIC_SUPABASE_ANON_KEY, shipped in the frontend bundle).

Implication: if Supabase's Data API (PostgREST) exposes the public schema to anon (the default), then anyone with the public anon key can read and write the entire database directly via …supabase.co/rest/v1/…, bypassing the FastAPI backend and all require_self checks — including self-assigning admin via user_roles, deleting/forging rows, and reading every non-encrypted column. Column encryption only protects the encrypted columns; ids, flags, roles, scores, graph, etc. are plaintext.

Unconfirmed link

Whether the Data API/PostgREST actually serves the anon key (vs. being disabled at the project level). A read-only zero-row probe was attempted to confirm and was correctly blocked as beyond the approved checks. To confirm: authorize the probe, or check Dashboard → Settings → API → Data API "Exposed schemas". Default Supabase = exposed.

Realtime (the original #124/#231 angle)

• room_messages: RLS off + in the supabase_realtime publication + anon has SELECT → an anon subscriber receives all room_messages change events for any room (the client-side room_id filter is not a boundary). Message text is column-encrypted (ciphertext); metadata (author, timing, room, edits) is exposed.
• room_reactions: NOT in the realtime publication → the frontend's reaction realtime subscription is inert (no events fire). So no reaction leak — and that feature's realtime is effectively dead.
• room_members / rooms: not published.

Storage (live buckets)

Only three buckets exist: application_resumes (public — résumé PII), avatars (public, intended), issues-media-files (public). The code's chat-images and cosmetic-assets buckets do not exist (those upload paths are dead). Policies on storage.objects: a public SELECT on issues-media-files, and a public INSERT with no restriction → anyone can upload to any bucket, unauthenticated, unbounded (no size limit on application_resumes/issues-media-files).

Bottom line for the fix

The dominant, urgent fix is enable RLS / revoke anon grants across the public schema (the backend uses the service-role key, which bypasses RLS, so this won't break route code). That single lockdown closes the PostgREST master-key exposure for all 40 tables at once. The realtime a/b/c choice is secondary — it only determines how chat realtime keeps working after RLS is on.