Most multi-tenancy tutorials hand you two options: separate databases per tenant, or separate schemas. Both work — until they don't. Separate databases become an operational nightmare at 500 tenants. Separate schemas bloat your migration tooling and slow down schema changes. PostgreSQL's row-level security (RLS) gives you a third path: one schema, one set of tables, and the database engine itself enforcing tenant isolation. Here is how to build it correctly.

Why Row-Level Security Over Schema Separation

Schema-per-tenant sounds clean in architecture diagrams. In practice, running ALTER TABLE across 300 schemas during a release window is the kind of experience that ends careers. RLS keeps your schema flat — one users table, one invoices table — and attaches security policies that filter rows automatically based on the current session context.

The benefits compound as you scale:

  • Migrations stay simple. One schema means one migration, not N.
  • Connection pooling works properly. Tools like PgBouncer operate cleanly against a single schema.
  • Operational visibility is straightforward. Querying across tenants for analytics requires no cross-schema joins.

The trade-off is that RLS requires discipline at the application layer. Get it wrong, and a misconfigured policy leaks one tenant's data to another. This guide covers exactly where those mistakes happen.

Setting Up the Foundation

Start with a tenant_id column on every table that holds tenant-specific data. Use UUID rather than integer — it prevents enumeration attacks if a tenant_id ever surfaces in a URL or log.

-- Core tenants table
CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Example resource table
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY is critical. Without it, the table owner — typically your application's superuser — bypasses all policies. Always force it.

Writing the Policies

RLS policies are attached per table and per command type. A single permissive policy for ALL commands is the most common starting point:

CREATE POLICY tenant_isolation ON projects
  AS PERMISSIVE
  FOR ALL
  TO app_user
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::UUID);

The USING clause filters rows on reads. The WITH CHECK clause enforces the constraint on writes. Both must reference current_setting('app.current_tenant_id') — a session-level variable your application sets at the start of every database transaction.

In your application code, every connection should open with:

SET LOCAL app.current_tenant_id = '<tenant-uuid>';

SET LOCAL scopes the setting to the current transaction, which is safer than SET (session-scoped). In a pooled connection environment, session-scoped settings can bleed across requests — a serious data isolation bug.

Integrating with Your Application

The cleanest pattern is a middleware layer or a database wrapper that injects the tenant context before any query executes. In a Node.js/TypeScript stack using a query builder like Kysely or Knex, you wrap every transaction:

async function withTenant<T>(tenantId: string, fn: (db: Kysely<DB>) => Promise<T>): Promise<T> {
  return db.transaction().execute(async (trx) => {
    await trx.executeQuery(
      sql`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`.compile(trx)
    );
    return fn(trx);
  });
}

The true flag in set_config scopes the setting to the transaction, mirroring SET LOCAL. Every query run inside fn now operates under RLS with the correct tenant context — no extra WHERE clauses required.

Common Pitfalls

1. Background jobs without tenant context

Scheduled jobs and async workers often bypass the request lifecycle where tenant context is set. If a worker queries a RLS-protected table without setting app.current_tenant_id, PostgreSQL will throw an error — or worse, return no rows silently depending on how you handle missing settings. Always set tenant context explicitly in workers, even if it means passing tenant IDs through your job queue payload.

2. Superuser connections ignoring RLS

FORCE ROW LEVEL SECURITY does not apply to superusers. Your migration user, your DBA tooling, your database backups — these roles typically connect as superusers and see all rows. This is correct for operational access, but ensure your application's runtime role is a non-superuser with only the permissions it needs.

3. Forgetting indexes on tenant_id

RLS filters are applied after the planner builds its execution plan. Without an index on tenant_id, PostgreSQL will seq-scan the entire table before filtering. At small data volumes this is invisible; at 10 million rows across 1,000 tenants it becomes a crisis. Add a composite index on (tenant_id, <common_filter_column>) for your most-queried tables.

CREATE INDEX idx_projects_tenant_id ON projects(tenant_id);

4. Leaky joins to non-RLS tables

If you join an RLS-protected table to a non-protected lookup table, the policy only governs the protected side. Audit every table for whether it needs RLS. Reference data (country codes, plan tiers) is fine unprotected. Anything with tenant-specific records is not.

Performance Implications

RLS adds a predicate to every query — effectively a free WHERE clause appended by the engine. The overhead is negligible when indexes are in place. The bigger performance consideration is connection pooling.

Because tenant context is set per transaction, transaction-mode pooling (PgBouncer in transaction mode) works perfectly with SET LOCAL. Session-mode pooling is risky because session variables persist across connections returned to the pool. Design your pooling strategy around transaction mode from day one.

For tenants with high query volume, consider partial indexes scoped to that tenant's data, or partition large tables by tenant_id using PostgreSQL's declarative partitioning. RLS and partitioning compose well — policies still apply within each partition.

A Note on Multi-Region Deployments

If you later need to distribute tenants across regions — say, EU tenants on a Frankfurt cluster and US tenants on a Virginia cluster — the RLS approach makes migration straightforward. Tenant data is logically isolated at the row level already; moving a tenant's rows to another cluster is a well-scoped operation. Schema-per-tenant would require cloning entire schemas across regions.

Why This Matters for Your Project

For SaaS teams building on PostgreSQL — which is most teams — RLS is the architecture that scales from your first ten customers to your first ten thousand without a migration strategy overhaul. It keeps your codebase clean, your database tooling simple, and your isolation guarantees enforced at the layer that matters most: the database itself. If you are starting a new SaaS product or refactoring a legacy multi-tenant setup, investing in RLS now is significantly cheaper than untangling schema proliferation eighteen months from now.