Structural Multi-Tenant Isolation for Text-to-SQL

Overview#

Our txtToSql engine lets business users ask questions in plain English and get charts, dashboards, and PDF reports back, as shown in the Metabase, native charts, and Velocity PDF project pages. In a multi-tenant SaaS deployment that convenience carries a hard requirement: tenant A must never see tenant B’s rows, no matter what the model writes.

The common defense is a validator. Prompt rules tell the model to scope its SQL, and a regex/AST check inspects what comes back and tries to reject anything cross-tenant. That defense trusts a probabilistic model to have written safe SQL, then trusts a filter to recognize every unsafe shape. Prompt injection and jailbreaks exist precisely because that trust is breakable.

txttosql-isolation-core is our answer, built as a standalone, framework-agnostic Python library. The security guarantee comes from disallowed data being structurally absent from the model’s context, not from the model behaving correctly. A jailbroken prompt cannot leak rows that were never reachable in the first place. The adversarial harness that ships with it proves the claim on every run: 605 scoped queries, 0 cross-tenant rows, every threat-model probe blocked.

Figure 1 - Trust the model, or remove the choice: The validator-trust design (left) puts a probabilistic model between the user and the warehouse and hopes a filter catches every unsafe query. The Split-Plane design (right) builds a per-identity sandbox before the model is invoked. The model has no name for, and no rows from, any other tenant.

The Problem#

PAR Technology hit this exact wall building a production text-to-SQL agent for 300+ restaurant businesses, and their write-up names the failure mode precisely: LLMs “are probabilistic generators rather than deterministic policy engines. A model that correctly applies a business ID filter ten thousand times in a row may silently omit it on the ten thousand and first” [1]. Their answer was a three-layer architecture they call Split-Plane SQL, and they report over 50,000 production queries with zero cross-tenant exposure incidents [1]. That figure is PAR’s own self-reported claim, not an independent audit, and not our number. It is the narrative anchor for a sharper question we wanted to answer for our own engine: what would make that guarantee structural rather than statistical, and provable by a harness we can hand a client?

Our starting point was the txtToSql engine’s existing defense, a seven-point validator (start with SELECT, no stacked statements, no DDL/DML keywords, no system catalogs, no UNION, no comments, no procedure calls). Useful, and worth keeping. It is also a behavioral guarantee: it inspects what the model wrote and tries to recognize every unsafe shape. The weakest link in that chain is a validator saying “this looks fine.”

KEY INSIGHT: A security guarantee for an LLM pipeline must not depend on the model behaving. If the disallowed rows are structurally absent from everything the model can see and everything its SQL can reach, a fully jailbroken prompt has nothing to leak.

What We Built: Three Layers, One Invariant#

The library delivers three structural guarantees, each one closing the gap the previous one leaves open:

1. The token is the boundary, not the prompt. Tenant, business, and admin scope enter the system exactly once, from a verified JWT claim, and are threaded server-side. No tenant identifier is ever placed in a prompt, a request body, or any value the model can echo.
2. The model is shown a sandbox, never the warehouse. A server-side, parameterized CTE pre-filter scopes each view to the authenticated identity’s rows before the model is invoked. The planner sees only the scoped CTE alias and its columns.
3. Everything downstream binds to the same verified identity. Cache keys across all three tiers, and the authenticated-parameter fast path, key off the same composite session key.

The pre-existing seven-point validator stays in the pipeline, now as defense-in-depth running after structural scoping, never as the sole line. Two independent layers must both fail before anything leaks.

Figure 2 - The pipeline order is load-bearing: Each step closes a named attack class from the threat model. Structural scoping happens before the model is invoked, so the model never sees the warehouse. The validator runs after scoping, as a backstop, never instead of it.

The Invariant Lives in the Type System#

The single most important line of the project is not a filter. It is a constructor that does not exist. A SessionContext, the identity every scope binds to, can only be built from VerifiedClaims, and only the token verifier mints those. There is deliberately no code path that constructs a session from a bare tenant id, a request body, or a prompt.

1
class SessionContext(BaseModel):
2
    """The verified identity threaded through every isolation-critical operation."""
3

4
    @classmethod
5
    def verified(cls, verified: VerifiedClaims) -> SessionContext:
6
        """Build a session from verified claims. The ONLY supported constructor."""

