How to Set Up Row-Level Security Without Locking Yourself Out
Row-level security moves your authorization rules out of application code and into the data layer, so that a query which forgets its `WHERE user_id = ?` clause returns nothing instead of everything. That is a genuinely different safety property from middleware checks, and it's why it's worth the trouble.
The trouble is real, though. RLS is the feature most likely to be enabled once, break something confusing, and get switched off in frustration. Usually because the thing it broke was the developer's own admin script, and the failure mode — queries silently returning zero rows rather than erroring — is uniquely bad for debugging.
RLS is worth it when the blast radius of a missed filter is large
Database-enforced authorization costs you complexity in tooling and testing. That trade is clearly correct for multi-tenant data and clearly overkill for a single-user app.
- ›Multi-tenant SaaS where one customer seeing another's rows is an incident: use RLS, no debate.
- ›You have more than one client writing to the database (web app, mobile, background jobs, a Retool instance): use RLS, because middleware only protects the paths that go through the middleware.
- ›Your frontend talks to the database directly (Supabase, Firebase): RLS isn't optional, it's the only thing standing between users and each other's data.
- ›Single-tenant internal tool with one server-side codebase: application-layer checks are fine and much easier to debug.
The approaches
Postgres RLS (Supabase, Neon, RDS, self-hosted)
// best for: Multi-tenant Postgres apps, especially ones where clients query the database directly.
- Supabase
- PostgreSQL
- Nhost
- PlanetScale
- Railway
- Render
Postgres RLS attaches policies to tables. Once `ENABLE ROW LEVEL SECURITY` is on, every query from a non-superuser is implicitly filtered by the policies that apply to it. Supabase wires this to the JWT so `auth.uid()` returns the signed-in user inside SQL.
The part that trips people up is that RLS is deny-by-default once enabled, and policies are permissive by default when combined — multiple policies for the same action are OR'd together, not AND'd. So adding a second policy to a table almost always loosens access rather than tightening it, which is the opposite of what most people assume the first time.
Separate policies per operation matter too. A `FOR SELECT` policy doesn't restrict updates; you need `FOR UPDATE` with both `USING` (which rows can be targeted) and `WITH CHECK` (what they're allowed to become). Omitting `WITH CHECK` on an update policy is how users end up able to reassign their own rows to another tenant.
alter table documents enable row level security;
-- USING controls which rows you can touch.
-- WITH CHECK controls what they're allowed to become.
-- Omit WITH CHECK and a user can move their row to another tenant.
create policy "tenant isolation"
on documents for update
using (tenant_id = current_setting('app.tenant_id')::uuid)
with check (tenant_id = current_setting('app.tenant_id')::uuid);The gotcha
RLS does not apply to the table owner or superusers, which means your migrations and admin scripts bypass it entirely — and so does anything connecting with the service-role key. Teams test with the service key, see everything work, ship, and only then discover no real user can read anything. Always test policies with a non-privileged role.
Firestore / Realtime Database security rules
// best for: Firebase apps where the client SDK talks to the database directly.
- Firebase
Firebase's rules are a separate declarative language evaluated on every read and write. They're structurally similar to RLS in intent but differ in one expensive way: rules can read other documents to make a decision, and each of those reads is billed and counts toward latency. A rule that checks a user's role by fetching their profile document runs that fetch on every single query.
The usual fix is denormalization — copy the role onto the document itself, or into custom claims on the auth token, so the rule can decide without a lookup. Custom claims are the cleaner path because they're in the token the client already sent, but they're capped at 1000 bytes and only refresh when the token does, so a permission revocation can lag by up to an hour unless you force a refresh.
The gotcha
Rules are not filters. A query that a rule would reject doesn't return a filtered subset — it fails entirely. This means you cannot write `collection('docs').get()` and expect to receive only the documents you're allowed to see; you have to write the query so it matches the rule, and the rule verifies the query rather than trimming the result.
Application-layer scoping
// best for: Single codebase, server-side only, where you control every query path.
- Django
- Ruby on Rails
- Laravel
- Express.js
- NestJS
- Prisma
- Drizzle
The pragmatic approach: a base query scope that every query is built from. Rails has `default_scope` and association-based scoping; Django has custom managers; with an ORM like Prisma or Drizzle it's usually a repository function that takes the tenant and never exposes the raw client.
This is genuinely fine when there's exactly one codebase touching the database and code review reliably catches unscoped queries. It stops being fine the moment a second consumer appears — a background worker, an analytics job, a no-code tool someone connected — because those don't go through your scope.
If you take this path, make the unsafe thing hard to reach. Don't export the raw database client from your data module at all; export only scoped accessors. An unscoped query should require deliberately importing something named to make you think twice.
The gotcha
`default_scope` in Rails applies to reads but interacts badly with `unscoped`, joins, and `delete_all`, and it's inherited in ways that surprise people. Most teams that adopt it end up removing it. Explicit scoping at the repository boundary is more verbose and far more predictable.
Common pitfalls
Testing policies with the service-role key
The service key bypasses RLS by design. If your integration tests use it, they prove nothing about your policies. Write at least one test per table that connects as an ordinary user and asserts it cannot see another tenant's row — that test is the entire point of having RLS.
Forgetting that RLS makes missing rows look like empty results
A denied read returns zero rows, not an error. So an RLS misconfiguration presents identically to 'there is no data yet,' which sends people debugging their seed scripts for hours. When something returns empty unexpectedly, check policies before you check the data.
Policies that don't use an index
An RLS policy is a predicate added to every query against the table. If `tenant_id` isn't indexed, you've just added a sequential scan to every read in the application. Index every column referenced in a policy, and check the query plan after enabling RLS — the plans change.
Enabling RLS without any policy
`ENABLE ROW LEVEL SECURITY` with no policies denies everything to non-owners. Done in production, this takes the application down instantly while looking like a database outage. Write the policies first, then enable.
Questions people actually ask
Does RLS slow down queries?
It adds a predicate to every query, so the cost depends entirely on whether that predicate is indexed. With an index on the tenant or user column, the overhead is usually negligible. Without one, it's a full scan on every read. Always check `EXPLAIN` output before and after enabling it.
Can I use RLS and application-layer checks together?
Yes, and you should. RLS is the backstop that catches the query someone forgot to scope; application checks give better error messages and let you fail fast before hitting the database. They're defense in depth, not alternatives.
Why do my admin scripts see everything despite RLS?
Table owners and superusers bypass RLS unless you explicitly set `FORCE ROW LEVEL SECURITY` on the table. Service-role connections in Supabase bypass it too. That's intentional — it's how migrations work — but it means privileged connections need to be treated as trusted code paths.