I gave an LLM the keys to a multi-tenant database

Five things that had to be true before I would point it at data belonging to
more than one customer.

---

An agent that writes good SQL is a solved problem, near enough. The
first half of this project was about that: pulling
descriptions, join keys, real filter values and signed-off metric definitions out
of dbt and handing them to the model, so the query it writes is the query a
competent analyst would have written.

None of it makes the query safe. A perfectly grounded query against exactly the
right table will return every tenant's rows if nothing stops it, and it will do
so without an error, because from the warehouse's point of view nothing went
wrong.

So this half is the opposite discipline. The model gets no say in any of it.

The model writes the query. The server enforces the rules. Different jobs,
and only one of them is safe to delegate. Whatever SQL comes back goes through a
pipeline the model cannot influence:

validate → guard → qualify → limit → offset → mask → govern → assert → estimate → execute

Pure functions over (sql, catalog, tenant_scopes). No network, no model, no I/O
of any kind, which is why the security-critical core can be tested exhaustively.
About a thousand lines, small enough to read on a train.

1. Prove the filter, don't trust the injector

When a query reads a governed table, one with a tenant_id column, the
server parses the SQL into a syntax tree with
sqlglot, walks every SELECT scope,
and AND-injects a predicate.

The model writes this:

SELECT region, sum(amount) FROM customer_orders GROUP BY region

The warehouse executes this:

SELECT region, SUM(amount)
FROM "analytics"."customer_orders"
WHERE customer_orders.tenant_id = ANY(%(qg_tenant_scopes)s)
GROUP BY region
LIMIT 100

Tenant IDs are bound parameters; they never appear in the SQL text. The walk
covers joins, subqueries and CTEs, so a governed table three levels deep inside
a WITH still gets filtered, while a public table in the same query is left
alone.

Then comes the part I'd argue is the actual design decision. Having injected the
filter, the server re-parses the finished SQL and independently proves the
filter is there:

def assert_tenant_filter_present(sql, catalog):
    tree = _parse(sql)                          # fresh parse, no shared state
    for select in tree.find_all(exp.Select):
        for handle, model in _governed_refs(select, catalog, ctes):
            if not _scope_has_tenant_filter(select, handle, model.tenant_column):
                raise GovernanceError(...)      # before execution, always

Why bother, when the injector ran forty microseconds ago?

Because the injector could have a bug. Because a refactor in eight months could
introduce one. Because I am the sort of person who writes an injector and then
lies awake wondering whether it handles LATERAL. The assert shares no state
with the injector: it re-derives the proof from the text itself and demands the
exact predicate shape: right column, right parameter, top-level AND
conjunct. A filter that's missing, OR-ed with a tautology, or bound to the
wrong parameter gets rejected.

And it's fail-closed. A governed table with an empty tenant scope doesn't return
zero rows; it refuses to run. Absence of permission is not permission.

Does it hold? Here are the numbers

Three bearer tokens, three tenant scopes, one question:

tok_acme    →  $55,952.59
tok_globex  →  $62,841.34
tok_multi   →  $118,793.93     ← exactly the sum

No bleed, no double-counting. There is no prompt you can hand tok_acme that
returns Globex's revenue, because the filter is added after the model is done
and re-checked before execution. The model is never in the loop for that call.

For fun, I spent an afternoon being the attacker. Asking as tok_acme for
WHERE tenant_id = 'globex' returns NULL, because the injected predicate ANDs with
yours and the intersection is empty. Hiding the table in a CTE returns acme's
own total. OR 1=1 returns acme's own total. Selecting the masked customer name
returns a column of MD5 hashes. Asking WHERE customer_name = 'Alice Smith' is
refused outright, and I'll explain why in a moment. SELECT 1; DROP TABLE
customer_orders gets told, politely, that two statements is one too many.

This runs as an integration test against real Postgres in CI, not a mock.

---

2. The cache is part of the security perimeter

This one nearly got me, and I think it's the most transferable lesson here.

Caching query results looks like a pure optimisation. In a multi-tenant system
it is not. Two callers can send byte-identical SQL and legitimately be owed
different rows, and the only thing separating them is the tenant scope the server
binds. A cache keyed on the query alone hands one tenant another's data
without ever running their query, elegantly bypassing every guarantee above.

The fix isn't a code-review checklist item, because code-review checklist items
are things humans do on Friday afternoons. It's a type:

