The retrieval was perfect. That was the problem.

Picture an internal assistant that answers employee questions from a shared document store. Someone asks a reasonable question about bonus timelines. The vector search does exactly what it was built to do and finds the most semantically similar chunks. One of those chunks comes from a compensation review meant only for HR.

No model hallucinated, no index was corrupted, and no query timed out. Every component worked as designed, and that’s why no alert fired. The system answered the question it was asked, which was “what is most relevant?” Nobody had asked it “what is this person allowed to see?”

I argued in a recent Forbes Technology Council article that relevance is not permission [add link to your Forbes article]. In this post I want to show what that means in PostgreSQL itself, in the SQL. It comes down to four patterns. First, put authority in the table. Second, don’t let the filter quietly starve your results. Third, make hard boundaries physical. Fourth, prove what the agent could see.

Similarity search has no concept of authority

An embedding captures what a chunk means. It doesn’t capture who owns the chunk, which tenant it belongs to, or which regulation covers it. Two paragraphs from a public FAQ and a confidential board memo can sit right next to each other in vector space. That’s because they talk about the same thing.

Most RAG pipelines I review handle permissions in one of two ways. Both are fragile.

  • Filter in the application, after retrieval. The app fetches the top k chunks and then drops the ones the user can’t see. One missed code path, a new agent, or a debugging script, and the filter is gone.
  • Filter in a WHERE clause written by whoever writes the query. That’s better, until an LLM-generated query, a notebook, or a new microservice forgets it.

In both cases, permission is a habit of the caller and not a property of the data. When an agent can query on its own, the caller is no longer a developer you can code-review. The permission has to live where the data lives, which is in PostgreSQL.

Pattern 1: Put authority in the table with Row Level Security

Row Level Security (RLS) turns “who can see this row” from a coding convention into a database guarantee. Every query against the table, including a vector similarity query, gets the policy appended to it. It doesn’t matter who wrote the query or how.

Start with a chunk table that carries its own authority metadata next to the embedding:

 
CREATE EXTENSION IF NOT EXISTS vector;
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 doc_chunk_embedding_idx
ON doc_chunk USING hnsw (embedding vector_cosine_ops);
CREATE INDEX doc_chunk_tenant_idx
ON doc_chunk (tenant_id);

Next, give agents their own role and a policy that denies by default:

 
CREATE ROLE ai_agent NOLOGIN;
GRANT SELECT ON doc_chunk TO ai_agent;
ALTER TABLE doc_chunk ENABLE ROW LEVEL SECURITY;
ALTER TABLE doc_chunk FORCE ROW LEVEL SECURITY;
--- NULLIF: a missing or emptied setting becomes NULL, so no rows match
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), ''), ',')
);

Then wrap retrieval in a function that runs with the caller’s rights, so the policy always applies:

 
CREATE OR REPLACE FUNCTION sf_search_chunks(
p_query_embedding VECTOR(1536),
p_limit INTEGER DEFAULT 10
)
RETURNS TABLE (chunk_id BIGINT, content TEXT, distance DOUBLE PRECISION)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
SELECT c.chunk_id,
c.content,
c.embedding <=> p_query_embedding AS distance
FROM doc_chunk c
ORDER BY c.embedding <=> p_query_embedding
LIMIT p_limit;
$$;

The trusted middleware, not the model, sets the context for each request inside a transaction:

 
BEGIN;
SET LOCAL ROLE ai_agent;
SET LOCAL app.tenant_id = '42';
SET LOCAL app.user_roles = 'support,emea';
SELECT *
FROM sf_search_chunks(
p_query_embedding => $1,
p_limit => 10
);
COMMIT;

