Row-Level Security for Multi-Tenant Retrieval

A multi-tenant retrieval system leaks when one query out of several hundred forgets its tenant predicate. This page shows that query working, closes it with a row-level security policy that makes the predicate impossible to forget, and gives a test that fails the build if anybody ever removes it.

The leak

Here is the table and the query that leaks. Everything about it looks correct at review.

CREATE TABLE chunk_embeddings (
  chunk_id   bigint PRIMARY KEY,
  tenant_id  uuid   NOT NULL,
  content    text   NOT NULL,
  embedding  vector(1536) NOT NULL
);

-- The intended query. Fine.
SELECT chunk_id, content
FROM chunk_embeddings
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 10;

-- The leak, six weeks later, in a "similar documents" feature
-- somebody added by copying the retrieval helper and adjusting it:
SELECT chunk_id, content
FROM chunk_embeddings
ORDER BY embedding <=> $2
LIMIT 10;

The second query returns the ten globally nearest chunks. Some of them belong to other customers. It returns their text, and that text is then put into a prompt and paraphrased back to a user by a language model, which is a data breach with a summarisation step in front of it that makes it harder to detect.

Nothing errors. The response looks plausible. The feature works in staging, where there is one tenant. In production it is wrong from the first request, and the only signal is a customer eventually recognising content that is not theirs.

Two nastier variants of the same class, both of which pass a code review that is only checking for the presence of tenant_id:

-- Filter present, but applied AFTER the limit: the subquery takes the
-- global top 10 and then filters, so a tenant with no chunks in the
-- global top 10 sees nothing, and the count is a side channel telling
-- you how many of the global nearest neighbours are somebody else's.
SELECT * FROM (
  SELECT chunk_id, tenant_id, content
  FROM chunk_embeddings ORDER BY embedding <=> $2 LIMIT 10
) t WHERE t.tenant_id = $1;

-- Filter present on the wrong table. The join to chunks is filtered,
-- the aggregate over embeddings is not.
SELECT count(*) FROM chunk_embeddings WHERE embedding <=> $2 < 0.2;

Why the application filter is not enough

Because it relies on every developer, on every code path, forever. That is not a security control; it is a hope with a code review attached. The properties that make it fail are structural.

• The safe query and the unsafe query differ by one clause, and the unsafe one is shorter.
• The unsafe one produces plausible results, so it passes manual testing by anybody who is not specifically looking for cross-tenant content.
• Analytics queries, migrations, back-office tools and one-off scripts all touch the same table and none of them go through your retrieval helper.
• An ORM makes it worse, not better: a relation traversal or a lazily loaded association can produce a query you never wrote.

Row-level security moves the predicate from the query into the table. It is applied by the planner to every statement against that table, from every client, including ones that do not know it exists.

The policy

-- 1. A role for the application. It must NOT own the table, and must
--    NOT have BYPASSRLS or be a superuser. Both of those skip policies.
CREATE ROLE app_user LOGIN PASSWORD '…';
GRANT SELECT, INSERT, UPDATE, DELETE ON chunk_embeddings TO app_user;

-- 2. Turn it on. Without this line the policies below do nothing.
ALTER TABLE chunk_embeddings ENABLE ROW LEVEL SECURITY;

-- 3. And this one, which applies policies to the table OWNER too.
--    Without it, any connection as the owner sees everything.
ALTER TABLE chunk_embeddings FORCE ROW LEVEL SECURITY;

-- 4. The policy. nullif() is load-bearing: an unset GUC returns the
--    empty string, and ''::uuid raises rather than returning NULL.
CREATE POLICY tenant_isolation ON chunk_embeddings
  USING      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

That last property is the one to appreciate. If your application forgets to set the tenant, the query returns zero rows rather than all of them. The failure mode of a misconfiguration is an empty page, which somebody reports within minutes, instead of a silent breach.

Setting the tenant, and the pooling trap

BEGIN;
SET LOCAL app.tenant_id = '3f2a…-…';   -- from the verified session, never
                                        -- from a request header or body
SELECT chunk_id, content
FROM chunk_embeddings
ORDER BY embedding <=> $1
LIMIT 10;
COMMIT;

SET LOCAL, not SET, and this is not a stylistic preference. SET persists for the life of the connection. Under a transaction-mode pooler such as PgBouncer, that connection is handed to a different request — belonging to a different customer — as soon as your transaction ends, and it arrives carrying your tenant id. The next tenant’s queries then run under your identity, and RLS enforces the wrong thing perfectly.

SET LOCAL is scoped to the transaction and is reset on commit or rollback, which makes it safe under pooling. The interaction between long AI requests and connection pools is worked through in connection pooling for AI workloads; the short version for this page is that every session-scoped setting you rely on is a bug waiting for a pooler.

The tenant id must come from your authenticated session. Taking it from a request parameter reproduces the original vulnerability with more infrastructure — the attacker simply asks for a different tenant.

The test that proves it

A policy nobody tests is a policy that will be dropped by a migration in eight months. This is plain SQL, runs in your test suite against a real database, and raises on failure:

-- rls_test.sql — run as app_user, not as the owner.
BEGIN;

SET LOCAL app.tenant_id = '11111111-1111-1111-1111-111111111111';
INSERT INTO chunk_embeddings (chunk_id, tenant_id, content, embedding)
VALUES (900001, '11111111-1111-1111-1111-111111111111',
        'tenant A secret', array_fill(0.1, ARRAY[1536])::vector);

