Multi-tenant SaaS on Postgres row-level security

Tenancy is the one thing you cannot retrofit

In a multi-tenant product every business shares the same tables, and every query is one missing WHERE clause away from showing one customer another customer’s data. Filtering by tenant in application code works until the first query that forgets to. We would rather the database refuse.

The details below come from a multi-tenant point-of-sale platform we are building now: a shared schema, a tenant id on every business table, and Postgres row-level security to enforce it. The restaurant search we delivered isolates its tenants with row-level security too. The platform is still in development, so what follows is its design and its test suite, not a production war story.

A shared schema is cheaper to run than a schema or a database per tenant, and it keeps the option to shard by tenant later. The price is that isolation has to be enforced on every table, every time, by something other than developer discipline.

The policy every tenant table gets

Every tenant-scoped table gets the same policy, created in the same migration as the table itself.

alter table orders enable row level security;
alter table orders force  row level security;

create policy tenant_isolation on orders
  using      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid)
  with check (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

Five details in it carry the weight, and each exists because the version without it fails in a way that looks like something else.

• The true in current_setting means missing is fine. Without it, a query issued with no tenant set raises an unrecognised-parameter error, which reads like a bug in the query and tempts someone to drop the policy. With it, the setting is null, the comparison is null, and the row is invisible.
• nullif turns an empty setting back into null. SET LOCAL reverts a custom setting to the empty string, not to unset, so on a pooled connection that has already served one tenant, casting that empty string to a uuid raises an error on every read outside a tenant transaction. Our first version of the policy lacked it; we measured the failure and amended every migration.
• with check as well as using. Using filters the rows a session can read and the old row of an update; with check validates the new row. Without it, a session could insert a row stamped with another tenant’s id.
• force row level security. A table’s owner bypasses its policies unless force is set, and migrations run as the owner.
• The cast to uuid makes a malformed tenant id an error, never a string comparison, which is why the application validates the id’s shape before Postgres ever sees it.

Who connects matters as much as the policy

Row-level security binds nobody if the application connects as a superuser, and binds a table’s owner only when force is set. So the platform uses separate database roles: an owner role that runs migrations and creates tables and policies, and an application role that serves every request and is neither the owner nor a superuser. Point both connection strings at one superuser and every policy goes inert while still looking correct in the migration diff.

Privileges are the second lock. The application role gets select, insert, update and delete on business tables by default, so the append-only audit log needs explicit revokes of update and delete, and its policies cover select and insert only. Granting less is not enough: privileges add up, and only a revoke takes one away.

Some business rules are policies of their own. Orders carry restrictive policies on top of tenant isolation: only a parked order can be deleted, and no update can turn an order back into a parked one. A completed sale cannot be deleted, whichever tenant the query runs as.

The platform console, which really does need to read across tenants, gets a third role with BYPASSRLS, select only, its own connection pool, and one module allowed to import it. What it does not get is an escape hatch inside the policy, such as an admin flag in the session. An escape hatch in the policy is an escape hatch for every bug in the request path.

Setting the tenant without string interpolation

The tenant id reaches Postgres through one helper. It validates the id, opens a transaction, sets the tenant for that transaction only, and hands the callback a transaction handle to use.

Three choices in it are deliberate. SET LOCAL does not accept bind parameters, which is what tempts people into building the statement as a string; set_config with its local flag does the same thing as a function call, so the id travels as a parameter. The setting is transaction-scoped, so it cannot leak to the next request on a pooled connection, and a query issued outside the transaction sees no tenant and gets no rows. And the id is a branded type that only the validator produces, so a raw string from a request cannot be passed where a checked one is required.

export async function withTenant<T>(
  db: Database,
  tenantId: string,
  fn: (tx: Transaction) => Promise<T>,
): Promise<T> {
  assertTenantId(tenantId) // a UUID, or throw before any SQL runs

  return db.transaction(async (tx) => {
    await tx.execute(sql`select set_config('app.tenant_id', ${tenantId}, true)`)
    return fn(tx)
  })
}

Foreign keys do not enforce tenancy

One gap surprises people: Postgres runs foreign-key checks as the table owner, bypassing row-level security. A row in one tenant can reference a parent in another, and the constraint will allow it.

For the parent chains that matter, such as company to branch to terminal, the parent carries a unique key on tenant and id, and the child’s foreign key is composite. A cross-tenant reference becomes impossible rather than merely unlikely.

Testing isolation against a real database

Row-level security is database behaviour, so it is tested against a real Postgres; a mock would only test the mock. The suite creates two tenants, writes rows to both, and proves table by table that a session opened for one tenant can neither read nor write the other’s rows. The cases it asserts:

• The application role is a separate role, and neither the owner nor a superuser.
• Reads return exactly the tenant’s own rows.
• An insert stamped with the other tenant’s id is rejected.
• An update that re-stamps a row as another tenant’s is rejected.
• Reading another tenant’s row by its id returns zero rows.
• With no tenant set, on a connection that has already served both tenants, a query returns zero rows and raises nothing.
• The owner does not bypass its own policy, and the force flag is checked in the catalogue, not inferred from behaviour.
• The tenant-id validator rejects anything that is not a UUID before set_config sees it.

The rule that keeps it honest: every new business table adds its cases in the same commit that adds the table, and the suite runs as the application role, because a suite that passes while connected as the owner proves nothing.

When a shared schema is the right call

Row-level security is not free. Every tenant-scoped query runs inside a transaction, background work needs a tenant context of its own, and anything genuinely cross-tenant needs its own role and its own review. For most business software that is a good trade: one schema to migrate, one database to operate, and isolation that holds even when application code is wrong.

If you are starting a multi-tenant product, put the tenant id and the policy in the first migration. Adding them to a live schema later means auditing every query you have ever written.