Row Level Security: pushing tenant isolation down to the database

When you build a multi-tenant system, the scariest failure is not a crash. A crash is visible, it sits in the logs, you fix it. The real fear is different: in one query you forget to write WHERE organization_id = ?, nothing breaks, the system looks perfectly healthy — and one customer sees another customer's data.

While building a school management system I lived with that risk for a long time. Every table has an organization id, and every query has to include it. One forgotten place is enough. And the data is serious: student rosters, grades, attendance, parent contacts.

This article is about moving that problem out of the application layer and down into the database layer.

The problem: discipline is not protection

The usual solution looks like this: every repository method takes an organizationId, and passing it is required everywhere.

It works. More precisely, it works as long as everyone writes it correctly.

The trouble is that this relies on discipline rather than on a mechanism. A new developer joins, an urgent fix is needed, somebody writes a quick SELECT — and the protection has a hole. Worse: the hole opens silently. No error, no alert.

The rule is simple: if your security depends on a human writing the right thing every single time, it is not security, it is hope.

What RLS does

PostgreSQL's Row Level Security takes the condition off the query and attaches it to the table itself. Once enabled, the database returns only the rows the policy allows — no matter who wrote the query or how.

The simplest form:

alter table students enable row level security;

create policy students_tenant_isolation on students
  using (organization_id = current_setting('app.organization_id')::uuid);

Now even select * from students returns only rows for the current organization. Forgetting the WHERE no longer leaks data — it just produces an empty result.

The context is set per connection:

set local app.organization_id = '90e60e0c-4cb7-433f-b968-5f718ed3133a';

set local is an important detail. It holds the value only inside the current transaction. If you use a plain set, the value stays on the connection, and connections get reused through a pool — so the next query, in another customer's context, sees the wrong organization. This is the quietest and most dangerous RLS mistake.

Writes need restricting too

The using clause restricts reads only. To close writes you need with check:

create policy students_insert on students
  for insert
  with check (organization_id = current_setting('app.organization_id')::uuid);

Without it a user can insert a row carrying someone else's organization id — they will not see it afterwards, but the data lands in another customer's table. That is the kind of bug that stays unnoticed for a long time, especially in reports.

Two serious traps

The first: the table owner is not subject to RLS. If migrations and application queries run under the same role, RLS is enabled, policies are written, and in practice nothing is restricted. The application must connect with a separate, non-owning role.

The second is force row level security. The docs say it "forces the owner too", and at first glance that looks like the right move. But if you have security definer functions, they execute as the function owner, and force blocks them as well. I ran into this while building the comment system for my blog: everything worked locally, and after adding force every write function failed with a permission error.

The rule: enable force only if you have no security definer functions.

The no-policy variant

Policies are not always the answer. In the database that stores view counts and comments for my blog I took a different route: RLS enabled, with no policies at all.

The result is interesting — with no policies, RLS grants nobody anything. Anyone connecting directly with the publishable key gets zero permissions. All queries instead go through a server-side route handler under the service role.

This is real isolation too, just in a different model: access to the data goes through a single door, and RLS closes the side entrances.

The choice comes down to this: if clients connect to the database directly, write policies. If every query goes through your server, leaving RLS in its "everything closed" state is simpler and more reliable.

How to test a policy

After writing RLS you must verify it, because a mistake here fails silently. The most useful test shape I have found is two organizations, one query:

begin;
set local app.organization_id = 'aaaa...';
select count(*) from students;  -- only A's students
rollback;

begin;
set local app.organization_id = 'bbbb...';
select count(*) from students;  -- only B's students
rollback;

If both counts are right and they add up to the total, isolation is working.

The second important test is the no-context query. Send a query without setting app.organization_id at all. In a correctly configured system it errors out or returns nothing. If it returns the whole table, the policy is not actually being applied — most likely you are running as the table owner.

Does it cost anything

It does, but not in the way most people assume. The RLS condition is added to the query and the planner treats it like an ordinary WHERE. So if there is an index on organization_id, it gets used.

A practical tip: in multi-tenant tables, put the organization column first in composite indexes. (organization_id, created_at) is almost always more useful than (created_at, organization_id), because every query filters by organization anyway.

Conclusion

RLS is not magic and it does not replace checks in your application layer. It gives you one specific thing: it makes tenant isolation impossible to forget.

The rollout order is this. First, connect the application with a non-owning role. Then enable RLS on your single most sensitive table and write a policy with both using and with check. Pass the context via set local, always inside a transaction. Write the two-organization test and check the no-context case as well. Once you trust it, roll it out to the remaining tables.

And before touching force row level security, check whether you have security definer functions. That one line can stop your entire write path.