If you're using Supabase, and have created tables in the Postgres database from outside of Supabase (e.g. with something like Prisma or Strapi), RLS (Row Level Security) won't be active on them.

This means anyone querying via the Supabase API will be able to read, write, delete anything. In this case you may want to activate RLS on many tables at once (as I did with Strapi).

Here is a SQL command that will do it by fetching all table names on the public schema, and then looping through them, activating RLS one by one, so they can't be read publicly:

-- Enable row-level security (RLS) dynamically on all base tables in the public schema
DO $$
DECLARE
  table_record RECORD;
BEGIN
  FOR table_record IN 
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema = 'public' 
      AND table_type = 'BASE TABLE'
  LOOP
    EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY;', table_record.table_name);
  END LOOP;
END $$;

Alternative 2 step approach

If you want to pick and choose which tables to activate RLS on, you could also do it in 2 steps - first by getting a list of the tables (which you could then edit), and then pasting those into the second query:

1. first run this to get a list of all your tables:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

2. Then add RLS to all of them in the results by pasting the result into chatgpt:

Paste into chatgpt to get the query:

Useful links:

Row Level Security | Supabase Docs

Secure your data using Postgres Row Level Security.

ChatGPT - Shared chat on ChatGPT

Shared via ChatGPT