PostgreSQL Row-Level Security for Multi-Tenant RAG

1. Define the retrieval boundary

A RAG query forgets its tenant filter. Your database returns another customer's document, and that document enters the model context. Asking the model to ignore unauthorized text leaves the retrieval failure in place. This guide prepares a database experiment where two synthetic tenants have separate reading roles, and an unfiltered query must still return only the active role's documents.

The fixture tests one narrow contract: direct SELECT access to a document table under two non-owning roles. It uses four invented rows, two per tenant, with recognizable phrases so you can identify a leaked row. It does not call an embedding service or a model. Start with Retrieval and RAG for Developers for the surrounding pipeline, then use this experiment to check the database boundary independently of answer quality.

PostgreSQL describes row security as a restriction on which rows normal queries can access, alongside ordinary privileges. Its row security documentation also identifies privileged bypasses. That is why the experiment separates a setup administrator, a table owner, and tenant readers instead of judging isolation from an administrator's successful query.

2. Prepare an isolated database and explicit roles

Use a disposable PostgreSQL database and a setup connection with superuser privileges. The administrative connection is needed for the deliberate BYPASSRLS control, not for retrieval. Choose a database with none of the four fixture role names already present. The script creates roles and a dedicated schema inside a transaction and rolls back at the end; an error stops the script. Do not paste this setup into a production session or replace existing roles to make it fit.

The two tenant roles are NOLOGIN, NOSUPERUSER and NOBYPASSRLS. They receive schema USAGE and table SELECT, while the table belongs to a third role. Role switching from the setup connection makes the experiment repeatable without introducing passwords. This is a laboratory arrangement; your application's authentication and connection routing need their own tests.

Each policy applies only to SELECT and one named tenant role. Its USING expression compares the stored tenant value with a fixed fixture value. The CREATE POLICY reference explains that only rows satisfying USING are visible, and that policies require enabled row security. Ordinary grants remain necessary. Do not mistake an empty result caused by missing table access for a passing isolation test.

3. Save and run the complete fixture

Save the following as rls-fixture.sql. Keep the assertions with the queries: a script that prints rows without checking them is easy to misread. The version query records the actual server, while the role and table queries record the relevant configuration before any tenant probe.

psql -X --dbname=postgres --set=ON_ERROR_STOP=1 --file=rls-fixture.sql

Replace the database name with your disposable database and supply your normal local connection settings. The script must complete with the final PASS message and successful process exit. Save the full transcript, including the exact PostgreSQL version, active roles, privilege flags and row IDs. If connection setup or role creation fails, resolve that prerequisite before interpreting any expected results below.

\set ON_ERROR_STOP on
\pset pager off
SELECT version();
BEGIN;
CREATE ROLE rag_rls_owner NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE rag_rls_a NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE rag_rls_b NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE rag_rls_bypass NOLOGIN NOSUPERUSER BYPASSRLS;
CREATE SCHEMA rag_rls_fixture AUTHORIZATION rag_rls_owner;
SET ROLE rag_rls_owner;
CREATE TABLE rag_rls_fixture.documents (
  id integer PRIMARY KEY,
  tenant text NOT NULL,
  body text NOT NULL
);
INSERT INTO rag_rls_fixture.documents VALUES
  (1, 'a', 'Synthetic A: copper lantern'),
  (2, 'a', 'Synthetic A: maple bridge'),
  (3, 'b', 'Synthetic B: silver compass'),
  (4, 'b', 'Synthetic B: cedar tower');
GRANT USAGE ON SCHEMA rag_rls_fixture TO rag_rls_a, rag_rls_b, rag_rls_bypass;
GRANT SELECT ON rag_rls_fixture.documents TO rag_rls_a, rag_rls_b, rag_rls_bypass;
ALTER TABLE rag_rls_fixture.documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_a_read ON rag_rls_fixture.documents
  FOR SELECT TO rag_rls_a USING (tenant = 'a');
CREATE POLICY tenant_b_read ON rag_rls_fixture.documents
  FOR SELECT TO rag_rls_b USING (tenant = 'b');
RESET ROLE;
SELECT rolname, rolsuper, rolbypassrls FROM pg_roles
WHERE rolname IN ('rag_rls_owner', 'rag_rls_a', 'rag_rls_b', 'rag_rls_bypass')
ORDER BY rolname;
SELECT c.relowner::regrole AS owner, c.relrowsecurity, c.relforcerowsecurity
FROM pg_class c WHERE c.oid = 'rag_rls_fixture.documents'::regclass;
SELECT has_table_privilege('rag_rls_a', 'rag_rls_fixture.documents', 'SELECT') AS a_select,
       has_table_privilege('rag_rls_b', 'rag_rls_fixture.documents', 'SELECT') AS b_select;
SET ROLE rag_rls_a;
SELECT session_user, current_user;
SELECT id, tenant, body FROM rag_rls_fixture.documents ORDER BY id;
SELECT count(*) AS wrong_tenant_rows FROM rag_rls_fixture.documents WHERE tenant = 'b';
DO $$ BEGIN
  IF current_user <> 'rag_rls_a'
     OR (SELECT array_agg(id ORDER BY id) FROM rag_rls_fixture.documents) IS DISTINCT FROM ARRAY[1,2]
     OR (SELECT count(*) FROM rag_rls_fixture.documents WHERE tenant = 'b') <> 0
  THEN RAISE EXCEPTION 'tenant A isolation failed'; END IF;
