RLS policies don't fail, they drift: the definitive guide

Authorization on Supabase fails quietly: not with errors, but with slow widening. This piece follows one fabricated project through six months of completely ordinary migrations and shortcuts, showing exactly how isolation erodes at each step — and which cheap check would have caught each one.

Here is the property of row-level security that shapes everything about operating it: a policy is never wrong on the day it ships. It was tested against the schema that existed then, and it matched someone's intent. What happens afterward is not breakage — Postgres does not lose your policies, corrupt them, or mis-evaluate them. What happens is that everything around them changes: tables appear without them, columns they reference get dropped, roles get used in ways nobody planned, and whole databases get restored from states that predate hardening.

Each individual change looks reasonable in review. None of them produces an error. Stack six of them together and you have a system where the effective authorization model differs from the intended one by an amount nobody can see from inside. That difference has a name — drift — and this guide walks it end to end, using one fabricated example project followed across six months of plausible decisions.

What drift means when nothing throws

Formally: your isolation level is a property of the entire database state — policies, flags, grants, keys, functions — evaluated together at any moment. It is not stored anywhere as a spec you can diff against intent. So every migration silently redefines it:

Read the right column top to bottom: nothing there requires incompetence, and nothing there raises an exception. The dashboard reports tables as "protected" whenever RLS is enabled — including when the only surviving policy says true. Advisors lint obvious patterns but cannot know your intent, so they stay quiet about a system that has drifted far from it. The failure is not in any component. The failure is the assumption that components failing loudly is how security erodes.

The example project: day zero, done right

Everything below runs as printed against current Postgres/Supabase — it was executed verbatim during this article's preparation. The project is fabricated (two workspaces, three users' worth of fixtures) so that results are deterministic and nobody's real data is implied.

create table workspaces (
  id uuid primary key,
  name text not null,
  owner_id uuid not null
);

create table workspace_members (
  workspace_id uuid not null references workspaces(id),
  user_id uuid not null,
  role text not null default 'member',
  primary key (workspace_id, user_id)
);

create table documents (
  id uuid primary key,
  workspace_id uuid not null references workspaces(id),
  title text not null,
  owner_id uuid not null,
  confidential boolean not null default false,
  created_at timestamptz not null default now()
);

alter table workspaces enable row level security;
alter table workspace_members enable row level security;
alter table documents enable row level security;

create policy "workspaces_member_read"
  on workspaces for select to authenticated
  using (exists (
    select 1 from workspace_members m
    where m.workspace_id = workspaces.id and m.user_id = (select auth.uid())
  ));

create policy "members_read"
  on workspace_members for select to authenticated
  using (user_id = (select auth.uid()));

create policy "documents_select"
  on documents for select to authenticated
  using (
    exists (
      select 1 from workspace_members m
      where m.workspace_id = documents.workspace_id
        and m.user_id = (select auth.uid())
    )
  );

create policy "documents_insert"
  on documents for insert to authenticated
  with check (
    exists (
      select 1 from workspace_members m
      where m.workspace_id = documents.workspace_id
        and m.user_id = (select auth.uid())
    )
    and owner_id = (select auth.uid())
  );

create policy "documents_update"
  on documents for update to authenticated
  using (owner_id = (select auth.uid()))
  with check (owner_id = (select auth.uid()));

create policy "documents_delete"
  on documents for delete to authenticated
  using (owner_id = (select auth.uid()));

-- Restrictive gate: confidential documents stay owner-only,
-- no matter what permissive logic admits.
create policy "documents_confidential_gate"
  on documents as restrictive for select to authenticated
  using (confidential = false or (select auth.uid()) = owner_id);

insert into workspaces (id, name, owner_id) values
  ('aaaaaaaa-0000-0000-0000-00000000000a', 'Acme',  '11111111-1111-1111-1111-111111111111'),
  ('aaaaaaaa-0000-0000-0000-00000000000b', 'Globex', '22222222-2222-2222-2222-222222222222');

insert into workspace_members (workspace_id, user_id) values
  ('aaaaaaaa-0000-0000-0000-00000000000a', '11111111-1111-1111-1111-111111111111'),
  ('aaaaaaaa-0000-0000-0000-00000000000b', '22222222-2222-2222-2222-222222222222');

insert into documents (id, workspace_id, title, owner_id, confidential) values
  ('dddddddd-0000-0000-0000-000000000001', 'aaaaaaaa-0000-0000-0000-00000000000a', 'acme plan',    '11111111-1111-1111-1111-111111111111', false),
  ('dddddddd-0000-0000-0000-000000000002', 'aaaaaaaa-0000-0000-0000-00000000000a', 'acme secret',  '11111111-1111-1111-1111-111111111111', true),
  ('dddddddd-0000-0000-0000-000000000003', 'aaaaaaaa-0000-0000-0000-00000000000b', 'globex plan',  '22222222-2222-2222-2222-222222222222', false);

