Six layers, one guarantee: the read-only role under an AI analyst's SQL guard

Every query our analyst agent runs was written by a language model. The model reads user questions, table comments and its own error messages, and writes SQL for a warehouse holding a travel and fintech group’s bookings and ledger. Usually the SQL is good. Occasionally it is wrong, and sometimes a user or a piece of text in the data coaxes it toward something it should not write.

We put six layers between that text and the warehouse. This post is about which one is the guarantee and which five are depth. The application checks make the agent pleasant to use and cheap to run, and they will have bugs. The database role is what makes a bug survivable.

To check our own account, we ran the guard function (sqlglot 30.12.0, the version pinned in the lockfile) against hostile queries and ran the database layers on a throwaway Postgres 17 with the same role settings. The outputs quoted below come from those runs.

The constraints came first

Constraint	Consequence
The SQL author is a language model	Treat its output as untrusted input, the way you would treat a form field
The agent has a single data tool	One chokepoint to guard; the model cannot reach Postgres any other way
Users ask real finance questions	The guard cannot reject everything interesting, or people stop using the agent
The model reads every rejection	Error text is part of the product, not a log line
The agent is internal, behind invitation-only sign-in	The realistic threats are injection, model error and runaway queries, not a stranger with credentials

Rows three and four pull against each other: a strict guard gets loosened, and a vague one makes the model guess.

What we were afraid of

Three threats, written down before any guard code.

• Prompt injection. A user, or text the model reads in a table comment, steers it into DML, DDL or a query against a schema it should not touch.
• Model error. A hallucinated DELETE as a “clean-up step”, two statements in one response, or a table that resolves through search_path into a schema nobody meant to expose.
• Resource abuse. A correct but pathological query: a cartesian join, an unbounded scan, or a million rows pushed into a prompt, which is a token bill as well as a database load.

Two first guesses we ruled out

The prompt says SELECT only. It does, and it tells the model to schema-qualify every table. Both help, and neither is a control. A prompt is a request to a text generator that also ingests user input. Whatever can talk to the model can argue with the prompt.

A single parser. The next idea is to check the text: does it start with SELECT? A prefix check passes SELECT 1; DROP TABLE ... and fails on a leading comment. A parser is the right tool, and we use one. But a parser is someone else’s model of what Postgres will do, and the grammar keeps changing. If the whole defence is “our parser understood the statement,” every parser bug is a data-loss bug.

Both put the control in the same process as the thing being controlled, and then trust that process.

The stack

Layers 1 to 5 live in the agent’s process, in validate_and_prepare() and run_read_only(). Layer 6 is the role the agent connects as. The module docstring lists five layers and folds the role into the fifth; we split them because they fail differently.

Layer	What it checks	What it cannot see
1 Parse	Valid Postgres, exactly one statement, top level is SELECT or WITH	Anything the parser misreads
2 AST walk	No Insert, Update, Delete, Drop, Create, Alter, Command, Grant, TruncateTable, Merge anywhere in the tree	Dangerous things that are not those node types
3 Allowlist	Every real table is schema-qualified and in an allowed schema	What a function does when called
4 LIMIT	A row cap is injected or clamped; SQL is re-emitted from the checked tree	Cost: a capped query can still scan everything
5 Transaction	SET TRANSACTION READ ONLY, a statement timeout, and a rollback with no code path to commit	Session state the caller can change
6 Role	SELECT-only grants, read-only by default, timeout defaults	What the role is allowed to read, including through functions; this is the floor

Four hostile queries, four different layers

Table and schema names are generalised. The error text is otherwise as the guard returned it.

1. Stacking: SELECT 1; DROP TABLE analytics.bookings_daily

Dies at layer 1. The guard parses with sqlglot as Postgres and counts statements:

statements = sqlglot.parse(sql, read="postgres")
...
statements = [s for s in statements if s is not None]
if len(statements) != 1:
    raise SqlGuardError("Exactly one statement is allowed.")

Two statements parse, so the count is two and nobody looks at the second. The model receives Query failed: Exactly one statement is allowed.

The same layer turned away COPY, SET ROLE postgres and EXPLAIN ANALYZE SELECT 1: sqlglot parses them as a copy or a generic command, and none is a SELECT or a WITH.

2. Nesting: a data-modifying CTE

WITH cleanup AS (DELETE FROM analytics.bookings_daily RETURNING 1)
SELECT count(*) FROM cleanup

Survives layer 1: one statement, top level WITH ... SELECT. Postgres allows data-modifying statements inside a WITH, which is why layer 2 walks every node instead of checking the root:

_FORBIDDEN = (
    exp.Insert, exp.Update, exp.Delete, exp.Drop, exp.Create, exp.Alter,
    exp.Command, exp.Grant, exp.TruncateTable, exp.Merge,
)
...
for node in stmt.walk():
    if isinstance(node, _FORBIDDEN):
        raise SqlGuardError(f"Statement type '{type(node).__name__}' is not allowed.")

Result: Statement type 'Delete' is not allowed.

