Multi-tenant SaaS on PostgreSQL: row-level security in practice

Every SaaS product makes one promise that matters more than any feature: one customer will never see another customer's data. In a multi-tenant system, where many customers share one application and one database, keeping that promise depends on every query in the code including the right filter. Miss it once, in one report or one background job, and the promise is broken.

PostgreSQL's row-level security moves that promise from the application code into the database. This is how we use it in practice, and the traps worth knowing about before you rely on it.

Three ways to separate tenants

• A database per tenant. Strongest separation and easy to explain to security reviewers. But every schema change has to run on every database, and costs grow with the number of customers. Good for a small number of large customers with strict requirements.
• A schema per tenant. Similar trade-offs, and very large numbers of schemas become hard to manage.
• Shared tables with a tenant column. Every row carries a tenant_id. Simplest to run and scale, and the usual choice for products with many small and medium customers. The risk is the forgotten filter, which is what row-level security addresses.

Many products end up with a mix: shared tables for most customers, and a separate database for the few who require it.

How row-level security works

With row-level security turned on for a table, PostgreSQL adds a condition, called a policy, to every query against it. If the policy says a row is visible only when its tenant_id matches the current tenant, then a query that forgets the filter still returns only that tenant's rows. The database applies the rule even when the application code does not.

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.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

The USING part controls which rows can be read, updated or deleted. The WITH CHECK part stops the application from writing a row into another tenant. FORCE applies the policy to the table's owner as well, which otherwise bypasses it.

Setting the tenant for each request

The policy reads the current tenant from a setting, here app.tenant_id. The application sets it at the start of each request, from the signed-in user, inside a transaction:

BEGIN;
SELECT set_config('app.tenant_id', '…tenant uuid…', true);
-- the request's queries run here
COMMIT;

The final true makes the setting last only until the end of the transaction. That matters with connection pools, where the same database connection serves different customers one after another. A setting that outlived its transaction could leak into the next customer's request. Setting it per transaction avoids that.

If the setting is missing, current_setting('app.tenant_id', true) returns nothing, the comparison fails, and the query returns no rows. A missing tenant fails closed, not open, which is exactly what you want.

The traps

• The database role. Superusers and roles with the BYPASSRLS attribute ignore policies. The application must connect as an ordinary role that does not own the tables. Migrations run with a different, more powerful role.
• Views. By default a view runs with the permissions of its owner, which can bypass the policies of the tables underneath. On PostgreSQL 15 and later, create views with security_invoker so the caller's policies apply.
• Functions. Functions marked SECURITY DEFINER also run as their owner. Use them rarely and review each one.
• Background jobs and reports. Jobs that work across all tenants, such as billing runs or usage reports, need a deliberate, separate role with a clear purpose, not a quiet switch that turns security off.
• Every table. Row-level security protects only the tables it is enabled on. We add a check to the build that fails if any table with a tenant_id column has no policy.

Keeping it fast

The policy adds a condition to every query, so indexes should start with tenant_id: an index on (tenant_id, created_at) rather than on created_at alone. Foreign keys that include the tenant, such as (tenant_id, customer_id), also stop a row from pointing at another tenant's record. Keep policy conditions simple; a policy that runs a subquery on every row can slow large tables down noticeably.

Test isolation like a feature

Tenant isolation deserves its own automated tests, run on every change: create two tenants with data, act as the first, and try to read, update and delete the second tenant's records through every API endpoint and every report. Every attempt must fail. These tests are cheap to write and they protect the one promise customers care about most.

Row-level security is a safety net, not the whole design

The application should still filter by tenant, and still check permissions within a tenant: an employee should not see another department's records just because they belong to the same customer. Row-level security makes sure that a mistake in that code does not become a cross-customer data leak. That second line of defence is worth a great deal when a security questionnaire, or an incident, asks how isolation is guaranteed.

We design and build SaaS products, including multi-tenant architecture, billing and tenant isolation testing, from the first version onwards. See also how we took a multi-tenant SaaS product from MVP to version one.

Originally published on the Silver Scintilla blog.