END $$;
RESET ROLE;
SET ROLE rag_rls_b;
SELECT session_user, current_user;
SELECT id, tenant, body FROM rag_rls_fixture.documents ORDER BY id;
SELECT count(*) AS wrong_tenant_rows FROM rag_rls_fixture.documents WHERE tenant = 'a';
DO $$ BEGIN
  IF current_user <> 'rag_rls_b'
     OR (SELECT array_agg(id ORDER BY id) FROM rag_rls_fixture.documents) IS DISTINCT FROM ARRAY[3,4]
     OR (SELECT count(*) FROM rag_rls_fixture.documents WHERE tenant = 'a') <> 0
  THEN RAISE EXCEPTION 'tenant B isolation failed'; END IF;
END $$;
RESET ROLE;
SET ROLE rag_rls_owner;
SELECT current_user, count(*) AS owner_rows FROM rag_rls_fixture.documents;
DO $$ BEGIN
  IF (SELECT count(*) FROM rag_rls_fixture.documents) <> 4
  THEN RAISE EXCEPTION 'owner control failed'; END IF;
END $$;
ALTER TABLE rag_rls_fixture.documents FORCE ROW LEVEL SECURITY;
SELECT current_user, count(*) AS forced_owner_rows FROM rag_rls_fixture.documents;
DO $$ BEGIN
  IF (SELECT count(*) FROM rag_rls_fixture.documents) <> 0
  THEN RAISE EXCEPTION 'forced owner default-deny failed'; END IF;
END $$;
RESET ROLE;
SET ROLE rag_rls_bypass;
SELECT current_user, count(*) AS bypass_rows FROM rag_rls_fixture.documents;
DO $$ BEGIN
  IF (SELECT count(*) FROM rag_rls_fixture.documents) <> 4
  THEN RAISE EXCEPTION 'BYPASSRLS control failed'; END IF;
END $$;
RESET ROLE;
ROLLBACK;
\echo PASS: tenant A, tenant B, owner, forced owner, BYPASSRLS

4. Compare permitted, forbidden and unfiltered reads

For tenant A, the query without a WHERE clause should return IDs 1 and 2. The query explicitly requesting tenant B should return a count of zero. Tenant B should see IDs 3 and 4, with zero rows when explicitly requesting tenant A. These are the fixture's expected results, and they matched the recorded run below. The assertions reject other ID sets, including an empty permitted result.

Inspect current_user at each probe rather than trusting the connection label. The table-owner field must identify the separate owner, and both tenant roles must have SELECT while their superuser and bypass flags are false. Together, those checks distinguish row isolation from a permission error and from a test accidentally running with administrator authority.

5. Use privileged controls to expose misleading tests

The owner probe intentionally expects all four rows before FORCE ROW LEVEL SECURITY. After FORCE, it expects zero because no policy in this fixture applies to the owner. The bypass role still expects four. PostgreSQL documents that owners normally bypass row security, that FORCE can subject the owner to it, and that superusers and BYPASSRLS roles bypass it. FORCE is therefore not a substitute for removing privileged retrieval credentials.

Keep these control queries separate from tenant acceptance criteria. A four-row owner result demonstrates a privileged path, not a tenant-policy failure. Conversely, running only the owner probe cannot establish that the actual application reader is isolated. Carry the role, grants, ownership and policy inspection into your deployment review, and repeat the direct-read probes under the credentials your retrieval service uses.

6. Carry the assertions into RAG retrieval

In this design the application must bind an authenticated tenant to the correct database role through trusted server code. A tenant request must not choose its own role, and the retrieval connection must not retain the setup administrator's ability to switch into other tenant roles. Treat that routing requirement as a separate acceptance test: the SQL fixture deliberately uses an administrator to exercise several identities.

Run the same permitted and forbidden document assertions against your real retrieval query, then inspect the chunks placed into model context. This fixture contains no vectors and establishes no pgvector performance or recall result. Keep a positive control alongside each denial so a broken index or failed ingestion cannot look like successful authorization.

Document the remaining boundaries. Cached responses and previously assembled conversation context are outside this table-read experiment. Use Caching AI Responses to examine cache identity, and Adding Teams and Permissions for application membership checks. Test those paths separately, especially when a user changes teams or a pooled connection is reused for another tenant.

Execution record

On 12 October 2026 the user ran the complete fixture on PostgreSQL 18.6 on x86_64-windows, compiled by msvc-19.44.35229, 64-bit. The saved output was reviewed: the session user was postgres, while current_user was the intended tenant role at each probe. Both readers had SELECT and neither had superuser or BYPASSRLS privileges. The table belonged to rag_rls_owner with RLS enabled.

The observed results matched every row in the table above: tenant A returned IDs 1 and 2, tenant B returned IDs 3 and 4, and both wrong-tenant counts were zero. The owner control returned four rows, the forced owner zero, and the bypass role four. All five DO assertion blocks completed, followed by ROLLBACK and PASS: tenant A, tenant B, owner, forced owner, BYPASSRLS. The saved output contains no error; a separate process exit code was not recorded. This is evidence from the synthetic database fixture, not a test of application authentication, a vector index or model output.