Your AI Agent Can Write SQL. That Was the Easy Part.

An agent is asked: “What is our revenue per customer?”

It writes a tidy query, joins three tables, and answers with a confident table. No error. No warning. Every figure is exactly double what it should be, because each invoice was counted once per line item.

Now a second agent, a different day. A user pastes a document containing a hidden instruction. The agent obediently writes a query that reads another company’s invoices.

These look like the same problem: “the model wrote bad SQL.” They are not. One is a failure of meaning, the other of isolation. They need different defenses, they live in different layers, and almost every design I read mixes them up.

We hit both while giving an agent direct, governed query access to Ezzo, our multi-tenant ERP. This post is the design reasoning, including the part I got wrong first, and a plain statement of what is proven and what is still a hypothesis.

Table of contents

Why let the agent write SQL at all

Our assistant answers finance questions through curated tools: an aging report, a P&L, a trial balance. Each tool encodes business semantics that raw SQL gets subtly wrong, which is exactly why they exist.

The cost is linear. Every new question is a new tool. The long tail of questions (“which customers with a credit limit above X had a late invoice in a cost center that was later closed?”) never ends, and an ERP has hundreds of tables.

So the temptation is obvious: give the model SQL and let it cover the long tail. The mistake is believing that “SQL access” is one decision.

Problem one: isolation

Our tenant isolation is Postgres row-level security. A policy compares each row’s company_id to a session variable the application sets at the start of every request. It works well for application code, because application code sets the variable correctly.

An agent writing SQL is a different kind of caller. A session that can run arbitrary statements can also run this:

SELECT set_config('app.company_id', '<someone else>', true);

The policy trusts a variable that the caller controls. For code you wrote, that is a contract. For a model reading untrusted text, it is an open door. Row-level security was never the weak part; trusting a session variable you hand to your adversary is.

The fix we built and validated is boring on purpose:

• A dedicated read-only login, separate from the application role, with its own connection pool and no ability to escalate.
• A tenant claim that cannot be forged from inside the session. The application mints a short-lived token bound to the database backend and the transaction, signed with a key the AI role cannot read. A function inside the database verifies the signature and returns the company, or NULL. On any mismatch the answer is zero rows, never an error that leaks structure.
• A gate, not a hope. A script attacks the role the way an injected agent would, and the build is not done until every attack fails.

The gate covers tenant-claim tampering, transaction binding, session privilege boundaries, and direct table access. Its detailed probes and operational follow-ups remain in the private test plan.

It passed in development and staging against representative tenant data.

That is the part I am confident about. The next part is where I took a wrong turn.

The view trap

To carry the tenant predicate, my first design put it in a view: one security_barrier view per entity, column-allowlisted, filtering on the verified company. It worked. The tests passed.

Then I looked at what it meant to cover an ERP. Hundreds of tables. A view for each, kept in sync with every migration. A dropped column fails because a view depends on it. And worse, the curated list recreates the original problem: the agent can only answer what someone wrapped.

Someone pushed back on exactly this (“you will end up with an usine à gaz”), and they were right. The view was a means to carry a predicate. I had mistaken the means for the design.

Postgres has a better primitive, and it is in the CREATE POLICY documentation:

all the PERMISSIVE policy expressions are combined using OR, all the RESTRICTIVE policy expressions are combined using AND, and the results are combined using AND.

A row is visible only if at least one permissive policy passes and every restrictive policy passes. So an extra restrictive policy is additive. The existing permissive policies stay untouched, forged variable and all, and the restrictive one still has to pass:

-- what the app already has: permissive, trusts the session variable
CREATE POLICY tenant_isolation ON sales_invoices
  USING (current_setting('app.tenant_bypass', true) = 'on'
         OR company_id = current_setting('app.company_id', true));

-- sketch (not yet validated): a restrictive policy that only bites AI sessions
CREATE POLICY ai_tenant_guard ON sales_invoices AS RESTRICTIVE FOR SELECT
  USING (NOT is_ai_session()
         OR company_id = (SELECT verified_company_id()));

Add column-level GRANT SELECT on the base tables and a new column stays invisible until someone grants it. New sensitive columns fail closed, with no view to maintain.

I want to be exact about the status. The combination rule is documented behavior. The rest, whether it holds up against my attack gate, how it targets the AI role across databases, and what it costs on large tables, is a hypothesis I have not yet tested. The next step is a spike that runs the same 25 attacks against it.

Problem two: meaning

Isolation answers: may this session see this row? It says nothing about whether the number is right. A query can be perfectly authorized and perfectly wrong.

