Postgres row-level security

Short version: row-level security makes Postgres itself decide which rows a query may see or change. You enable it per table and attach policies. Done properly it means a forgotten WHERE clause in application code cannot leak another tenant's data.

The problem it solves

In a multi-tenant app, every query needs the same filter: only this account's rows. Written in application code, that filter has to be correct in every query, forever, including the one someone adds at 6pm on a Friday.

It only has to be missed once. RLS moves the rule into the database, so the filter applies whether or not the query remembered it.

Turning it on

-- 1. enable, and force it for the table owner too
alter table orders enable row level security;
alter table orders force row level security;

-- 2. without a policy, nothing is visible. add one.
create policy "tenant reads own orders"
  on orders for select
  using (tenant_id = current_setting('app.tenant_id')::uuid);

create policy "tenant writes own orders"
  on orders for insert
  with check (tenant_id = current_setting('app.tenant_id')::uuid);

Two details worth knowing. force row level security matters because without it the table's owner bypasses policies, and that is frequently the role your migrations run as. And using versus with check are not the same: using decides which existing rows are visible, with check validates rows being written. A policy with only using will happily let someone insert a row belonging to another tenant.

Audit what you have in 10 seconds

Before writing any policies, find out where you actually stand:

select
  (select count(*) 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) as tables_without_rls,
  (select count(distinct table_name)
   from information_schema.role_table_grants
   where table_schema = 'public'
     and grantee in ('anon', 'authenticated')) as tables_granted_to_public;

Both numbers should be near zero. If the second one is high, RLS alone will not save you, which is the next section.

Gotcha 1: RLS does not revoke privileges

This is the one that cost me. Enabling RLS filters rows. It does not remove a role's table privileges. A public role can still hold SELECT, INSERT, UPDATE, DELETE, even TRUNCATE, on tables you thought were protected.

On one of my own apps I ran the audit above and found the anonymous key, which ships in the browser bundle and is therefore public, could read real customer names, phone numbers, and addresses, and held delete rights on every table in the schema. That is a live data leak, not a hardening task for next quarter.

So do both:

revoke all on all tables in schema public from anon;
grant select on public.published_posts to anon;  -- grant back only what is public

Then prove it. Hit your own API with nothing but the public key and try to read the table you care about. A theoretical audit convinces nobody, including you.

Gotcha 2: a refused write is not an error

If you use PostgREST or Supabase, an UPDATE or DELETE that RLS filters out does not raise. It returns success with zero rows affected. So this code is silently wrong:

const { error } = await supabase
  .from('tasks').update({ active: false }).eq('id', id);
if (error) throw error;   // never fires. the row is still there.

The user taps delete, the sheet closes, the list reloads, and the row is still sitting there with no explanation. Optimistic UI makes it worse: the row vanishes locally and reappears on the next load.

Select the affected rows back and check the count:

const { data, error } = await supabase
  .from('tasks').update({ active: false }).eq('id', id).select('id');
if (error) throw error;
if (!data?.length) throw new Error('You can only change items you created.');

This bites hardest where an UPDATE is your delete path. If you archive with active: false, writing a policy that only guards DELETE leaves the real deletion path wide open.

Gotcha 3: index the columns your policies filter on

A policy is a predicate bolted onto every query touching that table. If it filters on tenant_id and that column has no index, every query becomes a sequential scan and the table gets slower as it grows.

create index concurrently on orders (tenant_id);

When people say RLS is slow, this is almost always what they actually hit.

The order that does not break things

1. Audit first. Run the query above and write the numbers down.
2. Check how your app reads. If every query goes through server routes on a service-role key, RLS will not break anything, because that key bypasses policies. If the browser queries the database directly, you need policies before you enable.
3. Enable and force, table by table. Not all at once.
4. Write policies, then revoke grants. Both halves, in that order.
5. Verify as an attacker. Public key, real endpoint, try to read something you should not.
6. Index the policy columns.

FAQ

What is row-level security?

Row-level security (RLS) is a Postgres feature that filters which rows a query can see or change, based on policies you attach to each table. Instead of every query needing a correct WHERE clause, the database enforces the rule itself, so a forgotten filter in application code cannot leak another tenant's data.

Does enabling RLS break my app?

It can, and that is the point. Enabling RLS with no policies denies everything, so any query that was relying on unrestricted access stops working. Server-side code using a service-role key bypasses RLS entirely, so apps that read through server routes usually survive. Apps querying the database directly from the browser need policies written first.

Is RLS enough on its own?

No. RLS filters rows but does not remove table privileges. A role can still hold GRANTs it should not have, and enabling RLS without revoking those grants leaves a gap. Do both: enable and force RLS, then revoke the privileges the public role never needed.

Does RLS slow down queries?

A policy is a predicate added to your query, so it costs roughly what the same WHERE clause would cost. The usual performance problem is not RLS itself but an unindexed column inside the policy: if your policy filters on tenant_id, that column needs an index or every query does a sequential scan.

Why did my delete succeed but nothing was deleted?

Because an UPDATE or DELETE that RLS filters out is not an error. PostgREST returns success with zero rows affected, which is indistinguishable from a successful write if you only check for an error. Always select the affected rows back and check the count.

If you have an app in production and are not sure where it stands, the audit query above takes ten seconds and is worth running today. If you would rather someone else went through it properly, that is part of what I do.