SET LOCAL app.tenant_id = '22222222-2222-2222-2222-222222222222';
INSERT INTO chunk_embeddings (chunk_id, tenant_id, content, embedding)
VALUES (900002, '22222222-2222-2222-2222-222222222222',
        'tenant B secret', array_fill(0.1, ARRAY[1536])::vector);

DO $$
DECLARE n int;
BEGIN
  -- 1. As B, the unfiltered vector query — the exact leaking query
  --    from the top of this page — must not reach A's row.
  PERFORM set_config('app.tenant_id',
                     '22222222-2222-2222-2222-222222222222', true);
  SELECT count(*) INTO n FROM (
    SELECT chunk_id FROM chunk_embeddings
    ORDER BY embedding <=> array_fill(0.1, ARRAY[1536])::vector
    LIMIT 50
  ) t WHERE chunk_id = 900001;
  IF n <> 0 THEN RAISE EXCEPTION 'LEAK: tenant B can read tenant A'; END IF;

  -- 2. B can still see its own row. A policy that denies everything
  --    also passes test 1, so this assertion is not optional.
  SELECT count(*) INTO n FROM chunk_embeddings WHERE chunk_id = 900002;
  IF n <> 1 THEN RAISE EXCEPTION 'BROKEN: tenant B cannot read its own row'; END IF;

  -- 3. B cannot write a row attributed to A. Expect a WITH CHECK failure.
  BEGIN
    INSERT INTO chunk_embeddings (chunk_id, tenant_id, content, embedding)
    VALUES (900003, '11111111-1111-1111-1111-111111111111',
            'forged', array_fill(0.1, ARRAY[1536])::vector);
    RAISE EXCEPTION 'LEAK: tenant B wrote a row as tenant A';
  EXCEPTION WHEN insufficient_privilege THEN
    NULL;  -- expected: new row violates row-level security policy
  END;

  -- 4. With no tenant set at all, nothing is visible. Fail closed.
  PERFORM set_config('app.tenant_id', '', true);
  SELECT count(*) INTO n FROM chunk_embeddings;
  IF n <> 0 THEN RAISE EXCEPTION 'LEAK: rows visible with no tenant set'; END IF;
END $$;

ROLLBACK;

Four assertions, and every one of them has failed in somebody’s production system. Assertion 2 is the one people leave out and the one that catches an over-broad fix: a policy of USING (false) passes the leak test perfectly and breaks the product.

Two ways RLS is silently off

The role bypasses it. Superusers, roles with the BYPASSRLS attribute, and (without FORCE) the table owner all skip every policy. Most applications are deployed connecting as the owner of their own tables, which means most first attempts at RLS do nothing at all. Verify:

SELECT current_user, rolsuper, rolbypassrls
FROM pg_roles WHERE rolname = current_user;

SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class WHERE relname = 'chunk_embeddings';
-- both must be true

A new table has no policy. RLS is per table. Add chunk_metadata next quarter and it is unprotected until somebody remembers. Make that a query your test suite runs, so a table without a policy fails the build:

SELECT c.relname AS unprotected_table
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
  AND c.relname NOT IN ('schema_migrations', 'backfill_state')
  AND NOT (c.relrowsecurity AND c.relforcerowsecurity);

What it costs the vector query

RLS policies are added to the query as predicates, which means enabling it converts every vector search in your application into a filtered vector search overnight, with the selectivity of one tenant against the whole table.

That is exactly the situation analysed in filtering and vector search in one query: the HNSW scan returns hnsw.ef_search candidates and the policy discards the ones belonging to other tenants, so the expected number of survivors is ef_search × s where s is that tenant’s share of rows. A tenant holding 0.1 per cent of the corpus gets 0.04 expected results from a default scan — an empty page for a query that used to work.

Three responses, in the order to try them:

1. Raise hnsw.ef_search per query from the tenant’s known row count. Adequate down to about one per cent selectivity and no further, because the parameter caps at 1000.
2. Enable iterative scan (pgvector 0.8.0): SET LOCAL hnsw.iterative_scan = strict_order. It keeps rescanning until the limit is satisfied or the budget runs out, which converts wrong answers into slow ones.
3. Partition by tenant. The structural fix. Each partition has its own HNSW index containing only that tenant’s rows, so the policy no longer filters anything at query time and the arithmetic disappears. Policies still apply per partition and still protect you; they simply stop costing anything.

RLS and partitioning together is the arrangement that is both correct and fast, and it is worth reaching for before the workaround of maintaining a separate database per tenant, which trades one well-understood Postgres feature for an operational burden that grows linearly with your customer list.

Two things RLS does not do, and both have been mistaken for holes in the policy. It does not hide the existence of rows from timing: a query that scans a large index and returns nothing takes measurably longer than one over an empty table, so a determined observer can learn something about volume. And it does not protect against a query that never reaches the table — a cached result, a materialised view built without the policy, a search index in another system, a log line containing the content. Every one of those is a copy of the data outside the boundary you just built, and each needs its own tenant scoping. The cache case in particular is covered in caching retrieval results, where the tenant is one of six mandatory parts of the key for exactly this reason.

Which is the general shape of the risk once the database is fixed: the leak moves to whichever component holds a copy. Enumerate them — the cache, the search index, the analytics warehouse, the log aggregator, the object store, the backups, the LLM provider’s own retention — and confirm each has a tenant boundary of its own. Multi-tenant RAG without leaking between customers covers the pipeline-level patterns; this page is the database half, and the database half is the one that can be proven with a test.