A safer pattern for AI-generated SQL

The prompt says "only generate SELECT statements." The model complies. It produces this:

WITH removed AS (
  DELETE FROM invoices WHERE status = 'void' RETURNING *
)
SELECT count(*) FROM removed;

It starts with WITH, not DELETE, so a naive prefix check passes. It's still a SELECT at the top level. And it just deleted rows.

This is the central problem with AI-generated SQL: you are not defending against a parser, you are defending against a system that can compose any valid statement from a huge grammar, driven by text that may include user content. Prompt instructions are a suggestion. The database is where the decision actually gets made.

The safer pattern is not a better prompt or a smarter regex. It's a stack of independent controls, each of which assumes the layer above it failed.

The threats are broader than SQL injection

Prompt injection gets the attention, but it's only one entry in the list:

• Destructive statements. DELETE, UPDATE, DROP, TRUNCATE, or a data-modifying CTE hidden behind a SELECT.
• Tenant leakage. A syntactically perfect query that returns another customer's rows because the model guessed a tenant_id or omitted the filter.
• Denial of service. A cartesian join, generate_series(1, 1e9), or an unindexed scan that pins a CPU for minutes.
• Exfiltration. Reading pg_shadow, information_schema, environment-setting functions, or files via pg_read_file where the role allows it.
• Wrong answers presented as facts. A plausible join across unrelated tables produces a confident number that happens to be meaningless.
• Cost blowups. A query that scans a 400 GB table on a warehouse billed per byte.

Most of these are not injection. They're the model doing exactly what it was asked, with a schema it doesn't fully understand.

Least capability beats least text

The instinct is to inspect the SQL string and block the bad parts. That approach has a poor track record because SQL has too many ways to spell the same operation, and because the check has to be perfect every time while the attacker only needs one gap.

A better principle: design the system so that a malicious query has nothing to damage.

If the connection can only read, DELETE fails regardless of how it's spelled. If the role can only see four views, pg_shadow doesn't exist for it. If row-level security is enforced by the database, a missing tenant filter returns nothing rather than everything. Each of those is a hard boundary that doesn't depend on getting a regex right.

String inspection then becomes a performance and quality control, not the security control. That's a much easier thing to build and maintain.

What the model sees is part of the boundary

Before any of the database controls matter, the model has to choose tables and columns. The schema summary you provide shapes that choice more than any instruction.

Dumping pg_dump --schema-only into the prompt is the common mistake. It leaks internal naming, includes tables the model shouldn't touch, and produces a 40,000-token context in which the relevant three views are buried. It also teaches the model that users.password_hash is a legitimate column to select, which is true at the database level and false at the policy level.

A better schema summary is hand-written and small:

v_orders(order_id uuid, customer_id uuid, status text, total_cents integer,
         currency char(3), created_at timestamptz, shipped_at timestamptz)
  status is one of: pending, paid, shipped, refunded, cancelled.
  total_cents is an integer in the minor currency unit; never a float.

v_customers(customer_id uuid, display_name text, country char(2),
            created_at timestamptz)
  display_name is user-editable and may contain any unicode.

v_customer_revenue_monthly(customer_id uuid, month date, revenue_cents bigint)
  month is the first day of the month in UTC.

Three properties make this better than DDL:

• Semantics over types. "total_cents is an integer in the minor unit" prevents the classic 100× error that no amount of validation catches after the fact.
• Enumerated values. Listing statuses removes a whole class of WHERE status = 'complete' mistakes.
• Exclusions are visible. The model never learns that internal_notes exists, so it never asks for it.

Update the summary in the same commit that changes a view. A stale summary is worse than none, because it produces queries that reference columns you removed and errors the model can't fix.

Step 1: a principal that cannot write

Start with a dedicated database role for AI-generated queries. Not the application role, not a role with broad grants, and definitely not the owner of the tables.

CREATE ROLE ai_reader LOGIN PASSWORD :'ai_reader_password';

GRANT CONNECT ON DATABASE analytics TO ai_reader;
GRANT USAGE ON SCHEMA reporting TO ai_reader;

-- Read-only by default, at the role level.
ALTER ROLE ai_reader SET default_transaction_read_only = on;
ALTER ROLE ai_reader SET statement_timeout = '5s';
ALTER ROLE ai_reader SET lock_timeout = '1s';
ALTER ROLE ai_reader SET idle_in_transaction_session_timeout = '10s';
ALTER ROLE ai_reader SET search_path = reporting, pg_catalog;

-- No table access at all.
REVOKE ALL ON ALL TABLES IN SCHEMA reporting FROM ai_reader;

GRANT SELECT ON reporting.v_orders, reporting.v_customers TO ai_reader;

