Five Postgres RLS mistakes that leak tenant rows

Teams adopt Postgres Row Level Security because it pushes tenant isolation into the database, where application bugs cannot forget it. That is the right instinct. The failure mode is worse than a missing WHERE clause: RLS can be present, policies can exist, every test can pass, and application roles can still read another tenant's rows.

This post walks five mistakes that produce exactly that outcome. Each one comes from a real policy shape. For each, you get the vulnerable SQL, what a behavioral probe shows, and the fixed policy.

You enable RLS on the tables that "matter" and leave the rest alone. But if a table is granted to an application role and RLS is off, the grant is the only boundary — and it is an all-rows boundary.

-- The grant says: this role may read orders.
grant select on rls_doctor_demo.orders to authenticated;

-- RLS was never turned on. No policy is even consulted.
-- authenticated can select every row in the table.

A probe makes this concrete. Impersonating authenticated with two different owner_id subjects returns both users' rows:

rls_doctor_demo.orders
  authenticated@user-1   rows  sampled 2 row(s), 1 foreign row(s), 1 own row(s) by owner_id
  authenticated@user-2   rows  sampled 2 row(s), 1 foreign row(s), 1 own row(s) by owner_id

- [high] probe-cross-owner-read rls_doctor_demo.orders: Role authenticated
  reads rows across 2 distinct owner_id values

alter table rls_doctor_demo.orders enable row level security;

create policy "users read own orders"
  on rls_doctor_demo.orders
  for select
  to authenticated
  using (owner_id = (select auth.uid()));

Do this for every table an application role can reach, including join tables and audit logs. A single unguarded table is a tenant leak.

This is the most common leak in Supabase projects. A policy exists, RLS is enabled, and the policy allows everyone to read everything.

create policy "anyone can read profiles"
  on rls_doctor_demo.profiles
  for select
  to public
  using (true);

Two separate problems stack here. First, to public reaches every role that can use the schema, including anon if it has USAGE. Second, using (true) places no row-level condition on the result set.

The fix is not "add a policy." The fix is to name the audience and constrain the predicate:

alter policy "anyone can read profiles"
  on rls_doctor_demo.profiles
  to authenticated
  using ((select auth.uid()) = id);

Public content is a real category. If a row is genuinely public, scope the policy to the columns or a published flag — do not use an unconditional predicate on a multi-tenant table.

A USING clause decides which rows a command may see. It does not decide which rows a command may write. Without WITH CHECK, an updater can move a row into another tenant.

create policy "anon can update profiles"
  on rls_doctor_demo.profiles
  for update
  to public
  using (true);   -- can update any row ...
                  -- ... and no WITH CHECK: can set owner_id to anyone

The attack does not need a bug in the application. A normal profile-edit endpoint that accepts an owner_id field is enough. The row passes USING, the new values are never checked, and the tenant boundary moves.

alter policy "anon can update profiles"
  on rls_doctor_demo.profiles
  to authenticated
  using ((select auth.uid()) = id)
  with check ((select auth.uid()) = id);

Audit rule: every INSERT and UPDATE policy needs an explicit WITH CHECK. If the write path cannot be described as a predicate over the new row, the policy is incomplete.

RLS does not protect TRUNCATE. Not partially. At all. If an application role can truncate a table, it can empty another tenant's rows regardless of every policy on that table.

grant select, update, truncate
  on rls_doctor_demo.profiles to authenticated;

-- Every policy on profiles is irrelevant for TRUNCATE.
truncate rls_doctor_demo.profiles;  -- succeeds, wipes every tenant

Application roles should never hold TRUNCATE. Reserve it for a maintenance role that is not reachable from the app connection string:

revoke truncate on rls_doctor_demo.profiles from authenticated;
grant truncate on rls_doctor_demo.profiles to maintenance_role;

The same class of problem applies to default privileges. If a default privilege grants INSERT to authenticated on a schema, every future table created in that schema starts with that grant — before anyone writes a policy.

revoke all on tables in schema rls_doctor_demo from authenticated;
alter default privileges in schema rls_doctor_demo
  revoke all on tables from authenticated;

By default, the table owner is exempt from RLS. If your application connects as the owner — the role that ran the migrations — every policy you wrote is decorative.

This is silent. Tests pass because the test connection is the owner. Production leaks because the production connection is also the owner. Superusers and BYPASSRLS roles bypass RLS in every case; FORCE RLS closes the owner path only.

alter table rls_doctor_demo.profiles force row level security;

Pair this with a separate application role. The app connects as authenticated, not as the migration owner. FORCE RLS is then the second lock, not the first.

A catalog read — policies, grants, role memberships, defaults — catches all five mistakes. That is worth doing in CI. It is also bounded.

• Catalog inspection cannot see application-level authorization. A policy can be correct and the API can still return another tenant's rows through a join the policy never touches.
• Hosted platform settings, views, and SECURITY DEFINER functions need a separate review. Views especially: a security_barrier view can widen access beyond the base table's policies.
• Row counts in a probe depend on the fixture. A probe that sees zero cross-owner reads on empty tables proves nothing. Seed two owners before you trust a green result.

Use both layers. Catalog analysis answers "are the policies shaped safely?" Behavioral probing answers "can this role actually read another tenant's rows right now?"

The whole point of these mistakes is that they survive code review. A repeatable check is the only durable fix. Two commands cover the static and behavioral layers:

# Static: catalog metadata only. Read-only. No data touched.
npx rls-doctor check --schema public --fail-on high

# Behavioral: impersonate app roles in a rolled-back transaction.
npx rls-doctor probe --app-roles authenticated --owner-columns owner_id

Run check on every pull request that touches migrations or policies. Run probe against a seeded staging schema before release. Save a baseline so CI fails on new findings, not on known ones you have already decided to accept.

These two commands are what rls-doctor does. You can get the same result by querying pg_policies and pg_class by hand; the CLI exists so the check is one line and the output is the same every time.