Tenancy filters go missing wherever the request path does not reach

The compaction job at a mid-sized customer support vendor ran at 03:00 and did something entirely sensible: it pulled memories older than thirty days, grouped them into batches of 200, asked a small model to compress each batch into a shorter set of durable statements, and wrote the result back. It had been running for four months when a customer at a plumbing supplies distributor asked the agent what their standard lead time was and got the answer "your standard lead time is 5 working days, and your Ontario warehouse is closed Fridays" — which was true of a completely different company, an electrical wholesaler on the same platform.

The query at the top of the job was three lines long and had passed review twice:

rows = db.execute(
    "SELECT id, org_id, user_id, text, created_at FROM memories "
    "WHERE created_at < now() - interval '30 days' "
    "  AND compacted_at IS NULL "
    "ORDER BY created_at ASC LIMIT 5000"
).fetchall()

for batch in chunked(rows, 200):
    summary = summarise(batch)          # one LLM call per batch
    db.execute(
        "INSERT INTO memories (org_id, user_id, text, kind) VALUES (?, ?, ?, 'summary')",
        (batch[0].org_id, batch[0].user_id, summary, ),
    )

Every record it read carried a correct org_id. The job selected org_id and even used it on the write. What it never did was constrain a batch to a single tenant, so a batch that happened to span the boundary between two customers' rows produced one summary containing both companies' facts, stamped with whichever org_id sorted first by created_at. Across 340 active organisations and roughly 5,000 rows a night, a batch crossed the boundary on the order of twenty-five times per run — twenty-five contaminated summaries a night, silently, for four months. The original rows stayed correct, which is why nothing in the store looked wrong: the leak lived only in derived records that no one thought of as a separate class of data.

Why almost every batch was contaminated#

The figure of twenty-five is not an estimate of how often something rare happened. It is the number of batches, and it is worth deriving, because the derivation shows that the contamination rate was insensitive to everything an engineer would have thought to tune.

Start with the batch count. The query takes 5,000 rows and chunked(rows, 200) cuts them into groups of two hundred, which is twenty-five groups. Each group becomes one summarise call and one inserted row, so the job produces twenty-five derived records a night whatever else happens.

Now ask when one of those groups is clean. A group is clean only if all two hundred of its rows carry the same org_id. The rows arrive in created_at order, which is the order in which customers happened to talk to the agent thirty days earlier, and across 340 active organisations that ordering interleaves tenants thoroughly — two hundred consecutive rows all belonging to one company would require that company to have been the only one writing memories for a stretch of the night in question. The chance of that is small for any group, and it has to hold for all twenty-five for the run to be clean. So the count of contaminated summaries per run is not "twenty-five times something went wrong"; it is the batch count itself, minus the rare group that happened to fall inside one tenant's run of rows.

Two consequences follow, and both are the opposite of what an engineer looking at the symptom would guess. Raising the batch size does not reduce contamination — it makes each batch span more tenants while producing fewer summaries, so the contamination rate per summary goes up as the count goes down. Adding tenants does not increase it either, once there are enough tenants to interleave; the count is pinned to rows ÷ batch_size and stops depending on the tenant population entirely. There is no value of either parameter that fixes this, which is the tell that the defect is structural rather than a matter of tuning.

The write carries a second error the symptom never revealed. batch[0].user_id is taken from the same arbitrary first row as batch[0].org_id, so even a batch that happened to fall inside one tenant produces a summary attributed to whichever of that tenant's users sorted first. Inside a single company that is a scope error of the kind chapter 1 describes rather than a tenancy breach, and it is invisible for the same reason the tenancy breach was: the summary is a plausible sentence sitting on a plausible row.

That invisibility is the property worth carrying forward. A derived record is a first-class record — it is stored in the same table, it is embedded, it is retrieved — and it is the only record in the system whose tenancy was decided by arithmetic on a list rather than by a principal. Nothing in the store looked wrong because nothing in the store was wrong except the rows nobody was treating as data.

Every process that touches the table is an access path#

The request path gets attention because it is where authentication happens, where the reviewers' eyes go, and where the security team asks questions. It is also a minority of the code that reads a memory table in a mature system. Writing the inventory out is the exercise, and the useful form of it names, for each path, what identity it runs as and what would have to be true for the tenancy constraint — the requirement that any statement touching the table sees the rows of exactly one tenant — to be present.

Read the third column downwards before reading anything else, because that is where the table stops being a list and starts being an argument. Two rows say a repository layer supplies the constraint. Six say nothing supplies it. One says a separate query builder, and one says the cache key. The distribution is the finding: in seven of the nine paths there is no mechanism at all that would put a tenant predicate into the statement, and the two protected rows are protected by a component the other seven do not import.

