99 lines
3.4 KiB
SQL
99 lines
3.4 KiB
SQL
-- ============================================
|
|
-- FIX SERVICE ROLE RLS BYPASS
|
|
-- Ensure service_role can properly bypass RLS for storage operations
|
|
-- ============================================
|
|
|
|
-- The service_role should have BYPASSRLS attribute in Supabase
|
|
-- But in self-hosted setups, this might not be configured correctly
|
|
-- This migration ensures proper access through multiple approaches
|
|
|
|
-- Approach 1: Grant service_role BYPASSRLS (if not already set)
|
|
-- Note: This requires superuser privileges, which the migration might not have
|
|
-- If this fails, the explicit policies below will still work
|
|
DO $$
|
|
BEGIN
|
|
-- Check if service_role exists and doesn't have bypassrls
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'service_role' AND NOT rolbypassrls) THEN
|
|
ALTER ROLE service_role BYPASSRLS;
|
|
RAISE NOTICE 'Granted BYPASSRLS to service_role';
|
|
ELSE
|
|
RAISE NOTICE 'service_role already has BYPASSRLS or does not exist';
|
|
END IF;
|
|
EXCEPTION
|
|
WHEN insufficient_privilege THEN
|
|
RAISE NOTICE 'Could not grant BYPASSRLS (insufficient privileges) - using explicit policies instead';
|
|
WHEN OTHERS THEN
|
|
RAISE NOTICE 'Error granting BYPASSRLS: % - using explicit policies instead', SQLERRM;
|
|
END $$;
|
|
|
|
-- Approach 2: Ensure RLS is properly configured on storage.objects
|
|
-- Check if RLS is enabled and ensure our policies exist
|
|
DO $$
|
|
BEGIN
|
|
-- Ensure RLS is enabled on storage.objects (it should be by default)
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_tables
|
|
WHERE schemaname = 'storage' AND tablename = 'objects' AND rowsecurity = true
|
|
) THEN
|
|
ALTER TABLE storage.objects ENABLE ROW LEVEL SECURITY;
|
|
RAISE NOTICE 'Enabled RLS on storage.objects';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Approach 3: Create permissive policies for service_role on ALL storage buckets
|
|
-- These use a single policy per operation type that covers all buckets
|
|
|
|
-- First, clean up any existing service_role policies
|
|
DROP POLICY IF EXISTS "service_role_all_select" ON storage.objects;
|
|
DROP POLICY IF EXISTS "service_role_all_insert" ON storage.objects;
|
|
DROP POLICY IF EXISTS "service_role_all_update" ON storage.objects;
|
|
DROP POLICY IF EXISTS "service_role_all_delete" ON storage.objects;
|
|
|
|
-- Create universal service_role policies (allow access to ALL buckets)
|
|
CREATE POLICY "service_role_all_select" ON storage.objects
|
|
FOR SELECT TO service_role
|
|
USING (true);
|
|
|
|
CREATE POLICY "service_role_all_insert" ON storage.objects
|
|
FOR INSERT TO service_role
|
|
WITH CHECK (true);
|
|
|
|
CREATE POLICY "service_role_all_update" ON storage.objects
|
|
FOR UPDATE TO service_role
|
|
USING (true);
|
|
|
|
CREATE POLICY "service_role_all_delete" ON storage.objects
|
|
FOR DELETE TO service_role
|
|
USING (true);
|
|
|
|
-- Approach 4: Grant necessary table permissions to service_role
|
|
GRANT ALL ON storage.objects TO service_role;
|
|
GRANT ALL ON storage.buckets TO service_role;
|
|
GRANT USAGE ON SCHEMA storage TO service_role;
|
|
|
|
-- Also ensure service_role can use sequences in storage schema
|
|
DO $$
|
|
DECLARE
|
|
seq_name text;
|
|
BEGIN
|
|
FOR seq_name IN
|
|
SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'storage'
|
|
LOOP
|
|
EXECUTE format('GRANT USAGE, SELECT ON SEQUENCE storage.%I TO service_role', seq_name);
|
|
END LOOP;
|
|
END $$;
|
|
|
|
-- Verify the setup
|
|
DO $$
|
|
DECLARE
|
|
policy_count int;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO policy_count
|
|
FROM pg_policies
|
|
WHERE schemaname = 'storage'
|
|
AND tablename = 'objects'
|
|
AND roles @> ARRAY['service_role']::name[];
|
|
|
|
RAISE NOTICE 'service_role has % policies on storage.objects', policy_count;
|
|
END $$;
|