Three details matter more than they look.

  1. FORCE ROW LEVEL SECURITY. Without it, the table owner bypasses the policy. Superusers and roles with BYPASSRLS always bypass it, so agents should never connect as either.
  2. current_setting(..., TRUE). The second argument returns NULL when the setting has never been defined. NULL makes the policy false, so a forgotten context means zero rows, not every row. There’s a catch I only found by running this. After a SET LOCAL in an earlier transaction on the same pooled connection, the setting comes back as an empty string, not NULL, and ''::INTEGER throws an error. Wrapping it in NULLIF(..., '') makes both cases fail closed quietly.
  3. Who sets the context. Custom settings like app.tenant_id can be changed by any session. If the model can send raw SQL over this connection, it can change its own tenant. Keep SQL generation behind a fixed function interface, or set identity through a SECURITY DEFINER function that checks it against a signed session token.

Pattern 2: Don’t let the filter starve the answer

RLS fixes the leak, but it can also cause a quieter failure. HNSW and IVFFlat are approximate indexes. By default, pgvector walks the index once, collects a candidate set, and then applies every filter, including the RLS policy. If most of the nearest neighbors belong to other tenants or roles, LIMIT 10 can quietly come back with one row, or none.

Nothing errors. The agent just gets less context, gives a thinner answer, and looks less intelligent than it is. Teams often blame the model or the embedding at this point. The real cause is a security filter applied after an approximate scan.

pgvector 0.8.0 added iterative index scans for this case. When enabled, the index keeps scanning until enough rows survive the filter, or until it hits a safety limit:

 
--- Keep scanning until LIMIT is met (pgvector 0.8.0+)
SET hnsw.iterative_scan = relaxed_order;
--- Upper bound on tuples visited; 20000 is the default
SET hnsw.max_scan_tuples = 20000;
--- Raise this if a higher max_scan_tuples doesn't improve recall
SET hnsw.scan_mem_multiplier = 2;

strict_order keeps results in exact distance order. relaxed_order allows small reordering in exchange for better recall. If you need exact order with relaxed scanning, materialize the results first and sort them again outside:

 
WITH relaxed_results AS MATERIALIZED (
SELECT chunk_id,
content,
embedding <=> $1 AS distance
FROM doc_chunk
ORDER BY distance
LIMIT 10
)
SELECT *
FROM relaxed_results
ORDER BY distance;

You can bake the setting into the retrieval function so no caller forgets it. Add SET hnsw.iterative_scan = 'relaxed_order' to the CREATE FUNCTION statement for sf_search_chunks. That scopes it to the function call.

Then measure. Run EXPLAIN (ANALYZE, BUFFERS) as the agent role, with a realistic tenant context, and look at Rows Removed by Filter on the index scan. If that number is large compared with the rows returned, your security model and your recall are fighting each other. Patterns 3 and 4 are how you make them stop.

Here’s the trap on the demo data (the full script is near the end of this post). Tenant 42 owns about 2% of 100,000 chunks, the agent asks for LIMIT 10, and hnsw.ef_search is left at its default of 40:

 
--- hnsw.iterative_scan = off
Limit (actual rows=1 loops=1)
-> Index Scan using doc_chunk_embedding_idx on doc_chunk c (actual rows=1)
Order By: (embedding <=> $0)
Filter: (... RLS policy ...)
Rows Removed by Filter: 39
--- hnsw.iterative_scan = relaxed_order
Limit (actual rows=10 loops=1)
-> Index Scan using doc_chunk_embedding_idx on doc_chunk c (actual rows=10)
Order By: (embedding <=> $0)
Filter: (... RLS policy ...)
Rows Removed by Filter: 568

The agent asked for ten chunks and got one, with no error. HNSW returned its 40 candidates, the RLS policy removed 39 because they belonged to other tenants, and the query ended there. With iterative scans on, the index kept walking. It looked at 578 candidates and returned all ten. (On pgvector 0.8.1 the filter removed 578 instead of 568. Every other number in this post is identical on both versions.)

One more detail surprised me. Inside sf_search_chunks, LIMIT p_limit is a parameter, so the planner estimated about 200 rows instead of 10. It skipped HNSW, used the B-tree on tenant_id, and sorted all 2,061 of the tenant’s visible rows exactly. For a small tenant that’s the right plan, with perfect recall in 3 to 4 ms. But it means you can’t predict the trap from the SQL text alone. Check the plan inside the function with auto_explain (log_nested_statements = on).

