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
users
table and a
roles
roles
table. A user has a role assigned to them by
role_id
role_id
. 2. I have a private storage bucket called
liveness_checks
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.
Supabase gives you the tools, documentation, and community that makes managing databases, authentication, and backend infrastructure a lot less overwhelming.