Day-zero verification, impersonating each persona (the harness pattern is documented in our testing guide):

begin;
set local role authenticated;
select set_config('request.jwt.claims',
  '{"sub":"11111111-1111-1111-1111-111111111111"}', true);   -- Alice
select title from documents order by title;
rollback;

begin;
set local role anon;
select count(*) as anonymous_rows from documents;
rollback;

Results, as executed: Alice sees acme plan and acme secret; Bob (the other member fixture) sees only globex plan; anonymous callers see zero rows. Six permissive policies plus one restrictive gate, all matching written intent. Print this state out — the rest of the story is what six months does to it.

(One trap worth flagging while we're here: the instinctive version of members_read — letting members see colleagues' membership rows via a subquery back into workspace_members — fails with infinite recursion detected in policy, because the policy would invoke itself. It is the canonical reason Supabase projects reach for security definer helper functions, and a drift story of its own; treated fully in the search-path function risk.)

Month one: a new table ships naked

Product adds notifications. Ordinary feature work — table, indexes, API routes, done:

create table notifications (
  id bigint generated always as identity primary key,
  user_id uuid not null,
  body text not null,
  read boolean not null default false,
  created_at timestamptz not null default now()
);

insert into notifications (user_id, body) values
  ('11111111-1111-1111-1111-111111111111', 'your invoice is ready');

No enable row level security line appears anywhere in the migration. Nothing complains. In fact the feature works better for it — client queries return rows immediately, no policy fiddling required.

The verification that nobody runs, because nothing feels broken:

begin;
set local role anon;
select count(*) from notifications;   -- returns 1
rollback;

An anonymous caller — any human, script, or crawler holding your public key — can read every notification for every user. The defect is live from the instant of deploy. The mechanism is the default we covered in the evaluation model: a new table starts open, because enabling RLS is an opt-in act, and grants for client roles are provisioned by default on Supabase projects. The dashboard shows notifications sitting right next to the protected tables, indistinguishable at a glance. Full treatment of this window and its structural fixes lives at new table shipped without RLS.

Month two: a quick fix that never un-fixes

A support ticket: some enterprise customer reports seeing colleagues' draft documents. Pressure, debugging, and someone notices the select policy's membership subquery — and ships a bypass:

create policy "documents_select_any"
  on documents for select to authenticated
  using (true);

Intended as temporary ("we'll do it properly after launch"). Re-running Bob's impersonation shows the new shape of the world:

 acme plan      -- Bob can now read Acme's documents
 globex plan

Note carefully what happened and what didn't. The tautology joined the permissive OR group, so every authenticated user now matches every row — Bob gained acme plan. But acme secret stayed hidden, because the month-zero restrictive gate still intersects the union. The dashboard, asked whether documents is protected, answers yes: RLS is enabled and policies exist. Both statements are true and the situation is unacceptable anyway. This is the permissive-union mechanics from policy sprawl playing out in slow motion — and it sets up the cruelest beat of this timeline, two months ahead.

Month three: the shortcut that becomes architecture

An edge function needs a user's notification count, but JWT forwarding has a bug and the query returns empty under the user's own token. Deadline. Someone reaches for the server key that always works:

// edge function, month three
const admin = createClient(url, SERVICE_ROLE_KEY); // bypasses RLS entirely
const { count } = await admin
  .from("notifications")
  .select("*", { count: "exact", head: true })
  .eq("user_id", userId);

It works, it ships, and the function is genuinely server-side — today this is even correct-ish, since the endpoint authenticates the caller before querying. The drift is subtler: the pattern now exists. Next sprint, a different function copies the working snippet for a different endpoint, minus the authentication step. Then another. Each copy compiles, deploys, and passes functional tests, because service-role code cannot fail on policy grounds — it has none. The containment discipline that decides which paths deserve exemption, and why the exemption must stay rare, is laid out in service-role usage in edge functions.

Month-three state, summarized: one invisible-to-dashboard public table, one tautology widening internal reads, one growing family of policy-free server paths. No errors thrown yet. None coming.

Month four: the migration that deletes protection legally

Security finally gets attention: someone proposes classifying documents properly instead of the boolean flag. Refactor time — drop confidential, add a classification column:

alter table documents drop column confidential restrict;

Postgres refuses, with the exact message as executed:

ERROR:  cannot drop column confidential of table documents because
        other objects depend on it
DETAIL:  policy documents_confidential_gate on table documents
         depends on column confidential of table documents
HINT:   Use DROP ... CASCADE to drop the dependent objects too.

This error is actually the system working — the database noticing that a policy depends on the column and refusing to amputate blindly. But it blocks the migration, the developer is on deadline, and the hint is right there in the message:

alter table documents drop column confidential cascade;
-- NOTICE:  drop cascades to policy documents_confidential_gate

