Permission-Aware RAG: Enforcing Tenant and Document Access with pgvector Row-Level Security

Your RAG demo works. Then the first enterprise customer asks: "If one of our employees isn't allowed to open the HR folder, can the chatbot still quote it?"

If your answer is "we filter the results in the app layer", you have a problem. Retrieval is the one place in an AI product where every document the user could ever see and every document they could never see sit in the same index, ranked by a single similarity score. One missing WHERE clause and the model happily summarises another tenant's contract.

This post covers how to make that class of bug structurally impossible: put authorization in the database, before chunks ever reach the prompt.

Why post-retrieval filtering is the wrong layer

The naive design looks like this:

Embed the query.
Fetch the top-k chunks across the whole index.
Drop the ones the user shouldn't see.
Send the rest to the LLM.

Three things go wrong:

Recall collapses. If you fetch 10 chunks and 8 belong to other tenants, the user gets 2 chunks of context, and answers get worse for no visible reason.
Leaks via side channels. Reranker scores, logs, traces, and cached embeddings may all see chunks you later "filtered".
Authorization lives in application code. Every new retrieval path (a hybrid search, an agent tool, a background job) is a new place to forget the filter.

The principle: authorization must be enforced before documents reach the LLM's context window, and ideally before they leave the database.

The model: tenant isolation plus document ACLs

Two levels of access control cover most B2B products:

Level	Question it answers	Typical mechanism
Tenant	Does this chunk belong to the caller's organization?	`tenant_id` column + RLS policy
Document / group	Within the tenant, is this caller allowed to read this document?	ACL array of group IDs or user IDs per chunk

Both are checked inside the same SQL query that does the vector search, so the ANN ranking only ever considers rows the caller may see.

Schema

create extension if not exists vector;

create table documents (
  id          uuid primary key default gen_random_uuid(),
  tenant_id   uuid not null,
  title       text not null,
  source_uri  text,
  -- groups allowed to read; empty array means "whole tenant"
  allowed_groups text[] not null default '{}',
  updated_at  timestamptz not null default now()
);

create table chunks (
  id          uuid primary key default gen_random_uuid(),
  tenant_id   uuid not null,
  document_id uuid not null references documents(id) on delete cascade,
  content     text not null,
  embedding   vector(1536) not null,
  -- denormalised from documents so the policy needs no join
  allowed_groups text[] not null default '{}'
);

create index chunks_tenant_idx on chunks (tenant_id);
create index chunks_embedding_idx
  on chunks using hnsw (embedding vector_cosine_ops);

Note the denormalised tenant_id and allowed_groups on chunks. A policy that has to join to documents on every candidate row is slow and easy to get wrong. The cost is that when a document's ACL changes you must update its chunks. More on that in the pitfalls section.

Row-level security policies

alter table chunks enable row level security;
alter table chunks force row level security;  -- applies to the table owner too

create policy tenant_isolation on chunks
  using (tenant_id = current_setting('app.tenant_id')::uuid);

create policy group_acl on chunks
  as restrictive
  using (
    cardinality(allowed_groups) = 0
    or allowed_groups && string_to_array(current_setting('app.groups', true), ',')
  );

Permissive policies are OR-ed together and restrictive policies are AND-ed on top, so a row is visible only when it matches the tenant and passes the group ACL. Check the PostgreSQL docs for CREATE POLICY if you want to adjust the semantics.

Two operational rules:

The application must not connect as a superuser or as a role with BYPASSRLS. Superusers skip RLS entirely. Create a dedicated app_user role.
Give your backup and migration roles BYPASSRLS deliberately, otherwise a full dump silently contains one tenant's data.

Setting identity per request

The app sets the caller's identity as transaction-local settings, derived from the authenticated principal, never from request parameters:

import psycopg
from pgvector.psycopg import register_vector

def search_chunks(pool, principal, query_embedding, k=8):
    """principal comes from your verified JWT/session, not from user input."""
    with pool.connection() as conn:
        register_vector(conn)
        with conn.transaction():
            # set_config(..., is_local=true) scopes the setting to this transaction
            conn.execute(
                "select set_config('app.tenant_id', %s, true),"
                "       set_config('app.groups', %s, true)",
                (str(principal.tenant_id), ",".join(principal.groups)),
            )
            rows = conn.execute(
                """
                select id, document_id, content,
                       embedding <=> %s AS distance
                from chunks
                order by embedding <=> %s
                limit %s
                """,
                (query_embedding, query_embedding, k),
            ).fetchall()
    return rows

