I keep forgetting to turn on Row Level Security. So I made Postgres do it for me

Every table you create on Supabase begins its life either safe or wide open, and which one you get depends entirely on how you made it.

As you construct a table using the Table Editor on the dashboard, Supabase will silently enable Row Level Security on your behalf. If you construct the identical table using a migration, a plain SQL file, or an object relationship manager (ORM) such as Prisma or Drizzle, the RLS will be disabled. Without prior notice. It remains unlocked and does nothing.

An empty table of any size cannot be found in the ⁎ schema. The public anon key that Supabase provides to your frontend is intentionally public. Anyone can read it because it’s embedded in your JavaScript. To prevent a key from seeing more than the rows it is authorised to, there is Row Level Security. Bypassing it will effectively make the table publicly available to anyone with browser dev tools.

I know a lot about this. For a long time, I’ve known it. Even now, when I go to create a table, my mind is immediately filled with columns, foreign keys, and the feature I'm attempting to ship, rather than a security toggle that resides in another part of the database.

So a few days ago I stopped trusting myself to remember.

Let the database catch me

An event trigger is a feature of Postgres. Data changes, such as the addition or removal of a row, cause a typical trigger to respond. A schema is what causes an event trigger to respond. A new, changed, or removed item was made. I wanted that as my hook. Whenever a table is created, execute some code.

There is one catch with Supabase, and I thought it would ruin the concept: event triggers are usually only available to superusers, and Supabase doesn’t actually grant you that privilege. It turns out that every project comes with an extension called supautils, which allows the ordinary postgres role to create event triggers anyway. A few lines of SQL were the thing I believed was off-limits.

The actual code

Two parts. A trigger tells the function when to run, and the function itself does the work.

-- The function: runs after a table is created
create or replace function public.enable_rls_on_new_tables()
returns event_trigger
language plpgsql
as $$
declare
  cmd record;
begin
  for cmd in select * from pg_event_trigger_ddl_commands()
  loop
    if cmd.command_tag = 'CREATE TABLE'
       and cmd.object_type = 'table'
       and cmd.schema_name = 'public'
    then
      execute format(
        'alter table %s enable row level security;',
        cmd.object_identity
      );
    end if;
  end loop;
end;
$$;

-- The trigger: fires the function after every CREATE TABLE
create event trigger enable_rls_on_new_tables_trigger
  on ddl_command_end
  when tag in ('CREATE TABLE')
  execute function public.enable_rls_on_new_tables();

In simple terms, Postgres will tell you what it made if you ask after it is done. That’s why I ask. I go through that list and turn on row-level security for any real table that lives in the ⁣ schema. Every time I got a new table with RLS on, it was from a migration, the SQL editor, or an ORM I had almost forgotten was running.

You need to create this as the user, but in practice you don’t have to worry about that because the SQL editor and your migrations already run as.

The part where I knew I had broken everything

Here is where it gets funny.

The first table after wiring this up was blank. My app was unable to read one row and threw a flat permission denied. I thought the trigger broke something for a good minute. It had not. It had worked perfectly.

RLS is fail-closed. You turn it on with no policies attached, and the table blocks everyone other than roles that bypass it, which is your service key and postgres⁣. My table was not broken. It was locked, as it should have been, waiting for me to say who gets in. The fix was not to undo anything. It was the policy I was going to have to write anyway.

That’s the whole point, really. The old failure mode was a table open to the whole internet, and I never noticed. A new failure mode is a loud, locked table that I notice in a matter of seconds. One of those you catch in development. The other you find out from a breach report.

Don't forget the tables you already have

The trigger fires only forward. All the tables you built before were still there, in whatever state you left them; in my case, a handful of older ones with RLS silently turned off. So I ran a one-off sweep to gather them up:

do $$
declare t record;
begin
  for t in
    select tablename from pg_tables
    where schemaname = 'public' and rowsecurity = false
  loop
    execute format('alter table public.%I enable row level security;', t.tablename);
  end loop;
end $$;

It scans every table in the public schema that still has RLS off and turns it on. You add the trigger, you run it once, and you’re starting clean.

Why I like this kind of fix

It doesn’t require anything of future-me. There’s no checklist to hold onto and no discipline to draw on at 1am when I’m scrambling to get a migration out the door. The rule lives in the database. It runs by itself. Worst thing that can happen is a locked table instead of an open one.

If you have a reason, you can still switch it off for a single table. You can pause the whole trigger with a line. Most days I forget it’s even there – that was the idea. The ideal security setting is one you don’t need to remember to turn on because you’re not the one turning it on.