Generating SQL Safely: Least Privilege Beats Better Prompts

Text-to-SQL has two failure modes and they need completely different defences. The first is the obvious one: a generated query that deletes data or reads a table it should not. The second is quieter and far more common: a query that runs fine, returns a number, and the number is wrong.

Prompt engineering does not solve either. Database permissions solve the first, and design choices about what you show the user solve the second.

The database enforces safety, not the prompt

Instructions like "only generate SELECT statements" are a hint, not a control. They can be overridden by content in the data you show the model, by an unusual phrasing, or by nothing in particular. Assume the model will eventually emit a DROP, and make that harmless.

Connect through a dedicated role that cannot write, and grant it only the tables it needs:

CREATE ROLE analytics_ro LOGIN PASSWORD '...';
REVOKE ALL ON SCHEMA public FROM analytics_ro;
GRANT USAGE ON SCHEMA analytics TO analytics_ro;
GRANT SELECT ON analytics.orders, analytics.customers TO analytics_ro;

ALTER ROLE analytics_ro SET statement_timeout = '10s';
ALTER ROLE analytics_ro SET default_transaction_read_only = on;
ALTER ROLE analytics_ro SET search_path = analytics;

A read-only transaction in PostgreSQL disallows INSERT, UPDATE, DELETE, MERGE and COPY FROM into non-temporary tables, all CREATE, ALTER and DROP commands, plus COMMENT, GRANT, REVOKE and TRUNCATE. It also blocks EXPLAIN ANALYZE and EXECUTE when the underlying command is one of those.

One caveat worth knowing: default_transaction_read_only is a default, and a session can turn it off for itself. It is a guard against accident, not a security boundary. The security boundary is the grants — a role with no INSERT privilege cannot insert regardless of transaction mode. Set both, and rely on the grants.

Belt and braces at the connection level costs nothing:

BEGIN READ ONLY;
SET LOCAL statement_timeout = '10s';
-- generated query here
COMMIT;

Parse the query before you run it

Permissions stop damage. Parsing stops surprises, and gives you a much better error message than a permission denial.

Use a real SQL parser rather than a regular expression — sqlglot, pglast, or your driver parse tree. String matching for the word DELETE fails on a column named deleted_at and passes a statement hidden in a CTE.

Reject anything that is not a single statement. Multiple statements separated by semicolons are the classic path from a benign SELECT to something else, and there is no legitimate reason for a generated analytics query to contain two.

Then check the parse tree: the root node must be a select, every referenced table must be on your allowlist, and no CTE may contain a data-modifying statement — PostgreSQL supports writable CTEs, so an INSERT can hide inside what looks like a WITH clause.

import sqlglot
from sqlglot import exp

def validate(sql: str, allowed: set[str]) -> str:
    statements = sqlglot.parse(sql, read="postgres")
    if len(statements) != 1:
        raise ValueError("exactly one statement required")
    tree = statements[0]
    if not isinstance(tree, exp.Select) and not isinstance(tree, exp.Subquery):
        raise ValueError(f"only SELECT allowed, got {type(tree).__name__}")
    for node in tree.find_all(exp.Table):
        name = f"{node.db or 'analytics'}.{node.name}"
        if name not in allowed:
            raise ValueError(f"table not permitted: {name}")
    return tree.sql(dialect="postgres")

Bound the cost before execution

A join with a missing predicate against two large tables will happily consume your database. A statement timeout catches it, but only after it has held resources for the full ten seconds, and a handful of concurrent ones will still hurt.

Run EXPLAIN first — without ANALYZE, which executes — and reject on estimated cost or estimated rows:

EXPLAIN (FORMAT JSON) SELECT ...;

The plan gives you a total cost estimate and a row estimate. Pick thresholds from your own workload and refuse anything above them with a message asking the user to narrow the question. Estimates are imperfect, but they catch cartesian products reliably, which is the case that matters.

Also wrap every query in a hard row limit so a legitimate query does not return four million rows into your application memory. Appending a limit to the outer select is easy to do on the parse tree and harder to get wrong than asking the model to include one.

The wrong-answer problem

Everything above is the easy half. The hard half is that a syntactically valid query against the right tables can still answer the wrong question, and the output looks identical to a correct one.

The recurring causes are structural, not linguistic. Joining a fact table to a dimension that has multiple rows per key silently multiplies your totals. Filtering on a nullable column excludes the null rows the user assumed were included. Using a column named status without knowing that soft-deleted records also carry a status. Aggregating over a timestamp without accounting for timezone.

None of these produce an error. All of them produce a plausible number.

Make the schema description do the work

Most wrong answers come from missing semantics, not from a weak model. The schema dump tells it column names and types; it does not tell it that orders.total is in cents, that rows with deleted_at set must be excluded, or that customers has one row per version and you must filter to the current one.

Write that down and put it in the prompt. A curated schema description with a sentence per non-obvious column and three or four worked example queries outperforms almost any other intervention. Use column and table comments in the database itself so the description stays close to the schema and can be extracted programmatically.

Better still, do not expose raw tables. Build views that encode the joins and filters correctly and grant access only to those. The model then cannot get the join grain wrong because the join is not its decision. This is more work up front and it converts an open-ended correctness problem into a bounded one.

Always show the query

If a human cannot see the SQL, they cannot catch the wrong-answer case, and they will present the number in a meeting.

Display the generated query alongside the result, with the row count and the execution time. For anything consequential, require an explicit confirmation before running. It feels like friction; it is the only place a semantic error can realistically be caught.

Log every generated query with the natural-language question that produced it, the user, and the result size. That log is how you find the systematic mistakes — you will notice the same wrong join appearing across a dozen questions, and that tells you which view to build next.

Treat data as untrusted input

If the model sees query results, or column comments, or user-supplied names in the data, then the data is part of the prompt. A row containing text that reads like an instruction is a known technique, and the model has no reliable way to distinguish it from your own instructions.

The defence is architectural rather than textual. The query validator runs on every generated query regardless of where the intent came from, the database role cannot write no matter what the model was persuaded to emit, and no generated string is ever executed outside that path.

A deployment checklist

1. Dedicated role with SELECT on an explicit table list, nothing else.
2. statement_timeout and read-only default set on the role.
3. Parse with a real SQL parser; reject multiple statements and non-selects.
4. Allowlist tables from the parse tree, including inside CTEs.
5. EXPLAIN gate on estimated cost and rows.
6. Hard outer row limit applied by your code, not by the prompt.
7. Curated schema semantics and example queries in the prompt.
8. Views that encode correct joins wherever grain is non-obvious.
9. Query shown to the user; every query logged with its question.

The order matters. Get the permissions right first, because they are the only layer that holds when everything else is bypassed, and they take an afternoon.