Stop bookmarks and replies from copying a message the user cannot read #410

Summary

Two DEFINER paths return or copy a message's text using only its id:

1. Bookmarks. The message_bookmarks INSERT policy checks only user_id. A user can bookmark any message_id. get_user_bookmarks (DEFINER) then returns that message's text with no channel check.
2. Reply previews. The BEFORE trigger set_replied_message_preview (DEFINER) copies the preview of any reply_to_message_id into the new message, from any channel. The reply notification trigger also reads the parent by id only. So a reply posted in the user's own channel notifies the author of any message on the platform. The notice goes by push and email, with the replier's chosen display name.

• Severity: Low for the read paths today, because every channel is PUBLIC. It becomes Medium once Private chat is gated (see the Private chat issue): these paths would bypass that gate. Today, anyone can use the cross-channel reply notification to send spam and phishing.
• Area: Supabase
• Source: security review of 2026-10-06

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

• get_user_bookmarks is SECURITY DEFINER and executable by anon and authenticated. Its body uses auth.uid() but has no can_read_channel or channel_members check.
• message_bookmarks policies: INSERT WITH CHECK (auth.uid() = user_id) only.
• set_replied_message_preview is SECURITY DEFINER.

Where

• Bookmark RPC: packages/supabase/migrations/20260625150000_bookmark_tab_scoped_list_rpc.sql.
• Bookmark table policy: packages/supabase/scripts/06-message-bookmarks.sql:57-60. The RPC path checks visibility (packages/supabase/scripts/07-bookmark-functions.sql:36-56); the table path does not.
• Reply preview trigger: packages/supabase/migrations/20260623120000_chat_media_attachments.sql:496-521, 563.
• Reply notification trigger: same file :642-705.

Fix plan

1. One reply guard. In set_replied_message_preview, raise an error when the parent message's channel_id differs from NEW.channel_id. No real UI path replies across channels. This one guard closes the preview copy, the metadata.replied copy and the cross-channel reply notification.
2. Bookmarks.
   • Revoke INSERT, UPDATE and DELETE on message_bookmarks from authenticated and anon. The webapp uses only the bookmark RPCs (confirm with grep -rn "message_bookmarks" apps/webapp/src).
   • In get_user_bookmarks, join through internal.can_read_channel(m.channel_id) so a bookmark of an unreadable message returns nothing.
3. Mirror in packages/supabase/scripts/, then run bun run --filter @docs.plus/supabase_back types.

Acceptance criteria

• A reply whose parent is in another channel is refused.
• A normal reply in the same channel still shows its preview and notifies the parent author.
• A direct POST /rest/v1/message_bookmarks is refused. Bookmarking through the UI still works.
• get_user_bookmarks does not return a message the caller cannot read.

Generated by Claude Code