01
why tenant filters in the orm leak
Most multi-tenant Django apps isolate tenants the same way: a tenant_id column on every table and a custom manager that adds .filter(tenant=request.tenant) to every queryset. That works for as long as every query goes through the manager, and the trouble is how many queries don't. A raw SQL report bypasses it, a management command calls Model.objects because there's no request to take the tenant from, someone copies an old model and forgets the default manager, a third-party package runs its own ORM queries without knowing your manager exists, and an admin action iterates over a queryset built somewhere else. None of these fail loudly. The query succeeds and returns rows, they just belong to another customer, and with one tenant in development and staging nobody sees it until production.
Postgres row-level security moves the rule into the database. You attach a policy to each tenant table, Postgres adds it as an implicit predicate to every SELECT, INSERT, UPDATE and DELETE, and a query that forgets the tenant filter gets the current tenant's rows or nothing. Invoice.objects.all(), a raw cursor and a library's internal query all get the same treatment. It also makes the argument to an auditor much shorter: instead of proving that every code path uses the right manager, you show the policies, the one place that sets the tenant, and a test that checks the database refuses cross-tenant reads.
This post covers the version of that setup that actually works with Django 6 and Postgres 17 or 18: the schema, the roles, the Django wiring (where most published examples are broken), PgBouncer, performance, and when a schema or a database per tenant is the better choice. The SQL and Python below were checked against Postgres 18 and Django 6.1.
02
the schema: tenant_id everywhere and composite foreign keys
Shared tables with a tenant_id column are the default we'd pick for B2B SaaS. Every tenant-scoped table gets the column, even the ones that could reach the tenant through a join, because a policy that only compares a column on the row itself is cheap, predictable in EXPLAIN, and indexable. The alternative, a policy on line_item with a subquery against invoice, works but drags a second table into every query plan.
Foreign keys between tenant tables should include the tenant. This matters more than it looks, because the Postgres docs state that referential integrity checks always bypass row security. A single-column foreign key will happily accept a reference to another tenant's row that the current tenant can't even see:
With the composite key, inserting an invoice for tenant B that points at tenant A's project fails with violates foreign key constraint. With a plain project_id references project(id), the same insert succeeds even though a select from tenant B returns zero rows for that project, and now tenant B's data hangs off tenant A's row. The unique (tenant_id, id) constraint exists only so the composite key has something to reference.
Uniqueness needs the same treatment. A global unique (email) on users means one tenant's signup can fail because another tenant already has that address, which is both a bug and an information leak, so it usually becomes unique (tenant_id, lower(email)).
If you're retrofitting an existing database, add tenant_id as nullable, backfill it in batches, then add the foreign keys not valid and run validate constraint separately, because validation takes a SHARE UPDATE EXCLUSIVE lock and doesn't block writes. Don't finish with a bare set not null on a big table: it scans the whole table under an ACCESS EXCLUSIVE lock. Add a check (tenant_id is not null) not valid constraint, validate it, and then set not null skips the scan. On Postgres 18 you can add the not null constraint itself as not valid and validate it later. The zero-downtime migrations post on this blog walks through that sequence in Django.
03
roles and policies
RLS only protects you from roles it applies to. The Postgres docs are precise about who escapes it: superusers and roles with BYPASSRLS always bypass it, and the table owner bypasses it unless the table has FORCE ROW LEVEL SECURITY. Ordinary roles that aren't the owner are always subject to policies, so FORCE only changes things for the owner. That suggests three roles:
The app never connects as the owner, so a stray migration-time superuser connection can't end up serving requests. FORCE is still worth setting, because it means a script that accidentally runs as app_owner sees nothing instead of everything. The policy itself:
Each piece of that expression is there for a reason. current_setting('app.tenant_id', true) returns NULL instead of raising unrecognized configuration parameter when the setting has never been set on the connection. But once a connection has run SET LOCAL app.tenant_id and the transaction has ended, the placeholder doesn't go back to NULL, it reads as an empty string, and ''::uuid raises invalid input syntax for type uuid. On a pooled connection that means a query without tenant context crashes instead of returning nothing, which is why the nullif is there. With it, a missing tenant compares against NULL and matches no rows. using filters what you can read, update and delete, and with check stops you from writing a row with someone else's tenant_id, which fails with new row violates row-level security policy.
In Django this goes into a migration that runs as app_owner:
The tenant table itself and the user table stay outside RLS, because you have to look up the user and their tenant before there's a tenant to filter by. Then add a test or CI check that fails when a new table slips through:
In a real project you'd filter that by tables that have a tenant_id column, but the idea is that a forgotten policy breaks the build, not a customer.
04
setting the tenant in django
This is where most published Django examples go wrong. SET LOCAL only lasts until the end of the current transaction, and outside a transaction block Postgres ignores it with WARNING: SET LOCAL can only be used in transaction blocks. Django runs in autocommit by default, so a middleware that executes SET LOCAL and then calls the view sets nothing at all. ATOMIC_REQUESTS = True doesn't rescue it either, because Django wraps only the view in that transaction, and middleware runs outside it. With the policy above, the result is that every query in the view returns zero rows, which is at least the safe way to fail.
The fix is to open the transaction yourself and set the tenant inside it. set_config() with true as the third argument is the function form of SET LOCAL, and unlike SET it accepts a bound parameter, so the tenant id never gets formatted into SQL:
The middleware wraps the rest of the request in that context:
Put it after AuthenticationMiddleware, since it reads the tenant from the logged-in user (here a custom user model with a tenant foreign key). Never take the tenant from a header or query parameter the client controls, because then RLS faithfully enforces whatever tenant the attacker asked for. The set_rollback line matters too. By the time the response reaches this middleware, Django has already turned a view exception into a 500 response, so without it the transaction would commit whatever the view wrote before it crashed. With it, the behaviour matches ATOMIC_REQUESTS, and you don't need that setting on top. One thing this can't cover is a StreamingHttpResponse: its iterator runs after the middleware has returned and the transaction has closed, so queries inside the generator see no rows. Fetch the data in the view, or open a tenant_context inside the generator.
Celery tasks and management commands have no request, so they call the same context manager with an explicit tenant id. Pass the id as a task argument, not the model instance:
Jobs that legitimately need every tenant, like billing runs or cross-tenant reports, use a second DATABASES entry that connects as app_admin, and only code you've reviewed for that purpose may use it. Keep it out of anything reachable from a request.
05
pgbouncer in transaction mode
Transaction pooling is the usual way to run many Django workers against a modest Postgres, and it fits this design because SET LOCAL and set_config(..., true) die with the transaction, which is exactly when PgBouncer hands the server connection to someone else. Plain SET is the dangerous one. Run SET app.tenant_id = '...' through PgBouncer in transaction mode and every later client that gets the same server connection inherits it, which you can reproduce with two psql sessions and select pg_backend_pid(), current_setting('app.tenant_id', true). Grep your codebase for session-level SET before rolling out RLS behind a pooler.
Older guides add server_reset_query = DISCARD ALL to clean up between clients. In transaction mode that line does nothing: the PgBouncer docs say the reset query is not used when transaction pooling is on, and it only runs in session pools unless you turn on server_reset_query_always, which the docs describe as a workaround for broken setups. The same guides say to disable prepared statements, which was true until PgBouncer 1.21 (October 2023) added support for protocol-level named prepared statements in transaction mode, controlled by max_prepared_statements. A config for this setup looks like this:
On the Django side, the PostgreSQL notes in the Django docs say that transaction pooling requires "DISABLE_SERVER_SIDE_CURSORS": True in the database settings, because a server-side cursor from .iterator() outlives the transaction and the next fetch may land on a different server connection. Migrations should connect straight to Postgres or through a session pool, as app_owner, since CREATE INDEX CONCURRENTLY and friends don't belong in a transaction pool.
The last thing to watch is long transactions. Wrapping each request in transaction.atomic() is fine until a view makes a slow HTTP call or uploads a file in the middle of it. The connection sits idle in transaction, holds a pool slot and its locks, and keeps vacuum from cleaning up anything newer than its snapshot. Do slow external work before or after the database part, and consider idle_in_transaction_session_timeout on the app_user role.
06
performance and policy gotchas
The policy adds tenant_id = <setting> to every query, so indexes for hot paths should lead with tenant_id: (tenant_id, created_at desc) rather than (created_at desc), because the real query is rarely "all open invoices" and almost always "this tenant's open invoices, newest first". The comparison against current_setting() is fine for index use, since the function is stable within a statement.
The subtler cost comes from leakproof functions. Postgres evaluates policy conditions before any user-supplied condition that could leak information, and the only functions allowed to run earlier are ones marked leakproof. Equality on text and uuid is leakproof, but lower() and the jsonb containment operator @> aren't. In practice, a GIN index on a jsonb column or an expression index on lower(name) that the planner uses happily for a BYPASSRLS role can be ignored under RLS, and the same query turns into a sequential scan filtered by the policy. Reproducing this takes a few minutes: build a test table, compare EXPLAIN as app_admin and as app_user, and run that comparison for your heaviest queries before rollout, not after. Marking functions leakproof yourself requires a superuser and a real guarantee that the function can't reveal its arguments through errors, so it's rarely the right fix. Usually the answer is an index that starts with tenant_id so the tenant filter does most of the narrowing.
Views and functions are the other place isolation quietly disappears. A view runs with its owner's permissions by default, and that includes the owner's RLS status, so a reporting view created by a superuser in a migration returns every tenant's rows to app_user. Since Postgres 15 you can create views with (security_invoker = true), which applies the caller's policies instead, and that should be the default for any view over tenant tables. Functions declared security definer have the same property and deserve the same suspicion.
07
when a schema or database per tenant is the better call
Schema-per-tenant gets picked because it feels safer, and it fits a specific situation: a few dozen large tenants, each wanting custom objects, custom release timing, or easy pg_dump --schema exports. Past that, catalog bloat, migrations that fan out across hundreds of schemas and search_path mistakes start to cost more than RLS ever does. A search_path bug also tends to fail open, by reading the wrong tenant's table, while a missing RLS context fails closed with zero rows or a policy violation.
A separate database per tenant is the right answer for reasons that are usually contractual: a customer who needs data pinned to a region you don't run, separate encryption keys with operator boundaries, or a tenant whose write volume would dominate shared tables and their vacuum. The good news is that a clean RLS design makes that move an extraction rather than a redesign. Every row already carries tenant_id, the composite keys already encode ownership, and the application already assumes a tenant scope on every query, so copying one tenant out in dependency order and switching its routing is a planned maintenance job.