Then protect the day the planner picks HNSW anyway. In the demo I forced that plan by disabling bitmap and sequential scans. The function returned 1 row out of 10. After ALTER FUNCTION sf_search_chunks(VECTOR, INTEGER) SET hnsw.iterative_scan = 'relaxed_order', the same call returned 10. A setting attached to the function travels with every call, whoever the caller is.

Pattern 3: Make hard boundaries physical

RLS is the right tool for fine-grained rules. Some boundaries are blunt, though: tenant A must never share an index with tenant B, or regulated records must stay separate from everything else. For those, stop relying on a filter and change where the data lives.

Partition by tenant. With list partitioning, each tenant gets its own partition with its own HNSW index. The nearest-neighbor search then runs only on vectors the tenant owns. Other tenants’ vectors aren’t filtered out after the fact. They’re never scanned at all.

 
CREATE TABLE tenant_chunk (
chunk_id BIGINT GENERATED ALWAYS AS IDENTITY,
tenant_id INTEGER NOT NULL,
content TEXT 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);

Keep an RLS policy on the partitioned table as a safety net, and then read the plan. How the tenant reaches the query changes what you’ll see:

  • Tenant in the query (WHERE tenant_id = 42): The planner prunes at plan time. The other partitions don’t appear in the plan at all, and the search uses tenant_chunk_t42‘s own HNSW index.
  • Tenant only in the RLS policy: The planner can’t know the tenant until run time, because current_setting() is only stable. Pruning still happens, and it shows as Subplans Removed: 1. In the demo, though, the planner then did a sequential scan and exact sort over the partition instead of using its HNSW index. That’s fine for 2,061 rows, but not for a large tenant.

So pass the tenant explicitly and keep the policy. The explicit filter gives the planner what it needs to prune and use the right index. The policy guarantees that a query which forgets the filter still can’t cross tenants. For small tenants, you may not need an approximate index at all. A B-tree on the partition key followed by an exact scan gives perfect recall with no approximation loss, and it can be faster than walking a graph.

Partial indexes for sensitivity tiers. When a small, stable set of classifications covers most queries, build an index per tier:

 
CREATE INDEX doc_chunk_public_embedding_idx
ON doc_chunk USING hnsw (embedding vector_cosine_ops)
WHERE sensitivity = 'public';

In the demo, a query with WHERE sensitivity = 'public' picked this partial index on its own. Every candidate it returns is already eligible, so the filter has nothing to throw away.

It’s useful to state the rule plainly. Use RLS for who may see a row. Use physical layout for what must never meet.

Pattern 4: Prove what the agent could see

After an incident, “we have RLS” isn’t an answer. The questions an auditor, a regulator, or your own CISO will ask are more specific. Which rows did the agent retrieve for this answer? Under which identity? Could it have retrieved anything else?

Test the boundary the way the agent hits it. Write negative tests that run as ai_agent. Embed a chunk you know is restricted, search for it from a tenant or role that shouldn’t see it, and assert that you get zero rows:

 
BEGIN;
SET LOCAL ROLE ai_agent;
SET LOCAL app.tenant_id = '42';
SET LOCAL app.user_roles = 'support';
--- Expect TRUE: the HR-only chunk is not in the top 50 for support
SELECT count(*) = 0 AS hr_chunk_unreachable
FROM sf_search_chunks(
p_query_embedding => (SELECT embedding
FROM query_vector
WHERE label = 'when_are_bonuses_paid'),
p_limit => 50
) AS s
WHERE s.content LIKE 'HR ONLY%';
ROLLBACK;

query_vector holds labeled test questions, here one that lands right on top of the HR chunk. Pair every negative test with a positive one: run the same search with app.user_roles = 'hr', and the HR chunk must come back. A test that only checks for absence also passes when retrieval is simply broken. Put both in CI alongside your schema migrations. A policy change that widens access should fail a build, not show up in a postmortem.

Record retrieval as evidence. Logging the prompt and the response isn’t enough. Store which chunks went into the context, and under which identity, in the same transaction that serves the request:

 
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()
);

