Multi-Tenant SaaS With PostgreSQL Row-Level Security

Most multi-tenancy tutorials default to separate schemas or databases. PostgreSQL's native Row-Level Security enforces tenant isolation at the query layer — here's the exact setup, pitfalls, and trade

Separate databases per tenant sounds clean until you're managing 300 connection pools, running 300 migration scripts on every deploy, and debugging why tenant #214 is on a two-version-old schema. There is a better path, and it has been sitting inside PostgreSQL since version 9.5.

Row-Level Security (RLS) lets you declare, inside the database itself, which rows a given session is allowed to see or modify. For SaaS teams building on a single PostgreSQL instance, this means tenant isolation becomes a database-enforced guarantee rather than a layer of application logic you have to remember to apply everywhere.

The Core Idea: Isolation at the Query Layer

In a single-database multi-tenant model, every tenant's data lives in the same tables. A tenant_id column on each table is the only separator. Without RLS, isolation depends entirely on your application always appending WHERE tenant_id = $current_tenant to every query. One missing clause in one endpoint is a data leak.

RLS flips the contract. You teach PostgreSQL what "current tenant" means for a session, and the database enforces the filter automatically — even if your application forgets.

Step 1: Add tenant_id to Your Tables

Every tenant-scoped table needs a tenant_id column. Using a UUID is recommended for non-guessable identifiers:

ALTER TABLE orders ADD COLUMN tenant_id UUID NOT NULL;
ALTER TABLE invoices ADD COLUMN tenant_id UUID NOT NULL;

-- Index it — queries will filter on this column constantly
CREATE INDEX idx_orders_tenant_id ON orders(tenant_id);

This index is not optional. Once RLS policies are active, every read and write on these tables will include a tenant_id filter. Without the index, you will do sequential scans at scale.

Step 2: Create a Dedicated Application Role

Do not connect to PostgreSQL as a superuser from your application. Superusers bypass RLS entirely — which defeats the purpose. Create a role that RLS actually applies to:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Your application connects as app_user. Migrations and administrative tasks use a separate privileged role.

Step 3: Enable RLS on Each Table

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

By default, once RLS is enabled, no rows are visible to non-superusers unless a policy explicitly permits it. This fail-closed default is intentional and valuable.

Step 4: Write the Policies

The tenant identity needs to travel from your application into the database session. The cleanest mechanism is SET LOCAL on a session variable, which PostgreSQL exposes via current_setting():

-- Policy for SELECT
CREATE POLICY tenant_isolation_select ON orders
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- Policy for INSERT (WITH CHECK enforces on writes)
CREATE POLICY tenant_isolation_insert ON orders
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::UUID);

-- Combined policy for UPDATE and DELETE
CREATE POLICY tenant_isolation_modify ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant')::UUID)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::UUID);

In your application, at the start of every request, set the session variable before executing any queries:

SET LOCAL app.current_tenant = '8f14e45f-ceea-467a-a866-1234567890ab';

If you are using a connection pool like PgBouncer in transaction-pooling mode, SET LOCAL is scoped to the transaction, which makes this pattern safe — the variable resets when the transaction ends.

Common Pitfalls

Missing Policies on Every Operation

A SELECT policy does not cover INSERT. Write explicit policies for each operation, or use FOR ALL with both USING and WITH CHECK clauses. Forgetting WITH CHECK on inserts means a tenant can write rows with an arbitrary tenant_id.

Connection Pooling in Session Mode

If your pool operates in session mode, the app.current_tenant variable persists across requests on the same connection. A request for Tenant A could inadvertently read Tenant B's data if the variable is not reset before every transaction. Always set the variable inside a transaction, never outside one.

Superuser Bypass

RLS is bypassed for roles with BYPASSRLS privilege and for superusers. Audit your database roles. Your application role should have neither. Reserve privileged roles strictly for migrations and administrative scripts that run in controlled environments.

Performance at Scale

RLS adds a predicate to every query. This is efficient when:

The tenant_id column is indexed.
Queries already filter by tenant_id explicitly (the planner can merge the predicates).

It becomes a concern when tenants have wildly unequal data volumes — a tenant with 10 million rows in the same table as tenants with 100 rows can cause planner estimation errors. Partial indexes per tenant or table partitioning by tenant_id are the escalation paths when you hit that ceiling.

What About Schema-Per-Tenant?

Schema-per-tenant offers stronger isolation and simpler per-tenant migrations, but it does not scale easily beyond a few hundred tenants. Connection pools bloat, deployment pipelines become complex, and operational overhead compounds. RLS is the right default for SaaS products expecting many tenants with moderate data volumes per tenant. Reserve schema-per-tenant for products where a small number of enterprise customers require contractual data isolation guarantees.

Testing Your Policies

Do not assume your policies work. Write explicit integration tests that:

Set app.current_tenant to Tenant A's ID.
Attempt to read rows owned by Tenant B.
Assert that zero rows are returned.

Run these tests against a real PostgreSQL instance, not a mocked database layer. Mocks will not catch policy logic errors.

Why This Matters for Your Project

For SaaS teams at any stage, choosing the right multi-tenancy model early determines how much operational debt you carry at scale. PostgreSQL's RLS gives you a single-database architecture that is simple to migrate, easy to back up, and enforceable at the lowest possible layer. The complexity budget you save on infrastructure can go directly into shipping features. If you are building a new SaaS product — or re-architecting one — this is the pattern worth getting right from day one.