Tenant isolation in Postgres: practical lessons

In a multi-tenant commerce platform, every store’s data lives side by side: products, customers, orders, receipts. The most damaging bug you can ship is not a crash. It is one merchant seeing another merchant’s customers.

Most applications protect against this with a where store_id = ? on every query. That works until the day someone forgets one. nqrah isolates stores at the database level as well, so a forgotten filter returns nothing instead of everything. These are the lessons that apply to any Postgres-backed SaaS.

#1. Every tenant-owned row carries the tenant

Every table that holds store data has a non-null store_id, indexed, usually as the first column of composite indexes. Joins between tenant tables include it too. It sounds redundant; it is what makes the rest possible.

#2. Let Postgres enforce it with row-level security

Row-level security (RLS) attaches a filter to the table itself. The application sets the current tenant at the start of each transaction, and the policy does the rest:

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

create policy tenant_isolation on orders
  using (store_id = current_setting('app.store_id')::uuid)
  with check (store_id = current_setting('app.store_id')::uuid);

await db.transaction(async (tx) => {
  await tx.execute(sql`select set_config('app.store_id', ${storeId}, true)`); // true = local to this transaction
  return tx.select().from(orders); // only this store's orders, even without a where clause
});

The with check clause matters as much as using: it stops a bug from writing a row into another tenant.

#3. Don’t let the app connect as a superuser

Superusers and table owners bypass RLS unless it is forced. The application should connect with a role that owns nothing and cannot bypass policies. Migrations run with a separate, more privileged role.

#4. Fail closed

If the tenant setting is missing, current_setting without a default raises an error, which is what you want. A query that runs without a tenant should fail loudly, not quietly return data.

#5. Platform-level work gets its own path

Some jobs legitimately cross tenants: billing, analytics, the AI budget breaker. They run with a dedicated role and live in clearly separated code, so “cross-tenant” is never an accident in a request handler.

#6. Test isolation like a feature

Write tests that create two stores, set the context to one, and try to read, update and insert into the other. They should fail every time. Run them on every change to the schema, because a new table without a policy is a hole.

#7. Secrets per tenant

Isolation is not only rows. Each store’s integration keys, for payment gateways or shipping companies, are encrypted in a vault scoped to that store. A leaked database backup should not leak working credentials.

#8. The data belongs to the merchant

Isolation has a friendly side too: because everything a store owns is clearly scoped, exporting it is easy. In nqrah, merchants can export their data at any time. That promise is much easier to keep when the database already knows where one store ends and the next begins.

Isolation is one of those features nobody notices when it works. That is exactly why it belongs in the database, not in everyone’s memory.