Multi-tenant SaaS on PostgreSQL: schema-per-tenant vs row-level security

Every B2B SaaS product eventually gets the same bug report: "I can see another company's invoices." It rarely comes from a sophisticated attack. It comes from a missing WHERE tenant_id = ? in a report query written at 11 p.m., or from a background job that reused a connection with the wrong context.

The way you separate tenant data in PostgreSQL decides how likely that bug is, how painful migrations become at 500 customers, and whether you can move one large customer to its own server later. This post compares the two approaches most teams argue about, schema-per-tenant and a shared schema with row-level security (RLS), with the configs and pitfalls that only show up in production.

The three options in one paragraph each

Shared schema, tenant_id column, RLS. All tenants live in the same tables. Every tenant-owned table has a tenant_id, and PostgreSQL policies filter rows based on a session variable. One set of tables, one migration run, one place to index.

Schema-per-tenant. Each tenant gets its own schema (tenant_0042.orders, tenant_0043.orders), and the application switches search_path per request. Isolation comes from namespaces, not from filters.

Database-per-tenant. Each tenant gets its own database, sometimes its own server. Strongest isolation, highest operational cost. Usually reserved for customers with regulatory or contractual requirements.

Most teams end up with a hybrid: shared schema with RLS for the long tail, and a dedicated database for the few tenants who need it. Keep that end state in mind, because it affects the choice you make on day one.

Shared schema with RLS: a setup that actually holds

Here is a minimal setup that fails closed, meaning that if the application forgets to set the tenant, it sees nothing rather than everything.

CREATE TABLE tenants ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text NOT NULL, plan text NOT NULL DEFAULT 'starter' ); CREATE TABLE customers ( tenant_id uuid NOT NULL REFERENCES tenants(id), id uuid NOT NULL DEFAULT gen_random_uuid(), email text NOT NULL, PRIMARY KEY (tenant_id, id), UNIQUE (tenant_id, email) ); CREATE TABLE invoices ( tenant_id uuid NOT NULL, id uuid NOT NULL DEFAULT gen_random_uuid(), customer_id uuid NOT NULL, amount numeric(12,2) NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (tenant_id, id), FOREIGN KEY (tenant_id, customer_id) REFERENCES customers(tenant_id, id) ); CREATE INDEX invoices_tenant_created ON invoices (tenant_id, created_at DESC); ALTER TABLE customers ENABLE ROW LEVEL SECURITY; ALTER TABLE customers FORCE ROW LEVEL SECURITY; ALTER TABLE invoices ENABLE ROW LEVEL SECURITY; ALTER TABLE invoices FORCE ROW LEVEL SECURITY; CREATE POLICY tenant_isolation ON customers USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid) WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid); CREATE POLICY tenant_isolation ON invoices USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid) WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid); 

And the application role:

CREATE ROLE app_user LOGIN PASSWORD '...' NOSUPERUSER NOBYPASSRLS; GRANT SELECT, INSERT, UPDATE, DELETE ON customers, invoices TO app_user; 

Every detail here exists because of a real failure mode.

FORCE ROW LEVEL SECURITY. Without it, the table owner bypasses policies. If your application connects as the same role that ran the migrations (very common in small teams), RLS is silently off. Superusers and roles with BYPASSRLS skip policies regardless, so the app must connect as a dedicated role with neither.

current_setting(..., true) plus NULLIF. The second argument makes a missing setting return NULL instead of raising an error. tenant_id = NULL is never true, so no rows match. The NULLIF matters because once a custom setting has been set in a session and the transaction ends, PostgreSQL reverts it to an empty string, not NULL, and ''::uuid throws an error. Without NULLIF you get intermittent "invalid input syntax for type uuid" errors on pooled connections.

WITH CHECK. USING filters what you read. WITH CHECK stops an insert or update from writing a row with someone else's tenant_id. Skip it and a bug in the API layer can plant data into another tenant.

Composite foreign keys. This one surprises people: referential integrity checks bypass RLS. A plain customer_id REFERENCES customers(id) lets tenant A's invoice point at tenant B's customer, and the database will accept it. Making the key (tenant_id, customer_id) makes cross-tenant references structurally impossible.

tenant_id as the leading index column. Almost every query is "this tenant's rows, sorted or filtered by something". Indexes that start with tenant_id keep those queries on a narrow slice of the table.

Setting the tenant per request, and why SET is a trap

The tenant context must be scoped to a transaction, not a connection:

BEGIN; SELECT set_config('app.tenant_id', $1, true); -- true = local to this transaction SELECT * FROM invoices ORDER BY created_at DESC LIMIT 50; COMMIT; 

In application code, wrap it so nobody can forget:

from contextlib import contextmanager @contextmanager def tenant_tx(pool, tenant_id: str): with pool.connection() as conn: with conn.transaction(): conn.execute( "SELECT set_config('app.tenant_id', %s, true)", (tenant_id,) ) yield conn 

A plain SET app.tenant_id = '...' (session level) survives after the request ends. With PgBouncer in transaction mode, the next transaction on that server connection may belong to a different client, and it inherits the previous tenant. That is exactly the "I see another company's data" bug, and it only appears under load, when connections are being reused aggressively. set_config(..., true) or SET LOCAL disappears at commit or rollback, so it is safe with transaction pooling.