A caveat that matters: default_transaction_read_only is a user-settable parameter. A session can turn it off with SET default_transaction_read_only = off unless the role lacks the privilege to write in the first place. The PostgreSQL documentation is explicit that it is "mainly useful for preventing yourself from accidentally changing data" rather than a security boundary. Treat it as a seatbelt, and rely on grants as the wall.

That's why the REVOKE ALL ON ALL TABLES line matters more than the read-only setting. The role has no write privilege to use even if it flips the flag. It also has no read privilege on base tables, only on the views you explicitly grant.

search_path is pinned for a different reason: without it, a role can be tricked into resolving an unqualified table name to a different schema, including one it controls. Pinning it to reporting, pg_catalog removes that class of confusion.

Step 2: expose views, not tables

Views give you three things at once: a narrow column set, a stable name the model can reason about, and a place to embed filters.

CREATE VIEW reporting.v_orders
WITH (security_barrier = true) AS
SELECT
  o.id,
  o.customer_id,
  o.status,
  o.total_cents,
  o.currency,
  o.created_at,
  o.shipped_at
FROM public.orders o
WHERE o.deleted_at IS NULL;

GRANT SELECT ON reporting.v_orders TO ai_reader;

By default, a view runs with the privileges of its owner, not the caller. That's what lets you grant SELECT on v_orders without granting anything on public.orders (CREATE VIEW). It also means the view owner must have the necessary privileges, which is fine — the owner is a controlled migration role.

