pgvector and row-level security: permission-aware RAG search in PostgreSQL

Most internal RAG assistants start the same way. Someone loads the policy library, the contract archive, and a few years of tickets into a table with a vector column, puts an HNSW index on it, and wires up a chat front end. The demo works. Then someone from HR asks the assistant a question, and the answer quotes a salary review that HR staff should never have seen.

The fix sounds simple: only retrieve chunks the user may read. In PostgreSQL the natural place for that rule is row-level security (RLS), because every query path gets it, not only the one the developer remembered. Combine RLS with an approximate vector index, though, and results get quietly worse, sometimes down to zero rows. Below: why it happens, the pgvector settings that fix it, the RLS details that decide whether the policy applies at all, and why the embeddings need the same protection as the documents. That last part is where CYBERTEC PostgreSQL Enterprise Edition (PGEE) helps.

The schema

Each chunk carries its tenant and the groups allowed to read its parent document. Copying the ACL onto the chunk looks redundant. It keeps the policy check cheap: one array comparison per row, with no join back to a permissions table for every candidate the index returns.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE doc_chunk (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
document_id bigint NOT NULL,
tenant_id int NOT NULL,
allowed_groups int[] NOT NULL,
body text NOT NULL,
embedding vector(1024) NOT NULL
);

CREATE INDEX doc_chunk_embedding_hnsw
ON doc_chunk USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON doc_chunk (tenant_id);
CREATE INDEX ON doc_chunk USING gin (allowed_groups);

ALTER TABLE doc_chunk ENABLE ROW LEVEL SECURITY;
ALTER TABLE doc_chunk FORCE ROW LEVEL SECURITY;

CREATE POLICY chunk_read ON doc_chunk
FOR SELECT TO rag_app
USING (
tenant_id = current_setting('app.tenant_id')::int
AND allowed_groups && string_to_array(current_setting('app.group_ids'), ',')::int[]
);

current_setting('app.tenant_id') without the missing_ok argument raises an error when the setting is missing. That is deliberate. A request that forgets to set its identity fails loudly instead of running with an empty filter.

Problem 1: the index returns candidates, the policy removes them

HNSW is an approximate index. It walks a graph and keeps a candidate list of size hnsw.ef_search, which defaults to 40. Conditions in the WHERE clause are applied after the index has produced its candidates. The pgvector documentation gives the arithmetic: if a condition matches 10% of rows, with the default ef_search of 40, only about 4 rows will match on average.

An RLS policy is just another condition from the executor's point of view. PostgreSQL adds the policy expression to the query and, as the documentation puts it, generally enforces policy filters before the query's own conditions, so that untrusted functions can't see protected rows. The only exception is leakproof functions. So when the planner picks the HNSW index for our query, the index produces up to 40 nearest chunks across the whole table, and the policy then discards every chunk from other tenants and other groups.

Take a realistic permission model. A user in one department of one tenant may be allowed to read 1% of the corpus, or less. Ask for LIMIT 10 and you often get two rows back, or none. Nothing errors. The application just gets an empty or thin context, and the model either says it found nothing or answers from whatever scraps it received.

You can see it in the plan. Run the retrieval query under EXPLAIN (ANALYZE, BUFFERS) as the application role, with the session variables set. Look for an index scan on doc_chunk_embedding_hnsw with an Order By on the distance, the policy conditions listed under Filter, a large Rows Removed by Filter, and actual rows below your LIMIT. Test as a superuser, or as the owner without FORCE, and you won't see the problem at all, because RLS isn't applied.

Fix: iterative index scans

pgvector 0.8.0 added iterative index scans. When filtering leaves too few rows, the scan goes back into the index for more candidates until it has enough results or reaches hnsw.max_scan_tuples (20,000 by default). There are two modes. strict_order keeps results exactly ordered by distance. relaxed_order lets results come back slightly out of order in exchange for better recall, and you restore the order with a materialized CTE.

A retrieval transaction then looks like this:

