Row-level security: the tenant check you can't forget

Row-level security lets a table filter its own rows. You turn it on and attach a policy, and from then on every query against that table gets the policy's condition added, whether the application asked for it or not.

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents FOR ALL
  USING      (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

USING decides which rows you can see, update or delete. WITH CHECK decides which rows you're allowed to write, so a tenant can't insert a row stamped with someone else's id either.

The true in current_setting(..., true) means “return NULL if it's not set” instead of raising an error. And tenant_id = NULL is never true. So a request that forgot to set a tenant sees nothing at all. It fails closed, which is exactly the direction you want a bug to fail in.

The application sets app.tenant_id at the start of every transaction, from the authenticated request:

@event.listens_for(session, "after_begin")
def _set_tenant(sess, transaction, connection):
    # set_config(..., true) is SET LOCAL, but it accepts bind parameters
    connection.execute(
        text("SELECT set_config('app.tenant_id', :tid, true)"), {"tid": tenant_id}
    )

It has to be SET LOCAL (that's what the third argument does), not plain SET. A plain SET lasts for the whole connection, and connections are pooled. The next request to borrow that connection would inherit the previous tenant's id. That's a cross-tenant leak created by the very mechanism meant to prevent one. SET LOCAL ends with the transaction.

This is the part worth reading twice. Row-level security has exits, and they are all quiet.

The table owner skips it. By default, policies don't apply to the role that owns the table. If your app connects as the same role that ran the migrations, you've enabled RLS and it does nothing. FORCE ROW LEVEL SECURITY makes it apply to the owner too.

Superusers skip it, even with FORCE. So do roles with BYPASSRLS. The default user in most Docker Postgres setups is a superuser. The application needs its own role that owns nothing and bypasses nothing; migrations run as the owner, the app runs as the app role.

Views can skip it. A view normally runs with its owner's rights, so a view created by the owner reads the underlying table as the owner. From PostgreSQL 15 you can create it with security_invoker = true so it runs with the caller's rights instead.

Two tables stayed outside RLS on purpose: tenants and API keys. Authentication has to look up the key before any tenant is known, so there's no tenant to filter by yet. The app role gets plain grants on those two and nothing else special.

One table needed a split policy. It holds a shared vocabulary: rows with no tenant that every tenant reads, plus rows each tenant adds for itself. So USING allows tenant_id IS NULL OR tenant_id = current tenant, while WITH CHECK only allows the current tenant. Every tenant can read the shared rows, no tenant can create or edit one, and the shared rows are seeded by the migration, running as the owner.

And the audit tables got a different kind of protection: the app role has INSERT and SELECT on them and nothing else. The application can add to the history but can't rewrite it, even with a bug.

A policy you haven't tested is a policy you hope works. The tests connect to a real Postgres as the app role and check the things that matter: with tenant A's context set, a hand-written query with no WHERE clause at all still returns only A's rows; with no context, it returns nothing; inserting a row for tenant B from tenant A's context is rejected; the shared rows can be read but not written; and UPDATE or DELETE on the audit log is refused.

The application still scopes its own queries by tenant. Row-level security isn't a replacement for that; it's what catches the query where someone forgot. That's the whole point: the IDOR bugs I fixed on the ERP would have returned empty results here instead of another company's data.