Four properties separate the bottom eight rows from the top two, and each is a reason the missing mechanism is missing. They run as a service identity rather than an end user, so there is no principal to derive a tenant from — the constraint has no value to take even if somebody wrote the predicate. They are batch-shaped, so their natural unit of work is "all rows" rather than "this user's rows", which makes an unconstrained query look correct while you are writing it. They are often written outside the main application — in a migration file, a cron script, a notebook that got promoted to a job — so they bypass the repository layer entirely and talk to the driver. And they have no test that would notice, because a compaction job is tested for whether it compacts, using a fixture with one tenant in it.

The inventory is worth building from evidence rather than from memory, because the paths that are easiest to forget in a review are exactly the ones that were easiest to forget when they were written. Three passes get most of it. Grep the repository for the table name and for the ORM model class, which finds the code that talks to the store directly and usually surfaces two or three call sites nobody could have named beforehand. Then query the database for what has actually been running: pg_stat_statements joined against pg_roles tells you which roles issued which query shapes over the retention window, and any statement touching memories that did not come from the application role is a path the request layer does not own. Finally, list the scheduled work — cron entries, the workflow definitions in your orchestrator, the Lambda triggers — because a job that runs monthly will not appear in a week of statement statistics and is no less capable of merging two tenants.

Enforcement belongs below the code that can forget#

A filter written in application code is a filter that exists on the paths that call that code. The fix is to move the constraint to a layer that every path traverses whether it wants to or not, which in practice means the database, and in Postgres means row-level security with the tenant supplied as a connection-scoped setting rather than a query parameter.

ALTER TABLE memories ENABLE ROW LEVEL SECURITY;
ALTER TABLE memories FORCE ROW LEVEL SECURITY;  -- applies to the table owner too

CREATE POLICY memories_tenant_isolation ON memories
  USING (org_id = current_setting('app.current_org', true))
  WITH CHECK (org_id = current_setting('app.current_org', true));

-- The jobs role gets no BYPASSRLS attribute and is not the table owner.
CREATE ROLE memory_jobs LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON memories TO memory_jobs;

The reason a connection setting is the right home for the tenant, rather than a parameter bound into each statement, is where the two are read from. A parameter is part of the statement, so it exists only if the statement's author wrote it, and the seven unprotected paths above are precisely the ones whose authors did not. current_setting is read by the policy, and the policy is attached to the table by the planner on every statement that touches it, whoever wrote that statement and whether or not they have ever heard of tenancy. Moving the value out of the statement moves the enforcement point from the code to the table, and the table is the one thing all nine paths have in common.

USING and WITH CHECK are two halves of the same rule and both are needed. USING is applied to rows being read, so it decides what a SELECT, UPDATE or DELETE can see. WITH CHECK is applied to rows being written, so it decides what an INSERT or UPDATE may leave behind. A policy with only USING lets a job read one tenant's rows and insert a row stamped with another's, which is exactly the compaction bug expressed in SQL.

FORCE ROW LEVEL SECURITY is the clause that turns this from documentation into a control, and the reason it is needed is a default that made sense when it was chosen and does not hold here. Postgres exempts a table's owner from that table's policies, on the reasoning that the owner is the administrator of the table and an administrator locked out of their own data is a support call. In a normal application the owner is a role nobody logs in as. In practice the migration credential and the jobs credential end up owning the tables they created, so the roles running the seven unprotected paths are frequently the exact roles the default exempts. FORCE removes the exemption.

The true second argument to current_setting makes a missing setting return NULL rather than raising, and what happens next is a consequence of Postgres's three-valued logic rather than of anything the policy says. NULL compared to org_id evaluates to NULL, which is neither true nor false. A USING clause admits a row only when it evaluates to true, so a NULL result admits nothing, and a connection that forgot to set the tenant reads zero rows. Failing to an empty result set rather than to an exception is a deliberate trade: it means a forgotten SET shows up as "the job did nothing" instead of "the job did everything", and the monitoring you need is an alert on a compaction run that processed zero rows.

The batch job then stops being a single query over the table and becomes a loop over tenants, each iteration running inside a transaction that has declared which tenant it is:

for org_id in active_org_ids():                    # one authoritative source
    with db.begin() as tx:
        tx.execute("SET LOCAL app.current_org = :org", {"org": org_id})
        rows = tx.execute(
            "SELECT id, user_id, text FROM memories "
            "WHERE created_at < now() - interval '30 days' AND compacted_at IS NULL "
            "ORDER BY user_id, created_at LIMIT 500"
        ).fetchall()
        for user_id, group in groupby(rows, key=lambda r: r.user_id):
            tx.execute(
                "INSERT INTO memories (org_id, user_id, text, kind) VALUES "
                "(current_setting('app.current_org'), :u, :t, 'summary')",
                {"u": user_id, "t": summarise(list(group))},
            )

