Natural-language database queries without opening the database to the model? WZ-IT builds AI agents and assistants that read approved data through restricted roles, views and validated queries, operated on your own infrastructure. The AI process assessment reviews process, data sources, permissions and risks up front. Explore AI agents · Book a call
Text-to-SQL lets a language model turn a question such as "Which customers had more than €50,000 in revenue in the third quarter?" into an SQL query and run it against the database. This is an obvious fit for ERP, CRM and controlling data, because the answers are not in documents but in tables. The difficulty lies in two places: the model has to understand the schema correctly, and it must not do anything to the database that nobody approved. This article explains the architecture, the permissions in PostgreSQL, the role of MCP connectors and how to measure quality. As of October 2026.
Table of contents
- How text-to-SQL works
- Benchmarks and practice: Spider and BIRD
- The pipeline: context, generation, validation, execution
- Database permissions as the real boundary
- Views as a semantic layer
- Free SQL or predefined queries
- MCP database connectors
- Risks: prompt injection, data leakage, load
- Measuring quality
- Local model and operations
How text-to-SQL works
The language model receives three things: the question, a description of the available tables and columns, and instructions on the SQL dialect. From these it writes a query. The application executes it and returns the result, as a table or summarised in a sentence.
The difference from RAG is fundamental. RAG retrieves text passages and lets the model phrase an answer. In text-to-SQL, the database does the computing. Sums, averages and counts are exact, provided the query is right. That is exactly where the risk lies: a wrong query returns a wrong number that looks just as convincing as a correct one.
| Question | Suitable approach | Reason |
|---|---|---|
| Revenue by region last quarter | Text-to-SQL | Aggregation over structured data |
| Open orders for a customer | Text-to-SQL or predefined query | Filter on known fields |
| What does the framework agreement say about termination? | RAG | The answer is in a document |
| Why did revenue drop? | Neither alone | Requires interpretation; data only provides indications |
Benchmarks and practice: Spider and BIRD
Two benchmarks shape the discussion. Above all, their numbers show how strongly results depend on the difficulty of the databases.
| Benchmark | Scope | What it measures | Figure |
|---|---|---|---|
| Spider 1.0 (EMNLP 2018) | 10,181 questions, 5,693 SQL queries, 200 databases, 138 domains | Generalisation to unseen schemas | o1-preview: 91.2% |
| BIRD (NeurIPS 2023) | 12,751 question-SQL pairs, 95 databases, 33.4 GB, 37 professional domains | Dirty data, external domain knowledge, efficiency | Humans 92.96%, best system 82.95% (GrainSQL, submitted 7 September 2026; as of October 2026) |
| Spider 2.0 (ICLR 2025) | 632 tasks from enterprise environments, databases often with more than 1,000 columns | BigQuery, Snowflake, SQLite; queries often exceed 100 lines | o1-preview: 21.3% |
Sources for the top scores: BIRD leaderboard; Spider figures from the Spider 2.0 paper, which compares o1-preview across all three benchmarks (73.0% on BIRD).
Two points follow for practice. First, accuracy drops considerably once schemas are large, messy and domain-heavy, which is exactly what grown ERP systems look like. Second, benchmark scores come from public datasets that models may have seen during training. On your own schema, your own test set decides, not the leaderboard.
The pipeline: context, generation, validation, execution
A robust text-to-SQL application consists of more than a prompt. A five-step flow has proven itself:
| Step | Task | Tool or mechanism |
|---|---|---|
| 1. Context | Select relevant tables, columns, comments and example queries | Schema description; for large schemas, search over table descriptions |
| 2. Generation | Model writes exactly one SELECT query | System prompt with dialect, rules, examples |
| 3. Validation | Parse the query, check statement type and tables, enforce a limit | SQL parser, allowlist |
| 4. Execution | Run the query with a restricted role | Dedicated database role, timeout, row limit |
| 5. Output | Show the result and the executed query | Interface with SQL view, logging |
Context. A model does not need the whole schema, only the relevant tables with understandable descriptions. Column comments (COMMENT ON COLUMN) and two or three example queries per subject area improve results noticeably. With schemas of hundreds of tables, the selection itself becomes a retrieval task, similar to retrieval in RAG.
Validation. The generated query is parsed before execution, not searched for "DELETE" as text. SQLGlot (MIT licence) is a Python parser for more than 30 SQL dialects, including PostgreSQL. The syntax tree allows the rules to be checked properly: exactly one statement, SELECT only, only tables and views from the allowlist, a LIMIT added or capped. An EXPLAIN before execution also shows whether a query is likely to be expensive.
Output. The application displays the executed query alongside the result. Anyone using the number can then see which filters and joins are behind it. This is the most effective protection against plausible-looking wrong results.
Database permissions as the real boundary
A language model can generate any SQL statement. Prompt rules such as "only write SELECT" lower the probability but prevent nothing. What actually happens is decided by the privileges of the database role under which the query runs. That role is therefore the real security boundary; all other measures complement it.
An example for PostgreSQL:
-- Dedicated schema containing only approved views
CREATE SCHEMA ai;
CREATE ROLE ai_reader LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA ai TO ai_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA ai TO ai_reader;
-- Grant no privileges on base tables in other schemas
-- Convenience defaults, not a security boundary
ALTER ROLE ai_reader SET default_transaction_read_only = on;
ALTER ROLE ai_reader SET statement_timeout = '15s';
Four details matter:
| Point | Background | Consequence |
|---|---|---|
| Session parameters | default_transaction_read_only and statement_timeout are defaults that a session can change itself with SET (PostgreSQL, Client Connection Defaults) |
Protection comes from missing write privileges; validation rejects SET statements, and the application enforces its own timeout |
| Functions | PUBLIC receives EXECUTE on new functions and procedures by default (PostgreSQL, Privileges) | Review functions with side effects, especially SECURITY DEFINER, and revoke EXECUTE selectively |
| pg_read_all_data | This predefined role reads all tables, views and sequences (PostgreSQL, Predefined Roles) | Unsuitable for text-to-SQL because it removes the selection of data |
| Row-level security | By default, views check privileges as the view owner; security_invoker (since PostgreSQL 15) checks as the calling user (PostgreSQL, CREATE VIEW) |
If you use RLS on base tables, set security_invoker = true, otherwise the owner's policies apply |
Where load on the production database is a concern, queries run on a replica. Connections to a hot standby in PostgreSQL are strictly read-only; not even temporary tables can be written there (PostgreSQL, Hot Standby). For MySQL and MariaDB the same principle applies with dedicated users that receive SELECT only on approved views.
If users should only see their own data, for example a sales representative only their region, that restriction also belongs in the database: through RLS policies or separate views per user group. RAG with permissions describes the same rule for documents: permissions apply before the model, not in its prompt.
Views as a semantic layer
Raw tables are hard to read for people and even harder for a model. Columns such as stat_cd, flag_cncl or dt_3 do not explain themselves, and many tables contain legacy data, test records or intermediate states. A semantic layer translates this into the terms people use when asking questions.
In its simplest form, this is a set of views in a dedicated schema:
| Measure | Effect |
|---|---|
Clear names (open_orders, net_revenue) |
Model maps questions to the right fields |
| Business filters inside the view (e.g. no cancellations, no test customers) | A typical meaning error disappears |
| Predefined metrics (revenue, contribution margin) | One definition instead of many variants in the prompt |
| Comments on views and columns | Passed along as schema context |
| Only the columns needed | Personal or confidential fields stay invisible |
Larger installations use dedicated metrics layers in BI tools for this. Views are enough to start: they can be versioned and tested, and they also limit which data is reachable at all.
Free SQL or predefined queries
Not every application needs free SQL. Often the set of meaningful questions is manageable, and then a collection of parameterised queries is safer and more accurate.
| Approach | How it works | Strengths | Limits |
|---|---|---|---|
| Free SQL | Model writes arbitrary SELECT queries against approved views | Also covers unforeseen questions | Meaning errors possible; validation and tests more demanding |
| Parameterised queries as tools | Model selects a predefined query and fills in parameters | Result always follows reviewed logic, hardly any injection surface | Only anticipated questions can be answered |
| Combination | Frequent questions via tools, the rest via free SQL with a label | Precise where it counts, flexible elsewhere | Two paths to maintain |
With parameterised queries, the model only passes values such as a customer number or a date range. The query itself is fixed and executed with placeholders, which rules out SQL injection through the model's text. For agents that use several systems, this is the usual pattern anyway: one tool per task with clearly limited privileges (AI agents: permissions and approvals).
MCP database connectors
The Model Context Protocol connects assistants to tools, including databases. The available servers differ considerably in their protection mechanisms, as of September 2026:
| Server | Licence | Approach | Note |
|---|---|---|---|
Postgres reference server (@modelcontextprotocol/server-postgres) |
MIT | Free SQL in a read-only transaction | Archived on 29 May 2025; read-only protection bypassable with an injected COMMIT (Datadog Security Labs) |
| Postgres MCP Pro | MIT | Unrestricted and restricted mode | Restricted: read-only transactions, parsing with pglast, COMMIT and ROLLBACK rejected, time limit |
| MCP Toolbox for Databases | Apache 2.0 | Predefined, parameterised SQL tools in tools.yaml, plus generic tools |
Many databases, including PostgreSQL and MySQL |
The reference server illustrates the core problem: it ran queries in a read-only transaction but accepted several statements in a single call. A COMMIT; ended the transaction, and everything after it ran unprotected. According to Datadog, the package was still downloaded around 21,000 times a week via npm when the analysis was published in August 2025. The lesson applies to every connector: protections in the application layer complement database privileges; they do not replace them.
Risks: prompt injection, data leakage, load
The risks map onto the categories of the OWASP Top 10 for LLM Applications 2025:
| Risk | OWASP | Example | Countermeasure |
|---|---|---|---|
| Prompt injection via data | LLM01 | Ticket or comment text contains instructions to the model | No write privileges, no output to third parties, mark data fields as data |
| Improper output handling | LLM05 | Generated SQL is executed without checks | Parser validation, allowlist, restricted role |
| Excessive agency | LLM06 | Connector runs with an admin or service role | Dedicated role with SELECT on views only |
| Sensitive information disclosure | LLM02 | Model reads salary or health data along the way | Do not include such columns in views at all |
| Unbounded consumption | LLM10 | Cartesian product across large tables | Timeout, row limit, EXPLAIN check, replica |
The Supabase MCP case from July 2025 showed what prompt injection through database content looks like in practice: a crafted support ticket told the assistant to read the table of integration tokens and write the contents into the ticket. The assistant ran with the service_role, which bypasses row-level security. Simon Willison calls the combination of access to private data, exposure to untrusted content and a way to send data out the "lethal trifecta". Remove one of the three ingredients and this attack fails. More in Protection against prompt injection.
A related pattern affects tools that generate code from results. In the Vanna library, prompt injection via the visualisation function led to execution of arbitrary Python code (CVE-2024-5565, CVSS 8.1). Charts should therefore be produced from fixed templates, not from model-written code.
Measuring quality
A query that runs without errors is not automatically correct. The most common errors are errors of meaning: cancellations counted, a join that duplicates rows, the wrong date column filtered. Measurement is therefore based on the result.
| Metric | What it measures | Use |
|---|---|---|
| Execution accuracy | Does the generated query return the same result as the reference query? | Main metric, also in BIRD and Spider |
| Exact match | Is the SQL text identical to the reference? | Of limited value, because many formulations are correct |
| Executability | Does the query run without errors? | Only a precondition; says nothing about correctness |
| Abstention rate | Does the system decline questions it cannot answer? | Important against invented numbers |
The test set is built with the domain experts who know the data: typical questions, one reviewed reference query each, plus questions the schema deliberately cannot answer. Every change to the model, prompt or views is measured against this set. Question, generated query, runtime and user feedback are logged, for example with Langfuse. The approach mirrors the one for retrieval systems in Measuring RAG quality.
Local model and operations
Text-to-SQL does not require a model from a cloud provider. With business databases in particular, there are good reasons to keep schema descriptions, questions and results inside your own network. The choice of model depends on complexity: a manageable schema with clean views works with a mid-sized model, while large schemas with many joins benefit from larger models and longer context. Which LLM to self-host? gives an overview of models suitable for self-hosting, and GPU and VRAM sizing for LLMs works out the memory requirements.
A clear separation has proven itself in operations:
| Component | Task |
|---|---|
| Inference (vLLM or Ollama) | Serve the model, enforce structured output |
| Gateway (LiteLLM) | Access, keys, budgets per application |
| Text-to-SQL service | Context, validation, execution with its own role |
| Database or replica | Views, privileges, timeouts |
| Tracing (Langfuse) | Trace questions, queries and results |
The text-to-SQL service holds the database credentials, not the model. The model only sees schema context and, where the answer requires it, the result.
What this means for your project
Text-to-SQL pays off when many recurring questions are asked of the same data and the answers currently come from exports or report requests. The order determines success: first a dedicated role and views with the approved data, then a test set with reference queries, and only then the model and prompt. Where the questions are manageable, parameterised queries are more accurate than free SQL.
WZ-IT implements such applications as AI agents on the open stack, with PostgreSQL or MySQL as the data source, restricted roles and tracing. Models run locally on the AI Cube or on WZ-IT managed GPU servers with an NVIDIA RTX PRO 4000 Blackwell (24 GB) or RTX PRO 6000 Blackwell Max-Q (96 GB). Support, consulting and implementation by WZ-IT.
Related topics: Protection against prompt injection, MCP in the enterprise, AI agents & automation and the AI agent frameworks comparison.











