I build text-to-SQL agents on Oracle and Postgres for a living. Every one of them had the same bug, and it wasn’t in my code. It was in the order of operations.
The bug
The schema goes into the prompt before the query runs. Row-level security runs when the query runs. So the model sees a table the user can’t read, writes perfectly valid SQL against it, the database returns zero rows, and the agent says “no records found”. A wrong answer, delivered with confidence. Vanna (23k stars, archived March 2026) applied identity exactly there: at execution, after the model had seen everything.
The fix
Apply identity at selection. Decide which tables the model is shown, per caller, before any SQL exists. A restricted table isn’t ranked low — it’s absent.
from schemagate import Catalog, Principal
cat = Catalog().bootstrap("postgresql://localhost/app")
cat.restrict("hr_compensation", roles=["payroll"])
analyst = Principal("okta:jdoe", roles={"analyst"})
cat.select("salary by employee", principal=analyst).table_names # no hr_compensation
pip install schemagate — one dependency, no API key, any SQLAlchemy database.
The side effect that pays for it
You’re now sending ~6 tables instead of the schema dump. Measured on the test schemas: 65–79% fewer prompt tokens on small ones, 97% on a 260-object one (16,095 → 444 per question). The selector never calls a model — BM25 plus a hashed embedder, offline, milliseconds.
What broke while building it
Six invented schemas found ten bugs before release. My favourite: a three-column orders_bkp outranked the real orders table, because short documents win cosine similarity. Backup and staging copies now rank below the object they shadow. The full list is in TESTING.md.
Where it plugs in
MCP server for Claude Desktop and Cursor, a LangChain retriever, a native Oracle 23ai VECTOR store, and a browser demo that needs no install: https://ashishsinha1602.github.io/schemagate/
Repo: https://github.com/ashishsinha1602/schemagate — tell me where it breaks on your schema.
Top comments (15)
Apply identity at selection rather than at execution is the same lesson RAG teams learn the hard way: filter before retrieval, not after, or the permission boundary holds while the answer still leaks. Here it leaks as a confident no records found, which is arguably worse than an error because the user believes it. Two adjacent cases worth handling explicitly. Column-level restrictions inside a table the user can otherwise read - showing the schema with the salary column in it means the model will happily reference it, so the redaction has to happen in the schema the model sees, not just in the result set. And schema metadata itself is disclosure: a table named hr_compensation_2027_layoffs tells the reader something even when zero rows come back. Absence rather than low ranking is the right call for both.
Both points are right, and the second one is why "absent, not de-ranked" is the rule — a table name is a disclosure on its own. Column-level was a real gap when you wrote this; it isn't now: cat.restrict_column("hr_employee", "home_address", ["hr_admin"]) removes the column from the DDL the model sees (not commented, not renamed) for callers without the role, and the MCP describe_object honours it too. Landing in 0.1.1 this week. Thanks for the push.
Removing home_address from both the DDL and describe_object closes the specific disclosure path I raised. I would exercise those two outputs in alternating role contexts, rather than only testing each role against a fresh catalogue.
That catches metadata cached under an authorized caller and later reused for a restricted one. It is also worth checking foreign-key descriptions and sample queries for references to hidden columns; the visible table definition can be correct while another schema-discovery surface still names the removed field.
Good points, and two of them are already covered:
run_querynow also refuses a hidden column, by name or throughSELECT *.The alternating-roles test is the one I don't have: authorized caller, then restricted caller, on the same catalogue object, to catch anything cached in between. Adding it.
Thanks for clarifying that the foreign-key references and ontology meaning lines are already filtered. The run_query check also makes the execution boundary much stronger than hiding schema descriptions alone.
For the alternating-role test, I would run both orders: authorized then restricted, and restricted then authorized. The first detects a disclosure carried through cached metadata; the second can reveal stale restrictions incorrectly suppressing a legitimate query. Using the same catalogue instance in both sequences should make any caller-specific state that escaped its intended scope easier to observe.
Done, both orders, on one catalog instance: github.com/ashishsinha1602/schemag...
Authorised then restricted checks for disclosure through anything the first call warmed. Restricted then authorised checks for a restriction going stale and hiding a table it shouldn't. A third test compares each caller's result against fresh catalogs, so order can't shape the answer.
To be sure they test the right thing, I broke the code on purpose: a select() cache keyed by the question only, not the caller. Both ordering tests failed. Thanks for the push on the second direction; I would have tested only the first.
The fresh-catalog comparison and intentionally broken question-only cache make those order tests much stronger. A third sequence worth keeping is the same caller before and after a permission change, with the catalog object reused.
That case cannot be caught by a cache key that merely adds caller identity: the identity stays constant while eligibility changes. Revoking one table between the two calls should remove its fields and references immediately, while granting it back should restore the same result as a fresh catalog under the new permission state.
Yes, and it splits into two cases. When the rules live in schemagate's own role lists, visibility is evaluated on every
select()call against the caller's current roles, with no per-caller cache, so a revoke takes effect on the next call with the same catalog object. When visibility is read from the database's GRANTs, it's captured at bootstrap, so a revoke in the database only lands afterrefresh_catalog. That second case is exactly the drift you're describing, and it's the first item on the roadmap: stamp the catalog with when its grants were read and refuse to answer past a configured age.Your sequence (revoke, call, grant back, compare with a fresh catalog) is the right regression test for both. I'll write it for the role-list path now and make it the acceptance test for the grants one.
The per-call role-list path and bootstrap GRANT path need separate freshness claims, as you describe. A maximum catalog age bounds how long old grants may remain visible, but it does not make a database revoke effective on the next call.
I would document that bounded window explicitly and test the configured age boundary with a controlled clock, including refresh failure. An expired catalog should fail closed rather than quietly reuse the last successful grants snapshot. The revoke-and-restore sequence can then verify both access changes and recovery.
Right, and "a maximum catalog age" only means something if expiry fails closed. If a refresh fails after the deadline and the server quietly keeps serving the last good snapshot, the bound is fiction. So the contract for that feature is: past the configured age, a refresh that fails makes
select_schemaandrun_queryrefuse, and the refusal says the grants are stale, not that the table doesn't exist.Testing it with a controlled clock is the only way to hit the boundary exactly: just inside the age, just past it, past it with the refresh failing, then the refresh recovering. I'll write those four cases as the acceptance test before the feature, and document the window in SECURITY.md as a window, not as "immediate".
Shipped in 1.4.0, built the way you described.
SCHEMAGATE_CATALOG_MAX_AGEbounds the window. Past it the server re-reads, and if the re-read fails every scoping tool refuses with "permissions are stale", naming no host or database error, until a re-read succeeds. SECURITY docs now state the role-list path and the GRANT-snapshot path as separate claims.The acceptance test came first, on a controlled clock: exactly at the limit, just past it, past it with the re-read failing, retry pacing, recovery, and ten simultaneous calls causing one re-read. Each of seven deliberate breakages fails it. Then end to end on real servers (PostgreSQL, MySQL, Oracle, plus SQL Server via the config path): a real GRANT, a REVOKE, the server's login locked, then unlocked. 17/17 on each.
The seven deliberate breakages and real GRANT/REVOKE runs make that a much stronger claim than a freshness setting alone. The controlled clock also gives the exact-at-limit behavior an unambiguous meaning.
For the ten-caller test, I would retain an assertion that none receives a result from the expired snapshot while the shared refresh is in progress. Coalescing refresh work protects the database; withholding stale results protects the authorization boundary. Recovery should publish one complete replacement snapshot before any waiting caller proceeds.
You're right that it didn't prove that. The ten-caller test checked one re-read and no errors, not which catalogue the callers were answered from, and I confirmed the gap: a version that lets a caller skip a busy re-read and answer from the expired catalogue passed it.
So there's now a test for exactly what you described. It revokes a table, lets the catalogue expire, starts ten callers together and holds the one re-read open. While it's in flight it asserts that no caller has any answer at all, since an answer then could only come from the expired catalogue. Then it releases the re-read and checks that all ten see the revoke, with one re-read total. Against that mutant the new test fails and the old one passes, which is the distinction you drew between protecting the database and protecting the boundary.
github.com/ashishsinha1602/schemag...
The schema boundary is a good place to fail closed. I’d also keep the generated SQL and selected tables in the audit record, because that makes a bad join or overbroad request much easier to replay.
Agreed — that's the record that makes a bad join replayable. select() now returns .to_dict(): who asked, the roles, and exactly which objects and columns the model was shown with the reason each was chosen. Log it next to the generated SQL and you can replay any answer. In 0.1.1.