This is ORBIT’s “Trace Everything” principle applied to retrieval. When someone asks why the agent said what it said, you can replay the exact evidence set. You won’t have to guess from the embedding. For broader coverage, pgAudit can log SELECT activity by role, so reads by ai_agent leave a trail even outside your retrieval function.

Try it yourself: a working demo with data

Everything above runs end to end in one self-checking script, [relevance_is_not_permission_demo.sql] [Vibhor: link to your GitHub gist]. The full script is also in the appendix. It builds a synthetic corpus with 100,000 chunks, 20 topic clusters, and 20 tenants. Tenant 42 is small, at 2,062 chunks. Two of its chunks are about bonuses: a support FAQ and an HR-only compensation review. The embeddings are random 64-dimension vectors around topic centers, so the script needs no model and finishes in about 40 seconds.

 
psql -X -f relevance_is_not_permission_demo.sql

You need PostgreSQL 16 or later, pgvector 0.8.0 or later, and a superuser, because the script creates a database and two roles. I ran it on PostgreSQL 16.15 with pgvector 0.8.1 and 0.8.2. The random seed is fixed, so you should see the same results:

StepWhat the agent didResult
1. Search-first, no RLSAsked “when are bonuses paid?”Top hit: HR ONLY: 2026 compensation review (distance 0.0024)
2a. RLS, tenant 42, supportSame questionTop hit: the support FAQ (0.0197). The HR chunk is gone from the top 50.
2b. RLS, context resetSELECT count(*) after an earlier SET LOCALapp.tenant_id is '', and 0 rows come back: it fails closed
3a. HNSW, iterative scan offLIMIT 101 row (39 removed by filter)
3b. HNSW, relaxed_orderLIMIT 1010 rows (568 removed by filter)
3c. Inside sf_search_chunksLIMIT p_limitPlanner chose the tenant B-tree plus an exact sort: 10 rows
3d. Function forced onto HNSWWithout, then with, the function-level setting1 row, then 10 rows
4a. Partitioned, tenant in queryWHERE tenant_id = 42Pruned at plan time; uses tenant_chunk_t42‘s HNSW index
4b. Partitioned, tenant in RLS onlyNo WHERESubplans Removed: 1, exact sort over 2,061 rows
4c. Partial indexWHERE sensitivity = 'public'Uses doc_chunk_public_embedding_idx
5a. Negative testSearch as support for the HR chunkPASS
5b. Positive testSame search as hrHR chunk visible, as it should be
5c. Retrieval traceLogged in the same transactionai_agent, tenant 42, {support}, 5 chunk IDs

The first two rows tell the story of this post. The question, the vector, and the index are the same. The only difference is whether PostgreSQL knew who was asking.

The script ends with a validation report that turns each row above into a PASS or FAIL check. If you change something, such as a policy, a setting, or your own data, run it again and watch which checks move:

 # | claim | result ----+----------------------------------------------------------+-------- 1 | Search-first, no RLS: top hit is the HR-only chunk | PASS 2 | RLS: HR chunk absent from top 50 for support role | PASS 3 | RLS: top hit for support role is the FAQ | PASS 4 | No context (after earlier SET LOCAL) returns 0 rows | PASS 5 | HNSW + RLS, iterative scan off: fewer than 10 rows | PASS 6 | HNSW + RLS, relaxed_order: exactly 10 rows | PASS 7 | Function forced to HNSW, no setting: fewer than 10 rows | PASS 8 | Function forced to HNSW, SET on function: 10 rows | PASS 9 | Literal tenant_id: other partition pruned at plan time | PASS 10 | Tenant from RLS only: runtime pruning (Subplans Removed) | PASS 11 | Partial HNSW index used for sensitivity = public | PASS 12 | Negative test: support cannot retrieve HR chunk | PASS 13 | Positive test: HR role can retrieve HR chunk | PASS 14 | Trace row records ai_agent and 5 chunk IDs | PASS passed | failed | hnsw_off_rows_of_10 | hnsw_relaxed_rows_of_10 --------+--------+---------------------+------------------------- 14 | 0 | 1 | 10

