Tenant isolation that survives my own mistakes

How Mero Pasal moves multi-tenant isolation out of Django querysets and into PostgreSQL row-level security, so a forgotten filter returns zero rows instead of another shop’s sales.

Mero Pasal runs every shop out of one PostgreSQL database. The usual way to keep shops apart is to filter every queryset by tenant, and that works right up until one developer forgets one filter. Then shop A is reading shop B’s sales, and nothing in the code review looked wrong. I did not want the guarantee to depend on discipline, so the database enforces it.

Two roles, and the owner obeys too

Migrations run as a privileged role that owns the tables. The application connects as a different role: not a superuser, not the owner. Every tenant-scoped table gets row-level security switched on and forced, so even a session running as the owner cannot read across tenants by accident. The same predicate guards writes, so a row cannot be inserted into another shop either.

ALTER TABLE "sales_invoice" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "sales_invoice" FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON "sales_invoice"
  USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);

What a migration emits for each tenant-scoped table (here, sales_invoice).

A helper generates that SQL from the table name, after checking the name is a plain identifier, so adding a tenant table is one line in its migration rather than a policy someone has to remember to write.

The tenant comes from exactly one place

Middleware resolves the shop from the subdomain of the Host header, and only from there: never from a header, a body field, a query parameter or a JWT claim. The lookup is cached, and a shop that is past due, suspended or cancelled is turned away with a 402, 423 or 404 before any business code runs.

SET LOCAL, inside the request transaction

The middleware opens one transaction around the request and sets the tenant for that transaction only. The value is passed as a query parameter, so there is nothing to quote or escape.

def set_local_tenant(connection, tenant_id):
    """Set app.tenant_id for the current transaction (SET LOCAL)."""
    with connection.cursor() as cursor:
        cursor.execute("SELECT set_config(%s, %s, true)", ["app.tenant_id", str(tenant_id)])

core/db/rls.py: the whole per-request hook.

LOCAL is what makes this safe behind PgBouncer. In transaction pooling mode, consecutive requests can share one server connection; a session-level SET would carry one shop’s tenant into the next request on that connection. A transaction-scoped setting dies with the transaction, so session pooling is simply forbidden for this database.

The empty-string trap

This one is easy to miss. After a transaction that used SET LOCAL rolls back, PostgreSQL does not return the custom setting to NULL; it leaves it at its boot value, the empty string. Casting an empty string to uuid raises an error instead of matching nothing. That is why the policy wraps the setting in NULLIF: NULL and empty must both mean “no tenant”, and both must fail closed.

Failing closed

Platform hosts such as www, app and admin get no tenant and no SET LOCAL at all. There, current_setting returns NULL, the policy matches no row, and an unfiltered query against a tenant table returns an empty list. That is the whole point: a mistake produces a bug report about missing data, never a data leak.

Some work legitimately crosses tenants: background jobs, the platform-admin control plane, support sessions. Each of those gets an explicit, audited elevation path rather than a bypass someone could reach for casually.

Append-only, enforced by grants

The same idea, the database enforcing what code review would otherwise have to catch, also protects the stock ledger. The application role has UPDATE and DELETE revoked on stock movements, so a wrong entry can only be corrected with a counter-entry. History is not editable by convention; it is not editable at all.

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'dokan_app') THEN
        REVOKE UPDATE, DELETE ON "stock_movement" FROM dokan_app;
    END IF;
END
$$;

Guarded so the migration still applies on test databases without the production role.

None of this replaces filtering in the application. It changes what a missed filter costs. The queryset is still written carefully; the database is there for the day it is not.

The case studyMero Pasal