Multi-Tenant SaaS with Postgres Row-Level Security: A Practical Guide

#saas #postgres #architecture #node

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

DEV Community