Relevance Isn't Permission: How to Filter Vector Search by Access Before Relevance in PostgreSQL
A piece published on Planet PostgreSQL on November 21 shows, in SQL, how to use RLS and pgvector to keep semantic search from returning documents a user shouldn't be able to see.

- Reading
- 9 min
- Tables
- 1
- Code
- 5
A piece published on Planet PostgreSQL on November 21 shows, in SQL, how to use RLS and pgvector to keep semantic search from returning documents a user shouldn't be able to see.
Imagine an internal assistant that answers employee questions from a shared document repository. Someone asks when the bonus is paid. The vector search does exactly what it was built to do: it finds the passages most semantically similar to the question. One of them comes from a compensation review that only HR should be able to access.
No model hallucinated, no index got corrupted, no query timed out. Everything worked as designed, and that's exactly why no alert went off. That's the central point of the piece published on Planet PostgreSQL on November 21, 2026, which revisits an argument the author already made in a piece of his own for the Forbes Technology Council.
The retrieval was perfect. That was the problem.
author of the original article
The author's argument is direct: a search system answers "what is most relevant," never "what can this person see." He describes four SQL patterns for solving this inside PostgreSQL itself, without relying on the application to remember to filter.
Vector Search Doesn't Know Who Owns the Data
An embedding captures the meaning of a piece of text. It doesn't capture who owns it, which tenant it belongs to, or which regulation covers it. A paragraph from a public FAQ and a confidential board memo can end up next to each other in vector space simply because they talk about the same subject.
Most RAG pipelines solve this in two ways, according to the author, and both are fragile. The first filters in the application, after the search: the app pulls the top k results and discards the ones the user can't see, until a new agent or a debug script forgets that step. The second writes the permission directly into the WHERE clause of whoever builds the query, until an LLM-generated query forgets the condition.
In both cases, permission is a habit of whoever calls the query, not a property of the data. Once whoever is querying becomes an AI agent, there's no longer any code review possible over that path. The access rule needs to live where the data lives: inside PostgreSQL.
Pattern 1: Row-Level Authority, Enforced by RLS
Row Level Security turns "who can see this row" from a coding convention into a database guarantee. Every query against the table, including a vector similarity search, has the policy attached automatically, no matter who wrote the SQL.
The first step is to load authority metadata into the same row as the embedding:
CREATE TABLE doc_chunk (
chunk_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id INTEGER NOT NULL,
sensitivity TEXT NOT NULL DEFAULT 'internal',
allowed_roles TEXT[] NOT NULL,
content TEXT NOT NULL,
embedding VECTOR(1536) NOT NULL
);
CREATE INDEX ON doc_chunk USING hnsw (embedding vector_cosine_ops);Then comes the policy, applied to a role dedicated to AI agents:
ALTER TABLE doc_chunk ENABLE ROW LEVEL SECURITY;
ALTER TABLE doc_chunk FORCE ROW LEVEL SECURITY;
CREATE POLICY doc_chunk_agent_read ON doc_chunk
FOR SELECT TO ai_agent
USING (
tenant_id = NULLIF(current_setting('app.tenant_id', TRUE), '')::INTEGER
AND allowed_roles && string_to_array(
NULLIF(current_setting('app.user_roles', TRUE), ''), ','
)
);The search itself sits inside a SECURITY INVOKER function, so that the policy always applies, and the trust middleware (never the model) sets the context on each transaction with SET LOCAL.
Three details, according to the author, make all the difference:
FORCE ROW LEVEL SECURITYis mandatory: without it, the table owner bypasses the policy, and superusers or roles withBYPASSRLSalways bypass it, so agents must never connect as one of them.current_setting(..., TRUE)combined withNULLIF(..., '')makes a missing context fail closed (zero rows) instead of open (all rows). The author found out, by running his own script, that on a reused pooled connection, a priorSET LOCALleaves the value as an empty string instead ofNULL, and''::INTEGERthrows an error without theNULLIF.- Who sets the context matters: if the model sends raw SQL on the same connection, it can change its own tenant. The way out is to keep SQL generation behind a fixed function interface, or to validate identity with a
SECURITY DEFINERfunction that checks a signed token.
Pattern 2: The Approximate Index Can Starve the Filter
RLS closes the leak, but opens a quieter problem. HNSW and IVFFlat are approximate indexes: pgvector walks the index once, assembles a candidate set, and only then applies all the filters, including the RLS policy. If most of the nearest neighbors belong to other tenants, a LIMIT 10 can come back with one row, or none, with no error at all.
Version 0.8.0 of pgvector introduced iterative scanning for exactly this case: the index keeps searching until it gathers enough rows after filtering, or until it hits a safety limit.
SET hnsw.iterative_scan = relaxed_order;
SET hnsw.max_scan_tuples = 20000; -- defaultIn the demo script that accompanies the piece (100,000 chunks, 20 tenants, tenant 42 with only 2,062 rows), the numbers make the problem concrete:
| Configuration | Result for LIMIT 10 |
|---|---|
hnsw.iterative_scan off | 1 row (39 discarded by the RLS filter) |
hnsw.iterative_scan = relaxed_order | 10 rows (568 discarded by the filter) |
The agent asked for ten passages and got one, with no alert at all. With iterative scanning on, the index kept searching until it returned the expected ten rows. relaxed_order allows for small reordering in exchange for more recall; strict_order preserves the exact distance order, at the cost of discarding more candidates.
One detail the author only caught by looking at the plan: inside the function, with a parameterized LIMIT, the planner estimated around 200 rows instead of 10, discarded the HNSW index, and used the B-tree index on tenant_id, sorting exactly the 2,061 rows visible to the tenant in 3 to 4 ms. For a small tenant that's the right plan, with perfect recall and no approximation. But the behavior isn't predictable just by reading the SQL text: you need to check EXPLAIN (ANALYZE, BUFFERS) running as the agent's role, with a real tenant context, and watch the Rows Removed by Filter line.
Pattern 3: Physical Boundaries for What Should Never Mix
RLS handles fine-grained rules, but some boundaries are too rigid to depend on a filter: one tenant should never share an index with another, or regulated records should never mix with the rest. For these cases, the author proposes changing where the data lives, not just how it's filtered.
Partitioning by tenant gives each one its own HNSW index, so the nearest-neighbor search runs only over that tenant's vectors, never scanning the others:
CREATE TABLE tenant_chunk (
chunk_id BIGINT GENERATED ALWAYS AS IDENTITY,
tenant_id INTEGER NOT NULL,
embedding VECTOR(1536) NOT NULL,
PRIMARY KEY (tenant_id, chunk_id)
) PARTITION BY LIST (tenant_id);
CREATE TABLE tenant_chunk_t42 PARTITION OF tenant_chunk FOR VALUES IN (42);
CREATE INDEX ON tenant_chunk_t42 USING hnsw (embedding vector_cosine_ops);The planner's behavior changes depending on where the tenant shows up in the query. With an explicit WHERE tenant_id = 42, pruning happens at planning time and the search goes straight to the HNSW index on the tenant_chunk_t42 partition. When the tenant comes only from the RLS policy, pruning only happens at execution time (it shows up as Subplans Removed: 1), and in the experiment described in the piece the planner chose a sequential scan with exact ordering over the 2,061 rows, instead of the HNSW index.
In short: it's worth keeping the RLS policy as a safety net, but passing the tenant explicitly gives the planner what it needs to prune and pick the right index. Partial indexes work in a similar way for sensitivity levels (WHERE sensitivity = 'public'), when the classification is stable and covers most queries: every candidate the index returns is already eligible, and the filter discards nothing.
Pattern 4: Proving What the Agent Could See
After an incident, saying "we have RLS" doesn't answer an auditor's questions: which rows did the agent retrieve for this answer, under which identity, and what else could it have retrieved.
The author recommends testing the boundary the way the agent encounters it: run as the ai_agent role, search for a restricted passage from a tenant or role that shouldn't see it, and confirm the result comes back empty. Every negative test needs a positive counterpart, with the correct role, to make sure an empty result isn't a broken search. Both should run in CI alongside schema migrations, so a policy that accidentally widens access breaks the build, instead of showing up in a post-mortem.
Beyond the tests, a trace table records, in the same transaction as the answer, which chunks went into the context and under which identity:
CREATE TABLE retrieval_trace (
trace_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
request_id UUID NOT NULL,
db_role TEXT NOT NULL DEFAULT current_user,
tenant_id INTEGER NOT NULL,
user_roles TEXT[] NOT NULL,
chunk_ids BIGINT[] NOT NULL,
retrieved_at TIMESTAMPTZ NOT NULL DEFAULT now()
);The author ties this table to the "Trace Everything" principle from the ORBIT framework applied to data retrieval: when someone asks why the agent said what it said, you can reconstruct the exact set of evidence, without relying on inference about the embedding. For broader audit coverage, the pgAudit extension logs SELECT activity by role, leaving a trail even outside the search function.
What This Costs, and When It's Not Worth It
None of these patterns is a recent novelty in PostgreSQL: RLS has existed since version 9.5, and list partitioning is a mature feature. What changes is applying them deliberately to vector search, where the index's approximation interacts with the filter in a way that doesn't show up just by reading the SQL.
Evaluating an RLS policy row by row has a cost, and it's worth measuring before generalizing. Partitioning by tenant solves rigid isolation, but multiplies operational work: each tenant gets its own partition and, possibly, its own HNSW index to maintain. For small tenants, like the 2,000-row one in the experiment, a B-tree with exact ordering delivers perfect recall faster than an approximate index, and skipping HNSW there is a sound decision, not a resource saving.
The full script that accompanies the piece is self-verifying, runs in about 40 seconds, requires PostgreSQL 16 or newer and pgvector 0.8.0 or newer, plus superuser access to create test databases and roles. For anyone running a RAG pipeline with real data, it's a more honest starting point than trusting that the application will never forget the filter.
Translated from the Brazilian Portuguese original · Read the original
PostgreSQL 19 brings concurrent REPACK, atomic upsert, and priority-based autovacuum
The release lost ambitious features like property graph queries and temporal UPDATE/DELETE, but brought concrete changes in locking, replication, and maintenance that affect those who operate databases day to day.





