# Show HN: Access-aware text-to-SQL – stop LLM agents overfetching data > Source: > Published: 2026-07-07 08:51:21+00:00 **A deterministic access guard for LLM text-to-SQL agents.** It enforces each user's real data-access rules on the SQL an agent generates — *before* the query runs — while keeping the agent aware of the full catalog so it can answer honestly about what it can't see. An LLM agent that queries a database connects as *itself* — one privileged service identity — not as the person asking. So a well-formed `SELECT` can return data the user was never entitled to, and neither the model nor the database notices: the query is authorized, it just *overfetches*. This repo shows the pattern that fixes it, with a small, readable, heavily-tested guard you can drop in front of any text-to-SQL agent. Three people ask the same bank assistant the same question — *"profitability by business unit for Q2"* — and each correctly gets a different answer: **Deterministic enforcement, outside the model.** The agent proposes SQL; a non-LLM guard parses it, checks it against the*asker's*role, injects row filters, and only then lets it run — or refuses. No prompt can talk it out of a decision, and it does not get less reliable as your policy grows.**Full catalog visibility.** The agent sees*every*table's name and description — including the ones it can't query — so it always knows what exists. Metadata is far less sensitive than data, and this is what keeps the agent from going blind.**Honest incompleteness.** Because it knows a restricted table exists, the agent can say*"I can show revenue, but not profitability, because the cost table is outside your access"*— instead of silently mislabeling revenue as profitability. It labels every answer`FULL` ,`SCOPED` , or`INCOMPLETE` . Requires **Python 3.14+** and [uv](https://docs.astral.sh/uv/). ``` uv sync # create .venv and install everything # Run the guard's test suite — 121 tests, no API key needed (pure deterministic Python) uv run pytest -q # Open the annotated showcase notebook (three personas, live agent vs. the guard) uv run jupyter notebook notebooks/showcase.ipynb ``` The **guard and the tests need no API key.** Only the notebook's *live agent* cells call an LLM — copy `.env.example` to `.env` and set `ANTHROPIC_API_KEY` to run them; without it, those cells fall back to a static illustration. `sql_guard.py` is a single deterministic module. Everything goes through one call: ``` python from sql_guard import SqlAccessGuard, DEMO_POLICY, DEMO_SCHEMA guard = SqlAccessGuard(DEMO_POLICY, DEMO_SCHEMA) # Dana is a "revenue_analyst": EMEA revenue only — no costs, no client identifiers. d = guard.check( "SELECT business_unit, SUM(revenue_amount) FROM fact_revenue GROUP BY business_unit", role="revenue_analyst", ) d.allowed # True d.injected_filters # ["fact_revenue.region IN ('EMEA')"] <- welded in by the guard d.safe_sql # the rewritten query, scoped to Dana's region, ready to execute # She reaches for the restricted cost table: d = guard.check("SELECT * FROM fact_direct_cost", role="revenue_analyst") d.allowed # False d.reasons # ["role 'revenue_analyst' is not permitted to access table 'fact_direct_cost'"] ``` `guard.check(sql, role)` returns a `GuardDecision` with `allowed` , `safe_sql` , `injected_filters` , `reasons` , and `referenced_tables` . It **never raises** — every parse error, unknown role, or unexpected construct returns a *denied* decision (fail-closed). | Path | What it is | |---|---| `sql_guard.py` | The guard. Parse → policy check → row-filter injection → rewrite/deny. ~540 lines, sqlglot only. | `catalog.py` | Builds the governed catalog: live schema introspection + a curated business overlay. | `agent_prompt.py` | The access-aware agent's system prompt: metric definitions, honesty rules, `INCOMPLETE / SCOPED / FULL` report format. | `tests/test_guard.py` | Behavioral tests — documents what the guard is supposed to do (47). | `tests/test_bypass.py` | Red-team suite — attacks that try to leak data past the guard (74). | `notebooks/showcase.ipynb` | The annotated end-to-end demo (see below). | `docs/ROADMAP.md` | Design notes and not-yet-built ideas. | `notebooks/showcase.ipynb` builds the whole thing over a miniature investment-bank warehouse (Revenue, Direct costs, a Client registry, a Product catalog) and three roles: | Person | Role | May see | |---|---|---| Frank | `finance_controller` | Everything | Bianca | `bu_manager` | Revenue + cost, her unit & region only | Dana | `revenue_analyst` | Revenue only, EMEA only — no costs, no client identifiers | The agent is built on LangChain; the user's `role` is injected via `InjectedState` so it **never appears in the tool schema the model sees** — the model cannot choose or spoof its own identity. The notebook shows: - the same profitability question producing `FULL` /`SCOPED` /`INCOMPLETE` for the three roles; - a **naive agent**(same model, ungoverned tools, no guard) leaking cost data and client PII live, for contrast; - a **blind-catalog contrast** showing why the agent must*see*restricted tables (metadata) even though it can't*read*them (data). The pipeline in `SqlAccessGuard.check` : - Parse with sqlglot; reject anything that isn't a single `SELECT` / set-operation-of-`SELECT` s. - Reject `NATURAL JOIN` — its implicit join key never surfaces as an explicit column reference, so it can't be access-checked. `qualify` against the schema so`*` , aliases, and CTEs resolve to explicit, table-qualified columns.- Enforce the **table** allow-list, then the**column** allow-list (deny-by-default — anything not provably allowed is refused; a`*` that expands to a forbidden column is denied, never silently dropped). - Inject `col IN (...)` **row filters** for the role. In a scope with an outer join, the filter is applied by wrapping the table at its source (`(SELECT * FROM t WHERE ...) AS t` ) rather than in a WHERE/ON clause — an earlier version leaked out-of-region rows through an outer-join no-op predicate;`tests/test_bypass.py` locks that fix in. - Render the rewritten AST back to SQL. Design stance: **deny-by-default and fail-closed.** Only the standard library and sqlglot; no network, no LLM in the enforcement path. ``` uv run pytest -q # all 121 uv run pytest tests/test_bypass.py -q # just the red-team suite uv run pytest tests/test_bypass.py -k "natural" # a single attack family ``` `test_bypass.py` is the point: it loads the exact demo data into in-memory SQLite, sends hostile queries through the guard, executes the rewritten `safe_sql` , and asserts on the **rows actually returned** — column-block bypasses, outer-join tricks, set-operation smuggling, `NATURAL JOIN` , correlated subqueries. A guard is only as good as its refusal to be tricked, so the red-team suite is treated as part of the control, not incidental coverage. **Access-safe, not analytically correct.** The guard makes sure a query only*reads what it's allowed to*. Whether the agent computed the*right number*(correct grain, no fan-out) is a separate concern — that belongs to prompting and a semantic layer, not the guard.**Defense-in-depth, not the only control.** Pair it with engine-native row-level security as the hard backstop, bind identity through your IAM/SSO (never let the agent choose its own), and route decisions to your audit log. The guard closes the gap where the agent's identity isn't the user's; the engine closes the gap a parser bug might miss.**Metadata visibility is itself governed** in the general case. Here every role sees every table name; where table*names*are sensitive, filter the catalog per role as a layer on top. A deterministic parse-and-rewrite guard is an established pattern; this repo's contribution is the *combination* — the guard plus full catalog visibility plus honest incompleteness, integrated into the agent. Credit where due: [Thales Group](https://github.com/ThalesGroup/sql-data-guard)— an open-source SQL guard on the same parser (sqlglot).`sql-data-guard` [HeimdaLLM](https://github.com/amoffat/HeimdaLLM)— "no AI in the enforcement path," static grammars and parsers.[LangShield (Pedro et al., ICSE 2025)](https://syssec.dpss.inesc-id.pt/papers/pedro_icse25.pdf)— peer-reviewed SQL-rewriting defense, measured at sub-2ms, recommended within defense-in-depth. [MIT](/sparklingneuronics/access-aware-text-to-sql/blob/main/LICENSE).