
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
WHEREclause 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 matchCREATE 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 sqlSTABLESECURITY INVOKERAS $$ 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.
FORCE ROW LEVEL SECURITY. Without it, the table owner bypasses the policy. Superusers and roles withBYPASSRLSalways bypass it, so agents should never connect as either.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 aSET LOCALin an earlier transaction on the same pooled connection, the setting comes back as an empty string, not NULL, and''::INTEGERthrows an error. Wrapping it inNULLIF(..., '')makes both cases fail closed quietly.- Who sets the context. Custom settings like
app.tenant_idcan 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 aSECURITY DEFINERfunction 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 defaultSET hnsw.max_scan_tuples = 20000;--- Raise this if a higher max_scan_tuples doesn't improve recallSET 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_resultsORDER 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 = offLimit (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_orderLimit (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 usestenant_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 asSubplans 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 supportSELECT count(*) = 0 AS hr_chunk_unreachableFROM sf_search_chunks( p_query_embedding => (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid'), p_limit => 50) AS sWHERE 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:
| Step | What the agent did | Result |
|---|---|---|
| 1. Search-first, no RLS | Asked “when are bonuses paid?” | Top hit: HR ONLY: 2026 compensation review (distance 0.0024) |
2a. RLS, tenant 42, support | Same question | Top hit: the support FAQ (0.0197). The HR chunk is gone from the top 50. |
| 2b. RLS, context reset | SELECT count(*) after an earlier SET LOCAL | app.tenant_id is '', and 0 rows come back: it fails closed |
| 3a. HNSW, iterative scan off | LIMIT 10 | 1 row (39 removed by filter) |
3b. HNSW, relaxed_order | LIMIT 10 | 10 rows (568 removed by filter) |
3c. Inside sf_search_chunks | LIMIT p_limit | Planner chose the tenant B-tree plus an exact sort: 10 rows |
| 3d. Function forced onto HNSW | Without, then with, the function-level setting | 1 row, then 10 rows |
| 4a. Partitioned, tenant in query | WHERE tenant_id = 42 | Pruned at plan time; uses tenant_chunk_t42‘s HNSW index |
| 4b. Partitioned, tenant in RLS only | No WHERE | Subplans Removed: 1, exact sort over 2,061 rows |
| 4c. Partial index | WHERE sensitivity = 'public' | Uses doc_chunk_public_embedding_idx |
| 5a. Negative test | Search as support for the HR chunk | PASS |
| 5b. Positive test | Same search as hr | HR chunk visible, as it should be |
| 5c. Retrieval trace | Logged in the same transaction | ai_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
BYPASSRLSrole - 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
- pgvector: iterative index scans
- PostgreSQL documentation: Row Security Policies
- Crunchy Data: Hybrid search patterns with Postgres and pgvector
- ClickHouse: How to scale vector search in Postgres (pgvector)
- pgAudit
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 offDROP DATABASE IF EXISTS rag_demo;CREATE DATABASE rag_demo;\c rag_demoCREATE EXTENSION IF NOT EXISTS vector;SELECT extversion AS pgvector_version FROM pg_extensionWHERE extname = 'vector';--- Roles are cluster-wide, so create them only if missingDO $$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 itCREATE OR REPLACE FUNCTION sf_explain_lines(p_sql TEXT)RETURNS SETOF TEXTLANGUAGE plpgsqlSECURITY INVOKERAS $$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 VECTORLANGUAGE sqlVOLATILEAS $$ SELECT array_agg((random() - 0.5) * p_scale)::VECTORPreviousHomelabber’s second-gen Optane build uses two RTX 3090s to run 2.8-trillion-parameter Kimi AI model at 3.11 tokens per secondNextDerek Lowe: Detonating a Pile of Lead AzideRelated posts

Sub-Millisecond Route & Eden Treaty Caching in Bun, Elysia.js, and Hono
0dev.to
Refactoring FinLedger's Entire Lending Subsystem: What I Broke and How I Fixed It
0dev.to
Your CRM Stage Probabilities Were Made Up - Calibrate Them in an Afternoon
0HackerNoon
I got tired of rebuilding preview environments, so I built Stagd
0dev.to
FBI reportedly investigating apparent hack of its criminal database
0Hacker News
4 concurrency bugs I found reviewing my own Go service
0dev.to