BEGIN;
SELECT set_config('app.tenant_id', '42', true),
set_config('app.group_ids', '7,19,233', true);

SET LOCAL hnsw.iterative_scan = relaxed_order;
SET LOCAL hnsw.ef_search = 100;
SET LOCAL hnsw.max_scan_tuples = 20000;

WITH hits AS MATERIALIZED (
SELECT id, document_id, body,
embedding <=> $1 AS distance
FROM doc_chunk
ORDER BY distance
LIMIT 10
)
SELECT * FROM hits ORDER BY distance + 0;
COMMIT;

The + 0 in the outer ORDER BY is there because the pgvector docs note it's needed on PostgreSQL 17 and later. The third argument to set_config is true, so the identity lasts only for this transaction. That matters with PgBouncer in transaction pooling mode, where the next transaction on the same server connection may belong to a different user. A session-level SET would leak one user's identity into another user's query.

Iterative scans have a ceiling. max_scan_tuples caps the work per query, and the docs say the limit is approximate and doesn't apply to the initial scan. If a user can see a tiny fraction of the corpus, even 20,000 visited tuples can come back with too few matches. Raising the cap trades latency for recall. pgvector also has hnsw.scan_mem_multiplier for cases where raising max_scan_tuples stops helping. We don't quote recall or latency figures because they depend on your corpus, dimensions, and permission distribution. Measure.

When the visible set is small, stop using the shared graph

If permissions split the corpus into many small slices, search a smaller structure in the first place. pgvector's guidance points in three directions:

Exact search over a pre-filtered set. With a B-tree on tenant_id (or a multicolumn index), the planner can fetch the visible rows first and compute distances exactly. That gives perfect recall, and it's fast when the slice is small. pgvector's docs say exact indexes work well for conditions that match a low percentage of rows. Check which plan you get with EXPLAIN, because the planner decides.
Partial HNSW indexes when you filter by a few distinct values, for example one index for a large, sensitive business unit.
List partitioning by tenant. pgvector's multitenancy section warns that a shared approximate index lets vectors from one tenant affect recall and speed for others, and recommends partitioning or separate tables. Each partition gets its own HNSW graph, and partition pruning on tenant_id keeps searches inside it.

CREATE TABLE doc_chunk (
tenant_id int NOT NULL,
id bigint GENERATED ALWAYS AS IDENTITY,
allowed_groups int[] NOT NULL,
body text NOT NULL,
embedding vector(1024) NOT NULL,
PRIMARY KEY (tenant_id, id)
) PARTITION BY LIST (tenant_id);

CREATE TABLE doc_chunk_t42 PARTITION OF doc_chunk FOR VALUES IN (42);
CREATE INDEX ON doc_chunk_t42 USING hnsw (embedding vector_cosine_ops);

Keep the RLS policy even with partitioning. Partitioning is for recall and speed. The policy is the access control. Also put the tenant condition in the query text itself, not only in the policy. A literal or parameter in the WHERE clause is what makes pruning reliable.

Problem 2: the policy you think applies, doesn't

Three RLS details decide whether any of this protects anything:

Superusers and roles with BYPASSRLS always bypass row security. If the RAG service connects as postgres, the policy is decoration.
Table owners bypass RLS unless the table has FORCE ROW LEVEL SECURITY. Migration tools often create tables as the same role the application later uses. Either separate the owner role from rag_app or force RLS, and preferably do both.
Revocation has to reach the chunks. With the ACL copied onto chunks, removing a group from a document must update allowed_groups on every chunk of that document in the same transaction. Do it with a trigger or in the same code path that changes the document ACL, and test it: revoke access, then query as the revoked user straight away.

The session variables are also a trust boundary. RLS here enforces whatever identity the application sets. It protects you from a forgotten WHERE clause, a new endpoint, or an analyst tool that connects as rag_app. It doesn't protect you from a compromised application server that sets someone else's group IDs. If that threat matters to you, have a SECURITY DEFINER function validate a signed token and set the variables, and revoke the application's ability to set them directly.

Problem 3: embeddings are not anonymous

