Make a Private document's chat readable and writable only by its owner #396

Summary

A Private document hides its content and its Pad title, but not its chat. Supabase does not know which documents are Private, so every chat policy treats every channel as public. The anon key reads the chat, any signed-in account can post into it, and former members keep getting notifications. join_workspace also lets any signed-in user read the member list.

• Severity: High
• Area: Supabase RLS, storage policies, notification triggers, hocuspocus Access mutation
• Source: security review of 2026-10-06, finding H1

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

• All 1022 channels in production have type = 'PUBLIC'.
• anon holds SELECT on public.messages. The anon policy messages_public_anon_select allows any row whose channel is PUBLIC.
• messages_visible_select (authenticated) and messages_self_insert both rely on internal.can_read_channel, which returns true for every PUBLIC channel.
• 373 of 845 live workspaces are visible to anon through workspaces_public_anon_select.
• The Private flag lives in the hocuspocus Prisma database, not in Supabase. So no policy can see it today.

Where

• internal.can_read_channel: packages/supabase/scripts/10-0-func-helpers.sql:143-162.
• Authenticated chat policies in packages/supabase/scripts/13-RLS.sql: channels_visible_select :121-123, channel_members_join_insert :154-165, messages_visible_select and messages_self_insert :191-200, pinned_messages_select :214-216, channel_message_counts_select :225-227.
• Anon policies: packages/supabase/scripts/13-RLS.sql:278-338. They expose every message, media path and live message (Realtime). channels.last_message_preview also carries message text.
• Chat media read policies: packages/supabase/scripts/12-buckets.sql:146-178.
• chatroom_read_topic_access: packages/supabase/scripts/10-functions.sql:1336-1351.
• join_workspace (no access check): packages/supabase/scripts/10-functions.sql:841-927.
• get_document_members: packages/supabase/scripts/10-8-func-workspace_members.sql:213-249.
• After a Private flip, past members still get bell rows, push (with message_preview on the lock screen) and email. Notification triggers pick receivers from channel_members or the parent author: packages/supabase/scripts/10-func-notifications.sql:9 (mention), :96 (reply), :183 (everyone), :265 (regular), :357 (reaction). The unread auto-enrol is at :464.
• The Access mutation writes only Prisma:
  • updateDocument in apps/hocuspocus.server/src/api/services/documents.service.ts:600-641.
  • updateDocumentFlags in apps/hocuspocus.server/src/api/services/adminStats.service.ts:243-288.
• The MCP server already refuses chat on a Private document (apps/hocuspocus.server/API.md:1066). The webapp path and the MCP path disagree.

Root cause

isPrivate and ownerId live only in Prisma. They gate the REST slug read and the WebSocket room. Supabase never receives them.

Fix plan

One design covers every path. Reads stay owner-only for a Private document, which matches the WebSocket rule.

1. Mirror table. Add public.document_access (document_id varchar(36) primary key, is_private boolean not null, owner_id uuid).
   • Enable RLS with no policy, and revoke all from anon and authenticated. Only the service role writes it.
   • Do not add columns to public.workspaces. join_workspace creates that row lazily and is its only writer, so a flip can happen before the row exists.
   • A missing row means public. document_id is the documentId verbatim, the same value as channels.workspace_id.
2. One helper. Add internal.can_open_document(p_document_id varchar, p_user_id uuid) returns boolean, security definer, stable. It returns true when no row exists, the row is not Private, or owner_id = p_user_id.
   • Grant EXECUTE to anon and authenticated. Also grant USAGE on schema internal to anon. Today anon lacks it (13-RLS.sql:17-19).
3. Gate reads and writes on the helper. AND the helper into each existing check, so channel membership alone never opens a Private document. Join channels to get workspace_id where a policy only has channel_id.
   • internal.can_read_channel, with auth.uid(). This also covers messages_self_insert, pinned, counts, get_user_bookmarks and the INVOKER chat RPCs.
   • channels_visible_select, with auth.uid().
   • The anon policies on channels, messages, channel_message_counts, pinned_messages and workspaces, with a null user.
   • channel_members_join_insert, the three chat media read policies, and chatroom_read_topic_access.
   • join_workspace and get_document_members: refuse a non-owner of a Private document.
   • Any other security definer function that returns message text without calling internal.can_read_channel. List them with the query in Verify.
