Moving tenant isolation from agent prompts into PostgreSQL RLS

Summary

I built tenant isolation for a multi-tenant analytics system where an AI SQL agent answered natural-language questions over live business data. Rows from multiple tenants lived in the same PostgreSQL tables. I designed and implemented the security boundary in PostgreSQL instead of trusting the prompt.

The argument I kept having

The obvious shortcut was to pass the current tenant_id into the SQL agent's system prompt and instruct it to always include the right filter. Something like:

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

For ordinary requests, this works fine most of the time. That standard isn't good enough for an authorization boundary. Tenant isolation would depend on the generated SQL being correct every single time: after model upgrades, when the agent encounters new tables, and through every join, subquery, or future execution path. At that point, the filter is only a convention.

There are plenty of ordinary ways for this to break. The agent can miss the predicate on a new table or join through a relationship that skips the tenant column. A prompt injection or override can interfere with the instruction. A model version can interpret it differently, or a future code path can build the system message another way and accidentally drop the tenant context. Malice isn't required. The model only has to behave like the probabilistic system it is.

The query had to remain tenant-safe even when the model generated SQL nobody expected. "The model usually writes tenant-safe SQL" was not a security property I was willing to ship.

Why the first secure design got scrapped

My initial approach enforced isolation in the database. Shared tables were exposed through tenant-filtered views, each assigned to a restricted PostgreSQL role with grants only for the views it should see. The SQL agent would connect with credentials that couldn't reach another tenant's rows. If it hallucinated a query, the query would fail because the credentials didn't have the required privilege.

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

The design was correct in principle, but it created too much operational work. With dozens of source tables and many expected tenants, the number of database objects and grants grows in proportion to tenants × exposed tables. Each schema migration has to update a multiple of views. Adding a tenant requires a batch of object creation and connection configuration. Off-boarding one requires cleanup in the view and role layers, along with the connection pool. Debugging a query plan through stacked tenant-specific views is unpleasant. I also wanted to avoid the particular headache of connection pooling across per-tenant roles.

We also considered stronger isolation through separate databases for each tenant. That gives you a clean security boundary, while multiplying the work for backups and migrations. Connection management and monitoring grow too, as does the infrastructure cost. It is the same scaling problem at a different layer.

I wanted deterministic enforcement without letting per-tenant database operations become the dominant operational concern. That pushed us toward another mechanism.

The production design

I implemented PostgreSQL Row-Level Security over the shared tables. The database engine applies the RLS policy regardless of the SQL the model generates. The model doesn't have to remember WHERE tenant_id = 'A'. PostgreSQL applies the filter at the row level before the query result reaches application code.

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

I wired tenant context from the authenticated identity, upstream of anything the agent could influence. The server resolves the caller and their membership, then sets the database execution context before the agent's SQL runs. Since the model doesn't establish that context, it can't switch the tenant it queries as. Letting the model supply the tenant ID would recreate the prompt-filtering problem one layer down and waste everyone's time.

The rest is ordinary database engineering, with several details that have to be right. Privileged roles for migrations and admin work can bypass ordinary RLS. You need to choose deliberately which role connects for each purpose and whether to force policies. Read and write paths also need separate treatment because a policy controlling visible rows says nothing about which rows a user may insert.

Session-level tenant context interacts badly with connection pooling. The context must be reset between checkouts or scoped to the transaction. Otherwise, one tenant's scope can leak into the next request. Tables without a tenant column need policies expressed through relationships, a denormalized key, or security-barrier views. This work has a cost, but the result is deterministic. You can test and review it, which is why the approach made sense.

Reasoning and authority are different things

This project changed how I think about agent architecture beyond SQL.

A model can decide which table is relevant and whether it needs another query. It can choose a tool and summarize the result. These are reasoning problems that benefit from probabilistic systems. The tenant data a request can access is an authority question. I think that answer should be deterministic.

I now think of these as stacked in a specific order:

deterministic authority
    ↓
probabilistic reasoning

Authority based on a model prompt would fail a security review. The same issue applies to retrieval, file access, external integrations, delegated agents, memory, customer connectors, and internal tools. An agent can decide which capability is useful. The system decides what it is allowed to access.

Delegation and authority

The agent platform later added delegated specialists and cross-domain search. That raised the same question in another form: when an agent delegates work to another component, does its authority expand automatically? The delegated component may offer specialized context or cheaper execution. It may also have domain-specific tools. The caller's ability to delegate doesn't imply unlimited data sharing with the receiving component. The principle behind the RLS design still applies. Combining capabilities should never silently escalate privileges.

What shipped

I designed and implemented the RLS layer end to end, including 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.

I use prompts to guide model behavior, not to grant authority. In this system, the agent's credentials and the data plane prevent it from reading rows outside the tenant boundary, regardless of the SQL it writes.