TokenVerifier.verify_token (src/txttosql_isolation/identity.py) is the single minter. The algorithm is pinned to RS256, so alg: none and HS256 key-confusion tokens are refused before any signature math. JWKS is fetched with a TTL cache plus one forced refetch on an unknown key id, so key rotation works without hammering the identity provider. Issuer, audience, expiry, and a required scope are all enforced, and every failure exits through one fail-closed TokenRejected. Swapping our dev issuer for Auth0, Cognito, or Keycloak is JWKS configuration, not code.

Figure 3 - No constructor, no attack: Identity substitution is not caught by a check; it is unrepresentable. The only path to a SessionContext runs through cryptographic verification, so a request body carrying its own tenant_id has nowhere to go.

The Crown Jewel: the Split-Plane CTE Pre-Filter#

build_scoped_ctes (src/txttosql_isolation/cte.py) emits parameterized T-SQL that pre-filters each warehouse view to exactly the authorized rows. The identity values are bound parameters (@tenant_id, @admin_id), never string-interpolated. Each CTE joins the admin’s scope table cross-checked against the tenant map, so even a mis-seeded scope row pointing at another tenant’s customer is excluded by the tenant predicate. A fail-closed resolve runs first: an empty, unmapped, or cross-tenant scope raises before any SQL is emitted.

The planner then receives only the scoped alias and its columns. build_prompt (src/txttosql_isolation/planner.py) is the single door to the model, and the prompt it assembles names exactly one relation: the CTE alias. A test greps that outgoing prompt and asserts no base table or warehouse view name appears. The execution plane (src/txttosql_isolation/executor.py) completes the trap: only the CTE aliases are addressable, and a reference to any other relation raises ExecutionRejected, the mock’s faithful model of SQL Server error 208 under a low-privilege role with no rights on the base tables.

The Jailbreak That Fails Structurally#

The thesis fits in one test. Our seven-point validator deliberately has no allowed-relations check, so when the jailbroken mock planner emits a dump of a warehouse base table, the validator passes it. It is, after all, a read-only SELECT over a non-system table:

1
WITH scoped_Customers AS (
2
    SELECT v.*
3
    FROM Sales._v_Customers AS v
4
    JOIN iso._admin_scope AS s ON s.CustomerID = v.CustomerID
5
    JOIN iso._tenant_map  AS t ON t.CustomerID = v.CustomerID
6
    WHERE s.AdminID = @admin_id AND t.TenantID = @tenant_id
7
)
8
SELECT * FROM Sales.Customers;

The composed statement’s sandbox contains one relation, scoped_Customers. The model’s SQL reaches for Sales.Customers, a table it was never shown, and the query dies at execution: ExecutionRejected, zero rows, error logged, nothing leaked. The failure is structural, not a filter catch. test_validator_does_not_catch_the_base_table_jailbreak and test_pipeline_jailbreak_fails_at_execution_with_zero_leak (tests/test_split_plane.py) hold that behavior permanently.

Figure 4 - The validator passes it, the sandbox kills it: The jailbroken query is syntactically innocent, so the behavioral filter waves it through. It fails anyway, because the table it names does not exist in the composed statement’s world. That is the difference between catching an attack and making it unrepresentable.

KEY INSIGHT: The strongest security test in the suite is the one where the filter fails. Proving the validator does NOT catch the jailbreak, and the leak still does not happen, is what separates a structural guarantee from a well-tuned filter.

The Subtle One: an Identity-Scoped Cache#

Caching is where multi-tenant text-to-SQL quietly leaks. Admin A asks a question, the result is cached by question hash, admin B asks the identical question, and the cache serves B a payload scoped to A. That is the confused-deputy attack, and it does not need a jailbreak, only a cache key that forgets who asked.

Every key in our three-tier cache (src/txttosql_isolation/cache.py: 24h dashboards, 24h SQL templates, 1h results) runs through scoped_key, which prefixes the composite verified identity. Two identities can never collide, so B’s read misses A’s entry and computes fresh, scoped-to-B results.

The SQL-template tier needs one extra discipline. A composed statement embeds the writer’s security-plane prefix, so the tier caches only the post-CTE analytical fragment. set_template refuses a composed statement or any security-plane reference outright, and on reuse the fragment is re-composed onto CTEs freshly built from the reader’s verified session. A’s template is never pasted onto B’s prefix.

Figure 5 - The cache remembers who asked: The composite identity prefix on every tier means the poisoning read cannot be expressed. At harness volume, 403 cross-identity cache reads all missed and recomputed.

Two Paths, One Identity Model#

