Enforce Permissions Inside the pgvector Query, Not After It

#ai #rag #security #postgres

Most permission bugs in RAG systems look harmless in review. You retrieve the top-k nearest chunks, then you drop the ones the user is not allowed to see. It reads as correct. It is not, and the failure is quiet.

I want to show you why the post-filter is the wrong place, and how to push the access-control decision inside the retrieval query itself so that an unauthorized chunk is never selected, never ranked, never counted. I will ground this in a small permission-aware RAG project of mine called vaultrag, but the technique is portable to any Postgres plus pgvector stack.

Why post-filtering leaks and lies

Here is the pattern I want you to stop writing.

# fetch top 10 by vector distance
rows = fetch_nearest(query_embedding, limit=10)
# then remove what the user cannot see
visible = [r for r in rows if user_can_see(r)]

Two things are wrong here.

The first is a correctness bug. Your LIMIT 10 runs against every chunk in the table. If eight of the ten nearest chunks belong to documents this user cannot read, you filter them out and hand back two results. The user experiences this as a broken search, not as a security boundary. Relevant material they are allowed to see sat at rank 11 and never made it into the candidate set.

The second is worse, and it is the reason to care. Every code path that touches the raw result set is now a place a leak can happen. Logging the pre-filter rows, a metrics counter, a debug endpoint, a caching layer that memoizes by query text: each one can see chunks the user cannot. The filter protects exactly one exit. Everything upstream of it is holding forbidden data.

The fix is to make it structurally impossible to hold that data in the first place.

Model the access control as data

Start with an explicit access-control list per document. In vaultrag a document has an ACL made of principals, where a principal is either a user id or a group name.

CREATE TABLE documents (
    id          TEXT PRIMARY KEY,
    title       TEXT NOT NULL,
    deleted_at  TIMESTAMPTZ
);

CREATE TABLE doc_acl (
    doc_id      TEXT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    principal   TEXT NOT NULL
);

CREATE TABLE chunks (
    id          BIGSERIAL PRIMARY KEY,
    doc_id      TEXT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    text        TEXT NOT NULL,
    embedding   VECTOR(1536)
);

The critical design choice is resolving the caller's principals on the server, never from the request. If a client can assert its own group membership, the ACL is decorative.

async def resolve_principal(conn, user_id: str):
    async with conn.cursor(row_factory=dict_row) as cur:
        await cur.execute("SELECT id, groups FROM users WHERE id = %s", (user_id,))
        row = await cur.fetchone()
    if row is None:
        return None
    # principals we match against: the user id plus every group they belong to
    return [row["id"], *(row["groups"] or ())]

Put the boundary in a CTE that everything reads from

The technique is a single common table expression, visible, that defines the universe of chunks this caller may see. Every other part of the query reads from visible, not from chunks. There is no path that starts anywhere else.

WITH visible AS (
    SELECT c.id, c.doc_id, c.text, c.embedding
    FROM chunks c
    JOIN documents d ON d.id = c.doc_id
    WHERE d.deleted_at IS NULL
      AND EXISTS (
          SELECT 1 FROM doc_acl a
          WHERE a.doc_id = d.id
            AND a.principal = ANY(%(principals)s)
      )
),
ranked AS (
    SELECT id, doc_id, text
    FROM visible
    WHERE embedding IS NOT NULL
    ORDER BY embedding <=> %(embedding)s::vector
    LIMIT %(limit)s
)
SELECT * FROM ranked;

Read the order of operations, because it is the whole point. The EXISTS against doc_acl runs before the ORDER BY ... <=> .... The nearest-neighbor ranking and the LIMIT operate on visible, which already excludes forbidden chunks. Your top-k is now the top-k of what the user is allowed to see. The correctness bug from earlier is gone, and so is the leak surface, because the query planner never materializes a forbidden row into the candidate set.

A detail worth stealing: use EXISTS rather than a plain JOIN doc_acl. A document with three matching ACL rows would otherwise multiply into three copies of each chunk. EXISTS short-circuits on the first match, so each visible chunk appears exactly once.

The same visible CTE composes cleanly with hybrid search. In vaultrag both the vector arm and the full-text arm select from visible before they are fused with reciprocal rank fusion, so neither arm can surface a chunk the other could not.

WITH visible AS ( ... ),                 -- the boundary, defined once
vec AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> %(embedding)s::vector) AS rank
    FROM visible WHERE embedding IS NOT NULL
    ORDER BY embedding <=> %(embedding)s::vector LIMIT %(candidates)s
),
kw AS (
    SELECT id, ROW_NUMBER() OVER (
        ORDER BY ts_rank_cd(tsv, websearch_to_tsquery('english', %(q)s)) DESC) AS rank
    FROM visible WHERE tsv @@ websearch_to_tsquery('english', %(q)s)
    LIMIT %(candidates)s
)
-- fuse vec and kw, both already scoped to visible

Because both arms read from visible, there is no code path in the function that can rank an unauthorized chunk. That is a property you can state about the query, not a behavior you hope the filter enforces.

The honest caveat

Pushing the ACL into the query moves the boundary, it does not make the boundary free. The EXISTS subquery runs per candidate document, and on a large table the planner's choices matter. You want an index that makes the ACL check cheap:

CREATE INDEX ON doc_acl (doc_id, principal);

There is real tension here with the pgvector index. An HNSW or IVFFlat index gives you approximate nearest neighbors fast, but the approximation happens before your WHERE filter is applied inside the scan. When a filter is very selective, meaning the user can see only a tiny fraction of documents, the vector index can return a page of candidates that are then almost entirely filtered out, and you get fewer results than your LIMIT asked for. This is the well-known filtered-search problem. Mitigations exist: raise hnsw.ef_search, use partitioning, or on recent pgvector use iterative index scans. Measure it on your data. Do not assume the CTE is free just because it is correct.

The takeaway

Correctness and security both improve when the permission check is a precondition of ranking rather than a cleanup step after it. Define the visible set once, in a CTE, and make every arm of your search read from it. The guarantee you get is structural: an unauthorized chunk is not filtered out late, it is never selected.

If you want a full working reference, the retrieval query, the ACL schema, and the server-side principal resolution live in my project at github.com/AgentPostmortem/vaultrag. Borrow the CTE and adapt the ACL model to your own tenancy rules.

DEV Community