One word, added in good faith, deletes the only restrictive policy on the table. Migration succeeds, tests pass, feature works. From this moment, month two's using (true) tautology stands unopposed: every authenticated user can read every document. Policy count on documents: five, down from six — and the one that died was the only thing keeping month two contained.

The general lesson outranks the specific case: policies have dependencies, and dependency-breaking migrations destroy them as a side effect. Renames update cleanly; drops require CASCADE; recreating a table (drop + create) discards every policy and the RLS flag itself. The recurring patterns and their safe alternatives are catalogued in migrations that weaken policies.

Months five and six: rollback, then the audit

Month five brings a bad deploy and a 2 a.m. restore from a snapshot taken before the classification refactor — and before several other things nobody thinks about, because restores restore state, and authorization posture is part of state. Simulated here by its net effect on one table:

alter table documents disable row level security;

Verification, for the third time in this story, being the thing that isn't run:

begin;
set local role anon;
select count(*) from documents;   -- returns 3
rollback;

Anonymous callers now read the entire documents table — the worst state of the timeline, reached without a single hostile actor or a single error message.

Month six, an engineer finally runs the honest manual audit — the same introspection any operator can do today (full walkthrough):

-- Tables and their RLS flags:
select c.relname, c.relrowsecurity as rls_enabled
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r'
order by c.relname;

-- Surviving policies:
select tablename, policyname, cmd
from pg_policies
where schemaname = 'public'
order by tablename, policyname;

As executed, the truth:

Six months, zero incidents reported, zero exceptions raised — and an authorization model that shares almost nothing with day zero except table names. That is what "RLS policies don't fail, they drift" means in practice.

What would have caught each step

Every event above had a cheap detector available at the moment it happened:

Notice the structure: no single technique covers the whole surface. CI assertions miss runtime restores; tests cover only tables someone remembered to test; probes see outside-facing truth but not internal widening; humans reliably catch none of it at 2 a.m. Defense here is layered the same way the authorization itself is — each layer covering the others' blind spots.

And the layers must run continuously, because the timeline's deepest lesson is cadence: this project's authorization-relevant state changed roughly monthly, without anyone ever deciding to change authorization. Whatever verification rhythm you choose sets the maximum age of your isolation guarantees. Making that guarantee continuous rather than annual is precisely the gap RowShield occupies — scan first, remediate from proposed SQL reviewed by you, then monitor so the sixth month of some future timeline ends differently.

That reframing is also the constructive answer to the two dead ends teams reach for when they finally see their own timeline: panic (rip it out and rebuild) and fatalism ("this is just how Supabase is"). Neither follows from the evidence. The platform provides sound primitives — default-deny evaluation, dependency tracking, role separation, signed tokens. What those primitives do not provide is memory: nothing remembers what your authorization was supposed to look like and diffs reality against it. That job belongs to operations, and operations problems have known solutions: make the intended state explicit, check it mechanically at every change, and alert on transitions rather than hoping someone notices stasis gone wrong. The alternative — reviewing policies once and trusting time — is the exact bet this timeline made, month after month, each bet individually reasonable, the sum indefensible.

Common questions

Is this scenario exaggerated?

Every individual step is a pattern we document independently because it recurs constantly: new-table-without-RLS, placeholder tautologies, service-key spread, cascade-dropped policies, restore rollbacks. The compression — six in half a year — is narrative pacing, not implausibility; active projects ship all six classes of change routinely.

Would Supabase's advisor linter have caught these?

Parts. Advisor lints flag disabled RLS and some tautology shapes at lint-run time. They cannot know that a legal-looking CASCADE removed the policy enforcing your compliance posture, nor that a service-key path grew three new call sites. Lints check syntax-shaped facts; drift is semantic — distance from intent.

How often should a team re-verify authorization state?

Proportionally to change frequency. The project above changed its authorization-relevant state roughly monthly without anyone intending to. If migrations ship weekly, annual audits measure last year's product. Continuous checks exist because change cadence, not calendar, sets the verification cadence.

Does FORCE ROW LEVEL SECURITY fix any of this?

It closes one specific gap — owner-path bypass — and is worth enabling deliberately on tables where even privileged mistakes must respect isolation. It does nothing about months 1, 2, 3, or 5 of this story. Tools, not tools alone.

We're a two-person startup. What's the minimum viable anti-drift setup?

Three items, an afternoon: enable RLS on every table as a standing migration rule; add the two-line catalog assertion to CI so any table shipping naked fails the build; and re-scan production monthly — a free scan catches what CI can't see, like restores, dashboard hotfixes, and everything else that happens outside the pipeline.

---

See your project's current position on this curve: run the free scan — paste your app URL, and read findings with proposed remediation SQL before anything drifts further.

RowShield is an independent product and is not affiliated with, endorsed by, or sponsored by Supabase, Inc.