Multi-Tenant Conversation Isolation with Postgres RLS and pgvector

The worst kind of data leak is the one that looks like a correct answer. That is what you risk the moment you add AI search to a multi-tenant app (anything serving more than one customer). A search by meaning returns whatever is most relevant and pays no attention to who owns it, so a missing filter comes back not as an error but as a clean, on-topic reply that happens to draw on another customer’s private conversation. In a plain database you would catch that fast, because the wrong rows look obviously wrong; here they read as perfectly reasonable, so nothing flags them.

The usual defense is a WHERE tenant_id = ... on every query, but that only holds while every developer remembers it on every path, forever. Postgres row-level security (RLS) moves the rule out of your code and into the database itself, where a forgotten filter cannot undo it. That is what this post builds. And if writing security policies is not how you want to spend your time, the last section shows how DialogueDB enforces the same isolation on every call, search included, with nothing for you to wire up.

Why is a missed filter worse in vector search?

A missed filter is worse in vector search because of what the mistake gives back. A normal SELECT * FROM messages without its WHERE tenant_id clause returns every row in the table, so someone notices immediately. A similarity search without the same clause returns a small, tidy set of the closest embeddings across every tenant, ranked by relevance, and nothing about that response shape looks like a bug.

This is specific to how retrieval-augmented queries work. A join or a report can be sanity-checked by a human skimming the output. A ranked list of “most similar messages to this query” cannot, because the whole point of the query is that the results are supposed to look relevant. Application-level filtering puts the entire isolation guarantee on every developer remembering to add the same WHERE clause to every retrieval path, including ones added six months later by someone who never saw the original schema decision.

Enforce tenant isolation with Postgres RLS

RLS moves that guarantee into Postgres. Instead of trusting every caller to filter correctly, you enable RLS on the table and attach a policy that Postgres applies to every command, regardless of what the query looks like.

Start with three tables, each carrying the tenant_id you’ll filter on:

CREATE TABLE conversations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  title TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  conversation_id UUID NOT NULL REFERENCES conversations(id),
  role TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE message_embeddings (
  message_id UUID PRIMARY KEY REFERENCES messages(id),
  tenant_id UUID NOT NULL,
  embedding VECTOR(1536) NOT NULL
);

Turn on row-level security for each table, then force it so the policy applies even when your app connects as the table’s owner:

ALTER TABLE conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE message_embeddings ENABLE ROW LEVEL SECURITY;

ALTER TABLE conversations FORCE ROW LEVEL SECURITY;
ALTER TABLE messages FORCE ROW LEVEL SECURITY;
ALTER TABLE message_embeddings FORCE ROW LEVEL SECURITY;

The FORCE ROW LEVEL SECURITY line matters and gets skipped often. By default, Postgres exempts the table owner from RLS policies, on the theory that the owner is a trusted administrative role. If your application connects as the table owner, which is common in smaller setups, RLS silently does nothing unless you force it.

Finally, attach a policy that scopes every row to the current tenant:

CREATE POLICY tenant_isolation ON conversations
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation ON messages
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation ON message_embeddings
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Every policy reads from current_setting('app.tenant_id'), a session variable your application sets, not a value baked into the query text. That’s the part that turns this into a database-enforced guarantee instead of a convention.

How to set tenant context safely with connection pooling

The policy is only as good as how you set that session variable, and this is where a real, well-documented failure mode shows up: connection pooling.

Set the tenant per transaction, not per session:

BEGIN;
SET LOCAL app.tenant_id = '3f9a2b10-8e21-4c77-9b4a-1122aabbccdd';

SELECT id, content, created_at
FROM messages
ORDER BY created_at DESC
LIMIT 20;

COMMIT;

SET LOCAL scopes the setting to the current transaction. It’s automatically cleared on commit or rollback, which is exactly what you want when a connection pooler like PgBouncer is handing the same physical Postgres connection to different tenants’ requests in sequence.

