-- Run after 202611120003_community.sql in a TEST Supabase project first. -- Public images are intentionally publicly downloadable by URL, even if a profile is private. begin; insert into storage.buckets(id, name, public, file_size_limit, allowed_mime_types) values ('community-media', 'community-media', true, 5242880, array['image/png','image/jpeg','image/webp']) on conflict (id) do update set public=excluded.public, file_size_limit=excluded.file_size_limit, allowed_mime_types=excluded.allowed_mime_types; create or replace function public.community_media_upload_allowed() returns boolean language sql stable security definer set search_path = '' as $$ select auth.uid() is not null and exists(select 1 from public.hub_access a where a.user_id = auth.uid() and a.status = 'approved') and not exists(select 1 from private.hub_suspensions s where s.user_id = auth.uid() and s.lifted_at is null and (s.suspended_until is null or s.suspended_until > now())); $$; revoke all on function public.community_media_upload_allowed() from public, anon; grant execute on function public.community_media_upload_allowed() to authenticated; -- Only verified, approved (and not currently suspended) accounts can upload, -- and only into a directory named after their own auth.uid(). create policy "community_media_insert_own" on storage.objects for insert to authenticated with check ( bucket_id = 'community-media' and (storage.foldername(name))[1] = (select auth.uid()::text) and name ~ '^[0-9a-f-]{36}/(avatar|banner)-[0-9a-f-]{36}\.(png|jpg|webp)$' and (select public.community_media_upload_allowed()) ); -- Owner can remove images they previously uploaded. No overwrite/upsert. create policy "community_media_delete_own" on storage.objects for delete to authenticated using ( bucket_id = 'community-media' and (storage.foldername(name))[1] = (select auth.uid()::text) ); -- Supabase Storage delete needs SELECT as well as DELETE. create policy "community_media_select_own" on storage.objects for select to authenticated using ( bucket_id = 'community-media' and (storage.foldername(name))[1] = (select auth.uid()::text) ); commit;