Notice what is absent: there is no WHERE tenant_id = ... in the query. If a developer writes a new retrieval path and forgets the filter, RLS still applies. If they forget to set the session variable, current_setting raises an error rather than returning another tenant's data. That is the failure mode you want: loud and closed.

Using set_config(..., true) (transaction-local) matters because of connection pooling. A session-level SET can survive on a pooled connection and leak identity into the next request. Transaction-local settings are discarded at commit or rollback. If you use PgBouncer in transaction mode, this is the only safe pattern.

The recall trap with approximate indexes

HNSW indexes find nearest neighbours first and apply filters afterwards on the candidate set. With a selective filter (a small tenant in a huge shared index), the index may return ef_search candidates, almost none of which pass RLS, and you get fewer than k rows back, or none.

Options, in rough order of preference:

Approach	Isolation	Recall under filters	Ops cost
Shared index + RLS, tune `hnsw.ef_search` / use iterative scan if your pgvector version supports it	Strong (DB-enforced)	Fair, needs tuning	Low
Partition `chunks` by `tenant_id`, index per partition	Strong	Good	Medium (partition management)
Separate schema or database per tenant	Strongest	Best	High
App-layer filter on a shared index	Weak	Poor	Low

Check the pgvector release notes for your installed version: newer releases added iterative index scans specifically to improve filtered search. Measure recall with and without the filter on your real tenant size distribution before choosing. Our retrieval evaluation guide shows how to build the golden set for that.

Testing that isolation actually holds

Authorization is a property you can test automatically. Add a leak test to CI:

def test_no_cross_tenant_leak(pool, seeded_tenants):
    a, b = seeded_tenants["acme"], seeded_tenants["globex"]
    # Query using a string that only exists in Globex's documents
    rows = search_chunks(pool, a.principal, embed("Globex confidential pricing"))
    assert all(r.tenant_id == a.tenant_id for r in rows)
    assert not any("Globex" in r.content for r in rows)

def test_missing_identity_fails_closed(pool):
    with pytest.raises(Exception):
        with pool.connection() as conn:
            conn.execute("select * from chunks limit 1")  # no set_config

Also run the same probes through your agent tools and background jobs, not just the main search endpoint.

Common pitfalls

Stale ACLs. Documents get reshared and access gets revoked. If allowed_groups is written once at ingest, revoked users keep access. Sync permissions from the source system on a schedule or via webhooks, and update chunks in the same transaction as the document.
Trusting user-supplied filters. Never accept tenant_id or groups from the request body or from the model's tool arguments. An LLM-generated tool call is untrusted input; see our prompt injection defense guide.
Leaks outside the vector store. Semantic caches, conversation memory, trace stores, and eval datasets can all contain retrieved chunks. Key caches by tenant and ACL scope, and apply the same isolation to them. See semantic caching.
Citations that reveal titles. Even if the text is blocked, showing "Q3 layoff plan.pdf" as a source is a leak. Run titles through the same policy.
Superuser connections in dev. Everything passes locally because you connect as postgres. Run your test suite as the real app_user role.
No post-retrieval layer at all. RLS is the hard guarantee, but masking sensitive fields and instructing the model to answer only from provided context are still worthwhile defence in depth.

Checklist

Application connects as a non-superuser role without BYPASSRLS
RLS enabled and forced on every table holding chunks, embeddings, or metadata
Identity set with transaction-local set_config from the verified principal
Missing identity raises an error rather than returning rows
ACL changes propagate to chunks, with a measured staleness bound
Cross-tenant leak tests run in CI against the real role
Caches, memory, and traces are scoped by tenant and ACL
Filtered recall measured on your largest and smallest tenants

Next steps

Start by adding the leak test to your current system before changing anything. If it fails, you have your priority. Then move tenant isolation into RLS, and only after that tune index recall.

Building production RAG systems with real access-control requirements is exactly what we work through hands-on in the Joinloop AI Engineering cohort, where you build and evaluate a multi-tenant retrieval pipeline end to end.