@dataclass(frozen=True)
class CacheKey:
    namespace: str
    tenant_scopes: frozenset[str]   # required, no default
    parts: tuple[str, ...]

You cannot construct a key without answering "on whose behalf?". Scopes are
sorted, so a multi-tenant caller maps to one key, and hashed with a separator,
so a crafted column name can't collide with somebody else's scope.

The end-to-end test makes the subtlety visible. Both tenants happen to have the
same region values, ['APAC', 'EU', 'NA']. The second tenant still gets a
miss:

acme  1st: ['APAC','EU','NA']  cached=False
acme  2nd: ['APAC','EU','NA']  cached=True    ← served from cache
globex 1st:['APAC','EU','NA']  cached=False   ← miss, not acme's entry

The cache never assumes what another tenant is entitled to see, even when the
answer would coincidentally be identical.

I also cache deliberately little: get_filter_values, which is repetitive and
stable, but not run_query. Analytical SQL is phrased differently on every
attempt, so the hit rate is poor while the blast radius of a wrong key is a
cross-tenant leak. Bad trade.

Worth knowing before you build any of this: Postgres does not cache query
results. It caches pages. A repeated identical query still re-plans and
re-executes, which I measured at 6-7 ms warm on demo-sized data: noise next to
a multi-second LLM round trip. BigQuery and Snowflake do cache identical
results for 24 hours, which makes an app-level query cache largely redundant
there. Caching earns its keep on large scans and repetitive lookups, not on
principle.

---

3. Fail open, fail closed, and know which is which

QueryGate has two guards that both protect the system and have opposite
failure modes. The asymmetry is deliberate and worth saying out loud.

Governance fails closed. Can't prove the tenant filter? Refuse. Empty tenant
scope on governed data? Refuse. The cost of being wrong is a leak, so the
default has to be no.

Rate limiting fails open. If Redis is unreachable, requests go through and
the server complains loudly in the logs. Rate limiting protects availability,
not confidentiality; refusing every query because the limiter is down trades a
small risk for a certain outage.

The limiter itself is a token bucket living entirely inside a Lua script, so the
read-refill-write cycle is atomic across replicas. Fifty concurrent calls
against a three-token bucket allow exactly three.

• Token bucket, not fixed window. A fixed window lets a caller spend a full quota at the end of one window and again at the start of the next, briefly doubling the intended rate.
• Per-caller buckets. A global counter lets one enthusiastic tenant starve everyone else, which is the same failure governance exists to prevent, just on a different axis.
• Per-tool costs. run_query reaches the warehouse and costs 5 tokens; search_catalog answers from memory and costs 1. Charging them equally either throttles cheap discovery or waves through expensive scans.

I can report that the limiter works, because it fired at me during the attack
run above and refused four of my six attempts. Genuinely annoying. Correct.

---

4. A second adapter is a test for the first

The project claimed to be engine-agnostic. It shipped one adapter. That claim
was therefore untested, and I knew better than to believe my own README.

So I added DuckDB, chosen because it's in-process: the portability tests need no
container and no credentials, and run in plain CI.

It found two defects on the first run.

The first was structural. The SQL dialect was a module-level constant, imported
by value, so no deployment could actually change it. The "swappable warehouse"
was swappable in the documentation only. It moved to config.

The second was a real latent bug. bind_params() always bound the tenant list,
including for public-table queries containing no tenant predicate at all.
Postgres silently ignores an unused named parameter. DuckDB does not:

Invalid Input Error: Parameter argument/count mismatch,
identifiers of the excess parameters: qg_tenant_scopes

That bug had been sitting there the entire time, invisible because exactly one
engine was polite about it.

BigQuery and Snowflake came later and made the same point louder. The canonical
predicate the governance layer injects and re-proves is col = ANY(:param),
which is Postgres syntax and nothing else's. BigQuery wants IN UNNEST(@param).
Snowflake wants ARRAY_CONTAINS(col::VARIANT, PARSE_JSON(:param)). So there is
now a rewrite at the last hop before execution, and because a rewrite that
quietly drops a predicate is the single worst bug this codebase could have, it
re-proves its own output: the count of engine-form predicates must equal the
canonical count it replaced, and no canonical form may survive. Same trick as
section 1, one layer down. Four adapters in, the pattern has stopped feeling
like paranoia and started feeling like the only way to sleep.