Open-ended natural language is not the right interface for a fixed production query surface, so the library ships an authenticated-parameter fast path beside the NL+CTE path (src/txttosql_isolation/parameterized.py). A tool is a named, hand-authored T-SQL statement with typed parameters. Identity parameters are bound from the verified SessionContext, and a caller- or model-supplied identity argument raises ToolRejected, loudly, never a silent override. Unknown tools and missing or extra arguments fail closed. A thin router sends ad-hoc questions to NL+CTE and the fixed surface to the parameterized path, and both enforce identical tenant scoping.

The design deliberately mirrors a semantic we verified in Google’s MCP Toolbox for Databases, where a tool parameter carrying authServices is resolved server-side from the validated ID token and does “not take input values in request bodies” [2]. The Toolbox docs only document Google Sign-In as the auth service type, and our stack verifies tokens from our own RS256 issuer, so we implemented the same contract in-core, in Python, and kept the verified tools.yaml shape as a documented sidecar deployment path (docs/mcp-toolbox-notes.md in the repo records the syntax and the caveats).

The Evidence: 605 Queries, 0 Cross-Tenant Rows#

No claim on this page ships unproven. The harness (src/txttosql_isolation/adversarial.py, uv run txtsql-isolation harness) runs two waves against the real verifier and pipeline, with genuine RS256 tokens minted in-process. Wave 1 fires at least one live probe per threat-model row. Wave 2 runs benign scoped queries at volume across all three reference admins, audits every returned row against the caller’s authorized set and tenant, and replays the same question hashes across admins to exercise the cache-poison surface. This is the harness output, verbatim:

1
== Adversarial probes (wave 1): one class per THREAT-MODEL row ==
2
  OK (a) 5/5  token forgery / tampering / downgrade / replay
3
  OK (b) 3/3  identity substitution in caller-supplied input
4
  OK (c) 4/4  prompt injection referencing another tenant
5
  OK (d) 3/3  jailbreak to dump base tables
6
  OK (e) 3/3  confused-deputy read of another identity's cache entry
7
  OK (f) 1/1  verified identity with empty / unmapped scope
8

9
== Evidence (wave 2): benign scoped queries at volume ==
10
  queries run                 605
11
  cross-tenant rows returned  0  (must be 0)
12
  token forgeries blocked     5
13
  identity overrides blocked  3
14
  prompt injections contained 4
15
  jailbreaks blocked          3
16
  cache-poison reads blocked  403
17
  empty-scope rejections      1
18
  isolation overhead (mock)   0.108 ms/query

605 scoped queries, 0 cross-tenant rows returned, all 19 wave-1 probes blocked. The zero is structural, not statistical: scale the per-admin query count up and it holds, because the leak path does not exist rather than because the model kept behaving. The forgery corpus covers a tampered payload, a token re-signed with an attacker’s key, an alg: none downgrade, an expired replay, and a scope-stripped token. Every one dies in the verifier before a session exists.

The Bake-Off: Same Question, Two Correct Answers#

The demonstration that lands with clients is not an attack at all. Three admins, deliberately seeded with different scope sizes, ask the identical question (“list my customers”) down both execution paths:

Same identity: identical answer down both paths. Different identities: different correct numbers, tenants fully disjoint. This is PAR’s franchise-owner-versus-brand-manager scenario [1] reproduced on our own reference fixture, with the added proof that two entirely different execution mechanisms, an open NL pipeline and a closed parameterized tool, enforce the same scope because they bind to the same verified identity.

KEY INSIGHT: “Same question, two correct answers” is the multi-tenant requirement stated as a demo. If your text-to-SQL surface cannot produce different, correct, disjoint answers for different identities asking identical questions, it does not have row-level security, whatever its validator says.

What It Does Not Do#

These caveats travel with the library, because they are the difference between a defensible guarantee and an overstated one.

• The evidence run uses mock intelligence and execution planes. The harness drives the real verifier, real CTE generation, real validator, and real cache against deterministic mock planner/executor seams, so the isolation contract is what gets proven, with no model call and no database. The T-SQL itself ships in the repo (sql/01_tenant_hierarchy.sql) for the real SQL Server instance, and the mssql-marked suite is where real-instance numbers belong.
• The ~0.1 ms/query overhead is the isolation machinery on the mock plane, not a SQL Server round trip. It prices the verify-resolve-build-validate path, nothing more.
• This is SQL Server / T-SQL over WideWorldImporters specifically, not Postgres or Databricks. Port the pattern, hand-write the dialect, and test against the real instance.
• The pre-filter is only as safe as the tenant mapping is complete. A customer with no tenant mapping is a silent gap. TenantHierarchy.integrity_gaps() exists to catch exactly that at seed time, before the gap can widen access.
• PAR’s “50,000 queries, zero incidents” is PAR’s self-reported claim [1]. Our headline is our own harness’s 605/zero, re-runnable by anyone with the repo.
• The MCP Toolbox interop is verified at the syntax level, not the binary level for non-Google issuers. We state which half we confirmed and ship our own implementation of the semantic.

