Multi-tenant SaaS on Postgres: row-level security done right

The scariest bug in multi-tenant SaaS is one missing WHERE tenant_id = ?. One forgotten clause in one endpoint, and customer A sees customer B's invoices.

Postgres row-level security (RLS) moves tenant isolation from "every developer remembers, every time" into a guarantee enforced by the database. Here's how to do it properly.

The basic pattern

-- Every tenant-owned table has tenant_id
alter table invoices enable row level security;
alter table invoices force row level security;

create policy tenant_isolation on invoices
  using (tenant_id = current_setting('app.tenant_id')::uuid)
  with check (tenant_id = current_setting('app.tenant_id')::uuid);

• using filters which rows can be read, updated or deleted
• with check stops writes that would put a row in another tenant
• force applies policies to the table owner too. Without it, the owner role bypasses RLS, which is a common surprise

Setting tenant context safely

Your application sets the tenant per request, inside a transaction:

begin;
select set_config('app.tenant_id', $1, true); -- true = local to this transaction
-- ... queries ...
commit;

Why "local" matters: with a connection pooler such as PgBouncer in transaction mode, connections are shared between requests. A session-level setting can leak into another tenant's request. SET LOCAL or set_config(..., true) dies with the transaction.

In ORMs (Prisma, Drizzle, Kysely and others), wrap each request's queries in a transaction helper that sets the context first, and make it the only way to get a database client.

Roles: the part people get wrong

• The application should connect as a non-owner role without BYPASSRLS
• Superusers and roles with BYPASSRLS ignore every policy. Keep them for migrations and admin only
• Background jobs that work across tenants should use a separate, audited role, or set the tenant context per job

Supabase specifics

On Supabase, clients query the database directly through the API, so RLS is not optional. Every exposed table needs it.

create policy "members read projects" on projects
  for select using (
    tenant_id in (
      select tenant_id from memberships
      where user_id = (select auth.uid())
    )
  );

Wrapping auth.uid() in (select ...) lets Postgres evaluate it once per query instead of once per row, a large speed-up on big tables. Our Postgres vs Supabase comparison covers when Supabase is the right base.

Keep it fast

• Index tenant_id, usually as the first column of composite indexes: (tenant_id, created_at)
• Keep policies simple. Avoid per-row function calls and deep joins in using
• For membership checks, consider a security-definer function that returns the user's tenant IDs, marked stable
• Check explain analyze with RLS active, not as a superuser, or you're measuring a different plan

Watch out for these

• Views run with the view owner's rights by default and can bypass RLS. On Postgres 15+, create them with (security_invoker = true)
• Functions marked security definer bypass RLS. Use them deliberately and sparingly
• Unique constraints are global: a unique email column will leak across tenants via "already taken" errors. Scope it as unique (tenant_id, email)
• Sequences and counts can reveal other tenants' activity. Use UUIDs for public IDs

Test it like a feature

Automated tests should prove isolation:

1. Create tenants A and B with data
2. Set the context to A; assert that no B rows are visible through any table or view
3. Attempt to insert or update a row with B's tenant_id while in A's context; assert that it fails
4. Run the suite in CI on every migration

These tests are also excellent evidence for SOC 2 and enterprise security reviews.

Bottom line

RLS turns tenant isolation from a code-review hope into a database guarantee. Enable and force it, set context per transaction, index tenant_id and test isolation in CI. If your app was AI-generated, check RLS first. It's the top issue in our vibe-coded MVP rescues.

Our custom software team designs multi-tenant Postgres schemas and retrofits RLS into existing SaaS products. Talk to an engineer or see sprint pricing.