CodePark/ questions / Multi-tenant isolation
START PRACTICINGPRACTICE
Backend question · Auth & security › Multi-tenant isolation

You enforce tenant isolation with Postgres Row-Level Security and set the tenant per request with SET app.tenant_id. The app uses a connection pool. What can go wrong, and how do you set it up safely?

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

-- per request
SET app.tenant_id = '6f1c...';
SELECT * FROM orders;

The danger is that SET lasts for the whole database session, which means the connection. A pool hands that connection to the next request. If the next request forgets to set its own tenant, or fails before it does, it runs with the previous tenant's id and reads their rows. With PgBouncer in transaction pooling mode, session settings can even leak between different app instances.

Safe setup:

  • Run each request's queries in one transaction.
  • Set the tenant with SET LOCAL app.tenant_id = ..., or set_config('app.tenant_id', $1, true). The true means "only for this transaction". The setting ends at COMMIT or ROLLBACK, so it can't leak.
  • Make the policy fail closed. A missing or empty setting must match no rows or raise an error.
  • Connect as a role that does not own the tables and has no BYPASSRLS. Superusers and roles with BYPASSRLS always skip RLS. Table owners skip it too, unless you add FORCE ROW LEVEL SECURITY.
  • Add a policy for writes as well, using WITH CHECK, so an insert can't use another tenant's id.

Keep tenant filters in the app too. RLS is the backstop, not the only lock.

  • SET lasts for the connection, so a pooled connection can carry the last tenant's id.
  • Use SET LOCAL or set_config(..., true) inside a transaction.
  • A missing tenant setting must match no rows.
  • The app's database role must not own the tables or have BYPASSRLS.
  • Use WITH CHECK so writes are scoped too.