If you're on PostgreSQL 15 or later and want the opposite behavior, security_invoker = true makes the view check the caller's privileges instead. Use it when the underlying tables also have grants you want enforced. Use the default (owner's rights) when the view is the entire permission surface.

Two rules for views used by models:

• Name them for the domain, not the schema. v_orders and v_customer_revenue_monthly are easier for a model to choose correctly than orders_base_v2 and mv_rev_agg.
• Never expose columns you wouldn't put in a support ticket. If a column has PII, an internal note, or a soft-delete flag that changes meaning, leave it out of the view. The model can't select what doesn't exist.

Step 3: tenant scoping enforced by the database

Multi-tenant systems need the tenant predicate to be non-optional. There are two reliable ways to do that, and they compose well.

Bake it into the view with a session variable.

CREATE VIEW reporting.v_my_orders
WITH (security_barrier = true) AS
SELECT o.*
FROM public.orders o
WHERE o.tenant_id = current_setting('app.tenant_id')::uuid
  AND o.deleted_at IS NULL;

The application sets the value per transaction:

with conn.transaction():
    conn.execute("SELECT set_config('app.tenant_id', %s, true)", (tenant_id,))
    rows = conn.execute(sql).fetchall()

set_config(..., true) is the function form of SET LOCAL: it applies only to the current transaction and resets at commit or rollback. Using SET LOCAL directly is also fine, but set_config accepts a parameter placeholder, which avoids string interpolation.

The failure mode to watch for: if app.tenant_id is unset, current_setting raises an error rather than returning NULL, which is the behavior you want. A missing tenant should be a hard failure, not an empty result.

Or use row-level security on the base table.

ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY orders_tenant_read ON public.orders
  FOR SELECT TO ai_reader
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

RLS is more robust than a view because it applies to every path that touches the table, including joins the model invents. It costs some query planning overhead and requires care with connection pooling, since the setting is per transaction. With PgBouncer in transaction mode, SET LOCAL inside an explicit transaction is safe; a session-level SET is not.

If you use both, the view and the policy reinforce each other. That redundancy is the point.

Step 4: parse the query for policy, not for proof

At this stage, a destructive statement is already impossible. The parser's job is narrower: reject queries that are legal but out of policy, and produce a clear error the model can learn from.

sqlglot is a good tool for this. It parses many dialects into an AST and lets you inspect statements, tables, and functions. Its own documentation is honest about the limits: the parser is intentionally lenient, it is a transpiler rather than a validator, and a query that parses may still fail at execution (sqlglot docs). Use it for policy checks, and let the database be the authority on validity.

import sqlglot
from sqlglot import exp
from sqlglot.errors import ParseError

ALLOWED_TABLES = {"v_orders", "v_customers", "v_customer_revenue_monthly"}
BANNED_FUNCTIONS = {
    "pg_sleep", "pg_read_file", "pg_ls_dir", "lo_import", "lo_export",
    "dblink", "dblink_exec", "set_config", "current_setting",
}

class QueryRejected(Exception):
    def __init__(self, code: str, detail: str):
        self.code = code
        self.detail = detail

def validate_query(sql: str) -> str:
    statements = sqlglot.parse(sql, read="postgres")
    if len(statements) != 1:
        raise QueryRejected("multiple_statements", "Send exactly one statement.")
    tree = statements[0]
    if tree is None:
        raise QueryRejected("empty", "No statement found.")
    if not isinstance(tree, exp.Select) and not isinstance(tree, exp.Union):
        raise QueryRejected("not_a_select", "Only SELECT queries are allowed.")

    # Reject data-modifying CTEs even though the top level is a SELECT.
    for node in tree.walk():
        if isinstance(node, (exp.Insert, exp.Update, exp.Delete, exp.Create,
                             exp.Drop, exp.Alter, exp.Command)):
            raise QueryRejected("write_operation", f"{node.key} is not allowed.")

    tables = {t.name.lower() for t in tree.find_all(exp.Table)}
    unknown = tables - ALLOWED_TABLES
    if unknown:
        raise QueryRejected("table_not_allowed", f"Unknown table: {sorted(unknown)[0]}")

    for func in tree.find_all(exp.Func):
        name = func.sql_name().lower()
        if name in BANNED_FUNCTIONS:
            raise QueryRejected("function_not_allowed", f"{name} is not allowed.")

    return tree.sql(dialect="postgres")

A few details make this more than a toy:

• sqlglot.parse returns a list, so a stacked statement like SELECT 1; DROP TABLE users; is caught by the length check.
• The walk() loop catches CTEs whose top-level node is a SELECT but which contain a Delete. isinstance(tree, exp.Select) alone would miss it.
• Checking exp.Table covers subqueries and joins, not just the FROM clause.
• Re-serializing with tree.sql(dialect="postgres") gives you a canonical string. Execute that, not the original, so the thing you validated is the thing you run.

The canonicalization step is worth emphasizing. Validating one string and executing another is a classic gap: comments, Unicode normalization, or dialect-specific quoting can make two strings behave differently while looking similar. Parse once, validate the AST, and emit the SQL you execute.

Step 5: add execution guards the parser can't provide

Some queries are read-only and still dangerous. A three-table join without a WHERE clause is a perfectly valid SELECT that can run for ten minutes. Guards:

LIMITS = {
    "statement_timeout": "5000",     # 5s of server execution
    "lock_timeout": "1000",          # 1s waiting for locks
    "work_mem": "64MB",              # avoid spilling a giant sort to disk
    "max_rows": 500,
}

def run_read_query(conn, sql: str, tenant_id: str) -> list[dict]:
    with conn.transaction():
        conn.execute("SET TRANSACTION READ ONLY")
        conn.execute("SELECT set_config('app.tenant_id', %s, true)", (tenant_id,))
        for name in ("statement_timeout", "lock_timeout", "work_mem"):
            conn.execute("SELECT set_config(%s, %s, true)", (name, LIMITS[name]))

        with conn.cursor(name="ai_query") as cur:   # server-side cursor
            cur.itersize = 100
            cur.execute(sql)
            rows = cur.fetchmany(LIMITS["max_rows"] + 1)

    if len(rows) > LIMITS["max_rows"]:
        raise QueryRejected("too_many_rows", "Query returned more rows than allowed.")
    return rows

The named cursor uses a server-side DECLARE, which bounds how many rows cross the network. It does not stop the server from computing a huge intermediate result, which is why statement_timeout and work_mem are also set.

There's an argument for running EXPLAIN first and rejecting plans above a cost threshold. It catches some disasters before they start, but it has three problems: planning itself can be expensive, the cost estimate can be wrong by orders of magnitude, and EXPLAIN on a query with certain functions can have side effects during planning. I'd use it as a soft signal in logs, not as the primary guard. A short statement_timeout is a more honest control.

Step 6: shape the output

The last layer is what leaves the database. Even a permitted query can return a column you didn't intend to expose, or 500 rows of free text that blow the model's context.

• Column allowlist per view. If v_customers includes internal_notes, the model will eventually select it. Leave it out of the view, or mask it in the projection.
• Row cap plus explicit truncation notice. Return a truncated: true flag so the model knows the answer may be incomplete. Silent truncation produces confidently wrong aggregates.
• Type normalization. Datetimes in ISO 8601 with offsets, decimals as strings or integer minor units, never floats for money.
• No raw error messages to the model. A Postgres error can include the full query, table names, and column names. Map it to a code and a safe message.

Gotcha: aggregating over a truncated result set gives a wrong number, not a partial one. If the model asks for a count, run the count server-side and return the scalar, rather than letting the model sum 500 sampled rows.

The attacks worth testing

Run these against your stack in staging. Each one should fail at a layer you can name.

Input	Layer that should stop it
SELECT 1; DROP TABLE users	Parser: multiple statements
WITH d AS (DELETE FROM orders RETURNING *) SELECT * FROM d	Parser: write node inside a CTE
SELECT * FROM pg_shadow	Database: no grant, table not in allowlist
SELECT pg_sleep(60)	Parser: banned function; database: statement timeout
SELECT * FROM generate_series(1, 1000000000)	Database: statement timeout
SELECT * FROM orders FOR UPDATE	Database: read-only transaction
COPY (SELECT ...) TO PROGRAM 'curl ...'	Parser: not a SELECT; database: no privilege
SELECT * FROM orders WHERE tenant_id = 'other'	RLS or view: returns zero rows
SELECT * FROM v_orders /* ; DELETE ... */	Parser: canonicalized, comment ignored
SELECT nextval('invoice_seq')	Parser: sequence not allowed; database: no grant

The FOR UPDATE row is a good example of why the database layer matters. It's a SELECT statement, it passes most string checks, and on a read-only transaction it fails cleanly.

Putting it together

The flow for one question looks like this:

1. The model receives a schema summary of the allowed views, with column descriptions and a note that only SELECT is permitted.
2. It emits SQL.
3. The service parses it with sqlglot, rejects anything outside policy, and canonicalizes it.
4. The service opens a transaction as ai_reader, sets the tenant and timeouts, and executes with a row cap.
5. The result is normalized, truncated if needed, and returned with a summary of what was queried.
6. The SQL, the policy decision, the row count, and the duration are logged without parameters.

If the query is rejected, the model gets a short, structured error and one chance to rewrite. If it times out, the model is told the query was too expensive and should add filters or aggregate — not simply retried, because a retry of the same query will time out again.

Raw SQL versus a semantic layer

There's a spectrum of interfaces between a model and a database, and "let it write SQL" sits at the most permissive end.

Interface	Model freedom	Safety	Answer quality	Build cost
Raw SQL on base tables	Total	Low	Variable	Low
Raw SQL on curated views	High	Medium–high	Medium	Low
Parameterized query templates	Low	High	High for covered questions	Medium
Semantic layer with named metrics	Very low	High	High	High
Fixed report endpoints	None	Highest	Highest for covered questions	Medium

The semantic layer gets dismissed as overkill, but it solves the problem that raw SQL doesn't: definitions. If "revenue" means gross bookings minus refunds as of the close date, that definition should live in one place, not be reconstructed by a model on every request. A metric layer exposes revenue(tenant, month) as a single callable concept, and the model's job shrinks to selecting the right concept and passing the right filters.

In practice, most teams land on a hybrid: a handful of parameterized tools for the questions that account for the majority of usage, plus a constrained SQL tool for the long tail. That keeps the common path fast and correct while preserving flexibility for questions nobody anticipated. The mistake is starting with the long-tail tool and never building the common path, because then every answer depends on the model's join graph.

When raw SQL is the right tool

This pattern is not an argument against ever letting a model write SQL. It's an argument for matching the capability to the context.

Raw SQL against a real schema is reasonable when:

• The caller is an internal analyst who already has database access.
• The database is a read replica with no PII, or a warehouse with masked views.
• The query runs in a sandbox with a hard timeout and no ability to reach other systems.
• A human reviews the query before execution, or the output is labeled as unreviewed.

It's a poor fit when the caller is an end user, the data crosses tenant boundaries, or the database holds records that would matter in a breach. In those cases, a narrower interface — a parameterized tool per question type, or a semantic layer with named metrics — will outperform free-form SQL on safety, latency, and answer quality.

The uncomfortable truth is that most "text-to-SQL" features would work better as five well-named tools than as one open query interface. The model is good at choosing between revenue_by_month(tenant, from, to) and top_customers(tenant, limit). It is much less reliable at reconstructing your join graph from table names, and every mistake it makes is a correctness bug you have to detect after the fact.

A review checklist

Before exposing SQL generation to a model, confirm:

1. The AI uses a dedicated role with no write privileges on any table.
2. Base tables are not granted; only views are, and the view list is small.
3. Tenant isolation is enforced by RLS or a view, not by the model's WHERE clause.
4. search_path is pinned, and the role cannot create objects in a schema it can write to.
5. Every query is parsed, checked for a single statement, and re-serialized before execution.
6. Data-modifying CTEs, SELECT ... FOR UPDATE, and sequence functions are rejected explicitly.
7. statement_timeout, lock_timeout, and work_mem are set per transaction.
8. A row cap exists, and truncation is reported to the caller.
9. Errors returned to the model are codes and safe messages, not raw driver text.
10. Rejected and timed-out queries are logged with the tenant, the policy reason, and the duration.
11. There's a test suite containing every attack in the table above.
12. Someone owns the view definitions, and adding a column is a reviewed change.

The pattern isn't glamorous. It's a role, a few views, a policy check, and three timeout settings. But it converts an open-ended capability into a bounded one, and it keeps working when the model is clever, the prompt is compromised, or the schema changes underneath you. That's what "safer" should mean.