4. Gate notifications. In the five triggers above, skip a receiver when internal.can_open_document(c.workspace_id, <receiver id>) is false. Apply the same check to the auto-enrol in increment_unread_count_on_new_message. Push and email are queued from these rows, so they follow.
5. Write the mirror from hocuspocus. Add one helper in apps/hocuspocus.server/src/lib/ beside accessRealtime.ts. It upserts the row through getServiceRoleClient(), as lib/padTitleChange.ts does.
   • Call it from updateDocument and updateDocumentFlags whenever the request carries isPrivate, even when the value did not change. A retry then repairs a failed write.
   • Write the stored values (isPrivate and ownerId from the row Prisma returns), not the request value. updateDocument ignores an isPrivate change on an open document.
   • Await it before the response. If it fails, return a 5xx so the owner can retry. Do not skip the wait: a lost write leaves the chat public.
6. Backfill. Add a one-off Bun script, apps/hocuspocus.server/scripts/backfill-document-access.ts. It reads every documentMetadata row with isPrivate = true and upserts its mirror row.

Repo rules that apply:

• Put the SQL in one new migration, and mirror it in packages/supabase/scripts/ (10-0-func-helpers.sql, 13-RLS.sql, 12-buckets.sql, 10-functions.sql, 10-8-func-workspace_members.sql, 10-func-notifications.sql).
• Seed order matters. Create the table in a new script numbered before 10-0-func-helpers.sql, for example 03-2-document_access.sql.
• The notification triggers live in scripts/ only, so recreate them inside the migration too (packages/supabase/CLAUDE.md). Keep every trigger function security definer.
• Regenerate the seed with bun run --filter @docs.plus/supabase_back seed. Never hand-edit packages/supabase/seed.sql.
• Run bun run --filter @docs.plus/supabase_back types and commit the regenerated apps/webapp/src/types/supabase.ts.

Out of scope

• Signed media URLs minted before the fix stay valid until they expire. Say so in the PR.
• Push, email and digest entries for notifications created before a flip still send once.
• The heading chat channel key (Scope heading chat channels to their own document #402). A duplicate of a Private document can open the source's chat through that bug.
• Editor media after Private (Stop serving editor media after a document moves to Trash #408) and deleted message text (Clear the text of a chat message when it is deleted #435).

Acceptance criteria

• With the anon key, a read of public.messages or public.channels for a Private document returns no rows.
• A signed-in non-owner gets 42501 on an insert into a Private document's chat.
• join_workspace and get_document_members refuse a non-owner of a Private document.
• A new message in a Private document creates no notification, push or email for a non-owner.
• The owner's chat still works: read, send, realtime, media, unread counts and notifications.
• Public documents behave as before, including anon read of their chat.
• After the backfill, every Private document in Prisma has a mirror row with is_private = true.
• Making a document public again restores chat for others.

Verify

1. Apply the migration locally: docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres < packages/supabase/migrations/<new file>.sql.
2. As user A, make a document Private in the local webapp. Confirm its mirror row exists.
3. Check each criterion in psql, inside begin; ... rollback;:
   • As anon: set local role anon;
   • As user B: set local role authenticated; then select set_config('request.jwt.claims', '{"sub":"<user B id>","role":"authenticated"}', true);
4. List DEFINER readers that skip the gate:
   select p.oid::regprocedure from pg_proc p where p.prosecdef and p.pronamespace = 'public'::regnamespace and p.prosrc ilike '%messages%' and p.prosrc not ilike '%can_read_channel%';
5. Test the mirror write: cd apps/hocuspocus.server && bun test tests/integration/documents.test.ts. Add a case for a flip that writes the row, if the suite has a Supabase seam.
6. The chatroom Cypress suite intercepts Supabase HTTP and cannot test policies. Check the owner path by hand in the local webapp.

Related

• Scope heading chat channels to their own document #402 heading chat binds to a global channel id
• Stop serving editor media after a document moves to Trash #408 editor media stays readable after Private
• Clear the text of a chat message when it is deleted #435 deleted chat text stays readable
• Stop bookmarks and replies from copying a message the user cannot read #410 get_user_bookmarks now filters on internal.can_read_channel, so this gate covers it
• Let non-members read only chat media that a live message uses #432 rewrites the two non-member chat media read policies. The second change to land must keep both conditions.
• Stop clients from writing server-owned columns on chat messages #400 changes the messages grants and the policy WITH CHECK clauses. This issue changes internal.can_read_channel, so the two migrations can land in either order.

Generated by Claude Code