权限过滤与 pgvector：为什么权限最受限的用户得到最少答案

If you build AI search over documents that not everyone may read, you filter by permission. In PostgreSQL with pgvector, that usually means a WHERE clause on the caller's groups and an ORDER BY on vector distance. With no vector index, that query is exact and always complete. Add an HNSW index for speed, and something quiet happens: the people allowed to read the least start getting the fewest answers. Nothing errors. Nothing leaks, because the filter still holds. The list just comes back short. Here is the mechanism, measured, and the two fixes. Everything below ran on PostgreSQL 17.10 with pgvector 0.8.4, and the script is at the end. The query Chunks of documents carry embeddings. Documents carry the groups allowed to read them. A search for one caller looks like this: SELECT c.id FROM chunk c JOIN document d ON d.id = c.document_id WHERE d.allowed_principals && ARRAY['group:hr'] ORDER BY c.embedding $1 LIMIT 20; Enter fullscreen mode Exit fullscreen mode The && operator is array overlap: a document qualifies if the caller holds any one of its groups. With no vector index, pgvector does exact nearest neighbor search, which its documentation describes as providing perfect recall. This query returns 20 rows whenever 20 readable chunks exist. What an HNSW index changes pgvector's README says it plainly: with approximate indexes, filtering is applied after the index is scanned. The scan produces a fixed list of candidates, sized by hnsw.ef_search, which is 40 by default, and the WHERE clause runs on that list. If the caller may read 10 percent of the rows, about 4 of the 40 survive. We measured it. 100,000 chunks across 10,000 documents: one document in ten readable by group:hr, the other nine by group:staff. Random 64-dimension vectors, an HNSW index with default settings, the same 40 random queries for each caller, LIMIT 20: Caller Can read Rows returned, of 20 group:staff 90 percent 20 on every query group:hr 10 percent median 4, as few as 1, never more than 8 Same query, same index, same data. The only difference is who asked. Why permissions make it worse A category filter is the same for everyone, so a short result shows up the first time anyone tests it. A permission filter depends on who is asking. Developers test with broad accounts, and a broad account gets full results. The short list only appears for the restricted user: the new hire, the contractor, the three-person team with its own folder. And a short list does not look like an error. An AI assistant handed 4 passages instead of 20 answers from the 4. If the passage that mattered was number 5, it can tell a new hire there is no policy on something that has one, and the new hire has no reason to doubt it. Fix one: iterative index scans pgvector 0.8.0 added iterative index scans. When the filter leaves too few rows, the scan keeps going through the index instead of stopping at the candidate list: BEGIN; SET LOCAL hnsw.iterative_scan = strict_order; -- the search query COMMIT; Enter fullscreen mode Exit fullscreen mode SET LOCAL ends with the transaction that runs the search, so a pooled connection cannot carry the setting anywhere else. Outside a transaction it does nothing. The other mode, relaxed_order, can return rows slightly out of distance order in exchange for better recall, and pgvector's README shows a materialized CTE that puts them back in order. On the same 40 queries, the HR caller got 20 of 20 every time, in either mode. It costs time, because the scan now looks at more of the index. The median query went from 1.7 ms to 27.8 ms with strict_order, and to 18.1 ms with relaxed_order, on one desktop. For comparison, exact search, with the index turned off, took 47.5 ms. Fix two: give narrow permissions an exact path Iterative scans have limits. A scan stops after visiting about hnsw.max_scan_tuples entries, 20,000 by default, or when it reaches a memory cap of work_mem times hnsw.scan_mem_multiplier. A caller who may read almost nothing can hit those limits before 20 matches turn up. So we added a third group, group:legal, readable on one document in a thousand: 0.1 percent of the chunks. Left alone, the planner did the right thing. It used the GIN index on allowed_principals to find the ten readable documents, joined their chunks, and sorted them by exact distance: 20 of 20 on every query, at a median of 33 ms. Then we forced the query through the HNSW index, to see what happens when the planner picks it. With iterative scans on and the default limits, the median was 16 of 20, and the worst query returned 10. Raising hnsw.max_scan_tuples to 100,000 alone changed nothing. Raising hnsw.scan_mem_multiplier to 4 alone changed nothing. Raising both got 20 of 20, at a median of 732 ms: about 22 times slower than the exact plan the planner had chosen on its own. So the second fix is not a setting. It is an ordinary index on the permission column. pgvector's README calls an index on the filter column "a good place to start" and notes that exact indexes work well for conditions that match a low percentage of rows. With one in place, the planner has a fast, complete path for your narrowest callers. Confirm it with EXPLAIN as your most restricted real user, not as yourself. The test that catches it Every number in this post came from asking as a specific group. That is the test that finds this problem, and a test run as an administrator never will. If your retrieval has a test set, give it cases that run as a restricted group and expect a specific document back. When an index change starves that group, the case fails before a user notices. The retrieval engine we build for clients works this way. Its vector search is exact, with no approximate index, until a corpus is large enough to need one and the recall cost has been measured. Its test sets run questions as a named group, and a case fails when an expected document is missing from the top five results or a forbidden one appears in them. Measure your own documents Random vectors keep the arithmetic clean: what a caller loses tracks what fraction of the rows they can read. Real embeddings cluster, and permissions often follow topic, so an HR user asking an HR question may lose less, and the same user asking about something outside their documents may lose more. The timings come from one desktop and will differ on yours. Both are reasons to measure your own documents, as your own restricted users. If you are putting AI in front of documents that not everyone may read, this is the kind of detail we work through with organizations that keep their data in-house: private AI. Run it yourself Needs PostgreSQL 17 with pgvector 0.8.0 or later; run it with psql. Each count comes from one random query, so yours will differ from the medians above. The index build may print a notice that it no longer fits in maintenance_work_mem; that only makes the build slower. -- PostgreSQL 17 with pgvector 0.8.0 or later. Run with psql. CREATE EXTENSION IF NOT EXISTS vector; -- 10,000 documents: one in ten readable by group:hr, one in a thousand also by group:legal. CREATE TABLE document (id int PRIMARY KEY, allowed_principals text[] NOT NULL); INSERT INTO document SELECT g, CASE WHEN g % 1000 = 0 THEN ARRAY['group:hr', 'group:legal'] WHEN g % 10 = 0 THEN ARRAY['group:hr'] ELSE ARRAY['group:staff'] END FROM generate_series(1, 10000) g; CREATE INDEX ON document USING GIN (allowed_principals); -- 100,000 chunks, ten per document, random 64-dimension embeddings. CREATE TABLE chunk (id int PRIMARY KEY, document_id int NOT NULL REFERENCES document(id), embedding vector(64) NOT NULL); INSERT INTO chunk SELECT g, (g % 10000) + 1, (SELECT array_agg(random() + 0 * g + 0 * i) FROM generate_series(1, 64) i)::vector(64) FROM generate_series(1, 100000) g; ANALYZE document; ANALYZE chunk; -- One random query vector. SELECT (SELECT array_agg(random()) FROM generate_series(1, 64))::vector(64)::text AS qv \gset \echo 'No vector index (exact search). Rows returned of 20, for group:hr:' SELECT count(*) FROM (SELECT c.id FROM chunk c JOIN document d ON d.id = c.document_id WHERE d.allowed_principals && ARRAY['group:hr'] ORDER BY c.embedding :'qv' LIMIT 20) t; -- Single process build, so it fits a small container's shared memory. SET max_parallel_maintenance_workers = 0; CREATE INDEX ON chunk USING hnsw (embedding vector_cosine_ops); \echo 'HNSW index, defaults. group:staff (reads 90 percent), then group:hr (reads 10 percent):' SELECT count(*) FROM (SELECT c.id FROM chunk c JOIN document d ON d.id = c.document_id WHERE d.allowed_principals && ARRAY['group:staff'] ORDER BY c.embedding :'qv' LIMIT 20) t; SELECT count(*) FROM (SELECT c.id FROM chunk c JOIN document d ON d.id = c.document_id WHERE d.allowed_principals && ARRAY['group:hr'] ORDER BY c.embedding :'qv' LIMIT 20) t; \echo 'Iterative scans on. group:hr again:' SET hnsw.iterative_scan = strict_order; SELECT count(*) FROM (SELECT c.id FROM chunk c JOIN document d ON d.id = c.document_id WHERE d.allowed_principals && ARRAY['group:hr'] ORDER BY c.embedding :'qv' LIMIT 20) t; Enter fullscreen mode Exit fullscreen mode Agave Information Solutions builds on-premises AI systems, data architecture, and custom software out of Scottsdale, Arizona. If your AI search has to respect who may read what, get in touch. Originally published at agaveis.com.