S
Supabase•2w ago
wtfmozart

Reference Custom Table In Storage Bucket Policy

Hey folks! I'm experimenting with the storage bucket policies and have been trying to figure out how I can set up USING clause for a SELECT policy. Here's some context: 1. I've created a custom users table and a roles table. A user has a role assigned to them by role_id. 2. I have a private storage bucket called liveness_checks for mp4 videos, and with INSERT allowed WITH CHECK: 
(bucket_id = 'liveness_checks'::text) AND (auth.role() = 'authenticated'::text) AND (storage.foldername(name))[1] = auth.uid()
(bucket_id = 'liveness_checks'::text) AND (auth.role() = 'authenticated'::text) AND (storage.foldername(name))[1] = auth.uid()
3. However, I want SELECT to be allowed to the sentinel role, for which I tried doing this but it doesn't work: 
((bucket_id = 'liveness_checks'::text) AND (auth.role() = 'authenticated'::text) AND (EXISTS ( SELECT 1
   FROM (users u
     JOIN roles r ON ((u.role_id = r.id)))
  WHERE ((u.id = auth.uid()) AND (r.name = 'sentinel'::text)))))
((bucket_id = 'liveness_checks'::text) AND (auth.role() = 'authenticated'::text) AND (EXISTS ( SELECT 1
   FROM (users u
     JOIN roles r ON ((u.role_id = r.id)))
  WHERE ((u.id = auth.uid()) AND (r.name = 'sentinel'::text)))))
4. I use an edge function with service role key to get the public URLs to the files and deliver to my app. I tried playing the videos using the URLs but the videos do not load. If I return signed URLs, I can play videos owned by the authenticated user, not those of others. What am I doing wrong?
2 Replies
Ad Intellegent
Ad Intellegent•2w ago
@wtfmozart instead of checking auth.role(), join your users and roles table against auth.uid() in the policy’s USING clause and ensure your edge function uses a service role key to bypass RLS for sentinel access
wtfmozart
wtfmozartOP•6d ago
@Ad Intellegent that worked. Thank you

Did you find this page helpful?