Let non-members read only chat media that a live message uses #432

Summary

Two media bucket read policies let anon and any signed-in non-member read every object whose channel is PUBLIC. Storage listing runs under the same SELECT policy as signing. So the public anon key can list the bucket and learn uploader user ids, channel ids and file names. That includes uploads that were never sent. The composer uploads a file as soon as it is attached. A closed tab leaves the object behind until the orphan cleanup removes it.

• Severity: Low
• Area: Supabase Storage policies, chat media
• Source: security review of 2026-10-06, finding L12

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

Policies Anon can read public channel chat media and Authed can read public channel chat media exist on storage.objects. Both check only that the channel is PUBLIC.

Where

• Current policy definitions: packages/supabase/migrations/20260726180000_remove_private_channel_type.sql:125-145. Script mirror: packages/supabase/scripts/12-buckets.sql:157-177.
• Upload on attach: apps/webapp/src/components/chatroom/components/MessageComposer/hooks/useComposerAttachments.ts:184-189 (runner.enqueue).
• Removing a ready attachment already deletes its object: apps/webapp/src/components/chatroom/utils/chatMediaUploadRunner.ts:125-138 (deleteReadyAttachment). The composer attachment store is not persisted, so a closed tab or a crash leaves its uploads as orphans.
• Orphan cleanup: internal.cleanup_orphan_chat_media in packages/supabase/scripts/10-3-func-message.sql:394-430. It deletes objects older than 24 hours with no live message reference. The cron runs once a day at 03:30 (packages/supabase/scripts/16-cron-jobs.sql:28-32), so an orphan can stay listable for up to about 48 hours.
• Readers sign URLs only. The webapp calls createSignedUrl and never list on media (apps/webapp/src/components/chatroom/utils/chatMediaUrl.ts:51, :90).

Root cause

Signing one object and listing the bucket both need SELECT on storage.objects. The two lurker policies grant SELECT on every object in a PUBLIC channel, whether or not a message uses it. Sent media is already visible through messages.medias, so the only new leak is unsent uploads.

Fix plan

1. Migration. Recreate the two lurker policies, Anon can read public channel chat media and Authed can read public channel chat media. Keep the PUBLIC channel check, and add one condition: a live message in the same channel references the object.
   • Match the message by m.channel_id = (storage.foldername(objects.name))[2] and m.deleted_at is null, so the lookup uses the channel_id index.
   • Match the media entry by coalesce(nullif(elem->>'path', ''), elem->>'url') = objects.name over jsonb_array_elements(coalesce(m.medias, '[]'::jsonb)). New messages always store a bare path (messageMediasForInsert in apps/webapp/src/components/chatroom/utils/messageMediaPaths.ts).
   • Do not call internal.normalize_chat_media_path here. anon has no USAGE on schema internal (packages/supabase/scripts/13-RLS.sql:17-19), so the anon policy would fail.
2. Legacy rows. Before shipping, count live messages whose media has no bare path and a full URL in url. If the count is not zero, also match the URL suffix after /media/ for those rows.
3. Script mirror. Apply the same change in packages/supabase/scripts/12-buckets.sql:157-177. Regenerate the seed with bun run --filter @docs.plus/supabase_back seed. No function signature changes, so types needs no run.

Do not change Channel members can read chat media (12-buckets.sql:146-155). The uploader is always a member, and the composer signs its own unsent upload through that policy (chatMediaStorageReadiness.ts).

Out of scope

• Shortening the orphan cleanup window. The policy change hides orphans from non-members.
• Deleting an object when an upload is cancelled mid-flight.

Acceptance criteria

• As anon, select name from storage.objects where bucket_id = 'media' returns only objects that a live message references.
• An uploaded but unsent attachment is not listed for anon or for a signed-in non-member.
• Anon and signed-in non-members still see images, video and files on PUBLIC chat messages.
• A channel member still sees the preview of an attachment before sending it.
• Signing one object as anon stays fast on the largest local channel (explain analyze).

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. Attach a file in the local webapp and do not send it. Send a second message with media.
3. In psql, inside begin; set local role anon; ... rollback;, list media objects. Only the sent object appears.
4. Open the channel signed out and as a signed-in non-member. Check that the sent media loads. The chatroom Cypress suite intercepts Supabase HTTP and cannot test policies, so check this by hand.

Related

• Make a Private document's chat readable and writable only by its owner #396 Private document chat. It rewrites the same media read policies. The second change to land must keep both conditions.
• Clear the text of a chat message when it is deleted #435 deleted chat text stays readable

Generated by Claude Code