Postgres RLS in Local Dev: The Three Silent-Fail Modes That Ship to Prod

• RLS doesn't fail loudly. A table with no policies and RLS off returns every row, to everyone, and looks like a working app
• Three ways local dev hides that: migrations that never run ENABLE ROW LEVEL SECURITY, a dev connection that bypasses RLS, and views that evaluate as their owner
• All three pass every test you'd write in the happy path, because the happy path is "the data shows up"
• A 30-line SQL check at boot catches every one of them, in about 50ms

Row Level Security has one property that makes it different from almost every other security control: when it's not working, the app works better. More rows come back. Nothing errors. Your local dev environment looks great right up until a customer in production sees another customer's invoices.

I've now seen this ship three different ways, and the fix for all three is the same boring boot-time check. Here's each failure and then the check.

Silent-fail 1: your migrations never enabled it

RLS is off by default on every table in Postgres. CREATE TABLE gives you a table with no row security, and it stays that way until something explicitly runs:

alter table invoices enable row level security;

The Supabase dashboard's table editor does this for you. Nothing else does. If you write migrations in raw SQL, dbmate, Prisma, Drizzle, Knex, or anything that turns a schema file into DDL, you get a table with RLS off, no warning, and no policies. And a table with RLS off and no policies is a table with no access control at all.

The Prisma case is the sneakiest, because Prisma's schema language has no concept of RLS. You write:

model Invoice {
  id      String @id @default(uuid())
  userId  String
  amount  Int
}

and prisma migrate dev generates a perfectly correct CREATE TABLE with no ENABLE ROW LEVEL SECURITY. You have to add it in a hand-written migration, and remember to do it for every table, forever.

There's a second half most people miss even after they've enabled it. The table owner bypasses RLS unless you also run:

alter table invoices force row level security;

Without force, the role that created the table (often the same role your migrations run as, often the same role your app connects as in dev) walks straight through every policy.

Silent-fail 2: your dev connection can't see RLS at all

Superusers bypass RLS. Roles with the BYPASSRLS attribute bypass RLS. Table owners bypass RLS unless force is set. In a lot of local setups, the app connects as one of those.

The common version: local Postgres, connection string is postgres://postgres:postgres@localhost, the postgres role is a superuser. Every query the app makes runs with RLS silently disabled. Your seed script inserted rows for five fake users, your UI shows all five users' data when logged in as one of them, and it looks like a demo, not a leak.

In production the app doesn't connect as a superuser. It connects as authenticated through PostgREST, or as a scoped application role, and now the policies apply. If they're wrong, or missing, that's the first time anyone finds out.

You can check what you're running as right now:

select rolname, rolsuper, rolbypassrls
from pg_roles
where rolname = current_user;

If either boolean is true, RLS is decorative in this session.

The fix isn't "don't use the postgres role locally," because you need it for migrations. The fix is to run the app as the same role it runs as in prod, and only the migration step as the owner. Supabase-style stacks do this for you because the API layer always connects as anon or authenticated. Anything that hands your app a raw connection string doesn't.

Silent-fail 3: views evaluate as their owner

This one bit me after I'd fixed the first two.

Before Postgres 15, a view runs its underlying queries with the view owner's privileges, not the caller's. That's security-definer semantics, and it means RLS on the base tables is evaluated as the owner. If the owner is postgres, or the table owner, or anything with BYPASSRLS, the view returns every row regardless of who's querying it.

-- invoices has RLS enabled, forced, with a correct policy.
-- this view still leaks every row, because it runs as its owner.
create view invoice_summary as
  select user_id, sum(amount) as total
  from invoices
  group by user_id;

Postgres 15 added a way out:

create view invoice_summary
  with (security_invoker = true) as
  select user_id, sum(amount) as total
  from invoices
  group by user_id;

With security_invoker, the view runs as whoever called it and the base-table policies apply normally. On Postgres 14 or earlier there is no equivalent; you have to either not expose the view through your API layer, or replicate the policy logic inside the view's WHERE clause and hope nobody edits one without the other.

The reason this ships to prod is that the view works identically in dev and prod, and in both places it returns all the rows. The difference is only that in dev, all the rows is what you expected to see.

The 30-line boot check

None of the three failures produce an error. So produce one yourself. This runs at app boot in development and in CI, against whatever Postgres you're using locally, and refuses to start if any of the three conditions are true:

do $$
declare
  bad text;
begin
  -- 1. every table in public must have RLS enabled AND forced
  select string_agg(c.relname, ', ') into bad
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
  where n.nspname = 'public'
    and c.relkind = 'r'
    and (not c.relrowsecurity or not c.relforcerowsecurity);
  if bad is not null then
    raise exception 'RLS not enabled+forced on: %', bad;
  end if;

  -- 2. the current role must not bypass RLS
  if exists (
    select 1 from pg_roles
    where rolname = current_user
      and (rolsuper or rolbypassrls)
  ) then
    raise exception 'connected as % which bypasses RLS', current_user;
  end if;

  -- 3. every view in public must be security_invoker (PG15+)
  select string_agg(c.relname, ', ') into bad
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
  where n.nspname = 'public'
    and c.relkind = 'v'
    and coalesce(
      (select option_value = 'true'
       from pg_options_to_table(c.reloptions)
       where option_name = 'security_invoker'),
      false
    ) = false;
  if bad is not null then
    raise exception 'views without security_invoker: %', bad;
  end if;
end $$;

Three catalog queries, roughly 50ms on a schema with a few hundred objects. Run it right after migrations apply and before the app accepts a request. If you have tables that legitimately shouldn't have RLS (lookup tables, public config), exclude them by name in check 1; the point is that the exclusion is explicit and reviewable rather than the default.

Check 2 is the one people push back on, because it means the app can't connect as postgres locally. That's the point. If your local app connects as a role that ignores RLS, you have never once tested your policies.

Where this actually runs

The check only means something if local dev is real Postgres with RLS semantics. Mocks, SQLite adapters, and in-memory Postgres emulators either don't implement RLS or implement it loosely enough that the check passes on schemas that would fail in prod. I ended up running it against tinbase, which is a single-process Supabase-compatible backend on real Postgres 17: the API layer connects as anon/authenticated the way hosted Supabase does, so check 2 passes naturally, and the catalog queries behave exactly as they will in prod. Full Docker Supabase works too; it's just heavier for a check that takes 50ms.

Whichever you use, the requirement is the same: the Postgres your app boots against locally has to be the Postgres that enforces RLS, or the boot check is testing nothing.

The pattern

Three silent failures, one shape: RLS not applying looks like RLS working, only with more data. Nothing in the normal test loop distinguishes "policy correctly returned my rows" from "no policy, returned all rows, and mine were among them."

The boot check turns the silent failure into a loud one at the earliest possible moment, which is the only place it's cheap. If you've got a fourth mode, I'd like to hear it. My current suspicion is SECURITY DEFINER functions called from policies, but I haven't seen that one ship yet.