Using plain SET instead of SET LOCAL is the mistake to watch for. SET persists for the life of the session, and in transaction-mode pooling, “the session” is not “one request.” It’s whatever sequence of transactions happens to land on that pooled connection. Set the tenant with plain SET, and if your code path returns the connection to the pool without explicitly resetting it, the next tenant’s transaction can inherit the previous tenant’s app.tenant_id. That’s not a theoretical bug. It’s the exact class of cross-tenant leak RLS was supposed to prevent, reintroduced by the session-variable mechanism meant to enforce it. Wrap every request in an explicit transaction, set the tenant with SET LOCAL inside it, and run the query in the same transaction.

RLS covers pgvector similarity search, not just plain SELECTs

This is the part generic RLS write-ups skip: the policy still applies when the query is a nearest-neighbor search instead of a plain SELECT, and that’s the point. RLS is enforced at the row level for every command against the table, including ORDER BY ... LIMIT queries driven by a pgvector index.

BEGIN;
SET LOCAL app.tenant_id = '3f9a2b10-8e21-4c77-9b4a-1122aabbccdd';

SELECT m.id, m.content, e.embedding <=> $1 AS distance
FROM message_embeddings e
JOIN messages m ON m.id = e.message_id
ORDER BY e.embedding <=> $1
LIMIT 10;

COMMIT;

Notice there’s no tenant_id filter written anywhere in that query. It doesn’t need one. The policy on message_embeddings applies before the rows reach the ORDER BY, so even a query that a developer wrote carelessly, without a tenant clause, cannot return another tenant’s embeddings. That’s the actual fix for the silent leak this post opened with: not “remember to filter every retrieval path,” but “make it structurally impossible to forget.”

There’s a performance tradeoff worth knowing about. An ivfflat or hnsw index doesn’t know about your RLS policy. It ranks candidates by vector distance first, and Postgres filters out disallowed rows afterward. If a tenant owns a small fraction of the table’s rows, the index can end up scanning past a lot of other tenants’ candidates before it finds enough matching rows to satisfy the LIMIT. In practice this usually means partitioning or indexing with tenant-aware strategies (a composite index leading with tenant_id, or a partitioned table by tenant for large accounts) once a tenant’s row count or the total table size makes plain ANN scans slow. Test this against your actual tenant size distribution rather than assuming RLS is free.

When RLS isn’t the right isolation layer

RLS is a policy enforced inside a single database, by a single Postgres instance, for tenants who are fine sharing physical storage, indexes, and a failure domain. That’s a reasonable default for most SaaS multi-tenancy. It stops being enough when isolation requirements are physical instead of logical:

• A contract or compliance requirement says a customer’s data must not share disk, indexes, or a crash domain with other customers.
• A single large tenant’s query volume or table size is degrading performance for everyone else on the same indexes (the noisy-neighbor problem RLS does nothing to solve, since it filters rows, not resource contention).
• You need per-tenant backup, restore, or deletion granularity that’s operationally painful to get right inside one shared schema.

In those cases, schema-per-tenant or database-per-tenant costs more in migrations and operational overhead, but it buys a stronger boundary than any policy inside a shared table can. Most teams don’t need that until a specific customer or compliance requirement forces the question. Don’t build it preemptively.

The same isolation without writing RLS policies yourself

RLS solves this well, but it leaves you owning the machinery: writing the policies, getting FORCE ROW LEVEL SECURITY right, wiring SET LOCAL correctly through your connection pooler, and re-checking all of it every time you add a table. With DialogueDB, none of that is yours to own. The platform is a conversation database, so dialogues, messages, memories, semantic search, and threads all live behind one client, and each is scoped to a namespace you pass on the call. Isolation isn’t a setting you switch on per table. It’s how every read, write, and search already behaves, including the similarity search that started this whole problem.

import { DialogueDB } from "dialogue-db";

const db = new DialogueDB({ apiKey: process.env.DIALOGUE_DB_API_KEY });

const results = await db.searchDialogues("renewal risk", {
  namespace: tenantId,
  limit: 10,
});

There’s no policy to forget to enable, no session variable to leak across a pooled connection, and no separate check to confirm the similarity search path is actually covered. The isolation is enforced the same way for every query type, because there’s only one code path to enforce it in.

If you’re weighing pgvector against a managed option for conversation memory more broadly, Where Should Agent Memory Live? covers the tradeoffs beyond isolation, including indexing, recall quality, and operational cost.