The general lesson: an abstraction with one implementation is a guess. If
your architecture diagram has a box labelled "pluggable" and only one thing has
ever been plugged in, that box is decoration.

---

5. Sometimes the right move is not to be the one enforcing it

Everything above assumes a tenant column exists to filter on. Then I described
the project to someone running a mid-sized company's warehouse and got the
obvious question back: what if it doesn't?

Their marts were modelled the way most marts are actually modelled, which is to
say by whoever needed them, at the time, for one dashboard. Team-by-team schemas,
no tenant column anywhere, and exactly one technical Snowflake user because
Snowflake seats cost money and nobody wanted to explain twelve of them to
finance.

Point QueryGate at that in inject mode and here is what happens. The catalog
loader marks a model governed if it has the tenant column. None of them do. So
nothing is governed, no predicate is injected anywhere, every query runs under
the one service identity, and each caller receives the union of everything that
identity can read. The query succeeds. No error is raised. Nothing anywhere
reports a problem.

Wrong rows, no error. Again. The exact failure this whole project exists to
prevent, arriving through the front door because the precondition quietly did not
hold.

The fix is not to inject harder. It is a second mode where QueryGate stops being
the thing that enforces row security and the warehouse does it, because the
warehouse is frankly better at it and their CDO already owns the grants. Set
QG_GOVERNANCE_MODE=warehouse and the query runs as the caller's own warehouse
role: their IdP role maps to exactly one Snowflake role, the session opens
under it, Snowflake's grants decide what is readable.

flowchart LR
    subgraph inject["QG_GOVERNANCE_MODE=inject"]
        direction TB
        A1[Agent writes SQL] --> B1[QueryGate injects<br/>tenant predicate]
        B1 --> C1[Independent re-parse<br/>proves it is there]
        C1 --> D1[(Warehouse)]
        D1 -.->|one service identity,<br/>rows already filtered| E1[Caller's rows]
    end

    subgraph wh["QG_GOVERNANCE_MODE=warehouse"]
        direction TB
        A2[Agent writes SQL] --> B2[QueryGate resolves<br/>caller to ONE role]
        B2 --> C2[Session opens<br/>under that role]
        C2 --> D2[(Warehouse)]
        D2 -.->|engine's own grants<br/>decide the rows| E2[Caller's rows]
    end

Alternatives, not layers. The left column needs a tenant column on every
governed model; the right needs a warehouse role per audience.

Three things make it hold up rather than just sound good.

The mapping is an allowlist, not a claim passed through. It would be one line
of code to read the role out of the caller's token and hand it to Snowflake. It
would also mean a loose IdP configuration lets somebody name any role in the
warehouse. So the map lives in operator config, and a role that is not in it does
not exist as far as this server is concerned.

flowchart LR
    KC["Keycloak / OIDC"] -->|"JWT"| T

    subgraph T["Token claims"]
        direction TB
        R["realm_access.roles<br/><b>analyst</b>"]
        TN["tenant_ids<br/><b>acme</b>"]
    end

    R --> MAP{"QG_WAREHOUSE_ROLE_MAP<br/>operator config"}
    MAP -->|"analyst maps to QG_ANALYST"| ROLE["Warehouse role<br/><b>QG_ANALYST</b>"]
    MAP -->|"unmapped, none,<br/>or several"| REF["REFUSED"]

    TN -.->|"used in inject mode only"| PRED["Tenant predicate"]
    ROLE --> SF[("Snowflake<br/>session")]

    style MAP fill:#fff4e6,stroke:#c98a3a,stroke-width:2px
    style REF fill:#ffe0e0,stroke:#c0392b

The token's roles are lookup keys, never values. A forged claim of
QG_FINANCE matches no key and is refused.

Refusal, never a fallback. No mapped role, no roles at all, anonymous caller,
or several mapped roles all refuse the query. That last one took me a minute to
land on: picking one silently would grant or withhold access by accident, and I
would rather an analyst with two hats be told to get a third role created than
guess which of their hats I meant.

It fails at startup, not at request time. warehouse mode with no role map
refuses to boot. A deployment that would serve every caller as one identity
should not accept traffic while somebody notices.

And then the part that actually cost me an evening.

Every surface you would think to check looks correct.

