Fourth in a series on building out-of-band application security testing (OAST) infrastructure from scratch. Earlier posts went down to the wire (JNDI, DNS, the sandbox). This one is about a quieter risk that kills more SaaS products than any exploit: one missing WHERE clause.

In a multi-tenant app, the entire security model often reduces to a single sentence: every database read that returns user-owned data is filtered by the current user’s id. Get it right everywhere and tenants are isolated. Forget it in one endpoint — one db.query(Modifier).filter(Modifier.id == requested_id) without the owner_user_id check — and any user can read any other user’s data by guessing an integer. It’s the most common serious bug in SaaS, and it’s a bug of omission, which is exactly the kind code review is worst at catching.

The explicit filter is layer one, and you keep it — it’s readable, it’s the contract, every reviewer understands it. But “we’ll just always remember” is not a security control. So this platform adds a second layer that’s automatic and fails closed: even if you forget the WHERE, the database query physically cannot return another tenant’s rows.

The idea: filter at the ORM boundary, not the call site

SQLAlchemy fires a do_orm_execute event for every ORM statement before it hits the database. That’s the choke point. If, at that moment, we can (a) tell whether the statement touches a tenant-owned table and (b) know who the current user is, we can inject the WHERE owner_user_id = :uid ourselves — on every SELECT, UPDATE, and DELETE, whether or not the call site remembered.

Two pieces make it work: a marker so we know which models are tenant-owned, and the hook that does the injection.

Mark the tenant-owned models

A mixin, and a classmethod that returns the filter expression. The common case is an owner_user_id column:

class TenantScoped:
    @classmethod
    def __tenant_filter__(cls, user_id: int):
        return cls.owner_user_id == user_id

Models with a different shape override it — User filters on its own id, UserState on user_id, join tables that own no direct FK to users (chain steps, modifier versions) scope through their parent. The point is that every owner-scoped model declares, in one place, how to constrain it to a tenant. Forgetting the mixin on a new model is itself catchable — a static check in the test suite flags any owner-scoped table that doesn’t mix it in.

Inject the filter on every statement

The hook collects every TenantScoped mapper the statement touches and adds the criteria via with_loader_criteria (which also reaches relationship loads and join-aliased copies, so a lazy-load can’t sneak around it):

@listens_for(Session, "do_orm_execute")
def _enforce_tenant_scope(state):
    if not (state.is_select or state.is_update or state.is_delete):
        return                      # INSERTs pin owner_user_id at the call site
    tenanted = [m.class_ for m in _mappers(state)
                if issubclass(m.class_, TenantScoped)]
    if not tenanted:
        return
    if _is_tenant_bypassed(state):
        return                      # explicit, grep-able opt-out (see below)

    uid = _current_user_id(state)
    if uid is None:
        raise TenantScopeMissing(...)   # <-- fail CLOSED

    for cls in tenanted:
        state.statement = state.statement.options(
            with_loader_criteria(cls, cls.__tenant_filter__(uid), include_aliases=True)
        )

The part that matters most: fail closed

Look at the uid is None branch. If a tenant-scoped query runs and we can’t determine the current user, we don’t run it unfiltered — we raise. A 500 error is a bug report. A query that silently returns every tenant’s rows because the user context wasn’t set is a breach you find out about on Twitter. The default has to be “refuse,” not “return everything.” This is the single most important design decision in the whole mechanism, and it’s one line.

It also changes the cost of the mistake in a useful way. With only the explicit-filter layer, a forgotten WHERE is a silent data leak. With this layer, the same forgotten WHERE becomes either (a) correctly filtered anyway (the hook did it) or (b) a loud TenantScopeMissing exception pointing at the exact route. Both are fine outcomes. A silent leak is not on the menu.

Where does uid come from? (a FastAPI threadpool trap)

The obvious place to stash “current user” is a ContextVar. It’s the wrong place here, and the reason is a genuinely sharp edge worth knowing.

FastAPI runs synchronous dependencies and route handlers in a threadpool. Python copies contextvars per task, so a ContextVar.set() inside one sync dependency is invisible to the next sync dependency or the route — they ran in different threadpool contexts. Pin the user id in a ContextVar in your auth dependency and it’ll be None again by the time the query runs. You get fail-closed 500s everywhere and conclude the whole approach is broken.

The fix: pin the id on the Session object itself (db.info["current_user_id"]), set by the same auth dependency that resolves the user. The Session is shared by value across the dependency chain and the route — it’s literally the thing you’re running queries against — so the pin follows the request without any contextvar gymnastics. (A ContextVar stays as a fallback for scripts and out-of-band paths that don’t go through the web dependency chain.)

uid = state.session.info.get("current_user_id") or current_user_id.get()

Bypasses: make them rare, explicit, and grep-able

Plenty of code legitimately reads across tenants — the listeners (they don’t know whose host a callback hit until attribution runs), scheduled jobs (owner-stats, retention), admin views, and the auth dependency itself (it has to look up the User before the tenant is known). For these there are exactly two opt-outs, both intentionally visible in a code search:

  • bypass_session() — a whole session with the filter off (listeners, jobs, attribution).
  • .execution_options(bypass_tenant=True) — one statement (admin views, auth deps, the public homepage stats counter).

The goal isn’t to forbid cross-tenant access — it’s to make every instance of it loud. A reviewer can grep bypass_tenant and audit the entire cross-tenant surface of the app in one command. Contrast with the default world where cross-tenant access looks identical to correct code (it’s just a query that happens to be missing a filter). Naming the exception turns an invisible risk into an auditable list.

One more SQLAlchemy gotcha: Session.get skips the hook

SQLAlchemy 2.0’s Session.get(Model, pk) short-circuits on an identity-map hit — if the object is already cached from an earlier query, it returns it without emitting do_orm_execute. So a row loaded in one request could, in principle, be handed back by a .get() in a later request that should’ve been filtered. The fix is to force populate_existing=True for TenantScoped entities on .get, which re-runs the SELECT (and thus the hook) instead of trusting the cache. Small, but it’s exactly the kind of corner where “we filter everything” quietly becomes “we filter everything except the fast path.”

Takeaways

  • The explicit WHERE owner_user_id is the contract; an automatic layer is the safety net. Keep both. The first is readable; the second is what saves you the day someone forgets the first.
  • Fail closed. A tenant query with no known tenant must raise, never run unfiltered. This one decision converts “silent cross-tenant leak” into “loud 500 at the exact line.”
  • Pin tenant identity on the Session, not a ContextVar — FastAPI’s sync-dep threadpool makes contextvars silently empty across the dependency chain.
  • Make every cross-tenant bypass explicit and grep-able. You can’t review what looks identical to normal code; you can review a named list.

This is “Phase 1” — application-layer defense in depth. The heavier hammer, Postgres row-level security (RLS) enforced in the database regardless of which ORM or raw query runs, is a natural “Phase 2.” But letting the app-layer version burn in first is the right order: it’s reversible, debuggable, and catches the bug class today. Next in the series: back to the wire, with a hand-rolled LDAP listener — the mail-less pillar of Log4Shell.

This is part of a series I’m writing on building OAST infrastructure from scratch — hand-rolling a JNDI Reference, an authoritative DNS server, and sandboxing untrusted Python for the response logic. This post added the multi-tenant safety net; next it’s back to the wire with a hand-rolled LDAP listener — the mail-less pillar of Log4Shell.