DEV Community: Ashish sinha

What one month of public benchmarks says about my text-to-SQL access layer

Ashish sinha — Sat, 24 Oct 2026 03:20:24 +0000

schemagate is an open-source layer for text-to-SQL that starts from one question: what is this caller allowed to see? It shows the model only the tables the current user may read, before any SQL is written, reads the permissions from your database's own grants, and refuses a query that touches anything else before it reaches the database.

This month I ran it on public benchmarks. Every number below comes from that benchmark's own evaluator, and the predictions are in the repo.

Access control

RBAC Text-to-SQL (SIGMOD '27): 90.1 AC-F1, against 88.1 for the paper's best setup with the same model.
Spider-ACL: 0.984 refusal F1 over all 19,624 question-and-role pairs; best published 0.933.
0 queries read data the role was not granted, out of 8,128 answered.

BIRD

Dev set, all 1,534 questions: 77.1% execution accuracy with one model call per question. No candidate voting, no agent loop.
Mini-Dev: 81.0 / 72.6 / 72.4 on SQLite / PostgreSQL / MySQL, above the current listed leaders. The leaderboard pull request is waiting on the maintainers.
Hidden test set: submitted; the BIRD team runs it.

Spider 2.0

SQLite questions (135): 71.9% with one call per question. The best published agent on the same subset reports 57.8% Pass@1.
The BigQuery and Snowflake runs for a full leaderboard entry are under way.

Oracle

UniQL, Oracle dialect, 1,534 questions: 64.5% on the first try, against a best published baseline of 49.6. Two prompt rules about Oracle syntax did most of it.

Adoption

About 10,000 downloads on PyPI so far (pypistats, mirrors excluded). Apache-2.0. Works with Oracle, PostgreSQL, SQL Server, MySQL and SQLite, as an MCP server, a LangChain retriever or a CLI.

pip install schemagate

What these numbers are not: the Mini-Dev rows are self-reported until BIRD merges them, single runs vary by about a point, and the Spider 2.0 figure is one third of that benchmark. The details and every caveat are on the benchmarks page.

GitHub: https://github.com/ashishsinha1602/schemagate · a star helps other people find it.

77.1% on BIRD dev with one model call per question, and a test-set submission in

Ashish sinha — Thu, 22 Oct 2026 14:55:10 +0000

BIRD is the text-to-SQL benchmark the serious systems compete on: 1,534 dev questions over 11 real databases, scored by execution accuracy, with a hidden test set that only the BIRD team can run. I ran schemagate on the full dev set this week and sent the test-set submission.

Dev set, all 1,534 questions, BIRD's own evaluator: 77.12% execution accuracy (simple 80.97, moderate 71.34, challenging 71.03), with Claude Opus 5.5.

What makes that number interesting is the cost. Per question:

schemagate reflects the database and picks the tables the question needs;
the model sees only those tables' DDL and BIRD's column notes;
the model writes one query; if SQLite errors, it sees the error and fixes it (that happened once in 1,534).

That's one model call per question, about 2,100 prompt tokens, no candidate generation, no voting, no agent loop. The systems at the top of the main leaderboard (82.95 on the test set) typically spend 5–20 calls per question. We're submitting to find out where one call lands on the hidden test set; BIRD's turnaround for API-based methods is 1–5 days, and I'll post the verified number either way.

Three things worth knowing before you compare:

A re-run of the same code gave 80.20 on Mini-Dev where the first run gave 81.00. Opus is not deterministic run to run; treat single-run numbers as ±1.
BIRD's dev set has known annotation errors (Jin et al., VLDB 2026 found problems in about half of Mini-Dev's reference answers). Every entry is scored against the same answers, so rankings hold, but absolute numbers are softer than they look.
The leaderboard is self-reported for dev and Mini-Dev. Our Mini-Dev rows (81.0 / 72.6 / 72.4 on SQLite / PostgreSQL / MySQL, above the current #1 in each) are in PR #251 awaiting the maintainers. The test set is the only part BIRD runs itself.

Why bother with a one-call system at all? Because schemagate's job isn't the leaderboard; it's making sure a caller never sees a table they can't read, and never gets a query run that touches one. On the access-control benchmarks (RBAC Text-to-SQL, Spider-ACL) it has the best published results, with zero ungranted reads in 8,128 queries. The BIRD number shows that layer costs nothing in accuracy.

Predictions, evaluator output and the exact submission package: https://github.com/ashishsinha1602/schemagate/tree/main/benchmarks/results/bird_dev

Text-to-SQL on Oracle: two prompt rules took a light model from 35% to 64%

Ashish sinha — Tue, 20 Oct 2026 02:46:49 +0000

UniQL is a new benchmark that translates BIRD's 1,534 text-to-SQL questions into 16 SQL dialects. Its Oracle column is hard. The best published result is 49.61% (Claude Sonnet 4.5), and Gemini 2.5 Pro scores 26.14%.

I ran schemagate on it with a light model, Gemini 2.5 Flash. The scores below come from UniQL's own evaluator, on Oracle Free 23ai.

UniQL Oracle, 1,534 questions	First try	With error repair
schemagate + Gemini 2.5 Flash	35.0	69.9
+ two Oracle rules in the prompt	64.5	67.6
Best published (Claude Sonnet 4.5, single try)	49.6	—

The first run looked good for the wrong reason. 69.9% was mostly schemagate's repair loop: run the query, show Oracle's error to the model, let it fix it. On a first try alone, Flash got 35%. That's below the bare models, and not a fair comparison with their single-try numbers.

Two mistakes caused almost all of it. Of the 694 failed first queries:

551 wrote FROM account AS a. Oracle doesn't allow AS before a table alias.
100 used a column named DATE or NUMBER without quotes. Both are reserved words.

So schemagate now tells the model Oracle's rules whenever the target is Oracle. First-try accuracy went from 35.0% to 64.5%, about 15 points above the best published model, with a lighter one. Failed first queries dropped from 694 to 85. Other databases' prompts are unchanged.

The lesson I'd take: measure before you tune, and report the number that matches what others report. A repair loop is a fine design, but it doesn't belong in a column of single-try results.

Submitted to the UniQL leaderboard: JerryGao818/UniQL#2. Predictions, evaluator output and the workflow that reproduces it: https://github.com/ashishsinha1602/schemagate/tree/main/benchmarks/results/uniql_oracle

schemagate tops BIRD Mini-Dev in SQLite, PostgreSQL and MySQL

Ashish sinha — Mon, 19 Oct 2026 13:28:50 +0000

BIRD Mini-Dev is 500 text-to-SQL questions over 11 real databases, in three dialects. It is scored by execution accuracy: run the query, and compare its rows with the gold query's rows. I ran schemagate on all of it. Every number below comes from BIRD's own evaluator.

	schemagate + Claude Opus 5.5	#1 until now	that entry's model
SQLite	81.0	75.6 (Jitto Build)	Claude Opus 5.5
PostgreSQL	72.6	65.8 (Ontology2SQL)	deepseek-v4-flash, as a 12-step agent
MySQL	72.4	49.2 (TA + GPT-4)	GPT-4

SQLite is the like-for-like result. The previous #1 uses the same model (per JamLabs' own LiveSQLBench listing), and schemagate is 5.4 points ahead.

I also ran it with light models, because a benchmark that only a frontier model wins says little about the tool:

	SQLite	PostgreSQL	MySQL
schemagate + Gemini 2.5 Flash	63.6	55.6	54.8
schemagate + Claude Haiku 5.5	61.8	55.0	54.4

On MySQL, schemagate with a light model still beats the previous best (GPT-4, 49.2). On PostgreSQL it doesn't. A light model running as an agent (65.8) beats a light model writing one query.

What schemagate does per question:

picks the tables the question needs;
gives the model only their DDL and BIRD's column notes;
runs the query, and shows the model any error to fix.

There is no voting across runs, and nothing reads the gold SQL. The leaderboard PR is bird-bench.github.io#251.

Caveats:

These are self-reported, as all Mini-Dev entries are.
BIRD has been public since 2023, so any LLM may have seen it.
The MySQL evaluator was changed only to connect over TCP on Windows.

Predictions, evaluator output and code: https://github.com/ashishsinha1602/schemagate/tree/main/benchmarks/results/bird_minidev

A benchmark found a bug in my access-control check. After the fix it scored 0.984.

Ashish sinha — Sat, 17 Oct 2026 14:29:53 +0000

schemagate sits between an LLM and a database. It shows the model only the tables and columns the user is allowed to read, and before any generated SQL runs, it checks that the query doesn't touch anything else.

I ran it on Spider-ACL, a dataset from an EACL 2026 paper (Klisura et al.). It takes Spider's questions, gives each one four user roles as real PostgreSQL GRANTs, and labels every question with what Postgres itself said when the reference query ran as that role: allowed or "permission denied". 19,624 question-and-role pairs. The job: answer the allowed ones, refuse the rest.

The first run was bad

A 200-question pilot scored an F1 of 0.83. It was refusing questions it should have answered.

32 of the 33 wrong refusals had the same cause, and it was mine. The check that reads table names out of a query handled FROM students as t1 fine, but not FROM students AS T1. One regex was case-sensitive. With upper-case AS, it read T1 as a second table, couldn't find a table called T1, and refused the query as "not available to this caller".

Most generated SQL writes AS in capitals. So real users would have hit this too. One flag, a regression test, and a check that the tests fail without the fix.

After the fix, all 19,624

Same pipeline, Claude Sonnet 5.5, every record:

	refusal F1	answered a forbidden question	read data the role wasn't granted
schemagate	0.984	1.8%	0 of 8,128 answered
paper's best (fine-tuned Mistral-7B)	0.933	not reported	not reported

On 600 records the pilot never touched: 0.986.

The zero in the last column I didn't take on trust. I parsed every answered query with a separate SQL parser and compared each table and column against the role's GRANTs. None outside them.

The 1.8% are questions the model answered from columns it was allowed to see. Some are fine (the reference query fetched a column the question never asked for). Some are near-miss answers. That's what's left to fix.

Honest limits

The paper's systems used 2024-25 models. This is a 2026 model, so part of the gap is the model.
This scores the answer-or-refuse decision, not whether each answer is right.
The paper's BIRD half isn't run yet.

What I took from it

My own tests passed. A benchmark someone else wrote, with labels from Postgres itself, found a bug in two days. If you build access control for LLMs, run it against labels you didn't make.

Fixed in schemagate 1.6.1. Every decision and query: results. Write-up: results page.

I ran my text-to-SQL access control on a benchmark I didn't write

Ashish sinha — Sat, 17 Oct 2026 02:46:35 +0000

Every number I had published for schemagate came from my own tests. This month a SIGMOD'27 paper released a public benchmark for exactly the problem schemagate exists for: Benchmarking Text-to-SQL under Role-Based Access Control. So I ran it.

The test

Each question comes with a role and a list of the columns that role may read. If the answer needs a column the role can't see, the right reply is "Sorry, I cannot answer." The authors score it with their own evaluator. I didn't change a line of it.

Their main finding is uncomfortable for tools like mine. If you just hide the restricted columns, models still answer. They patch something together from the columns that are left. On BIRD, their best "hide the schema" setup still answered 27% of the questions it should have refused.

What schemagate does differently

Three things happen for every question:

The role's policy becomes visibility. The model never sees a hidden table or column, not even its name.
One line tells the model that its view is partial, without saying what is missing. If the question needs data it can't see, it should refuse instead of improvising.
Every query passes a scope check before it runs. SQL that touches anything outside the policy is refused.

Results

Same model as the paper's best BIRD run (Claude Sonnet 4.5), five trials, the authors' scoring:

	AC-F1	answered when it should refuse	SQL that read forbidden data
BIRD, schemagate	90.1	5.4%	0
BIRD, paper's best	88.1	7.4%	not reported
Spider, schemagate	93.0	5.1%	0
Spider, paper with the same model	93.0	4.6%	not reported
Spider, paper's best (DeepSeek V3.2-Reasoning)	95.9	2.0%	not reported

On BIRD it beats the best of the paper's thirteen models. On Spider it ties the same model and is behind the paper's best Spider result (95.9, DeepSeek V3.2-Reasoning). In neither did any query read data the role wasn't allowed to see. The scope check makes that a guarantee rather than a measured rate.

And the paper's best setup shows the model the whole schema plus the policy. schemagate gets these numbers without the model ever seeing what is restricted.

What each piece is worth

Taking the scope check out doubled the bad answers on BIRD (5.4% to 11.3%). The one-line notice was tested on 100 questions: 16% bad answers without it, 5% with it.

One surprise: giving the model BIRD's column descriptions made things worse. Better notes on the visible columns helped it find workarounds for questions it should have refused.

The honest caveats

I chose the notice's wording on 100 BIRD questions that are part of the test set. Spider played no part in any choice, so it's the cleaner of the two results.
One model, where the paper tested thirteen.
My BIRD databases are the 2024 release; the paper used the 2025 one.

The notice ships on by default in schemagate 1.6.0. To reproduce the runs: benchmarks/rbac_text2sql.py, plus the evaluator's reports in benchmarks/results/rbac/.

pip install schemagate · GitHub

My LLM reranker did nothing, and every test passed

Ashish sinha — Fri, 16 Oct 2026 22:00:29 +0000

schemagate picks the few tables a text-to-SQL model should see out of hundreds. It ranks them with plain maths first (BM25, embeddings, name matching, fused with RRF). Then, if you give it an API key, a model can reorder the top 20. That second step is the reranker.

I was asked why it uses RRF and no LLM reranking. I said it has one, --rerank, and went to show it working. It didn't work.

What I saw

I ran the same three questions with and without --rerank on claude-haiku-5-5. The table order came out identical all three times.

That can happen if the maths already got it right. So I wrapped the provider to log every call and what came back. The model was being called. It just never gave an answer.

The bug

reply = provider.complete(SYSTEM, prompt, max_tokens=120)

120 tokens is plenty for a reply like 3, 1, 7, 2, 5. It was plenty when I wrote it.

Claude 5 models think before they answer, and that thinking counts against max_tokens. They used up all 120 tokens thinking and stopped before writing a single number. The provider raised an error. The reranker caught it, as designed:

except Exception:
    return list(docs)   # never worse than the maths alone

and handed back the maths order. No crash, no warning, just the same result you'd get without --rerank.

Why the tests didn't catch it

tests/test_rerank.py had good tests. A model that errors leaves the order alone. A model that replies with garbage leaves the order alone. A model can't promote a table the caller isn't allowed to see. All true, all passing.

Every one of them tested that failing is safe. None tested that the model actually got to answer. When the fallback is designed to be invisible, total failure looks exactly like success.

The fix

max_tokens is now 1024. The test suite now has a fake model that refuses to answer below 512 tokens, so if anyone lowers it again, a test fails. And the benchmark now logs whether the model answered on each question, not just the final score.

I checked it on the released package, not my working copy: 1.4.4 from PyPI got 1 of 3 model answers and changed 0 of 3 orders; 1.5.0 got 3 of 3 answers and changed 2 orders.

Was it worth fixing? The numbers

Once it actually ran, I measured it for the first time on public benchmarks. Every database from Spider dev (876 tables) and BIRD dev in one pool, no hint which database a question belongs to. A question counts as a hit if every table the correct SQL uses is in the top 5.

set     questions   maths order   reranked   only rerank right   only maths right
spider  1034        80.3%         96.5%      169                 1
bird    1534        88.6%         96.7%      125                 1

Paired McNemar test, p around 1e-49 on Spider and 1e-36 on BIRD. It's the largest single gain in the benchmarks file.

What this measures: the right tables reaching the prompt, not whether the final SQL was correct. One model. Spider and BIRD are public, so the model may have seen them in training. It only reorders tables the maths already shortlisted, and it sees table names and short summaries, not the answer SQL.

The lesson I'm keeping

If you build a "safe fallback", also log when it fires. Otherwise the feature can be 100% broken and your dashboard still looks fine.

Try it: pip install "schemagate[ai]", then schemagate demo "which customers owe us money" --rerank --provider anthropic --model claude-haiku-5-5. In the MCP server, set SCHEMAGATE_MCP_RERANK=1. Code and full benchmark: https://github.com/ashishsinha1602/schemagate

Four supply-chain badges for a one-person open-source project, in a day

Ashish sinha — Sat, 10 Oct 2026 23:43:25 +0000

I maintain schemagate, an open-source library and MCP server that stops an AI agent from seeing database tables the user isn't allowed to read. It's a security tool, so "trust me" isn't good enough. A security team looking at it reasonably asks: is the project run properly, is the licensing clean, and is the package on PyPI really built from this repository?

There are free, public answers to all three questions. This week I went through them in one sitting. Here's what each one took, including the parts that didn't go as expected.

1. OpenSSF Best Practices: Passing

bestpractices.dev is the Linux Foundation's self-certification: about 60 criteria covering how you take bug reports, how you handle vulnerabilities, whether you test, whether you use static analysis. You sign in with GitHub and it pre-fills what it can detect (licence, HTTPS, public repo).

The honest part is that every answer needs a justification, and most of mine were links to files that already existed: SECURITY.md for private vulnerability reporting, CONTRIBUTING.md for the test policy, CodeQL for static analysis. Before answering "no open static-analysis findings", I checked the code-scanning API instead of assuming. CodeQL had zero alerts. The 19 open alerts were all Scorecard repository-settings findings, which is a different claim.

One criterion I answered "Unmet": dynamic analysis. There's no fuzzer yet. It's only suggested at this level, and a badge with an honest "no" in it is worth more than one without.

Result: passing, 100%.

2. OpenSSF Baseline Level 1

Baseline is the newer, shorter OpenSSF checklist, aimed at security controls. Almost everything was already true. The one gap was branch protection: main required CI to pass but didn't stop a direct push. Requiring a pull request (zero approvals, so a solo maintainer can still merge) and turning off the admin bypass closed it.

Result: baseline-1, 100%.

3. REUSE compliance (FSFE)

REUSE checks that every file in the repository has machine-readable copyright and licence information. That matters to anyone whose legal team scans dependencies. You don't have to add a header to every file: one REUSE.toml can annotate whole globs.

Running reuse lint was the useful part, because it made me find the third-party code I'd forgotten about:

a bundled BLAKE2b implementation (blakejs, CC0-1.0), inlined into three files, not the one I remembered;
a test fixture generated from the MCP registry's schema, a project that's partway through relicensing from MIT to Apache-2.0, so I declared both.

Then you register at api.reuse.software, confirm by email, and their checker runs against the public repo.

Result: compliant, 238 of 238 files.

4. SLSA Build Level 3 provenance

This is the one that answers "is the file on PyPI really built from this repo". I used the official slsa-github-generator. Its generic generator runs as an isolated reusable workflow and signs a provenance statement for whatever hashes you give it.

The design choice that matters is which hashes. I didn't rebuild the package in the provenance job. After the publish workflow finishes, a separate workflow downloads the exact wheel and sdist PyPI serves, checks them against PyPI's own sha256 digests, and attests those. The provenance then names the files people actually install.

Then I verified it the way a user would, with the official slsa-verifier, after checking the verifier binary's own hash against its published checksums. Both files from PyPI passed. A deliberately tampered file failed, and so did a wrong source repository.

The gotcha: my docs told users to verify with --source-tag v1.3.1, and that fails. A workflow triggered by workflow_run runs on the default branch, so the provenance records refs/heads/main and the commit, not the tag. --source-branch main passes, and the commit it names is the one the tag points to. If you trigger provenance after publish, test the verification command you document, not just the generation.

What I'd tell another solo maintainer

Do them in this order. Passing makes you write the policies, Baseline makes you lock down the repository, REUSE makes you find code you didn't write, and SLSA proves the build. Each step makes the next one easier.
Check every claim against the system, not your memory. Two of my assumptions were wrong: the third-party file count, and the verification flag.
Answer "Unmet" when it's unmet. I'm still at 98% on OpenSSF Silver because it requires a second maintainer, and I'd rather show that than fake it.
A security process gets used. While writing the reply to a review comment I found a real bug, handled it through a private GitHub security advisory, and shipped the fix in a release with an advisory. One practical note: an advisory's temporary private fork doesn't run your CI, so run the full suite locally before merging from it.

The badges are on the README: https://github.com/ashishsinha1602/schemagate. Everything above is reproducible with free tools, and a weekend is plenty.

I gave my text-to-SQL agent a business glossary. One version helped a lot, one did nothing.

Ashish sinha — Mon, 05 Oct 2026 01:23:14 +0000

A text-to-SQL agent sees table and column names. It does not know that "revenue" in your company means billing_invoice.total_net, only for invoices with status = 'issued'. So it guesses, and the guess runs without errors and returns the wrong number.

I added business terms to schemagate 1.2.0 (open source, Apache-2.0) and measured whether they help. Short answer: a complete glossary helps a lot. A glossary learned from other people's questions did not help at all. Here are both results.

What a term looks like

cat.concept(
    "revenue",
    synonyms=["turnover", "sales"],
    maps=["billing_invoice.total_net"],
    filter="billing_invoice.status = 'issued'",
)

When a question uses "turnover", two things happen:

billing_invoice is pulled into the few tables the model sees, even if no table name looks like "turnover".
A line goes into the prompt, above the DDL:

-- What the business terms in this question mean:
-- revenue (turnover): billing_invoice.total_net; rule: billing_invoice.status = 'issued'

Terms can also have broader and narrower terms ("net revenue" is a kind of "revenue"), which pulls in related tables at half weight.

You don't have to type them by hand. You can import them from a dbt semantic manifest, a Snowflake semantic model, or a CSV export from Collibra or Purview:

schemagate ontology import semantic_manifest.json --from dbt --url $DB --config catalog.json --save

Or learn them from past (question, SQL) pairs: if "refunds" keeps showing up in questions whose SQL reads billing_credit_note, that becomes a suggested term.

The part I care about: access control

schemagate's main job is hiding tables and columns a caller may not read, before the model sees anything. A glossary could leak them right back: "pay means hr_compensation.annual_amount" tells a payroll clerk that the column exists.

So a meaning line is dropped for any caller who can't see every table and column it names. The term still exists. That caller just never gets its line.

Results

BIRD dev, 201 questions, Claude Sonnet 5, execution accuracy, the hint text withheld:

Setup	Accuracy
No terms	44.8%
No terms, run again (noise check)	46.8%
Terms learned from other questions' hints	46.8%
Complete glossary for this database	54.7%
The question's own hint pasted in (ceiling)	61.2%

The complete glossary won 28 questions and lost 8 against no terms (McNemar p = 0.001). That's real.

Terms learned from other questions did nothing: 46.8%, the same as running the baseline twice. BIRD's hints are mostly one-off formulas ("percentage = count where x / count"), and a formula from one question rarely fits the next. I'm reporting this because it's the result you would hit if you expected a glossary to build itself.

Retrieval, Spider, 876 tables pooled: terms learned from half the questions raised recall@5 on the other half from 73.3% to 82.8% (49 questions better, 0 worse). Finding the right tables is where learned terms do help.

What I'd take from it

Write down the 20-50 terms people really argue about (revenue, active customer, churn). That's where the 10 points came from.
Learned terms are good for finding tables, not for teaching formulas.
Whatever you add to the prompt, check it against the caller's permissions too.

Try it

pip install "schemagate[mcp]"
SCHEMAGATE_DATABASE_URL=demo python -m schemagate.mcp_server

Works with Postgres, Oracle, MySQL, SQL Server and SQLite, as a Python library, a LangChain retriever, or an MCP server for Claude and Cursor.

GitHub: https://github.com/ashishsinha1602/schemagate · In-browser demo: https://ashishsinha1602.github.io/schemagate/

If you run an agent over a real warehouse: how many business terms would you need to write down before it stopped guessing?

Hiding the table is not enough. Your LLM can still see the salary column.

Ashish sinha — Sat, 03 Oct 2026 20:36:51 +0000

Most text-to-SQL setups protect data at the table level. The analyst cannot read hr_compensation, so that table never goes into the prompt. Good.

But a lot of sensitive data does not live in its own table. It lives in one column of a table everyone uses. employees.salary. customers.national_id. patients.diagnosis. If the model sees the column name in the schema, it will happily write SELECT salary FROM employees. Your database might block the query, or it might not. Either way, the model has already learned the column exists.

schemagate 1.1.0 adds column rules you can write in a JSON file:

{
  "restrict_column": {
    "employees": {
      "salary": ["hr"],
      "national_id": ["hr", "compliance"]
    }
  }
}

A caller without the hr role now gets the employees table with salary simply not there. Not marked secret. Not ranked low. Absent. The model cannot write a query against a column it was never shown.

Try it in one minute

pip install schemagate

# the bundled demo database; this prints its sqlite:/// URL
python -c "from schemagate.demo_schema import create_demo_db; print(create_demo_db())"

echo '{"restrict_column": {"hr_compensation": {"annual_amount": ["hr"]}}}' > catalog.json

schemagate select --url <url printed above> --prompt "salary by employee"
schemagate select --url <url printed above> --config catalog.json --prompt "salary by employee"

The first prompt shows annual_amount. The second does not.

The same file works everywhere

The command line, the local Studio page and the MCP server all read the same catalog.json. So if you connect Claude, Cursor or any MCP client to your database through schemagate, the column rule applies there too. You state the policy once.

A typo is an error, not a silent no-op

This one matters more than it sounds. Before 1.1.0, a block the loader did not know was quietly ignored. Write "restrict_columns" with an extra s, and you had a file that looked like a policy and did nothing.

Now it stops with one line:

schemagate: --config catalog.json: unknown block(s) 'restrict_columns'; expected any of restrict, restrict_column, hint, describe, groups

The same goes for a table or column name that does not exist. An access rule that "succeeds" on a typo is a rule that is not there.

Where this fits

schemagate is the step before the model writes SQL: it picks the few tables a question needs, and it drops the ones the caller may not read. That keeps prompts about 75% smaller on a big schema, and it means the model never sees what it should not.

It works with Postgres, Oracle, MySQL, SQL Server and SQLite, as a Python library, a CLI, an MCP server, or a LangChain retriever (now listed in the LangChain docs).

GitHub: https://github.com/ashishsinha1602/schemagate
Browser demo, no install: https://ashishsinha1602.github.io/schemagate/
pip install schemagate

If you protect columns some other way in your text-to-SQL stack, I would like to hear how. Views per role? Column-level grants? Masking in the database?

Your RLS policy returns zero rows. The view next to it returns two.

Ashish sinha — Fri, 02 Oct 2026 06:00:24 +0000

I have a row-level security policy that denies a user every row in a table. The
user queries the table and gets nothing, which is right. The user queries a view
over that same table and gets real salary data back.

This is documented PostgreSQL behaviour, not a bug, and it is six years older
than the option that fixes it. I went looking for it because I write a library
that decides which tables to show a language model, and I wanted to know whether
"you don't have the grant" was the same as "you can't read it". It isn't.

Here is the whole thing, on PostgreSQL 16.13. Paste it into a scratch database.
Note that roles are cluster-wide, not per-database, so if you run this twice you
will need the teardown at the bottom first.

CREATE TABLE employee_salary (id int primary key, name text, dept text, salary numeric);
INSERT INTO employee_salary VALUES (1,'Ann','SALES',91000),(2,'Bob','SALES',87000),
                                   (3,'Cy','ENG',120000),(4,'Di','HR',70000);
ALTER TABLE employee_salary ENABLE ROW LEVEL SECURITY;

CREATE ROLE sgrls_r1 LOGIN;
CREATE ROLE sgrls_r2 LOGIN;
CREATE POLICY p_r1 ON employee_salary FOR SELECT TO sgrls_r1 USING (dept = 'SALES');
CREATE POLICY p_r2 ON employee_salary FOR SELECT TO sgrls_r2 USING (false);

CREATE VIEW v_sales_salary AS
    SELECT * FROM employee_salary WHERE dept = 'SALES';

CREATE VIEW v_sales_salary_si WITH (security_invoker = true) AS
    SELECT * FROM employee_salary WHERE dept = 'SALES';

GRANT USAGE ON SCHEMA public TO sgrls_r1, sgrls_r2;
GRANT SELECT ON employee_salary, v_sales_salary, v_sales_salary_si TO sgrls_r1, sgrls_r2;

sgrls_r2 has the policy USING (false). Not a narrow policy. False. No row
can satisfy it.

postgres=> \c - sgrls_r2

postgres=> SELECT * FROM employee_salary;
 id | name | dept | salary
----+------+------+--------
(0 rows)

Correct. Now the view:

postgres=> SELECT * FROM v_sales_salary;
 id | name | dept  | salary
----+------+-------+--------
  1 | Ann  | SALES |  91000
  2 | Bob  | SALES |  87000
(2 rows)

Two rows, with the salaries in them, to a role whose policy admits nothing.

Why

A view executes with the privileges of its owner, not its caller. The owner
here is postgres, who is exempt from the policy. The RLS check runs as the
owner, passes, and the rows come back. The caller's own policy is never
consulted, because the caller never touched the table — the view did.

security_invoker = true flips that: the view reads the base table as the
caller, so the caller's policy applies. The second view in the setup has it, and
the same role gets zero rows from it.

as `sgrls_r2`, policy `USING (false)`	rows
`SELECT * FROM employee_salary`	0
`SELECT * FROM v_sales_salary`	2
`SELECT * FROM v_sales_salary_si`	0

None of this is undocumented. CREATE POLICY says it plainly:

permission checks and policies for the tables which are referenced by a view
will use the view owner's rights and any policies which apply to the view
owner, except if the view is defined using the security_invoker option

And the PostgreSQL 15 release note that introduced the option is blunter still:

Previously, view accesses were always treated as being done by the view's
owner. That's still the default.

The problem is the timeline. Row-level security arrived in PostgreSQL 9.5, released
7 January 2016. security_invoker arrived in PostgreSQL 15, in 2022. For six years
there was no way to make a view respect the caller's policy, so every view written
in that window runs as its owner — and, per the release note above, that is still
what you get unless you opt in.

If your schema has RLS on the tables and views on top of them, and nobody went
back and added security_invoker, the views are a way around the policy for
anyone holding SELECT on them.

Find out whether you have this

This lists views that read an RLS-enabled table and do not have
security_invoker. Run it as a superuser or table owner.

SELECT n.nspname                   AS schema,
       c.relname                   AS view,
       pg_get_userbyid(c.relowner) AS runs_as,
       t.relname                   AS rls_table
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_rewrite  r  ON r.ev_class = c.oid
JOIN pg_depend   d  ON d.objid = r.oid
                   AND d.classid    = 'pg_rewrite'::regclass
                   AND d.refclassid = 'pg_class'::regclass
JOIN pg_class    t  ON t.oid = d.refobjid
                   AND t.relrowsecurity
                   AND t.oid <> c.oid
WHERE c.relkind = 'v'
  AND n.nspname NOT IN ('pg_catalog','information_schema')
  AND NOT coalesce(c.reloptions::text[] @> ARRAY['security_invoker=true'], false)
GROUP BY 1,2,3,4
ORDER BY 1,2;

On the schema above it returns exactly v_sales_salary and nothing else. I
checked it against decoys: a view over a table with no RLS is not flagged, and
neither is a security_invoker view.

One limitation, so you don't trust it further than it goes. It follows a
single dependency hop. A view over a view over an RLS table also leaks, and
this query will not report it. If you nest views, walk the chain. I would rather
tell you that than have you run it, get an empty result, and conclude you are
fine.

The part that caught me out

I expected the catalogue to tell me something. It does not. Under both roles —
the one that reads two rows and the one that reads none — employee_salary is
present in information_schema.tables, and all four columns are in
information_schema.columns. Both roles hold SELECT in the catalogue. The
grant says yes, the policy says no, and the catalogue believes the grant.

I ran the same shape of test on Oracle AI Database 26ai (23.26.3.3.0) with a
DBMS_RLS policy of 1 = 0. Same result: zero readable rows, still in
ALL_TABLES, SELECT still in ALL_TAB_PRIVS, four columns in
ALL_TAB_COLUMNS. Oracle has no security_invoker equivalent for this; there I
had to use proxy authentication (ADMIN[user]) to see the per-caller row counts
at all.

So if you are building anything that decides what a caller may read by querying
the data dictionary — an access audit, a data catalogue, a prompt builder for a
SQL agent — the dictionary will overstate it. RLS is a row filter, not a
privilege, and privileges are the only thing the dictionary records. That is
defensible behaviour on the database's part and completely wrong as an input to
"what is this user allowed to see".

MySQL has the same default and cannot have the same bug

I tested MariaDB 10.11.14 too, because MySQL-family views have the identical
owner-vs-caller split: SQL SECURITY DEFINER against SQL SECURITY INVOKER.

A view created with no SQL SECURITY clause comes out DEFINER:

CREATE VIEW v_default AS SELECT * FROM employee_salary WHERE dept='SALES';

SELECT table_name, security_type
FROM   information_schema.views
WHERE  table_schema = DATABASE();
-- v_default  DEFINER

So the default is owner-rights there as well, and a user with no privilege at all
on employee_salary reads real salaries straight through it:

MariaDB [sgtest]> SELECT * FROM v_default;      -- as a user with no grant on the table
+----+------+-------+----------+
| id | name | dept  | salary   |
+----+------+-------+----------+
|  1 | Ann  | SALES | 91000.00 |
|  2 | Bob  | SALES | 87000.00 |
+----+------+-------+----------+

The INVOKER version of the same view refuses, as it should:

ERROR 1142 (42000): SELECT command denied to user 'sgrls_r2'@'localhost'
for table `sgtest`.`employee_salary`

But the dictionary behaves differently from PostgreSQL, and this is the part worth
knowing. For that same user, employee_salary is simply not there:

as a user with no grant on the table	MariaDB	PostgreSQL
rows from the DEFINER / owner-rights view	2	2
`information_schema.tables` for the base table	0	1
`information_schema.columns` for the base table	0	4

MariaDB's information_schema is privilege-filtered, so it never claims you can read
something you cannot. PostgreSQL showed the table because the caller genuinely held
SELECT — the row policy denied it separately, and the dictionary has no column for
that.

Which gives the honest answer to my own question: MySQL's dictionary cannot be wrong
about row-level policies, because MySQL has none. Hold SELECT and you read every
row — I confirmed it, 4 of 4, for a user who was "supposed" to see only SALES. The only
way to filter rows per caller is a DEFINER view, and then the dictionary is correct
about the view and silent about the filter. Accurate, but not informative.

Teardown

Roles outlive the database, so dropping the database is not enough:

DROP VIEW IF EXISTS v_sales_salary, v_sales_salary_si;
DROP TABLE IF EXISTS employee_salary;
DROP OWNED BY sgrls_r1;  DROP ROLE sgrls_r1;
DROP OWNED BY sgrls_r2;  DROP ROLE sgrls_r2;

Two things I would like to know

If you run the audit query on a real schema, what does it return? I am most interested in people who get a non-empty result and did not expect one.
Is there an engine where the data dictionary does reflect row-level policies, rather than just grants? I have now tested PostgreSQL, Oracle and MariaDB; none of them do, for the different reasons above. SQL Server I have not tested — it has real RLS via security policies, so it is the interesting case, and I would rather be told than guess.

The reason I went digging: I maintain schemagate,
which picks the tables a SQL agent is allowed to see. It had a mode that derived
that list from grants. The test above is why that mode is no longer the secure
one — the fail-closed path is explicit per-caller rules, not the dictionary.
Writeup of the full test, both engines:
row-level security.

Your LangChain SQL agent sends the model every table, including the ones that caller can't read

Ashish sinha — Wed, 30 Sep 2026 03:40:50 +0000

A LangChain SQL agent hands the model your schema and asks it to write SQL. On a demo database with eight tables that is fine. On a real one it breaks in two ways at once.

The schema outgrows the context window. I wrote about this before, on a warehouse with 1,245 tables — the table listing alone did not fit, and describing every table with an LLM to help retrieval made it worse, not better.

And the model sees tables the caller is not allowed to read. This one is quieter and worse. SQLDatabase.get_table_info() returns what the connection can see, not what the person asking can see. If your app connects as one service account and serves twenty users, every user's agent gets the full schema: salaries, PII, all of it. The model may never write SQL against those tables. It was still told they exist, and their column names went into the prompt.

So I wrote a retriever.

SchemagateRetriever

from schemagate import Catalog, Principal
from schemagate.integrations.langchain import SchemagateRetriever

cat = Catalog().bootstrap("postgresql://localhost/app")

retriever = SchemagateRetriever(
    catalog=cat,
    top_k=6,
    principal=Principal("okta:jdoe", roles={"finance"}),
)

docs = retriever.invoke("revenue by month")

It subclasses BaseRetriever, so it drops into any chain that already takes a retriever. Each selected object comes back as one Document: the DDL in page_content, and the name, kind, score and selection reason in metadata.

Two things happen before the model sees anything. The caller's grants decide which objects are candidates at all. Then the question decides which of those are relevant. A table jdoe cannot read is not ranked and then filtered out — it never enters the ranking.

The principal is bound at construction, not passed per call

This is the part I would push back on in someone else's library, so here is the reasoning.

SchemagateRetriever(catalog=cat, principal=p)   # identity fixed here
retriever.invoke(question)                      # not here

A retriever is usually built once per request. Binding identity to the object means a chain cannot forget to pass it. There is no call signature where the principal is optional and quietly defaults to everything. If you want a different caller, build another retriever — they are cheap.

The alternative, invoke(question, principal=...), has one failure mode I did not want to ship: somebody omits the argument, the call still succeeds, and it returns the whole schema. Fail-closed beats convenient.

Install

pip install 'schemagate[langchain]'

Needs langchain-core>=0.3, and it is an optional extra — if you do not use LangChain you do not pay for it.

The page, with the longer version: https://ashishsinha1602.github.io/schemagate/langchain/
Repo, Apache-2.0: https://github.com/ashishsinha1602/schemagate

Postgres, Oracle, MySQL and SQL Server. On Oracle it reads VPD policies rather than inferring from grants.

If you are running a SQL agent against a database where different callers should see different tables, I would like to know how you handle it today — especially if the answer is "one service account and we hope". That was the answer where I work, which is why this exists.