Moving tenant isolation from agent prompts into PostgreSQL RLS

Summary

I built the tenant-isolation layer for an AI SQL agent querying live business data. Every tenant's rows lived in the same PostgreSQL tables, so one missing filter could expose another customer's data.

The easiest solution was to put the tenant_id in the system prompt and tell the model to include it in every query. That worked in normal demos. I still wasn't going to use it as an authorization boundary. The model did not need to be malicious. It only had to miss one predicate.

I first tried tenant-filtered views with restricted PostgreSQL roles. The security model worked, but the operational cost grew with every tenant and table. I eventually moved the boundary into PostgreSQL RLS and wired the tenant context from the authenticated request. The model could still decide what SQL to write. PostgreSQL decided which rows it was allowed to see.

The shortcut I didn't trust

The prompt-based version looked like this:

system:
"You are querying for tenant A. Always filter every query to tenant_id = 'A'."

It worked most of the time, which is fine for plenty of model behavior. It is not fine for tenant isolation. The SQL had to be correct after model upgrades, with new tables, and through every join and subquery we added later.

There were too many ordinary ways to get it wrong. The agent could miss the predicate on a new table. A join could bypass the tenant column. A future code path could build the prompt differently and drop the context. Prompt injection was another possibility, but malice was not required. One bad query was enough.

I wanted the query to stay tenant-safe even when the model generated SQL nobody expected.

The secure design that became an operational mess

My first database-enforced design used tenant-filtered views. Each tenant got a restricted PostgreSQL role with access only to its views.

shared source tables
    ↓
tenant-filtered views
    ↓
restricted PostgreSQL role
    ↓
AI SQL agent

The design was secure. It was also a pain to operate.

The number of objects and grants grew with tenants × exposed tables. Every schema migration had to update another set of views. Adding or removing a tenant meant changing roles, views, connection configuration, and the pool. Query plans also became harder to debug through tenant-specific views.

We considered a separate database for every tenant too. That gave us a cleaner boundary, but backups, migrations, monitoring, connections, and infrastructure cost would all scale with the tenant count. It moved the same operational problem somewhere else.

I wanted database-enforced isolation without turning tenant provisioning into a database administration project.

Moving the boundary into PostgreSQL RLS

I implemented PostgreSQL Row-Level Security on the shared tables. The database applies the policy regardless of the SQL the model generates. The query does not need to contain WHERE tenant_id = 'A' because PostgreSQL restricts the visible rows before the result reaches the application.

authenticated request
    ↓
validated tenant context
    ↓
database execution context (session variable or equivalent)
    ↓
PostgreSQL RLS policy
    ↓
model-generated SQL
    ↓
only rows visible to that tenant

The tenant context comes from the authenticated identity, before the agent gets involved. The server resolves the caller and their membership, then sets the database execution context before running the generated SQL.

The model cannot choose or replace that context. Letting it provide the tenant ID would have recreated the same prompt-filtering problem one layer lower.

The parts that were easy to get wrong

RLS solved the main boundary, but a few details still had to be handled carefully.

Migration and admin roles may bypass normal RLS policies, so each database role needs a deliberate purpose. Read and write paths need separate policies too.

Connection pooling was the dangerous part. Session-level tenant context can survive when a connection returns to the pool. If it is not reset or scoped to the transaction, one tenant's context can leak into the next request using that connection.

Some tables did not have a tenant column of their own. Those policies had to follow relationships, use a denormalized tenant key, or go through a security-barrier view. This was more work than adding a filter to a prompt, but it was work we could test and review.

What shipped

I designed and implemented the RLS layer end to end. That included authenticated tenant context, database execution context, policies, role separation, and connection-pool safety.

PostgreSQL now enforces tenant isolation for the SQL layer. The model can generate any query it wants, but it only sees rows allowed by the active RLS policy.

What I took away

The model is good at deciding which table to query, which tool to call, and how to explain the result. Those are reasoning problems. Deciding which customer's data it may access is an authorization problem, and I do not want a model making that decision.

The same rule held when the platform later added delegated agents and cross-domain search. Delegating work did not mean the receiving agent should automatically receive more data or permissions.

An agent can decide which capability is useful. The system decides what it is allowed to access.