Multi-tenant SaaS with PostgreSQL row-level security

Every SaaS platform eventually answers one question: how do we make sure one customer can never see another customer's data? There are three common models, each with trade-offs.

For platforms serving many small organisations, the shared-schema model is usually the economical choice. Its risk is obvious: every query must filter by tenant. Forget once, and data leaks. PostgreSQL row-level security (RLS) lets the database enforce that filter for you.

Setting the tenant per transaction

The application decides which tenant a request belongs to — from the hostname, a header or a claim in the access token — and passes it to PostgreSQL as a transaction-local setting. Because the setting is local to the transaction, it cannot leak across pooled connections.

BEGIN;
SELECT set_config('app.tenant_id', $1, true); -- true = local to this transaction
-- every query in this transaction now sees only this tenant's rows
COMMIT;

Defining the policy

Each tenant-owned table gets a policy comparing its tenant column with the current setting, for both reads (USING) and writes (WITH CHECK).

ALTER TABLE students ENABLE ROW LEVEL SECURITY;
ALTER TABLE students FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON students
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Roles: who bypasses and who does not

• Migration role — owns the schema, runs DDL, used only by the deploy pipeline.
• Application role — DML only, subject to every policy.
• Platform/admin jobs — a separate, audited role for cross-tenant operations such as billing, with no access to personal data where possible.

Testing isolation like a feature

Isolation deserves explicit tests. Create two tenants with similar data, run each API endpoint as tenant A and assert that nothing from tenant B appears — including in counts, exports and search. Also test writes: inserting a row with another tenant's ID must fail.

Performance considerations

RLS predicates are added to every query, so indexes should start with the tenant column — for example (tenant_id, created_at). Large tables such as attendance or audit logs benefit from partitioning by time once they grow.

What RLS does not solve

• Caches keyed without the tenant — Redis keys must include it.
• Files in object storage — prefix keys by tenant and sign URLs.
• Background jobs — set the tenant context in workers too.
• Analytics exports — apply the same scoping in reporting queries.

RLS is not a substitute for application checks; it is the safety net under them. Used together, they turn a single forgotten WHERE clause from a breach into a failing test. We use this pattern in our own Institution OS platform.