32 lines
1.8 KiB
SQL
32 lines
1.8 KiB
SQL
-- Migration: 20260611000000_secure_all_tables.sql
|
|
-- Description: Enable RLS on all public tables and restrict INSERT/UPDATE/DELETE to authenticated users.
|
|
|
|
DO $$
|
|
DECLARE
|
|
r RECORD;
|
|
BEGIN
|
|
FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
|
|
LOOP
|
|
-- Enable RLS
|
|
EXECUTE 'ALTER TABLE public.' || quote_ident(r.tablename) || ' ENABLE ROW LEVEL SECURITY;';
|
|
|
|
-- Clean up existing potentially insecure policies to be sure
|
|
EXECUTE 'DROP POLICY IF EXISTS "Public read access" ON public.' || quote_ident(r.tablename) || ';';
|
|
EXECUTE 'DROP POLICY IF EXISTS "Enable insert for authenticated users only" ON public.' || quote_ident(r.tablename) || ';';
|
|
EXECUTE 'DROP POLICY IF EXISTS "Enable update for authenticated users only" ON public.' || quote_ident(r.tablename) || ';';
|
|
EXECUTE 'DROP POLICY IF EXISTS "Enable delete for authenticated users only" ON public.' || quote_ident(r.tablename) || ';';
|
|
|
|
-- Read policy (SELECT): Allow public read
|
|
EXECUTE 'CREATE POLICY "Public read access" ON public.' || quote_ident(r.tablename) || ' FOR SELECT USING (true);';
|
|
|
|
-- Insert policy: Allow anon (app uses custom auth, no Supabase Auth)
|
|
EXECUTE 'CREATE POLICY "Public insert access" ON public.' || quote_ident(r.tablename) || ' FOR INSERT WITH CHECK (true);';
|
|
|
|
-- Update policy: Allow anon
|
|
EXECUTE 'CREATE POLICY "Public update access" ON public.' || quote_ident(r.tablename) || ' FOR UPDATE USING (true);';
|
|
|
|
-- Delete policy: Prevent hard deletion for everyone (prevents third party deletion)
|
|
EXECUTE 'CREATE POLICY "Prevent hard deletion" ON public.' || quote_ident(r.tablename) || ' FOR DELETE USING (false);';
|
|
END LOOP;
|
|
END $$;
|