To run it against real embeddings, change VECTOR(64) to your model’s dimension, load your chunks with their tenant and role metadata, and keep everything else as it is.

Filter first, search second

If you take one habit from this post, make it this one. Decide what the agent is allowed to see before you ask what’s most relevant, and let PostgreSQL enforce the order.

  • Authority metadata (tenant, roles, sensitivity) sits in the same row as the embedding
  • RLS is enabled and forced, and agents never connect as an owner, a superuser, or a BYPASSRLS role
  • Policies use current_setting(..., TRUE) wrapped in NULLIF(…, ”), so a missing or reset context fails closed
  • Trusted middleware sets identity per transaction, and the model never does
  • Iterative scans are on for filtered vector queries, and recall is measured as the agent role
  • Hard boundaries use partitions or partial indexes, not just filters
  • Negative access tests run in CI
  • Every answer can be traced back to the exact chunk IDs and identity behind it

None of this is new PostgreSQL. RLS has been around since 9.5, and partitioning, SECURITY INVOKER functions, and pgAudit are familiar tools. What’s new is the caller. When software can decide for itself what to look up, the database becomes the last place where “should it?” still gets asked, and the only place that can’t be skipped.

I’d love to hear how others in the community are handling this. Where does your team enforce permissions for vector search today: in the application, in the query, or in the table?

Sources

Appendix: the complete script

Save this as relevance_is_not_permission_demo.sql and run psql -X -f relevance_is_not_permission_demo.sql as a superuser. It drops and recreates a database called rag_demo and creates two roles, ai_agent and app_naive, if they don’t already exist, so run it on a test instance. To clean up afterwards: DROP DATABASE rag_demo; DROP ROLE ai_agent; DROP ROLE app_naive;

 
/*
* Relevance Is Not Permission: Filter-First Vector Search in PostgreSQL
* Complete, self-checking demo with synthetic data.
*
* Requirements : PostgreSQL 16+ and pgvector 0.8.0+ (iterative scans)
* Tested on : PostgreSQL 16.15 with pgvector 0.8.1 and 0.8.2
* Run as : a superuser (it creates a database and two roles)
*
* psql -X -f relevance_is_not_permission_demo.sql
*
* The script ends with a validation report: one row per claim in the post,
* each marked PASS or FAIL. Runtime is about 40-60 seconds.
*
* Embeddings are synthetic 64-dim vectors around 20 topic centers, so no
* embedding model is needed. Use your model's dimension (e.g. 1536) for
* real data.
*/
\set ON_ERROR_STOP on
\pset footer off
DROP DATABASE IF EXISTS rag_demo;
CREATE DATABASE rag_demo;
\c rag_demo
CREATE EXTENSION IF NOT EXISTS vector;
SELECT extversion AS pgvector_version FROM pg_extension
WHERE extname = 'vector';
--- Roles are cluster-wide, so create them only if missing
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'ai_agent') THEN
CREATE ROLE ai_agent NOLOGIN;
END IF;
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_naive') THEN
CREATE ROLE app_naive NOLOGIN;
END IF;
END
$$;
--- Helper: return an EXPLAIN ANALYZE plan as rows, so checks can inspect it
CREATE OR REPLACE FUNCTION sf_explain_lines(p_sql TEXT)
RETURNS SETOF TEXT
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
v_line TEXT;
BEGIN
FOR v_line IN EXECUTE
'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) ' || p_sql
LOOP
RETURN NEXT v_line;
END LOOP;
END
$$;
------------------------------------------------------------------------------
\echo ''
\echo '=== 0. Synthetic data: 20 topics, 100,000 chunks, 20 tenants ==='
------------------------------------------------------------------------------
SELECT setseed(0.42);
--- Random vector with each element in [-p_scale/2, p_scale/2]
CREATE OR REPLACE FUNCTION sf_random_vector(
p_dim INTEGER,
p_scale DOUBLE PRECISION DEFAULT 1.0
)
RETURNS VECTOR
LANGUAGE sql
VOLATILE
AS $$
SELECT array_agg((random() - 0.5) * p_scale)::VECTOR

Related posts