Row-Level Security and Multi-Tenant Isolation

Multi-tenant retrieval must enforce isolation before similarity ranking. Application predicates are useful but easy to omit; PostgreSQL row-level security (RLS) adds database-enforced policies tied to trusted session or transaction identity. Use a non-owner runtime role, force RLS where appropriate, set identity transaction-locally, and test every read/write, cache, reranker, citation, export, and maintenance path.

What will you be able to do?

Enable and force RLS on tenant tables.
Pass trusted tenant context safely.
Understand owner and bypass behavior.
Test cross-tenant denial comprehensively.
Compare shared-table and stronger isolation models.

What does a policy look like?

sql

ALTER TABLE app.chunks ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.chunks FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_chunks ON app.chunks
USING (
  tenant_id = current_setting('app.tenant_id', true)::uuid
)
WITH CHECK (
  tenant_id = current_setting('app.tenant_id', true)::uuid
);

The application opens a transaction and sets app.tenant_id from authenticated server context:

sql

SET LOCAL app.tenant_id = '00000000-0000-0000-0000-000000000001';

Use a runtime role that neither owns the table nor has BYPASSRLS. Missing settings should fail closed; handle casts deliberately. Harden function search paths and privileges.

Which isolation model is enough?

shared tables plus RLS: efficient but requires excellent policy and operations;
schema per tenant: more isolation, increasing migration/catalog complexity;
database/cluster per tenant: stronger boundaries, higher cost and fleet operations;
hybrid tiers: isolate high-sensitivity or large tenants.

Choose from risk, regulation, noisy-neighbor behavior, scale, and operational maturity.

How do you verify it?

Run tests as the exact runtime role, not an owner. Attempt SELECT, vector search, lexical search, insert, update, delete, foreign-key references, prepared statements, COPY, functions, views, caches, and citation resolution across tenants. Inspect policies with \d+ and catalog queries.

What breaks in production?

Tests run as a superuser and bypass RLS.
A pooled connection retains session tenant state.
A security-definer function has unsafe privileges/search path.
Background jobs use an all-tenant role without scoped work items.
RLS protects SQL but a shared cache leaks results.

AI pair-work prompt

Audit this PostgreSQL multi-tenant design [DDL, roles, policies, app transaction]. Find owner/BYPASSRLS paths, missing WITH CHECK, null setting behavior, pool leakage, function/view risks, background-job scope, cache boundaries, and denial tests.

Verification contract: Execute the adversarial suite using production-equivalent roles; zero unauthorized rows is a release gate.

Check your understanding

Why must runtime roles not own protected tables?
What does WITH CHECK control?
Which non-database components must preserve tenant isolation?

Official references

Row-Level Security and Multi-Tenant Isolation

What does a policy look like?

sql

ALTER TABLE app.chunks ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.chunks FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_chunks ON app.chunks
USING (
  tenant_id = current_setting('app.tenant_id', true)::uuid
)
WITH CHECK (
  tenant_id = current_setting('app.tenant_id', true)::uuid
);

The application opens a transaction and sets app.tenant_id from authenticated server context:

sql

SET LOCAL app.tenant_id = '00000000-0000-0000-0000-000000000001';

Use a runtime role that neither owns the table nor has BYPASSRLS. Missing settings should fail closed; handle casts deliberately. Harden function search paths and privileges.

Which isolation model is enough?

shared tables plus RLS: efficient but requires excellent policy and operations;

schema per tenant: more isolation, increasing migration/catalog complexity;

database/cluster per tenant: stronger boundaries, higher cost and fleet operations;

hybrid tiers: isolate high-sensitivity or large tenants.

Choose from risk, regulation, noisy-neighbor behavior, scale, and operational maturity.

AI pair-work prompt

Audit this PostgreSQL multi-tenant design [DDL, roles, policies, app transaction]. Find owner/BYPASSRLS paths, missing WITH CHECK, null setting behavior, pool leakage, function/view risks, background-job scope, cache boundaries, and denial tests.

Verification contract: Execute the adversarial suite using production-equivalent roles; zero unauthorized rows is a release gate.