Isolating several companies inside a single database

A SaaS ERP raises one question before all the others: how do you guarantee that company A will never see a row belonging to company B.

Three answers exist. One database per client, one schema per client, or a single database with a tenant_id column. The third is the cheapest to operate and the most dangerous to write.

The application-level filter is not enough

The temptation is to add WHERE tenant_id = :current everywhere. It works — until the first omission.

And there will be an omission. Not in the main query, written carefully: in the export script launched on a Friday evening, in the join added six months later, in the statistics query written by a developer who just joined.

One omission is enough. The cost is not a bug, it is a data leak between competing clients.

Row Level Security moves the guarantee

PostgreSQL can apply the filter itself, at the table level:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

From then on, every query against invoices is filtered, no matter how it is written. A developer who forgets the clause gets zero rows, not the neighbour's rows.

The application only has to set the variable at the start of each transaction:

SET LOCAL app.tenant_id = '…';

SET LOCAL, not SET: the value dies with the transaction. In a connection pool, a variable that outlives its transaction is exactly the bug you were trying to avoid.

The trap that cancels everything

RLS does not apply to the table owner or to a superuser. By default, applications often connect with the role that created the schema — that is, the role that silently bypasses every policy.

The test passes. Isolation does not work.

You need two distinct roles:

-- Migration role: owner, bypasses RLS.
CREATE ROLE erp_owner LOGIN PASSWORD '…';
 
-- Application role: subject to the policies.
CREATE ROLE erp_app LOGIN PASSWORD '…';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO erp_app;

The application runs as erp_app. Migrations run as erp_owner. This is not a precaution: it is what makes RLS actually exist.

And to be certain, force the policy even for the owner:

ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

The test worth writing

A single automated test covers the whole mechanism:

def test_tenant_isolation(db):
    with db.tenant("company-a"):
        db.execute("INSERT INTO invoices (…) VALUES (…)")
 
    with db.tenant("company-b"):
        rows = db.execute("SELECT * FROM invoices").fetchall()
 
    assert rows == []

If it passes with the right role, isolation holds. If it passes with the owner role, it proves nothing — hence the importance of running it with exactly the production connection.

What it costs

RLS adds a predicate to every query. On properly indexed tables — a composite index starting with tenant_id — the overhead is a few percent.

That is the price of a guarantee application code cannot offer: one that holds even on the day someone forgets.