3. Reaching sideways: a schema that is not on the list

SELECT * FROM raw_erp.ledger_entries

Parses, no forbidden nodes, qualified. Dies at layer 3: Table 'raw_erp.ledger_entries' is outside the allowed schemas ('analytics'). The same layer rejects pg_catalog.pg_shadow, the catalog-snooping query that “list every user and their password hash” prompts tend to produce.

Two details matter. First, unqualified names are rejected, not defaulted. A bare name resolves through search_path, which is connection state the guard cannot see. By refusing it, the allowlist needs nothing but the query text. The model gets Reference 'bookings_daily' must be schema-qualified ... e.g. analytics.<table>.

Second, CTE names are exempt, but only when unqualified.

cte_names = {c.alias_or_name.lower() for c in stmt.find_all(exp.CTE)}
...
for table in stmt.find_all(exp.Table):
    if table.name.lower() in cte_names and not (table.db or ""):
        continue  # reference to a CTE, not a physical table

Without the exemption, every CTE query is rejected, because sqlglot reports FROM monthly as a table. Our notes say the first version turned away roughly half of the non-trivial queries for this reason. A blanket exemption would let a CTE act as camouflage, so we checked: WITH m AS (SELECT 1) SELECT * FROM raw_erp.m is still rejected, because the reference is qualified.

4. The one that passes every application check

SELECT * INTO analytics.copy_of_bookings FROM analytics.bookings_daily

We added this query to see what the guard misses. It is one statement, it contains no forbidden node, and the target schema is allowed. The guard passes it and returns:

SELECT * INTO analytics.copy_of_bookings FROM analytics.bookings_daily LIMIT 1000

Postgres creates a table from SELECT ... INTO, so treating it as a read is wrong. It dies at layer 5, because run_read_only() opens the transaction like this:

with psycopg.connect(settings.analytics_dsn(), autocommit=False) as conn:
    with conn.cursor() as cur:
        cur.execute("SET TRANSACTION READ ONLY")
        cur.execute(f"SET statement_timeout = {settings.sql_statement_timeout_ms}")
        cur.execute(safe_sql)
        cols = [d.name for d in cur.description] if cur.description else []
        rows = [list(r) for r in cur.fetchall()]
    conn.rollback()  # read-only; never commit

Postgres answers ERROR: cannot execute SELECT INTO in a read-only transaction. SELECT ... FOR UPDATE takes the same route: the guard passes it, and the read-only transaction refuses it.

Four queries, four layers: 1, 2, 3 and 5. The layer that matters most has not fired yet.

Why layer 6 is the guarantee

Layer 5 looks like a guarantee, but it is a statement the application sends, and the application is what we do not trust. It is also weaker than it appears, because a session may change its own transaction mode. We connected as a role configured like the agent’s and tried it:

begin;
set transaction read write;
insert into analytics.t values (8, 8);
commit;

set transaction read write was accepted. The insert then failed with ERROR: permission denied for table t. A role-level default_transaction_read_only = on is a default, and a default can be overridden by the session that holds it. A missing grant cannot.

The role is defined in a SQL file in the pipeline repo (abridged):

ALTER ROLE analyst_ro SET default_transaction_read_only = on;
ALTER ROLE analyst_ro SET statement_timeout = '30s';
ALTER ROLE analyst_ro SET idle_in_transaction_session_timeout = '60s';

GRANT USAGE ON SCHEMA analytics TO analyst_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst_ro;

No INSERT, UPDATE, DELETE or TRUNCATE is granted anywhere in the roles file. With the application layers out of the picture entirely, the throwaway setup answered:

Query as the read-only role	Postgres response
INSERT into a mart	cannot execute INSERT in a read-only transaction
SELECT * FROM raw_erp.m	permission denied for schema raw_erp
SELECT * FROM pg_catalog.pg_shadow	permission denied for view pg_shadow
SELECT pg_read_file('/etc/passwd')	permission denied for function pg_read_file
CREATE TABLE analytics.x (a int)	cannot execute CREATE TABLE in a read-only transaction
SET statement_timeout = 500; SELECT pg_sleep(2)	canceling statement due to statement timeout

The pg_read_file row is the one the guard cannot reason about: layer 3 inspects tables, not function calls, and that query passes layers 1 to 4 untouched. The database refuses it because the role never had the privilege.

If layers 1 to 5 all fail, layer 6 holds. If layer 6 is wrong, layers 1 to 5 are reasonable-looking checks around a role that can do too much. The first four will have bugs; the CTE rule we nearly shipped wrong is one example. The sixth is why a parser upgrade is an ordinary event.

Keeping the grants true

A guarantee that is quietly false is worse than none, and ours was quietly false twice.

A rebuild dropped the grant. dbt’s table materialization drops and recreates each table, and a dropped table takes its grants with it. After a full rebuild, the agent’s next query failed with permission denied, and the model kept rewriting good SQL against a table it could no longer read. ALTER DEFAULT PRIVILEGES is the fix, with a catch. Default privileges belong to the role that creates the object. On a second set of marts, the unqualified form only covered objects created by whoever ran the file, not the role dbt builds as, so the first transform run turned the agent’s reads into permission errors. The grant for that schema now names the creating role with FOR ROLE; the first schema’s unqualified grant works only because the file is run as the role dbt builds as.