Platform Portability#

The core is deliberately framework-agnostic: no model SDK, no database driver, no web framework in the base install. The seams are protocols (Planner, Executor, CacheBackend, ToolExecutor), and FastAPI, pyodbc, and Redis arrive as optional extras. That shape is why this is a standalone library rather than a feature branch of the engine: the real txtToSql pipeline plugs in as a Planner, the planned txttosql-eve agent wraps it next, and if either bet changes, the crown-jewel security IP is untouched Python. It also sits naturally in our wider tooling suite: TraceKit measures the token bill and grades the trajectory, ContextLedger cuts the bill, RubricGate grades the artifact, and txttosql-isolation-core guarantees the data boundary underneath the agent doing the work.

As a Consulting Engagement#

This library is the multi-tenant differentiator behind our txtToSql bolt-on service. The engagement is direct: we bolt structural, claim-verified tenant isolation onto your data agent, so a jailbroken prompt structurally cannot reach another tenant’s rows, and we hand you the adversarial test suite that proves it, re-runnable on every release. The existing validator you already trust stays in place as the second layer. What changes is that it stops being the only one.

Technologies#

Conclusion#

The lesson of this project generalizes well beyond text-to-SQL. Anywhere a probabilistic model sits in front of data with an authorization boundary, the same three questions apply. Where does identity enter the system, and can anything the model or the caller controls influence it? What does the model actually see, and is the disallowed data absent or merely filtered? What do the downstream surfaces (cache, logs, tools) key on, and is it the same verified identity?

Our answers are a constructor that only accepts cryptographically verified claims, a parameterized CTE sandbox built server-side before the model is ever invoked, and a composite session key threaded through every tier. The validator we started with is still running. It caught nothing in the harness run, not because it is useless, but because the structural layer in front of it left nothing for it to catch. That ordering, structure first and filters as backstop, is the design we would defend in any security review.

The number to remember is the zero. Not as a track record that grows more impressive with volume, but as a property that holds at any volume, because the query that would break it cannot be expressed. That is what “structurally absent” buys, and it is the standard we now hold any multi-tenant LLM surface to, our own included.

The throughline: security guarantees for an LLM pipeline must come from disallowed data being structurally absent from the model’s context, not from the model behaving. 605 scoped queries, 0 cross-tenant rows, and the jailbreak the validator passed still leaked nothing.

The Series#

This is Part 4 of a 5-part series on natural-language data products built on a shared AI pipeline:

1. Ask Your Database Anything: The Metabase Version: The full-BI-platform version, built on Metabase, for clients who want saved dashboards, drill-through, and scheduled reports alongside the natural language layer.
2. Ask Your Database Anything: Native React Dashboards: The embedded-in-a-product version, native React rendering with ECharts, AG Grid, and Leaflet, no BI platform underneath.
3. Ask Your Database Anything: Printable PDF Reports: The document-shaped version, Apache Velocity templates rendered to PDF via WeasyPrint, with a vision-aware feedback chat for plain-English layout edits.
4. Structural Multi-Tenant Isolation for Text-to-SQL (this article): The security core underneath all three, claim-verified identity and a CTE sandbox the model cannot see past, proven by an adversarial harness at 605 queries and 0 cross-tenant rows.
5. txtToSql-eve: A Chat Front Door With the Tenant Boundary Intact: The chat front door on the engine, a Vercel Eve agent that puts the guaranteed tenant boundary behind a Discord chat surface, proven end to end at 15 chat-driven queries and 0 cross-tenant rows.

References#

[1] PAR Technology, “Multi-tenant LLM analytics with row-level security: How we built a secure agent on AWS,” AWS Machine Learning Blog, Jun. 29, 2026. https://aws.amazon.com/blogs/machine-learning/multi-tenant-llm-analytics-with-row-level-security-how-we-built-a-secure-agent-on-aws/

[2] Google, “Tools,” MCP Toolbox for Databases documentation. https://mcp-toolbox.dev/documentation/configuration/tools/