SET LOCAL scopes the setting to the transaction, so a pooled connection returned to the pool cannot carry a previous tenant's identity into the next unit of work — a bug that is otherwise nearly impossible to reproduce, since it only appears under connection reuse and looks like random cross-tenant contamination. The insert no longer takes org_id from a row it happened to be holding; it takes it from the same setting the policy checks, which removes the class of error where the filter and the write disagree. The ORDER BY user_id and the groupby are what repair the second defect: a summary is now built from one user's rows rather than from whichever rows landed in the same slice of two hundred.

The tenant-at-a-time loop costs something and it is worth quantifying rather than hand-waving. On the vendor's workload, 340 organisations meant 340 transactions instead of one, and the wall-clock time for the compaction pass went from 41 minutes to 47 — a 15 percent increase, almost all of it transaction setup and the loss of a single large sequential scan. The LLM calls dominated both numbers, which is typical for memory jobs and means the isolation loop is usually cheap relative to the work it wraps.

Two paths a policy alone will not save#

Two rows in the inventory deserve separate treatment because pushing enforcement down does not reach them.

The support console is built deliberately to search across tenants, so it is the one path where the correct behaviour is to see everything, and pushing enforcement down cannot distinguish a legitimate investigation from a curious engineer. Both arrive as the same role issuing the same statement. What works there is a different control: the console runs as a role that does bypass the policy, every query it issues is written to an append-only audit table with the staff identity and a required reason string, and access is time-boxed by a grant that expires. The control moves from what may be read to what is recorded and for how long the ability lasts, because the first question has no mechanical answer here.

The re-embedding job is the other, and it is the job that recomputes each record's embedding, the numeric vector that lets the record be found by similarity search. Its output lands in a system the database policy has no reach into. A vector written without its namespace is not recoverable, and the reason is worth stating: an embedding is a list of numbers with no textual content, so there is nothing in an orphaned vector from which its tenant could be inferred. Deleting the wrong ones is guesswork, so the remediation is a full re-index rather than a repair.

The migration path is the one that most often defeats an otherwise careful design, since a migration runs as a role chosen for its power and operates on the table as a whole by definition. Copying memories into memories_v2 with a new column layout, backfilling a nullable org_id with a default so the NOT NULL constraint can be added, or building a temporary denormalised table for a one-off analysis all produce a table that the policy does not cover, holding the same facts. The discipline that holds is to treat any table containing tenant data as subject to the same checklist as the original — policy enabled, policy forced, a test asserting both — and to make that checklist a schema-level assertion rather than a review item, so that a table added next quarter by someone who has not read this fails the build.

Where the policy's reach ends#

Row-level security constrains what a connection can see, so it addresses the paths that go through the database and nothing else. Vector indexes in a separate system have their own filtering model and their own way of getting it wrong, which chapter 5 takes up in detail. Caches sit in front of the database entirely and will happily serve a row that the policy would have refused, because the policy never runs on a cache hit. Object storage holding uploaded files referenced by a memory has its own access control lists (ACLs). Anything a job has already exported into a warehouse or a log is outside the database's jurisdiction from the moment it leaves.

Every item in that list is the same residual seen five times. The policy is enforced by the query planner, so it holds for exactly the statements the planner sees and for nothing else. A copy that leaves the database — into an index, a cache, a file, a warehouse, a log — leaves the planner's reach at the same moment, and there is no configuration of the policy that follows it. That is why the remaining chapters of this topic are organised by store rather than by rule.

RLS is also not free of foot-guns. Policies apply per table, so a new table added by a migration starts with none, and the only reliable defence is a test that enumerates tables in the schema and asserts that every one carrying an org_id column has RLS enabled and forced. Views do not inherit the policies of their base tables unless created with security_invoker, so a convenience view built for the admin console can quietly reopen everything — a view without that setting runs with the definer's rights, and the definer is usually the owner the FORCE clause was added to constrain.

The last foot-gun is the one that leads into the next chapter, and it is bigger than the others. A policy that reads a session setting is only as good as the middleware that sets it, so where that value comes from turns out to matter more than whether the policy is applied. The compaction job in this chapter took its tenant from a row it was already holding, which is a bad source because the row was chosen by arithmetic. A memory tool in an agent loop can take its tenant from an argument the model produced, and that is a materially worse source, because a model's arguments can be written by whoever wrote the text the model was reading.