A schema grant disappeared when the loader recreated its schema. An earlier version of the roles file also granted the raw landing schema and a staging schema. On the staging cluster, a privilege check returned false for the landing schema, because the loader had dropped and recreated it and a schema-level grant goes with the schema. Nothing reported the loss. The file records the decision that followed: grant the marts only. Narrow beats recoverable, because an agent answering from raw tables produces a number that disagrees with every dashboard, confidently.

So the guarantee has a verification step: after any change, and after anything that recreates a schema, a has_schema_privilege query per schema must return true for the marts and false for the rest, and a has_table_privilege check per mart must return true.

Two keys

The allowlist and the grants describe the same schemas, and neither is derived from the other. The agent’s config says so:

# NOTE: broadening here has NO effect until the analyst_ro DB role is also
# granted SELECT on those schemas (pipeline/sql/roles.sql, separate elt repo).
analytics_schemas: Annotated[list[str], NoDecode] = ["analytics"]

Widening the allowlist alone changes what the guard permits, but queries against the new schema still die at the database. The allowlist reads table references in the query text, so it is a filter on what the model writes, not a boundary around what the role can read. The boundary is the grants. The roles file adds a warning: listing a schema the role cannot read tells the model it may query something Postgres will refuse, and the retry can never succeed.

Guard errors are prompts

The SqlGuardError messages have an audience we did not plan for: the model. A rejection is not the answer the user sees. run_sql returns it as the tool result:

res: QueryResult = run_read_only(query)
if res.error:
    return f"Query failed: {res.error}", {"result": res.__dict__}

On its next turn the model reads that string and writes a corrected query. Guard rejections and Postgres errors travel the same path, so both are part of the prompt.

Compare two ways to say one thing. invalid table says something is wrong. The real message names the offending token, states the rule, lists the valid schemas and shows the shape of the fix. Our notes say an early draft used the short form and the retries guessed. With the long form the repair takes one retry. We have not measured that rate, so treat it as an observation.

We write guard errors the way a compiler does: name the token, state the rule, show a valid example. The executor helps in two ways. It returns raw Postgres error text rather than sanitising it, and it returns every failure as data on QueryResult.error instead of raising, so the retry loop treats a guard rejection and a database error identically. The cheapest improvement to a self-correction loop is usually an error message, not a model upgrade.

Where it fails

What the guard does not stop:

• It does not understand function calls. SELECT pg_sleep(100) and SELECT pg_read_file('/etc/passwd') both pass layers 1 to 4. The timeout and the role’s missing privileges stop them, so the application layers cannot be read as a statement of what the agent can do.
• It does not understand cost. A cartesian join of two marts passes with a LIMIT 1000 that caps what comes back, not what is scanned. The 30 second timeout is a session default that the session can change, and it is blunt.
• It does not limit what a user may see. Every invited user sees the same marts, with no row-level security, because all users share one connection role. That fits an internal tool for a known set of employees; it would not fit external users.
• It has only one resource limit. The timeout and the row cap are the only brakes. There is no per-role connection limit, temp-file limit or concurrency control, so several expensive questions at once can still load the database.
• The guard has false positives. SELECT * FROM analytics.t; -- trailing is rejected with “Exactly one statement is allowed”. A table function such as generate_series is rejected with a schema-qualification error whose text is useless to the model, which sits badly beside the section above. Both cost the model a round trip, and the second is a known bug.
• The prompt can disagree with the grants. The prompt template still calls the raw schemas a fallback for questions the marts do not cover. With the allowlist set to the marts alone, that sentence points at nothing. Prompt, allowlist and grants must stay in step, and nothing enforces it.
• The guard has no unit tests. The repository has no test file for validate_and_prepare(). The function is pure, a string in and a string or an exception out, so a table of (query, expected outcome) pairs would run in milliseconds and tell us whether a sqlglot upgrade moved the fence. The runs behind this post were an ad hoc script. We should have written the table first.
• It does not measure accuracy. None of this says whether the SQL is right. A query can pass all six layers and answer the wrong question. We have no scored evaluation of the agent, and we do not quote an accuracy figure.

This runs on a staging environment, operated by one person, and the design has not had an external security review.

What carries over

If you are putting a model in front of a database:

1. Name the guarantee. Decide which layer you would bet the data on, and make sure it is not in the process you are guarding.
2. Make it a grant, not a setting. A read-only default can be overridden by the session. A privilege that was never granted cannot.
3. Add application layers for everything else. They bound cost, give the model precise errors and turn most mistakes into a retry. That is depth, and it is worth having.
4. Verify the grants after anything that recreates objects. A quiet permission error looks exactly like the model choosing not to look.
5. Write errors for the model. Name the token, state the rule, show an example.

---

CloudGuys builds systems like this for startups and teams. Tell us what you are building.