Text-to-SQL, safely: roles, row security and query checks

A model that turns plain-English questions into SQL is one of the easiest AI features to demo. Someone types "which regions grew fastest last quarter", a chart appears two seconds later, and everyone in the room is impressed. It is also easy to get wrong, because the model writes code that runs against your real data, and the person typing the question is not the only one who can influence what that code does.

That is prompt injection, and the general rules against it are in A security checklist for AI features. This article covers what is specific to a model that writes SQL. The examples use PostgreSQL and were checked on PostgreSQL 14, except security_invoker, which needs version 15 or later. Other databases have the same ideas under different names.

What can go wrong

Three different things, and they need different fixes.

• Destruction. DELETE, UPDATE, DROP, TRUNCATE. Users rarely ask for it on purpose, but "show me everyone who hasn't logged in and delete the stale ones" is half a question and half a wish, and a helpful model may take it literally.

• Leaks. A perfectly valid SELECT that returns rows the person should never see: another customer's data, salaries, password reset tokens. This is the dangerous one, because nothing looks wrong. The query runs, a table appears, and nobody notices until someone forwards a screenshot.

• Cost. A short question that becomes a join across three large tables and scans millions of rows, slowing the database for everyone or, on a warehouse that bills per query, costing real money every time someone asks it.

Only the first one is about bad SQL. The other two are about good SQL that should not have been allowed, and that shapes everything below.

Read-only by construction, not by prompt

"Only generate SELECT statements. Never modify data." in the system prompt is a request, not a control. The model can be talked out of it, including by the data itself: a customer name set to a piece of SQL ends up in the model's context like any other text.

The real control is the database role the feature connects with. Give it read access to exactly the tables and columns you want people to query, and nothing else:

CREATE ROLE text_to_sql LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE app TO text_to_sql;
GRANT USAGE ON SCHEMA public TO text_to_sql;

-- Named tables and columns, not ALL TABLES:
-- no emails, no notes, no tokens.
GRANT SELECT (id, tenant_id, status, priority,
              created_at, closed_at, assignee_id)
  ON tickets TO text_to_sql;
GRANT SELECT (id, tenant_id, name, plan, created_at)
  ON accounts TO text_to_sql;

ALTER ROLE text_to_sql SET default_transaction_read_only = on;
ALTER ROLE text_to_sql SET statement_timeout = '8s';

Now "delete the stale accounts" is refused by the database however the model was manipulated, and a query that asks for the password_resets table or the email column fails the same way. So does SELECT * on a table with columns the role cannot read, which means the model has to name its columns. It is tempting to write GRANT SELECT ON ALL TABLES IN SCHEMA public because it is shorter, but that hands the model every table you will ever add, including the ones you are trying to protect.

The grants are the wall. The two settings are extras: statement_timeout stops a runaway query after eight seconds, and read-only transactions add a second layer. For heavy questions, point the role at a read replica so that analysis never competes with your customers' traffic.

Row-level security: each user sees only their own rows

Column grants decide what can be read. In a product where many customers share the same tables, you also have to decide which rows. If a user at one company asks for "top 50 accounts by spend", a model with no idea who is asking will happily return accounts from four different companies.

Do not rely on the model adding WHERE tenant_id = ... to every query. Let the database do it with row-level security:

ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;

CREATE POLICY tickets_by_tenant ON tickets
  FOR SELECT TO text_to_sql
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

Every table the role can read needs a policy like this, accounts included. Then the application runs each generated query inside a transaction that first sets the tenant of the logged-in user:

await client.query("BEGIN READ ONLY");
try {
  // The tenant comes from the session, never from the model.
  await client.query(
    "SELECT set_config('app.tenant_id', $1, true)",
    [user.tenantId],
  );
  await client.query("SET LOCAL statement_timeout = '8s'");
  const { rows } = await client.query(checkedSql);
  await client.query("COMMIT");
  return rows;
} catch (err) {
  await client.query("ROLLBACK");
  throw err;
}

Whatever SQL the model writes, the database adds the tenant filter itself. If the setting is missing, the query returns nothing or fails, which is the right way to fail. A few details decide whether this actually holds:

• the text_to_sql role must not own the tables and must not have BYPASSRLS: owners and superusers skip row-level security;

• enabling row-level security on a table denies rows to every other role that has no policy and does not own it, so check which role your main application uses before you switch it on;

• if you expose views instead of tables, create them WITH (security_invoker = true) (PostgreSQL 15 and later). Otherwise the view runs with its owner's rights, and the row policies are checked against the owner, not against the user;

• the query checker in the next section must refuse set_config. A generated query that calls it can switch the tenant while it runs: in a test, a single SELECT that set another tenant in a subquery got that tenant's rows back.

Check the query before it runs

Even with the right role, the generated SQL goes through one more gate. Parse it with a real SQL parser instead of matching strings, because comments and odd casing defeat any regular expression. sqlglot (Python) and libpg_query, which packages PostgreSQL's own parser, both work. Then allow only what you expect:

• exactly one statement, and it is a SELECT; no SELECT INTO, no FOR UPDATE, no data-changing statements hidden inside a WITH;

• only the tables you exposed;

• only the functions on your list, such as count, sum, avg, date_trunc and coalesce. Everything else is refused, which covers set_config, pg_sleep, and functions that read files or open connections.

Anything the parser cannot understand is refused too. Then wrap the query so it cannot return an unlimited result: SELECT * FROM (...) AS q LIMIT 1000, and tell the user when the limit cut the answer off.

On a warehouse that bills by data scanned, add the limit it offers. BigQuery, for example, lets you set a maximum number of bytes billed per query; a query that would scan more fails without being charged.

Give the model the real schema

The role and the checker stop disasters. They do nothing for correctness. A model that invents a signup_date column when the table has created_at produces SQL that fails, or worse, joins on the wrong key and returns a confident wrong number.

So describe the schema to the model before it writes anything: table and column names and types, primary and foreign keys, and one line of explanation for any column that is not obvious. Two details matter most:

• list the allowed values of small enumerations (status: open, pending, resolved, closed), because "closed tickets" means nothing to the model if it does not know the table stores resolved and closed;

• spell out the real join keys, because the most common wrong-but-running query is a plausible join on a column that is not the actual relationship.

For a large schema, do not paste all of it. Pick the few tables relevant to the question first, and describe only those. Describe only the columns the role can read, so the model does not write queries that are bound to fail.

Show the SQL and what it assumed

Always show the generated SQL next to the answer, together with a short plain-language summary of what it counted and how many rows came back: "Resolved tickets with closed_at in the last 7 days, grouped by assignee. 1,240 rows."

People who read SQL can spot a wrong join in seconds and correct it. People who do not still get a sentence they can check against what they meant, or forward to someone who can read the query. An answer with its working shown is something people can trust after checking it. A number without it asks them to trust blindly.

Ask instead of guessing

"How are we doing this month?" is not a query. When a key term is ambiguous (calendar month or the last 30 days, which of two amount columns, whether "active" means logged in or paying), the tool should ask one short question before writing any SQL. Ask too often and it feels slow; never ask and it quietly answers a different question from the one you meant. The right threshold comes from reading real questions, not from guessing in advance.

What to expect from it

A tool built this way is good at the routine questions people otherwise send to whoever knows SQL: how many onboarding calls happened last week, which plan is losing customers, how many tickets are open per team. It is not a replacement for an analyst. Questions that need a new metric definition, or a caveat about messy data, should still go to a person, and the tool should say so instead of producing a confident number.

In short: make the database role unable to do damage, let row-level security decide which rows each user sees, check every query with a parser and an allow-list, and show your working.