Multi-tenant SaaS with Row-Level Security

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) removes that failure by attaching the rule to the table itself, for every role the policy applies to. One exception, the table owner, is covered below. Here's a complete, working setup on WattleDB.

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 every query from a role the policy applies to, 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 current_setting('request.jwt.claims', true). One connection type works differently: see the table owner below.

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 = (current_setting('request.jwt.claims', true)::json ->> 'tenant_id')::uuid)
  with check (tenant_id = (current_setting('request.jwt.claims', true)::json ->> '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, as long as the JWT secret stays on your server. Never put it in a browser or mobile app: anyone holding it can mint a token for any tenant. Set the tenant_id claim from your own user records, never from what the client sends.

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 no rows and no error, because the row is invisible under the policy. That's the behaviour you want: no leak, no information about what exists outside your tenant.

The one exception: the table owner

PostgreSQL does not apply a table's policies to the table's owner. On WattleDB the owner is the database user app, which owns the tables you create as app. Tables created by a migrations role are owned by that role unless it runs set role app first. Superusers and roles with BYPASSRLS also skip policies; WattleDB gives neither to customer roles. These connections skip your policies unless you take the steps below:

• the app user itself, which is what your console DATABASE_URL and DIRECT_URL connection strings and the console's SQL editor use;
• any role that is a member of app, including every role made with the Owner (migrations) template.

Roles made with the Read-only or Read & write templates are not owners, so policies do apply to them. So do the REST API roles anon and authenticated. A server app that connects as app and queries your tables sees every tenant's rows. WattleDB does not turn RLS on for you either: you enable it per table and write the policies.

There are two fixes. We recommend (b).

(a) Force RLS on the table. This makes the owner obey policies too:

alter table projects force row level security;

The owner is then subject to any policy that names it, so add one for the roles that connect directly. A table with RLS forced and no policy for the owner returns no rows to the owner. Forcing RLS also applies to the owner's own migrations, backfills and seed inserts, and to pg_dump. Your own pg_dump works on that table only if a policy lets app see every row and you run it with --enable-row-security. WattleDB’s masked clones, which are paused for now, connect as app and can’t add that option, so a masked clone of a database with a forced table fails, whatever policies you add. That's why we recommend fix (b).

(b) Recommended: connect your server app with a Read & write role (or Read-only for reports) instead of app, and set the tenant yourself inside each transaction. Use set_config with true so the setting lasts only for that transaction. A plain SET is wrong here because DATABASE_URL is a transaction-mode pooler, and session settings can leak between clients:

begin;
select set_config('app.tenant_id', $1, true);
-- your queries
commit;

With psycopg, use an autocommit connection and with conn.transaction(): around the same statements. Then add a policy for that role:

create policy tenant_isolation_server on projects for all to <your_rw_role>
  using      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid)
  with check (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

Replace <your_rw_role> with the name of the Read & write role you created in the Roles tab. The nullif is there because after the transaction ends the setting reads as an empty string rather than null, and casting an empty string to uuid would raise an error. With nullif the policy matches nothing, so a request with no tenant set sees no rows and can't insert any.

Take the tenant from your server's verified session or token, never from a request body, header or URL. RLS enforces the tenant you set; it can't tell whether you set the right one.

Check it yourself. See whether RLS is on and forced:

select relname, relowner::regrole, relrowsecurity, relforcerowsecurity
from pg_class where oid = 'public.projects'::regclass;

Then connect as each role you use, with the Roles tab connection strings, and count the rows you can see with two tenants in the table. Check your own tables with this query.

Beyond tenant isolation

The same mechanism scales down to per-user ownership (owner_id = current_setting('request.jwt.claims', true)::json ->> '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.