wrkm.app
Search
Databases

Vibhor Kumar: Relevance Is Not Permission: Filter-First Vector Search in PostgreSQL

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

Editor
Nov 21, 2026 · 32 min read
Vibhor Kumar: Relevance Is Not Permission: Filter-First Vector Search in PostgreSQL

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 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 with BYPASSRLS always 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 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.

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 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 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 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:

StepWhat the agent didResult1. 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 closed3a. 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 rows3d. Function forced onto HNSWWithout, then with, the function-level setting1 row, then 10 rows4a. Partitioned, tenant in queryWHERE tenant_id = 42Pruned at plan time; uses tenant_chunk_t42‘s HNSW index4b. Partitioned, tenant in RLS onlyNo WHERESubplans Removed: 1, exact sort over 2,061 rows4c. Partial indexWHERE sensitivity = 'public'Uses doc_chunk_public_embedding_idx5a. Negative testSearch as support for the HR chunkPASS5b. Positive testSame search as hrHR chunk visible, as it should be5c. 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

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)::VECTOR FROM generate_series(1, p_dim);$$;CREATE TABLE topic_centroid ( topic_id INTEGER PRIMARY KEY, centroid VECTOR(64) NOT NULL);INSERT INTO topic_centroid (topic_id, centroid)SELECT t, sf_random_vector(p_dim => 64)FROM generate_series(1, 20) AS t;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, topic_id INTEGER NOT NULL, content TEXT NOT NULL, embedding VECTOR(64) NOT NULL);/ * Tenant 42 is small (about 2% of rows), the way most tenants are in a * multi-tenant SaaS. Everything else is spread across tenants 1..20. * Topic 3 is "compensation and bonuses". /INSERT INTO doc_chunk (tenant_id, sensitivity, allowed_roles, topic_id, content, embedding)SELECT v.tenant_id, 'internal', ARRAY['support'], v.topic_id, format('Tenant %s, topic %s, chunk %s', v.tenant_id, v.topic_id, g), c.centroid + sf_random_vector(p_dim => 64, p_scale => 0.6)FROM generate_series(1, 100000) AS gCROSS JOIN LATERAL ( SELECT CASE WHEN random() < 0.02 THEN 42 ELSE 1 + floor(random() * 20)::INTEGER END AS tenant_id, 1 + floor(random() * 20)::INTEGER AS topic_id, g AS g_ref) AS vJOIN topic_centroid c ON c.topic_id = v.topic_id;--- Two tenant-42 chunks about bonuses: one for support, one HR-onlyINSERT INTO doc_chunk (tenant_id, sensitivity, allowed_roles, topic_id, content, embedding)SELECT 42, 'internal', ARRAY['support'], 3, 'FAQ: annual bonuses are paid in the March payroll cycle.', centroid + sf_random_vector(p_dim => 64, p_scale => 0.2)FROM topic_centroid WHERE topic_id = 3UNION ALLSELECT 42, 'restricted', ARRAY['hr'], 3, 'HR ONLY: 2026 compensation review - bonus pool by band and name.', centroid + sf_random_vector(p_dim => 64, p_scale => 0.05)FROM topic_centroid WHERE topic_id = 3;--- 10% of chunks are public, for the partial-index exampleUPDATE doc_chunk SET sensitivity = 'public'WHERE chunk_id % 10 = 0 AND sensitivity = 'internal';SET maintenance_work_mem = '256MB';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);ANALYZE doc_chunk;SELECT count() AS total_chunks, count() FILTER (WHERE tenant_id = 42) AS tenant_42_chunks, count() FILTER (WHERE 'hr' = ANY (allowed_roles)) AS hr_only_chunksFROM doc_chunk;--- The "user question": an embedding near the compensation topicCREATE TABLE query_vector ( label TEXT PRIMARY KEY, embedding VECTOR(64) NOT NULL);INSERT INTO query_vector (label, embedding)SELECT 'when_are_bonuses_paid', centroid + sf_random_vector(p_dim => 64, p_scale => 0.05)FROM topic_centroid WHERE topic_id = 3;GRANT SELECT ON doc_chunk, query_vector TO ai_agent, app_naive;------------------------------------------------------------------------------\echo ''\echo '=== 1. The leak: search-first, no authority in the table ==='------------------------------------------------------------------------------SET ROLE app_naive;SELECT c.chunk_id, c.tenant_id, c.allowed_roles, left(c.content, 60) AS content, round((c.embedding q.embedding)::NUMERIC, 4) AS distanceFROM doc_chunk c, query_vector qWHERE q.label = 'when_are_bonuses_paid'ORDER BY c.embedding q.embeddingLIMIT 5;SELECT c.content LIKE 'HR ONLY%' AS chk_leak_top_is_hrFROM doc_chunk c, query_vector qWHERE q.label = 'when_are_bonuses_paid'ORDER BY c.embedding q.embeddingLIMIT 1 \gsetRESET ROLE;------------------------------------------------------------------------------\echo ''\echo '=== 2. Pattern 1: RLS policy + retrieval function ==='------------------------------------------------------------------------------ALTER TABLE doc_chunk ENABLE ROW LEVEL SECURITY;ALTER TABLE doc_chunk FORCE ROW LEVEL SECURITY;/* * Fail closed. current_setting(..., TRUE) returns NULL only if the setting * was never defined in this session. After a SET LOCAL in an earlier * transaction on the same pooled connection, it returns '' instead, and * ''::INTEGER raises an error. NULLIF turns both cases into NULL -> no rows. /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), ''), ',') );CREATE OR REPLACE FUNCTION sf_search_chunks( p_query_embedding VECTOR(64), 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;$$;\echo '--- 2a. Same question, tenant 42, support role'BEGIN;SET LOCAL ROLE ai_agent;SET LOCAL app.tenant_id = '42';SET LOCAL app.user_roles = 'support';SELECT s.chunk_id, left(s.content, 60) AS content, round(s.distance::NUMERIC, 4) AS distanceFROM sf_search_chunks( p_query_embedding => (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid'), p_limit => 5) AS s;SELECT bool_and(s.content NOT LIKE 'HR ONLY%') AS chk_rls_hides_hr, (array_agg(s.content ORDER BY s.distance))[1] LIKE 'FAQ%' AS chk_rls_top_is_faqFROM sf_search_chunks( p_query_embedding => (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid'), p_limit => 50) AS s \gsetCOMMIT;\echo '--- 2b. Context reset: earlier SET LOCAL, none now -> zero rows'BEGIN;SET LOCAL ROLE ai_agent;SELECT quote_literal(current_setting('app.tenant_id', TRUE)) AS app_tenant_id_now, (SELECT count() FROM doc_chunk) AS rows_visible_without_context;SELECT count() = 0 AS chk_no_context_zero_rows FROM doc_chunk \gsetCOMMIT;------------------------------------------------------------------------------\echo ''\echo '=== 3. Pattern 2: the overfiltering trap (HNSW, LIMIT 10) ==='------------------------------------------------------------------------------\echo '--- 3a. hnsw.iterative_scan = off (default): HNSW, then the RLS filter'BEGIN;SET LOCAL ROLE ai_agent;SET LOCAL app.tenant_id = '42';SET LOCAL app.user_roles = 'support';SET LOCAL hnsw.iterative_scan = off;EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)SELECT c.chunk_idFROM doc_chunk cORDER BY c.embedding (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid')LIMIT 10;SELECT count() AS off_rowsFROM ( SELECT c.chunk_id FROM doc_chunk c ORDER BY c.embedding (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid') LIMIT 10) AS r \gsetCOMMIT;\echo '--- 3b. hnsw.iterative_scan = relaxed_order'BEGIN;SET LOCAL ROLE ai_agent;SET LOCAL app.tenant_id = '42';SET LOCAL app.user_roles = 'support';SET LOCAL hnsw.iterative_scan = relaxed_order;EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)SELECT c.chunk_idFROM doc_chunk cORDER BY c.embedding (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid')LIMIT 10;SELECT count() AS relaxed_rowsFROM ( SELECT c.chunk_id FROM doc_chunk c ORDER BY c.embedding (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid') LIMIT 10) AS r \gsetCOMMIT;\echo '--- 3c. Inside sf_search_chunks, LIMIT is a parameter: check its plan'LOAD 'auto_explain';SET auto_explain.log_min_duration = 0;SET auto_explain.log_nested_statements = on;SET auto_explain.log_analyze = on;SET auto_explain.log_timing = off;SET auto_explain.log_parameter_max_length = 0;SET client_min_messages = log;BEGIN;SET LOCAL ROLE ai_agent;SET LOCAL app.tenant_id = '42';SET LOCAL app.user_roles = 'support';SET LOCAL hnsw.iterative_scan = off;SELECT count() AS rows_returned_of_10FROM sf_search_chunks( p_query_embedding => (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid'), p_limit => 10);COMMIT;SET client_min_messages = notice;SET auto_explain.log_min_duration = -1;\echo '--- 3d. Force HNSW inside the function: without vs with the setting'BEGIN;SET LOCAL ROLE ai_agent;SET LOCAL app.tenant_id = '42';SET LOCAL app.user_roles = 'support';SET LOCAL enable_bitmapscan = off;SET LOCAL enable_seqscan = off;SELECT count() AS func_rows_without_settingFROM sf_search_chunks( p_query_embedding => (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid'), p_limit => 10) \gsetCOMMIT;--- The fix: scope iterative scans to the retrieval function itselfALTER FUNCTION sf_search_chunks(VECTOR, INTEGER) SET hnsw.iterative_scan = 'relaxed_order';BEGIN;SET LOCAL ROLE ai_agent;SET LOCAL app.tenant_id = '42';SET LOCAL app.user_roles = 'support';SET LOCAL enable_bitmapscan = off;SET LOCAL enable_seqscan = off;SELECT count() AS func_rows_with_settingFROM sf_search_chunks( p_query_embedding => (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid'), p_limit => 10) \gsetCOMMIT;SELECT :func_rows_without_setting AS func_rows_without_setting, :func_rows_with_setting AS func_rows_with_setting;------------------------------------------------------------------------------\echo ''\echo '=== 4. Pattern 3: hard boundaries (partitions, partial index) ==='------------------------------------------------------------------------------CREATE TABLE tenant_chunk ( chunk_id BIGINT NOT NULL, tenant_id INTEGER NOT NULL, content TEXT NOT NULL, embedding VECTOR(64) 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 TABLE tenant_chunk_other PARTITION OF tenant_chunk DEFAULT;INSERT INTO tenant_chunk (chunk_id, tenant_id, content, embedding)SELECT chunk_id, tenant_id, content, embeddingFROM doc_chunkWHERE allowed_roles && ARRAY['support'];CREATE INDEX ON tenant_chunk_t42 USING hnsw (embedding vector_cosine_ops);CREATE INDEX ON tenant_chunk_other USING hnsw (embedding vector_cosine_ops);ANALYZE tenant_chunk;--- Same RLS safety net on the partitioned tableALTER TABLE tenant_chunk ENABLE ROW LEVEL SECURITY;ALTER TABLE tenant_chunk FORCE ROW LEVEL SECURITY;GRANT SELECT ON tenant_chunk TO ai_agent;CREATE POLICY tenant_chunk_agent_read ON tenant_chunk FOR SELECT TO ai_agent USING ( tenant_id = NULLIF(current_setting('app.tenant_id', TRUE), '')::INTEGER );\echo '--- 4a. Literal tenant_id: pruned at plan time (other partition absent)'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)SELECT t.chunk_idFROM tenant_chunk tWHERE t.tenant_id = 42ORDER BY t.embedding (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid')LIMIT 10;SELECT bool_and(l NOT LIKE '%tenant_chunk_other%') AS chk_plan_time_pruningFROM sf_explain_lines($q$ SELECT t.chunk_id FROM tenant_chunk t WHERE t.tenant_id = 42 ORDER BY t.embedding (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid') LIMIT 10 $q$) AS l \gset\echo '--- 4b. Tenant from RLS only: pruned at run time (Subplans Removed)'BEGIN;SET LOCAL ROLE ai_agent;SET LOCAL app.tenant_id = '42';EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)SELECT t.chunk_idFROM tenant_chunk tORDER BY t.embedding (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid')LIMIT 10;SELECT bool_or(l LIKE '%Subplans Removed: 1%') AS chk_runtime_pruningFROM sf_explain_lines($q$ SELECT t.chunk_id FROM tenant_chunk t ORDER BY t.embedding (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid') LIMIT 10 $q$) AS l \gsetCOMMIT;\echo '--- 4c. Partial HNSW index for one sensitivity tier'CREATE INDEX doc_chunk_public_embedding_idx ON doc_chunk USING hnsw (embedding vector_cosine_ops) WHERE sensitivity = 'public';ANALYZE doc_chunk;EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)SELECT c.chunk_idFROM doc_chunk cWHERE c.sensitivity = 'public'ORDER BY c.embedding (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid')LIMIT 10;SELECT bool_or(l LIKE '%doc_chunk_public_embedding_idx%') AS chk_partial_index_usedFROM sf_explain_lines($q$ SELECT c.chunk_id FROM doc_chunk c WHERE c.sensitivity = 'public' ORDER BY c.embedding (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid') LIMIT 10 $q$) AS l \gset------------------------------------------------------------------------------\echo ''\echo '=== 5. Pattern 4: negative test + retrieval trace ==='------------------------------------------------------------------------------\echo '--- 5a. Negative test: HR-only chunk must be unreachable for support'BEGIN;SET LOCAL ROLE ai_agent;SET LOCAL app.tenant_id = '42';SET LOCAL app.user_roles = 'support';SELECT count() = 0 AS chk_negative_testFROM 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%' \gsetCOMMIT;\echo '--- 5b. Same search as an HR user: the chunk is (correctly) visible'BEGIN;SET LOCAL ROLE ai_agent;SET LOCAL app.tenant_id = '42';SET LOCAL app.user_roles = 'hr';SELECT s.chunk_id, left(s.content, 60) AS contentFROM sf_search_chunks( p_query_embedding => (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid'), p_limit => 3) AS s;SELECT count() = 1 AS chk_hr_sees_hrFROM sf_search_chunks( p_query_embedding => (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid'), p_limit => 3) AS sWHERE s.content LIKE 'HR ONLY%' \gsetCOMMIT;\echo '--- 5c. Record what the agent saw, in the same transaction'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());GRANT INSERT ON retrieval_trace TO ai_agent;BEGIN;SET LOCAL ROLE ai_agent;SET LOCAL app.tenant_id = '42';SET LOCAL app.user_roles = 'support';INSERT INTO retrieval_trace (request_id, tenant_id, user_roles, chunk_ids)SELECT gen_random_uuid(), current_setting('app.tenant_id')::INTEGER, string_to_array(current_setting('app.user_roles'), ','), array_agg(s.chunk_id ORDER BY s.distance)FROM sf_search_chunks( p_query_embedding => (SELECT embedding FROM query_vector WHERE label = 'when_are_bonuses_paid'), p_limit => 5) AS s;COMMIT;SELECT trace_id, db_role, tenant_id, user_roles, chunk_idsFROM retrieval_trace;SELECT db_role = 'ai_agent' AND cardinality(chunk_ids) = 5 AS chk_traceFROM retrieval_traceORDER BY trace_id DESCLIMIT 1 \gset------------------------------------------------------------------------------\echo ''\echo '=== 6. Validation report: every claim in the post ==='------------------------------------------------------------------------------CREATE TEMP TABLE _temp_validation ( check_no INTEGER, claim TEXT, expected TEXT, actual TEXT);INSERT INTO _temp_validation (check_no, claim, expected, actual) VALUES (1, 'Search-first, no RLS: top hit is the HR-only chunk', 't', :'chk_leak_top_is_hr'), (2, 'RLS: HR chunk absent from top 50 for support role', 't', :'chk_rls_hides_hr'), (3, 'RLS: top hit for support role is the FAQ', 't', :'chk_rls_top_is_faq'), (4, 'No context (after earlier SET LOCAL) returns 0 rows', 't', :'chk_no_context_zero_rows'), (5, 'HNSW + RLS, iterative scan off: fewer than 10 rows', 't', (:off_rows < 10)::TEXT), (6, 'HNSW + RLS, relaxed_order: exactly 10 rows', 't', (:relaxed_rows = 10)::TEXT), (7, 'Function forced to HNSW, no setting: fewer than 10 rows', 't', (:func_rows_without_setting < 10)::TEXT), (8, 'Function forced to HNSW, SET on function: 10 rows', 't', (:func_rows_with_setting = 10)::TEXT), (9, 'Literal tenant_id: other partition pruned at plan time', 't', :'chk_plan_time_pruning'), (10, 'Tenant from RLS only: runtime pruning (Subplans Removed)', 't', :'chk_runtime_pruning'), (11, 'Partial HNSW index used for sensitivity = public', 't', :'chk_partial_index_used'), (12, 'Negative test: support cannot retrieve HR chunk', 't', :'chk_negative_test'), (13, 'Positive test: HR role can retrieve HR chunk', 't', :'chk_hr_sees_hr'), (14, 'Trace row records ai_agent and 5 chunk IDs', 't', :'chk_trace');SELECT check_no AS "#", claim, CASE WHEN actual::BOOLEAN = expected::BOOLEAN THEN 'PASS' ELSE 'FAIL' END AS resultFROM _temp_validationORDER BY check_no;SELECT count() FILTER (WHERE actual::BOOLEAN = expected::BOOLEAN) AS passed, count() FILTER (WHERE actual::BOOLEAN expected::BOOLEAN) AS failed, :off_rows AS hnsw_off_rows_of_10, :relaxed_rows AS hnsw_relaxed_rows_of_10FROM _temp_validation;

Kaynak: Planet PostgreSQL

#PostgreSQL
Share

From other categories