Here is the classic case. One sale of $100, with two line items:

The query is valid, authorized, and silent. It is also wrong by a factor of two.

-- every invoice total is repeated once per line
SELECT c.name, SUM(i.grand_total)
FROM customers c
JOIN sales_invoices i       ON i.customer_id = c.id
JOIN sales_invoice_lines l  ON l.sales_invoice_id = i.id
GROUP BY c.name;

This is a fan trap. Add a second one-to-many branch off the same parent (invoices and payments, say) and you get a chasm trap: the two branches multiply each other. Nothing errors. Nothing looks off. The number is plausible, which is the dangerous part.

This is not a niche worry. In ERP schemas the failure shows up as inflated totals, ambiguous terms (“revenue” means three things), and hidden business logic. One ERP-focused write-up reports that frontier models that score above 85% on the classic Spider benchmark fall under 25% on Spider 2.0’s enterprise schemas. I would treat those figures as indicative, not gospel.

The industry’s answer is the semantic layer: declare the grain of each measure and the relationships with their cardinality, let the model choose metrics and dimensions instead of joins, and have an engine aggregate each measure at its own grain before joining. Snowflake’s semantic views do exactly that for the example above. Holistics detects the risk from declared cardinality and restructures the query. Looker asks you to state the relationship and shows the manual check: count rows before and after the join.

The evidence that it helps is real but mostly vendor-run. In dbt Labs’ April 2026 benchmark, accuracy on the modeled questions went from 90.0% to 98.2% for one model and from 84.1% to 100% for another (independent paper reusing the suite). The more interesting finding is the failure mode: with a semantic layer, errors tend to become refusals instead of confident wrong numbers.

What the market actually does

A few things stood out when I read how others handle it.

Security is enforced by identity, below the model. Wren AI applies row and column security in its engine at query time, driven by who is asking, “even when the SQL was written by an AI.” The important design choice is an identity and policy boundary the model does not control, not a view for every entity.

RLS is necessary and nowhere near sufficient. Cockroach’s guidance on multi-tenant agents is the standard pattern: a non-owner role with no BYPASSRLS, a tenant setting scoped to the transaction, fail-closed policies. It also states plainly that RLS does not stop prompt injection or a tenant poisoning their own data. Supabase gives the same caution for its MCP server and recommends read-only, project-scoped tools for unattended use. Read-only removes the write and destructive leg of an attack. It does not prevent disclosure of rows the role can read.

The semantic layer is the meaning layer, not the security layer. The products that blur those two get the worst of both.

Two failures, two owners

The design I am converging on keeps the two concerns apart, and puts each at the layer that can actually enforce it:

The database is the boundary. Everything above it is useful, none of it is trusted to be the lock.

On meaning, I am deliberately not adopting a full semantic-layer engine yet. Cube, MetricFlow, and Wren are real answers, and each adds a service to run and a model to keep in sync with a schema that keeps moving. We already have some of the relationship metadata those products ask humans to write: it is in the foreign keys and unique constraints. A child foreign key without a uniqueness constraint normally signals many-to-one. That is enough for a conservative lint only when the parsed join exactly matches a verified foreign-key or unique relationship:

• flag an aggregate over a table that sits on the one side of a join traversing a one-to-many edge;
• flag two separate one-to-many branches off the same parent;
• keep the curated tools as the first choice for the questions the business asks every day.

The lint will not catch every error. Misreading business meaning (“revenue”) is a different failure that no join analysis fixes. It is a cheap guard against the most common silent one.

What I have not proven yet

• The restrictive-policy approach is a hypothesis. The semantics are documented. How it behaves against my attack gate, how the AI role is targeted without per-database role names leaking into migrations, and its cost on large tables are open.
• The fan-out lint has an unknown false-positive rate. If it cries wolf, people will route around it.
• Prompt injection is not solved by any of this. A read-only agent limits what an injection can do; the data it can read is still reachable within the caller’s permissions.
• Most accuracy numbers here come from vendors. The only benchmark that matters is a question set over your schema, with and without each defense. That evaluation is part of the plan, not a footnote.

The lesson

“Can we let the agent write SQL?” sounds like one question. It is two, and they fail in different ways:

• Is it allowed to see this? That is the database’s job, enforced below the model, tested by attacking it, and never delegated to the prompt.
• Does this number mean what the user thinks? That is a question about grain and relationships, and a security control cannot answer it.

Both failures are silent. A leaked tenant and a doubled total look identical at the moment they happen: a confident answer, no error. So the work is not making the model smarter. It is deciding, for each failure, which layer is allowed to say no.