Teams often treat the vector column as harmless derived data. The research says otherwise. Morris et al. (EMNLP 2023, "Text Embeddings Reveal (Almost) As Much As Text") showed that their Vec2Text method could recover 92% of 32-token inputs exactly from their embeddings, and recovered 89% of full names from embedded clinical notes. The authors' conclusion is that embeddings should be treated with the same precautions as the raw text.

For a PostgreSQL RAG stack, that has concrete consequences:

The text exists on disk several times: in the body column, in the embedding column, in the HNSW index pages, in WAL, on every replica, and in every backup.
A masked test copy isn't masked if you keep the original embeddings. If body is obfuscated but embedding was computed from the real text, the personal data is still recoverable in principle. Recompute embeddings from the masked text or drop the column in non-production copies.
If the source documents contain personal data, the embeddings deserve the same treatment under your PDPL data inventory and retention rules. That includes deletion. Deleting a document has to delete its chunks and vectors too, and old backups age out on the same schedule as everything else.
Where you compute embeddings matters as much as where you store them. A hosted embedding API outside the Kingdom receives the full chunk text. Running the embedding model on infrastructure in KSA keeps both the text and the vectors local.

Where PGEE fits

PGEE leaves pgvector's search behaviour alone. It reduces the at-rest exposure described above.

TDE. CYBERTEC lists PGEE's transparent data encryption as covering tables, indexes, WAL, temporary files, and backups. For a RAG workload that means the body column, the embedding column, and the HNSW index pages are encrypted on disk with the rest of the database, without application changes.
pgvector is packaged for PGEE. CYBERTEC's public PGEE package repository carries pgvector builds. At the time of writing it includes pgvector 0.8.6 for PostgreSQL 18, a version that has iterative scans. CYBERTEC has also published its own walkthrough of semantic search with pgvector. Confirm the exact package for your PGEE major version during evaluation.
Data masking for non-production copies of the source text. Per the point above, also plan what happens to the vectors in those copies. Masking the text column doesn't recompute embeddings for you.
Auditing. Object-level auditing on doc_chunk records who read which chunks, which you want when an assistant answer is challenged. See our earlier post on tamper-resistant audit logging.

RLS controls who can read rows through SQL. TDE controls what a stolen disk, snapshot, or backup reveals. A regulated RAG system needs both.

A staging test you can run this week

Load a realistic slice of your corpus with real ACL distributions, not one tenant with full access.
As rag_app, with identity set via set_config(..., true), run 50 typical questions with LIMIT 10 and record how many rows come back. Repeat with hnsw.iterative_scan off and on.
Run the same questions as an exact search over the user's visible rows and compare which chunks overlap. That gives you a recall estimate for your own data.
Revoke a group from a document, then query as a member of that group immediately.
Connect as the table owner and as a BYPASSRLS role, and confirm the application never uses either.

Talk to Worlber

Worlber is the authorised CYBERTEC reseller in Saudi Arabia. We deploy and support PGEE from Riyadh, including pgvector and RAG schema design, RLS and TDE setup, and the staging test above.

Sources

pgvector README (filtering, iterative index scans, multitenancy): https://github.com/pgvector/pgvector
PostgreSQL 18, row security policies: https://www.postgresql.org/docs/18/ddl-rowsecurity.html
PostgreSQL 18, CREATE POLICY: https://www.postgresql.org/docs/18/sql-createpolicy.html
Morris et al., Text Embeddings Reveal (Almost) As Much As Text, EMNLP 2023: https://aclanthology.org/2023.emnlp-main.765.pdf
CYBERTEC PGEE product page: https://www.cybertec-postgresql.com/en/products/cybertec-postgresql-enterprise-edition/
CYBERTEC PGEE package repository, pgvector: https://deb.cybertec-postgresql.com/public/pool/main/p/pgvector/
CYBERTEC, Semantic Search in PostgreSQL: An Overview: https://www.cybertec-postgresql.com/en/semantic-search-in-postgresql-an-overview/