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

#postgres #supabase #database #security

TL;DR

RLS has a brutal failure property: when it is misconfigured, everything works. Queries return rows, tests pass, nothing errors
Silent-fail 1: your migration tool (raw SQL, dbmate, Prisma) creates tables with RLS disabled by default
Silent-fail 2: your local dev connection runs as a role with BYPASSRLS, so policies are never exercised even when they exist
Silent-fail 3: views execute with the view owner's privileges, so RLS on the underlying tables is skipped unless you opt in (Postgres 15+)
All three are catchable with about 30 lines of SQL that run at boot in ~50ms

The nasty thing about row level security is that it fails open in development. A missing policy doesn't throw. A bypassed policy doesn't warn. Your app happily reads and writes, your test suite goes green, and the first person to discover the gap is a curious user in production changing an ID in a URL.

Here are the three ways I've seen that happen, and the boot-time check that makes all of them loud.

Silent-fail 1: migrations don't enable RLS

Whatever writes your DDL, the default is the same. This table has no row security:

create table user_notes (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null,
  body text
);

Raw SQL migrations, dbmate, Prisma's generated DDL: none of them add enable row level security for you. You have to remember two lines, every table, forever:

alter table user_notes enable row level security;
alter table user_notes force row level security;

That second line matters more than people think. Without force, the table's owner still bypasses RLS entirely. If your app connects as the role that ran the migrations, you have policies that apply to nobody.

Silent-fail 2: dev is seeded (and queried) under BYPASSRLS

Even with RLS enabled and policies written, they only apply to roles that are subject to them. Superusers and any role with the BYPASSRLS attribute skip the whole mechanism.

And that is exactly how most local stacks are wired: you seed and often query as postgres or an equivalent admin role. So locally, every policy is a no-op. The first connection that actually exercises your policies is the production one, which is the worst possible place to run a policy for the first time.

The fix is to make local dev connect the way production does: as a non-privileged role, with the admin role reserved for migrations only.

Silent-fail 3: views don't inherit RLS (until you ask)

Views in Postgres execute with the privileges of the view's owner, not the querying user. Create a convenience view over a policied table and you have quietly built a door around your own policies:

create view recent_notes as
  select * from user_notes order by created_at desc limit 100;
-- runs as the owner: RLS on user_notes is not applied to callers

Postgres 15 added the opt-in fix:

create view recent_notes
  with (security_invoker = true) as
  select * from user_notes order by created_at desc limit 100;

With security_invoker, the view runs as the caller and RLS applies normally. On anything older than 15 there is no clean equivalent, which is one of several reasons local dev should run the same modern Postgres major as production.

The boot-time check that catches all three

None of these need discipline. They need a tripwire. This runs at application boot (or as the last migration) and fails loudly on all three modes:

do $$
declare bad text;
begin
  -- 1: tables without RLS (or without FORCE)
  select string_agg(tablename, ', ') into bad
  from pg_tables t
  join pg_class c on c.relname = t.tablename
  where t.schemaname = 'public'
    and (not t.rowsecurity or not c.relforcerowsecurity);
  if bad is not null then
    raise exception 'RLS not enabled/forced on: %', bad;
  end if;

  -- 2: non-system roles that bypass RLS
  select string_agg(rolname, ', ') into bad
  from pg_roles
  where rolbypassrls and rolname not like 'pg\_%' and rolname <> 'postgres';
  if bad is not null then
    raise exception 'BYPASSRLS roles present: %', bad;
  end if;

  -- 3: views without security_invoker
  select string_agg(c.relname, ', ') into bad
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
  where c.relkind = 'v' and n.nspname = 'public'
    and coalesce(c.reloptions::text, '') not like '%security_invoker=true%';
  if bad is not null then
    raise exception 'Views without security_invoker: %', bad;
  end if;
end $$;

Adjust the allowlists to taste (maybe your migration role legitimately keeps BYPASSRLS; exclude it explicitly so the exception is a decision, not an accident). On a normal-sized schema this runs in tens of milliseconds. It costs nothing and converts all three silent fails into a crash at boot, which is where you want them.

This class of bug is also why I care about local dev running real Postgres with the same defaults as prod. It is one of the reasons we built Tinbase: a single-binary, MIT-licensed, Supabase-compatible local runtime on actual Postgres 17, no Docker, so checks like the one above behave identically on your laptop and in prod, security_invoker included.

The takeaway

RLS is enforcement only when three things are true: the table has it enabled and forced, the connecting role is subject to it, and every view over it runs as the invoker. Each condition fails silently on its own. Check all three mechanically, at boot, every time.

Have you been bitten by a policy that turned out to be decorative? What did the tripwire look like once you built one? Comments are open.

DEV Community