flowchart TB
    START["Service user connects<br/>role=QG_ANALYST"]
    START --> CHECK{"DEFAULT_SECONDARY_ROLES<br/>on the user?"}

    CHECK -->|"('ALL'), the default<br/>for several creation paths"| BAD1["Session activates<br/>EVERY granted role"]
    BAD1 --> BAD2["USE ROLE narrows nothing"]
    BAD2 --> BAD3["Reads every audience's views"]
    BAD3 --> BAD4["Connection looks correct<br/>Logs look correct<br/>No error anywhere"]

    CHECK -->|"( ), set explicitly"| OK1["Only the primary role<br/>is active"]
    OK1 --> OK2["USE ROLE QG_ANALYST"]
    OK2 --> OK3["SELECT CURRENT_ROLE()<br/>read back and compared"]
    OK3 --> OK4["Wrong role, refuse<br/>Right role, execute"]

    style BAD4 fill:#ffe0e0,stroke:#c0392b,stroke-width:2px
    style OK4 fill:#e0f5e0,stroke:#27795b,stroke-width:2px

Wrong rows, no error. The red path is what you get by default, and every
surface you would think to check looks healthy.

For any of this to work, the service user has to be granted every mapped role.
That is unavoidable: it is the account doing the switching. Now, Snowflake users
carry a property called DEFAULT_SECONDARY_ROLES, and for users created through
several perfectly normal paths it defaults to ('ALL'). What that means is the
session activates every role the user has been granted, regardless of which one
you connected with.

A property, not a session parameter, which matters when you go looking for it:

DESC USER QUERYGATE_SVC;   -- the DEFAULT_SECONDARY_ROLES row

SHOW PARAMETERS will not find it and will not complain either, it just returns
nothing, which is a memorable ten minutes. Reading another user's properties also
needs MONITOR on that user or a role like USERADMIN, so an analyst checking a
service account gets a privilege error. Correct behaviour, and a reminder that
the person who can answer this is the person who created the account.

Read that again with the previous paragraph in mind. USE ROLE QG_ANALYST
narrows nothing. The connection is configured correctly. The logs look right. The
analyst reads every audience's views. Wrong rows, no error, one layer further
down, and this time wearing the costume of the fix.

So the adapter does three things in order, before any query runs: USE SECONDARY
ROLES NONE, which is what actually narrows the session; then USE ROLE; then
SELECT CURRENT_ROLE() and compare, so a silent fallback to a default role fails
closed instead of answering as somebody else.

What I would say to anyone building this: the general shape of the bug is a
control whose precondition is invisible. Predicate injection needs a tenant
column. Role pinning needs secondary roles off. Both degrade to success when
the precondition is absent, which is why both now assert their own precondition
rather than assuming it.

You hand over one key. The ring comes with it.

One thing this mode does not fix, and I would rather say so than let someone find
out during an audit: the service user still holds the union of every mapped role.
Role pinning is a control QueryGate applies at runtime, not a property of the
account. Whoever holds those connection details holds the warehouse. The honest
end state is External OAuth, where the analyst's own identity reaches Snowflake
and no union principal exists at all, but that needs every analyst to have a
Snowflake identity, which is precisely what a company with one technical user
does not have. That is an org problem wearing an engineering costume, and it is
usually what decides the timeline.

The bug I'd rather show than hide

Column masking worked. PII columns marked in the dbt catalog got wrapped in a
hash on the way out, equal values hashed equal so count(distinct) still
returned the right answer, and I was pleased with myself for roughly a day.

Then I tested SELECT *.

A wildcard is a Star node, not a Column node. My rewrite walked columns.
It never saw the star. The single laziest query available to any user on earth
walked straight through the entire control.

Wildcards are now expanded from the catalog before masking runs. A test caught
it, which is the argument for writing the paranoid ones. The tests you write
while assuming you're an idiot are the tests that find out you were right.

The second half of that control is less obvious and, I think, more interesting.
Masking only the SELECT list is theatre. If a masked column can still appear in
a predicate, the caller has an oracle:

SELECT count(*) FROM customers WHERE customer_name = 'Alice Smith'

Run that a few hundred times and you have read the column one guess at a time,
while every returned value stayed dutifully masked. So masked columns are
confined to the projection: WHERE, JOIN, GROUP BY, ORDER BY and HAVING
are all refused, with an error explaining why.

---

How I tested it

