RLS 403 on Storage Upload despite valid subquery/SECURITY DEFINER
RLSstorage
Solved🟡javascriptCategory: help-and-questions Tags: Storage, RLS, Postgres
Context: Trying to allow staff to upload receipts to: receipts/assignments/[day_event_assignment_id]/file.jpg. Upload only allowed if auth.uid() matches the auth_user_id on the day_event_assignments table for that ID.
Environment:
Framework: React
Library: @supabase/supabase-js (Latest)
What's Happening: I get a 403 Unauthorized (new row violates RLS).
It works if I use a simple policy: (bucket_id = 'receipts').
It fails as soon as I add a subquery to my public table.
I've added a direct auth_user_id to the table to avoid complex joins, but the error persists.
The Code:
-- The Policy
CREATE POLICY "Staff Upload Policy" ON storage.objects FOR INSERT TO authenticated
WITH CHECK (
bucket_id = 'receipts' AND
EXISTS (
SELECT 1 FROM public.day_event_assignments
WHERE auth_user_id = auth.uid()
AND day_event_assignment_id::text = (storage.foldername(name))[2]
)
);
-- The Security Definer Bypass (Also tried this to avoid Table RLS recursion)
CREATE OR REPLACE FUNCTION public.check_upload_perm(path_name text)
RETURNS boolean AS $$
BEGIN
RETURN EXISTS (
SELECT 1 FROM public.day_event_assignments
WHERE auth_user_id = auth.uid()
AND path_name ILIKE '%' '%'
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
Steps Taken:
- Verified auth.uid() and day_event_assignment_id data in the SQL Editor (Query returns 1 row).
- Disabled RLS on the day_event_assignments table (Still 403).
- Granted SELECT on the table to authenticated and service_role.
- Verified the upload path in the frontend is exactly assignments/UUID/filename.
The Mystery: Why does the Storage engine fail the EXISTS check even when the subquery is valid and table RLS is disabled? Is there a specific search path or schema permission I'm missing for storage.objects to talk to public tables?
Context: Trying to allow staff to upload receipts to: receipts/assignments/[day_event_assignment_id]/file.jpg. Upload only allowed if auth.uid() matches the auth_user_id on the day_event_assignments table for that ID.
Environment:
Framework: React
Library: @supabase/supabase-js (Latest)
What's Happening: I get a 403 Unauthorized (new row violates RLS).
It works if I use a simple policy: (bucket_id = 'receipts').
It fails as soon as I add a subquery to my public table.
I've added a direct auth_user_id to the table to avoid complex joins, but the error persists.
The Code:
-- The Policy
CREATE POLICY "Staff Upload Policy" ON storage.objects FOR INSERT TO authenticated
WITH CHECK (
bucket_id = 'receipts' AND
EXISTS (
SELECT 1 FROM public.day_event_assignments
WHERE auth_user_id = auth.uid()
AND day_event_assignment_id::text = (storage.foldername(name))[2]
)
);
-- The Security Definer Bypass (Also tried this to avoid Table RLS recursion)
CREATE OR REPLACE FUNCTION public.check_upload_perm(path_name text)
RETURNS boolean AS $$
BEGIN
RETURN EXISTS (
SELECT 1 FROM public.day_event_assignments
WHERE auth_user_id = auth.uid()
AND path_name ILIKE '%' '%'
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
Steps Taken:
- Verified auth.uid() and day_event_assignment_id data in the SQL Editor (Query returns 1 row).
- Disabled RLS on the day_event_assignments table (Still 403).
- Granted SELECT on the table to authenticated and service_role.
- Verified the upload path in the frontend is exactly assignments/UUID/filename.
The Mystery: Why does the Storage engine fail the EXISTS check even when the subquery is valid and table RLS is disabled? Is there a specific search path or schema permission I'm missing for storage.objects to talk to public tables?
