Almost every SaaS product has to answer one early question: how do we keep one customer's data away from another's? Get it wrong and you have a data leak. Get it overcomplicated and you slow down every feature.

This post covers the common options and walks through one of the most practical: a shared schema protected by Postgres row-level security (RLS).

The three common tenancy models

Model Isolation Cost and ops Good for
Shared schema (tenant_id column) Logical Cheapest, simplest to run Many small tenants
Schema per tenant Stronger Migrations multiply Tens to hundreds of tenants
Database per tenant Strongest Most expensive Compliance-heavy or large customers

Most early-stage products start with the shared schema. The risk is simple: one forgotten WHERE tenant_id = ? leaks data. RLS moves that rule from application code into the database, so it can't be forgotten.

Step 1: Add a tenant column 

CREATE TABLE invoices ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id uuid NOT NULL, amount numeric(12,2) NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX invoices_tenant_idx ON invoices (tenant_id);

Index tenant_id on every tenant-scoped table. Nearly every query will filter on it.

Step 2: Enable row-level security 

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.current_tenant', true)::uuid) WITH CHECK (tenant_id = current_setting('app.current_tenant', true)::uuid);

A few details matter here:

  • USING controls which rows you can read, update or delete.
  • WITH CHECK controls which rows you can insert or update into.
  • Table owners bypass RLS unless you use FORCE ROW LEVEL SECURITY.
  • Superusers and roles with BYPASSRLS always bypass it, so your application should connect as a normal, limited role.
  • If the setting is missing, current_setting(..., true) returns NULL, the comparison is false, and no rows are returned. That is a safe default.

Step 3: Set the tenant for each request

Set the tenant inside a transaction, using set_config with true so it only lasts for that transaction. This also matters if you use a connection pool, because a setting that leaks to the next request would be dangerous.

// db.js import pg from "pg"; const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL }); export async function withTenant(tenantId, fn) { const client = await pool.connect(); try { await client.query("BEGIN"); await client.query("SELECT set_config('app.current_tenant', $1, true)", [tenantId]); const result = await fn(client); await client.query("COMMIT"); return result; } catch (err) { await client.query("ROLLBACK"); throw err; } finally { client.release(); } }

And a route that uses it:

app.get("/invoices", async (req, res) => { const rows = await withTenant(req.user.tenantId, async (db) => { const { rows } = await db.query("SELECT id, amount, created_at FROM invoices ORDER BY created_at DESC"); return rows; }); res.json(rows); });

Notice there is no WHERE tenant_id in that query. The database applies it.

Step 4: Test the isolation

Write a test that proves tenant A cannot see tenant B's rows. This is the most valuable test in a multi-tenant app.

test("tenants cannot read each other's invoices", async () => { await withTenant(tenantA, (db) => db.query("INSERT INTO invoices (tenant_id, amount) VALUES ($1, 100)", [tenantA])); const rows = await withTenant(tenantB, async (db) => (await db.query("SELECT * FROM invoices")).rows); expect(rows).toHaveLength(0); });

Common pitfalls

  • Connecting as a superuser or table owner silently disables RLS. Check your app's role.
  • Forgetting to enable RLS on new tables. Add a check to CI that lists tables with a tenant_id column but no policy.
  • Session-level settings with pooling. Always use transaction-scoped settings.
  • Background jobs and admin tools. They need an explicit, audited way to set the tenant (or a separate role).
  • Unique constraints. Usually they should include tenant_id, for example UNIQUE (tenant_id, email).

When RLS isn't enough

RLS gives strong logical isolation, but it doesn't solve noisy neighbours (one tenant using all the resources), per-tenant backups or restores, or data-residency requirements. If a customer needs those, schema-per-tenant or database-per-tenant may fit better for them, and many products end up with a hybrid.

What tenancy model are you using, and what made you pick it?

Further reading: SaaS Development