Every multi-tenant SaaS shares one hard rule: a customer must only ever see their own data. The naive way to enforce it is a where tenant_id = ? on every query — and it works right up until the one query where someone forgets. PostgreSQL Row-Level Security (RLS) makes that impossible by attaching the rule to the table itself. Here's a complete, working setup on WattleDB.

The idea in one line

  • Put a tenant_id on your rows.
  • Enable RLS and write a policy that ties rows to the caller's JWT.
  • The database filters every query — app code can't bypass it.

Why app-level checks aren't enough

Guarding isolation in application code means the guarantee is only as strong as your least careful query — an admin endpoint, a background job, a hastily added report. RLS inverts the default: instead of remembering to restrict every query, the database restricts them all, and you'd have to deliberately opt out. Safe by default beats safe by discipline.

The roles

WattleDB's REST API authenticates requests and runs them as one of two PostgreSQL roles: anon for unauthenticated callers and authenticated for anyone presenting a valid JWT. Your policies target these roles, and the JWT's claims are available inside the database via auth.jwt().

Enable RLS and write the policy

Say every row belongs to a tenant. Turn RLS on for the table, then add a policy that only lets a caller touch rows whose tenant_id matches the tenant_id claim in their token:

alter table projects enable row level security;

create policy tenant_isolation on projects
  for all
  to authenticated
  using      (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid)
  with check (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

using controls which rows are visible to reads, updates and deletes; with check controls which rows an insert or update is allowed to write. Setting both to the same condition means a caller can neither see nor create rows outside their tenant.

Where the tenant comes from

The tenant_id claim lives in the JWT you issue at login. A minimal payload looks like:

{
  "role": "authenticated",
  "tenant_id": "8f3b...c2",
  "sub": "user_123",
  "exp": 1750000000
}

Because the token is signed, the tenant can't be forged, and because RLS reads it server-side, the client never gets to choose which tenant it queries.

Prove the isolation holds

Don't take it on faith — test it. Insert rows for two tenants, then query as each and confirm you only ever see your own:

-- as tenant A's token: returns only A's rows
select count(*) from projects;
-- attempt to read a known row id from tenant B: returns 0 rows
select * from projects where id = '<tenant-B-row>';

The second query returns nothing — not an error, just no rows — because the row is invisible under the policy. That's the behaviour you want: no leak, no information about what exists outside your tenant.

Beyond tenant isolation

The same mechanism scales down to per-user ownership (owner_id = auth.jwt() ->> 'sub') and up to role-based rules (a policy that also allows an admin claim to see everything within a tenant). Because it's plain PostgreSQL, none of it is proprietary — your policies move with your database if you ever do.