Multi-Tenant SaaS Architecture: Row-Level Security vs Schema-per-Tenant

A multi-tenant SaaS application serves many customer organisations, called tenants, from one system. The most important rule is simple: one tenant must never see another tenant's data. How you enforce that rule shapes your database design, your costs and how easily you can grow. This guide compares the three main approaches and shows how to implement the most common one safely in PostgreSQL.

The three models

1. Shared tables

All tenants share the same tables, and every tenant-owned row has a tenant_id column. It is the cheapest and simplest to operate, and it scales to very large numbers of tenants. Its risk is that a single missing filter in application code can expose data, which is why database-enforced isolation matters.

2. Schema per tenant

Each tenant gets its own PostgreSQL schema containing a copy of every table. Isolation is clearer, and per-tenant backups or exports are easier. But every migration must run against every schema, and operations get harder as tenant numbers grow into the thousands.

3. Database per tenant

Each tenant has its own database. This gives the strongest isolation and makes data residency and per-customer encryption straightforward. It also costs the most, and connection management, migrations and monitoring multiply with every customer.

Comparison

Implementing shared tables with row-level security

PostgreSQL row-level security lets the database itself filter rows based on a policy. Even if application code forgets a WHERE tenant_id = ... clause, the database returns only the current tenant's rows.

CREATE TABLE invoices (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id  uuid NOT NULL REFERENCES tenants(id),
  number     text NOT NULL,
  total      numeric(12, 2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, number)
);

CREATE INDEX invoices_tenant_id_idx ON invoices (tenant_id);

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

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);

A few details in that example matter:

• USING filters which existing rows can be read, updated or deleted. WITH CHECK stops inserts or updates that would write a row for another tenant.
• Passing true as the second argument to current_setting avoids an error when the setting has never been set, and NULLIF(..., '') turns the empty value that a pooled connection can carry over into NULL. A NULL comparison matches no rows, so a request without a tenant sees nothing rather than everything.
• Unique constraints include tenant_id, so two tenants can both have invoice number 1001.
• An index on tenant_id keeps policy filtering fast.

Setting the tenant for each request

Your application must tell PostgreSQL which tenant the current request belongs to. Do this inside a transaction with set_config, passing true as the third argument so the value applies only to that transaction:

BEGIN;
SELECT set_config('app.tenant_id', $1, true);
-- every query in this transaction is now limited to that tenant
SELECT id, number, total FROM invoices ORDER BY created_at DESC;
COMMIT;

RLS pitfalls to avoid

• Owners bypass policies by default. PostgreSQL's documentation notes that table owners normally bypass row security. Use FORCE ROW LEVEL SECURITY, and ideally run the application as a separate role that does not own the tables.
• Superusers and BYPASSRLS roles always bypass RLS. Never connect your application with a superuser account.
• No policy means no access. When RLS is enabled but no policy exists, PostgreSQL applies default deny. This is safe, but it can surprise you during development.
• Background jobs need a tenant too. Queued jobs, scheduled tasks and webhooks must set the tenant before touching data, just like web requests.
• Admin tools need a deliberate path. Cross-tenant support or reporting should use a separate, audited role, not a disabled policy.
• Test isolation automatically. Add tests that create two tenants and assert that each cannot read or modify the other's rows.

When schema-per-tenant or database-per-tenant makes sense

Move beyond shared tables when a concrete requirement demands it:

• An enterprise contract requires physically separate data or a specific hosting region.
• A few very large tenants create load that affects everyone else.
• Customers need their own encryption keys, restores or maintenance windows.
• Regulations in your target industry require stronger separation.

A pragmatic default

1. Start with shared tables, a tenant_id on every tenant-owned table, and RLS enforced by the database.
2. Keep all tenant resolution in one place in your code, such as request middleware.
3. Store a tenant's database location in a tenant directory table, even if every tenant points to the same database today.
4. When a customer needs dedicated infrastructure, move that tenant to its own database and update the directory, without changing application logic.

This approach keeps an MVP simple, as described in our 12-week SaaS MVP playbook, while leaving a clear path to enterprise requirements.

Designing a SaaS platform or worried about isolation in an existing one? Our SaaS development service includes architecture reviews. Contact us to discuss yours.