A governance matrix. Every case that could leak a tenant is a test: single
governed table, aliased table, join of two governed tables, join of governed and
public where only one side gets filtered, subquery, CTE body, a schema-qualified
table shadowing a same-named CTE, empty scope, and the adversarial asserts:
filter missing, filter OR-ed with 1=1, right column but wrong parameter.
This is the suite that lets me sleep.

Ground-truth evals. Tests prove the code does what I said. They cannot tell
me whether the agent found the right table or computed the right number. So
there's a golden set of plain analyst questions, each pinned to a ground-truth
answer computed from a deterministic seed, with retrieval quality measured
separately as hit@k. Two independent signals: did it find the right table, and
did it compute the right number. When something's off, you know which layer to
fix, which saves you from tuning retrieval to solve what was actually a prompt
problem.

290 tests. 213 of them pass on a bare uv sync with no optional dependency and
no infrastructure at all; the rest need DuckDB, Redis or a real Postgres, and CI
runs every one except the Qdrant case.

---

Making it yours

Because the point was a template, the boundaries got drawn where a team would
actually need to cut.

The warehouse is two coroutines, estimate and execute. Postgres, DuckDB,
BigQuery and Snowflake ship; Trino is one module more plus a line in a dict. The catalog comes from whatever your dbt
CI already publishes, or a JSON bundle in a documented shape if you don't run
dbt. Auth is a TokenVerifier returning a principal, so an in-house SSO gateway
drops in beside the OIDC one. Tenancy is a column name in config, and if you'd
rather let Snowflake row access policies do that job, QG_GOVERNANCE_MODE=warehouse
turns injection off and keeps the pipeline for cost control, masking, grounding
and retrieval. A snow CLI script provisions the role model from the same map
the server reads, so the grants and the mapping cannot drift.

The part I'd ask a team to read before changing anything is query/: pure, no
I/O, the whole safety argument in about 1,200 lines. Its companion is
test_governance.py, where that argument is written down as assertions. Change
the test first and the code second.

What deliberately doesn't generalise is the dbt project in dbt/. Three tenants
and a few hundred orders, small enough that the diffs stay readable. It is meant
to be deleted.

The honest backlog

Things that are missing, stated plainly, because a template that oversells
itself wastes somebody's sprint:

No connection pooling. Every call opens a fresh psycopg connection, and
run_query opens two: one for the EXPLAIN, one to execute. Fine for a demo
and for a handful of analysts. It is unambiguously the first thing that falls
over under real concurrency.

The masking hash is MD5. It's a pseudonymiser, not a secret. Analytical
value survives; a low-cardinality column with a guessable domain, like customer
names, does not survive a determined dictionary attack by somebody who can see
the hashes. For regulated data, swap in a keyed HMAC whose secret the query
layer never returns.

The cost ceiling is off unless you set it. QG_MAX_PLAN_COST defaults to
zero, which disables it. Compose sets a real number. A bare install does not.

The guarantee is only as strong as sqlglot's parse. The assert is
independent of the injector but not of the parser, since both read the SQL through
the same library. If sqlglot resolves a scope differently from your engine, the
predicate can land in the wrong place and re-parsing won't catch it. This is the
honest boundary of the threat model, and it's why inject-and-assert belongs
over native row access policies rather than instead of them.

The takeaway

If you're wiring an LLM to a database, the question worth asking on day one is:
what is the model allowed to decide?

Let it write the query. That's its strength and it's genuinely good at it now.
Do not let it decide who may see what, whether the query is read-only, or how
much it's allowed to scan. Put those behind a deterministic layer, inject them
yourself, then re-verify them independently before you execute.

Trust the model to be clever. Don't trust it to be safe.

The exec, for the record, still doesn't know what SQL is. He gets his number
before the 2pm, it's correct, and it's only ever his company's number. That was
always the entire brief.

The analyst is still on item 38 of 37. But she's no longer on it because
somebody wanted a sum.

The code is on GitHub. Clone it, docker compose up, and try to make one tenant
read another's data. I'd genuinely like to know if you can, and if you lift it
into your own stack, I'd like to hear which seam turned out to be in the wrong
place.

---

QueryGate is MIT-licensed. Built with FastMCP, sqlglot and dbt, running on
Postgres, DuckDB, BigQuery or Snowflake.

I'm Arif Ismailov. If you
work on LLM data access, MCP servers, or multi-tenant analytics, I'd like to
compare notes.