Stop signed-in users from squatting a workspace slug to block chat #409

Summary

The workspaces_creator_insert policy lets any signed-in user insert a workspaces row with any id and any slug. slug is unique. If a user inserts a row whose slug equals a target document's id (lowercase), every later join_workspace for that document fails on the unique key. Nobody can then join, so chat and Follow never start for that document.

• Severity: Low
• Area: Supabase
• Source: security review of 2026-10-06

Production check (2026-10-06, read-only)

• Policy workspaces_creator_insert: FOR INSERT TO authenticated WITH CHECK (created_by = auth.uid()). No other check.
• authenticated holds INSERT on every workspaces column, including id, slug and deleted_at.
• Constraints: workspaces_pkey PRIMARY KEY (id), workspaces_slug_key UNIQUE (slug).

Where

• Policy: packages/supabase/scripts/13-RLS.sql:99-101 (and its migration).
• join_workspace creates the row with slug = lower(id): packages/supabase/scripts/10-functions.sql:861-867; latest migration body packages/supabase/migrations/20260708084510_owner_document_member_roster.sql:114-200.
• Misleading comment: apps/webapp/src/hooks/useMapDocumentAndWorkspace.ts:68-71 says a PostgREST insert "always 403s". That is false with Prefer: return=minimal.

Fix plan

1. Drop workspaces_creator_insert and revoke INSERT (and UPDATE) on public.workspaces from authenticated and anon. join_workspace (DEFINER) is the only writer the app uses.
2. Confirm no caller: createWorkspace, upsertWorkspace and getWorkspaces in apps/webapp/src/api/workspaces/ have no importer. Delete them.
3. Fix the comment in useMapDocumentAndWorkspace.ts.
4. Clean up any squatted rows: find workspaces rows whose slug differs from lower(id).
5. Mirror in packages/supabase/scripts/, then run bun run --filter @docs.plus/supabase_back types.

Acceptance criteria

• A signed-in POST /rest/v1/workspaces is refused.
• Opening a new document still creates its workspace and channel through join_workspace.
• The query select id, slug from workspaces where slug <> lower(id) returns no unexpected rows.

Generated by Claude Code