Enforcing tenant scoping

In a pooled system, the most important rule is that no query returns another tenant's rows. Relying on every developer to remember the right WHERE clause, in every query, forever, will eventually fail. The answer is defense in depth: several layers that each catch what the others miss.

The big idea

Each layer checks something different:

1. Derive the tenant from authentication. The tenant ID comes from the verified session or token. It never comes from a URL parameter or request body that a user can edit. An API that trusts ?tenant_id= is an insecure direct object reference waiting to happen.
2. Scope at the data-access layer. The repository or ORM adds the tenant filter automatically, so application code can't forget it. Many frameworks support default scopes keyed on a request-scoped tenant.
3. Enforce in the database. Row-level security filters rows no matter what SQL the application sends.
4. Test for leaks. Automated tests create two tenants, act as one, and assert that nothing from the other is visible through any API.

Walking through it

Postgres row-level security looks like this:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;  -- apply even to the table owner

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

-- Per request, inside the transaction, the app sets the tenant from the auth token:
BEGIN;
SET LOCAL app.tenant_id = '6f1c2a9e-0d4b-4c43-9a51-2f7e8b3d1c10';
SELECT id, tenant_id, status FROM invoices WHERE status = 'overdue';
COMMIT;

The SELECT has no tenant filter, yet it returns only the rows for the tenant in app.tenant_id:

   id   | tenant_id                            | status
--------+--------------------------------------+---------
   3018 | 6f1c2a9e-0d4b-4c43-9a51-2f7e8b3d1c10 | overdue

USING limits what can be read, updated or deleted. WITH CHECK blocks inserting a row into another tenant, or moving a row to one.

Details that bite:

• Superusers and roles with BYPASSRLS ignore policies. Table owners do too, unless you use FORCE ROW LEVEL SECURITY. Run the application as a dedicated role that owns nothing.
• Use SET LOCAL, not SET. SET LOCAL lasts only for the transaction. With a connection pooler such as PgBouncer in transaction mode, a plain SET would leak one request's tenant into the next request on the same connection. That is the exact bug RLS exists to prevent.
• Lead every tenant-scoped index with tenant_id. An index like (tenant_id, status) keeps the policy's filter fast. Without it, lookups become scans.

The same discipline applies beyond the database. Put the tenant ID in every cache key (t:{tenant}:user:{id}), every search document, every object-storage path and every message topic. A shared layer that ignores tenancy leaks what the database protects.

Common mistakes

• Taking the tenant ID from the request. An edited parameter then reads another customer's data.
• Using plain SET behind a pooler. The tenant setting outlives the request.
• Running the app as the table owner or a superuser. The policies are silently skipped.
• Leaving cache keys unscoped. The cache then serves one tenant's data to another.

Check yourself

1. Why does SET LOCAL matter when PgBouncer runs in transaction mode?

   A plain SET stays on the connection, so the next request reusing that connection would inherit the previous tenant.

2. Why is a tenant ID in a URL parameter risky?

   A user can edit it to another tenant's ID. The server must check it against the authenticated session, or ignore it entirely.

Key takeaways

• Take the tenant from authentication, never from user input.
• Scope automatically in the data layer, so developers can't forget the filter.
• Enforce in the database with row-level security, and run the app as a non-owner role.
• Test for leaks with two tenants, and scope every cache key, search document and path.

Further learning

The PostgreSQL documentation explains row security policies, including the bypass rules that catch teams out.

PostgreSQL docs: Row security policies5.9. Row Security Policies # In addition to the SQL-standard privilege system available through GRANT, tables can have row security policies …PostgreSQL Documentation

Next: noisy neighbors, which covers what happens when one tenant uses too much.