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 (4)
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.
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.