skip to content

In a shared-table multi-tenant database where isolation depends on every query carrying a tenant filter, how would you stop one missing filter from exposing another customer's rows? Discuss database row-level security policies versus filtering only in application code, including how the tenant identity reaches the database over a connection pool.

level: seniorimportance: must knowfreq 44%

answer

  1. app filter + database policy = two independent layers
  2. FORCE policies - the owner is otherwise exempt
  3. SET LOCAL per transaction, not per connection
  4. write check stops cross-tenant inserts
  5. test: query as A, assert zero rows of B

basics

~20 s

Layer defenses: inject the tenant predicate at one application choke point, and enforce it again in the database with row-level security policies keyed off a per-transaction session setting. Force policies so table owners are not exempt, reset the setting on pooled connections, and test cross-tenant access.

solid answer

~60 s

Application filtering is necessary but not sufficient: it is one forgotten predicate away from a breach, and ad-hoc queries or a new service bypass it entirely. **Defense in depth:** 1. One choke point injects the tenant predicate from the authenticated request context - no hand-written query is trusted. 2. **Row-level security** policies in the database (PostgreSQL 9.5+, SQL Server 2016+) attach `tenant_id = current tenant` to every read and, via a check expression, to every write, so the filter cannot be forgotten. 3. Run as a role that does **not** bypass policies - table owners and superusers are exempt unless the table forces row-level security. 4. Carry the tenant in a **transaction-scoped** setting (`SET LOCAL`), never a connection-scoped one: a pooled connection reused without a reset runs the next request under the previous tenant. 5. Test it: a suite that queries as tenant A and asserts zero rows of tenant B, plus a check that every new table has a policy. Costs: the predicate appears in every plan, policy expressions must be cheap and index-friendly, and debugging "the row is missing" gets harder.

code

sql · 12 lines
sql
ALTER TABLE invoice ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoice FORCE ROW LEVEL SECURITY;

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

-- per request, inside the transaction (not connection-scoped):
BEGIN;
  SET LOCAL app.tenant_id = '3f1c0000-0000-0000-0000-000000000000';
  SELECT * FROM invoice WHERE created_at > now() - interval '30 days';
COMMIT;

go deeper

for a junior

Say that relying on developers to remember a WHERE clause is not a security boundary, and that the database can enforce the filter itself.

for a middle

Describe the two layers, the read and write predicates, and how the tenant identity is supplied per request.

for a senior

Cover the operational traps: owner bypass, pooled-connection context leakage, missing policies on new tables, plan cost, and the cross-tenant test suite.

for a principal

Weigh policy-based isolation against role-per-tenant or siloed databases, define the org-wide invariant (no data path without a tenant context), and say how it is enforced in CI and across derived stores.

## The failure mode being defended against In a pooled schema, `WHERE tenant_id = ?` *is* the security boundary. Realistically it will be omitted eventually: a hand-written report query, a new service, a background job that iterates "all rows", an admin tool, a raw SQL escape hatch under the ORM. The consequence ranges from showing one customer another's invoices to an UPDATE that silently rewrites another tenant's data. Because the boundary is enforced in code, it fails the way code fails - quietly, in the least-reviewed corner. ## Layer 1: a single application choke point All data access flows through one component that reads the tenant from the authenticated request context (never from a client-supplied parameter) and injects the predicate: an ORM global filter, a repository base class, or query builders that refuse a query lacking a tenant scope. This is fast, portable across engines, and catches the ordinary case. Its weakness is that it protects only paths that go through it. ## Layer 2: row-level security in the database Row-level security lets the database attach a predicate to a table so that every statement, from any client, sees only matching rows. Conceptually you declare: rows are visible when `tenant_id = <current tenant>`, and a separate check expression governs INSERT and UPDATE so a tenant cannot write rows stamped with someone else's id. Without that write check, isolation is read-only and a bug can still plant rows in another tenant. Three traps: - **Bypass.** The table owner - often the same account the application connects as - is exempt from policies unless the table is explicitly set to *force* row-level security, and superusers or roles with a bypass attribute are always exempt. Connect the application as an ordinary role, and reserve a bypass role for migrations and support tooling. - **New tables.** A table created without a policy is wide open. Make policy creation part of the migration checklist and assert it with a test that scans the catalog for tenant-owned tables lacking a forced policy. - **Performance.** The policy expression becomes an extra qualifier on every access. Keep it a cheap, index-friendly comparison against a session setting; a policy that calls a lookup function per row destroys plans. Since the predicate is `tenant_id = constant`, indexes leading with tenant_id still work, but be aware that the optimizer treats policy predicates specially - some operators can be evaluated before a policy filter, which is why engines distinguish trusted (leakproof) functions. ## Getting the tenant identity to the database Three options: 1. **A session or transaction setting** the application assigns from request context, which the policy reads. Cheapest and the usual choice - but only safe when scoped to the transaction (`SET LOCAL`) or explicitly reset when a pooled connection is returned. This is the classic bug: with a shared pool, a connection still carrying `app.tenant_id = A` is checked out by a request for tenant B, and if any code path forgets to set it, B runs under A's policy. With a transaction-pooling proxy the risk is worse, because statements from different tenants can multiplex onto one backend session. 2. **A database role per tenant**, with policies keyed on the current user. Very strong - the credential *is* the tenant - but role and connection-pool sprawl make it impractical past a few dozen tenants. 3. **Signed context claims** verified inside the database - powerful, rarely worth the complexity. ## Layer 3: verification, and everything downstream Add automated tests that open a session as tenant A and assert zero rows belonging to tenant B, including through joins, aggregates and writes; a schema test that every tenant-owned table has a forced policy; and monitoring for queries executed with no tenant context. Remember the boundary must extend past the database: cache keys, search-index documents, object-storage prefixes, message payloads and exported files all need the tenant in the key. Row-level security protects tables and nothing else. ## Cost, and when it is not available Policies add plan noise and make debugging harder - a support engineer sees "no rows" rather than the truth - and they do not exist in every engine (MySQL has no equivalent, forcing view-based or purely application-level enforcement). The honest position: application filtering for ergonomics, database policies as the independent backstop, tests as the proof. Two independent layers mean one bug is a defect, not a breach.

  • Why is setting the tenant with a connection-scoped SET dangerous behind a connection pool?
    Pooled connections outlive a single request, so a connection still carrying the previous tenant's setting can be handed to another request; if any code path fails to set it, that request runs under the wrong tenant's policy. Transaction-scoped settings are discarded at commit or rollback, which fails closed instead. With transaction-level pooling proxies the risk is worse, since statements from different tenants can share one backend session.
  • If a policy defines only a read predicate and no write check, what can still go wrong?
    Reads are isolated but writes are not fully constrained: a buggy or malicious INSERT or UPDATE can stamp rows with another tenant's id, planting or moving data the writer cannot then see. A check expression on writes rejects any row whose tenant_id differs from the current context, which closes that hole and also prevents a tenant from reassigning ownership of its own rows.

saying these in an interview costs you the question

  • Assuming row-level security applies to the account that owns the table without forcing it
  • Setting the tenant context per connection and trusting the pool to clean it up
  • Taking the tenant id from a request parameter or header instead of the authenticated session
  • Treating database policies as complete isolation while caches, search indexes and exports stay unscoped
  • Writing policy predicates that call per-row lookup functions and wrecking query plans

context

resolving links…