Pitfalls that RLS does not cover

• Views. By default a view runs with the privileges of its owner. If the owner is the migration role, queries through the view skip the policies. On PostgreSQL 15 and later, create views with WITH (security_invoker = true). On older versions, make sure views are owned by a role subject to RLS.
• SECURITY DEFINER functions. Same problem: they run as their owner. Audit every one of them.
• Background workers and admin tools. Cron jobs, queue consumers and internal dashboards often connect with a privileged role "just to make it work". Give them their own role with RLS on, and set the tenant explicitly per job. For genuinely cross-tenant tasks (billing runs, platform analytics), use a separate role with BYPASSRLS and keep its credentials out of the web app.
• Caches and search indexes. RLS protects PostgreSQL only. Redis keys, Elasticsearch documents and files in object storage need the tenant in the key or path, and their own checks.
• Planner surprises. Policies are added as extra predicates. With simple equality on tenant_id and a good index, the overhead is negligible. If a policy calls a non-trivial function (for example, a lookup of the user's tenants), it may be evaluated per row. Mark helper functions STABLE, wrap them in a subselect so they are computed once per query, and check EXPLAIN ANALYZE on your top queries before and after enabling RLS.

Schema-per-tenant: what it really costs

Schema-per-tenant looks attractive: no tenant_id columns, no policies, and a tenant's data is easy to dump or delete with DROP SCHEMA tenant_0042 CASCADE. The costs appear as tenants grow.

Catalog size. Every table, index, sequence and TOAST table is a row in the system catalogs. A product with 150 tables and 300 indexes, times 2,000 tenants, means well over a million relations. pg_dump slows down sharply, \d in psql becomes sluggish, and each backend's relation cache grows as it touches more schemas, which multiplies memory across hundreds of connections.

Migrations. One schema change becomes N schema changes. A migration that takes 200 ms per schema takes almost 7 minutes for 2,000 tenants, and some of them will fail midway on data you did not expect. You need per-schema version tracking, a runner that can resume, and application code that tolerates tenants being on different schema versions during the rollout.

-- a per-schema version table you will end up writing anyway CREATE TABLE tenant_0042.schema_version (version int NOT NULL, applied_at timestamptz DEFAULT now()); 

Pooling. The search_path problem is the same as the SET problem above. Use SET LOCAL search_path TO tenant_0042, public inside each transaction, never a session-level SET, if a transaction pooler sits in front of the database. Also, unqualified names resolve to public if a table is missing from the tenant schema, which can quietly read shared data.

Cross-tenant queries. "Revenue by plan across all customers" becomes a generated UNION ALL over thousands of schemas, or an ETL job into a separate warehouse. With a shared schema it is a GROUP BY.

Schema-per-tenant does have real advantages: per-tenant restores are simple, a missing filter cannot leak data, and per-tenant customization (an extra table for one enterprise customer) is possible. It fits products with tens or low hundreds of tenants, each with substantial data, for example an ERP-like system sold to mid-size companies. It fits poorly with self-serve products that sign up hundreds of small tenants a month.

A rough decision guide

Note the last row. Schemas share CPU, memory and I/O on one server just as rows do. If one tenant runs heavy reports, schema-per-tenant does not protect the others. Only a separate database (on separate resources) does.

Plan for the hybrid from day one

The design that ages best is a shared schema with RLS plus a routing table:

CREATE TABLE tenant_placement ( tenant_id uuid PRIMARY KEY REFERENCES tenants(id), dsn_alias text NOT NULL DEFAULT 'shared-1' ); 

The application looks up dsn_alias and connects to the right cluster. Because every row already carries tenant_id, moving one customer to a dedicated database is a scripted export:

COPY (SELECT * FROM invoices WHERE tenant_id = '...') TO STDOUT WITH (FORMAT binary); 

followed by an import into an identical schema on the new server and a change to one row in tenant_placement. Going the other way, from thousands of schemas back to shared tables, means adding tenant_id to everything and rewriting the data, which is effectively a re-platform.

Test isolation like a feature

Put a test in CI that connects as app_user, sets tenant A, and tries to read, update and insert rows belonging to tenant B through every table, view and API endpoint. Also run one test with no tenant set and assert it returns zero rows. Add a migration check that fails if any table with a tenant_id column lacks relrowsecurity and relforcerowsecurity in pg_class:

SELECT c.relname FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'tenant_id' JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'public' AND c.relkind = 'r' AND (NOT c.relrowsecurity OR NOT c.relforcerowsecurity); 

An empty result is the only acceptable one. This catches the most common regression: a new table added six months later by someone who never read the original design notes.

Summary

For most SaaS products, start with a shared schema, tenant_id on every tenant-owned table, forced RLS, composite foreign keys and transaction-scoped tenant context. Reach for schema-per-tenant only when you have few, large tenants who need per-tenant restores or customization. Keep database-per-tenant as an option for the customers whose contracts demand it, and make it reachable through a routing table rather than a rewrite.

Isolation is only one of the decisions that are hard to change later; how plans and limits are counted and what happens to data on non-payment shape the schema just as much. If you are scoping a subscription product and want those questions settled before the first line of code, this overview of how we approach SaaS platform development lists what goes into a first release.