Text-to-SQL Guardrails: Run LLM Queries Read-Only and Test the Escapes

1. Separate the setting from the boundary

A reporting assistant writes SQL and you run it against a real PostgreSQL database. You set the connection to read-only, so the question seems closed. That setting says nothing about which rows the query reads or how long it runs, and the role can switch it off. This guide builds the execution boundary around model-written SQL and tests it with hostile queries and legitimate controls.

The OWASP entry on improper output handling lists LLM-generated SQL run without parameterization as an example and advises: "Treat the model as any other user, adopting a zero-trust approach." Parameters protect values inside a fixed statement. When the model writes the whole statement, they have nothing to bind, so we apply the same advice through the database role. That last step is our inference, not OWASP's wording.

This guide owns the execution boundary. For tenant isolation in retrieval, use PostgreSQL Row-Level Security for Multi-Tenant RAG. For permissions around tools, see AI Guardrails for Developers. For schema decisions, see the AI-assisted database design guide.

2. Decide which layer enforces which limit

This fixture sets no row limit on the role. The cap below is the count your application passes to FETCH. Each limit has one owner, and the last column names the probe that tests it.

3. Create the restricted role

Save this as 01-setup.sql and run it as a superuser on a disposable instance. It creates a database, a separate table owner and a login role with SELECT on two tables. The password is a fixture literal for this role only, so do not reuse the script on a shared server.

-- Run as a superuser on a disposable instance. Synthetic data only.
\set ON_ERROR_STOP on
SELECT version();
CREATE DATABASE t2s_guardrails_fixture;
CREATE ROLE t2s_owner NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE t2s_reader LOGIN PASSWORD 't2s-fixture-only'
  NOSUPERUSER NOBYPASSRLS NOCREATEDB NOCREATEROLE NOINHERIT CONNECTION LIMIT 5;
\connect t2s_guardrails_fixture
REVOKE TEMPORARY ON DATABASE t2s_guardrails_fixture FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE SCHEMA app AUTHORIZATION t2s_owner;
SET ROLE t2s_owner;
CREATE TABLE app.orders (
  id integer PRIMARY KEY,
  tenant text NOT NULL,
  status text NOT NULL,
  total_cents integer NOT NULL
);
INSERT INTO app.orders
SELECT g, CASE WHEN g % 2 = 0 THEN 'a' ELSE 'b' END,
       CASE WHEN g % 3 = 0 THEN 'open' ELSE 'paid' END, g % 1000
FROM generate_series(1, 20000) AS g;
CREATE TABLE app.notes (id integer PRIMARY KEY, tenant text NOT NULL, body text NOT NULL);
INSERT INTO app.notes VALUES
  (1, 'a', 'Synthetic A: copper lantern'),
  (2, 'b', 'Synthetic B: silver compass');
GRANT USAGE ON SCHEMA app TO t2s_reader;
GRANT SELECT ON app.orders, app.notes TO t2s_reader;
ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.notes ENABLE ROW LEVEL SECURITY;
-- Fixed tenant policy for the reader role.
CREATE POLICY reader_a ON app.orders FOR SELECT TO t2s_reader USING (tenant = 'a');
-- Deliberately weak policy: trusts a setting the session can change (probe H11).
CREATE POLICY forgeable ON app.notes FOR SELECT TO t2s_reader
  USING (tenant = current_setting('app.tenant', true));
RESET ROLE;
ALTER ROLE t2s_reader IN DATABASE t2s_guardrails_fixture SET default_transaction_read_only = on;
ALTER ROLE t2s_reader IN DATABASE t2s_guardrails_fixture SET statement_timeout = '1s';
ALTER ROLE t2s_reader IN DATABASE t2s_guardrails_fixture SET temp_file_limit = '50MB';
ALTER ROLE t2s_reader IN DATABASE t2s_guardrails_fixture SET search_path = app;
-- Positive control: a role that holds TEMPORARY can create a temp table.
GRANT TEMPORARY ON DATABASE t2s_guardrails_fixture TO t2s_owner;
SELECT has_database_privilege('t2s_reader', 't2s_guardrails_fixture', 'TEMPORARY') AS reader_temp,
       has_database_privilege('t2s_owner', 't2s_guardrails_fixture', 'TEMPORARY') AS owner_temp;
SET ROLE t2s_owner;
CREATE TEMP TABLE temp_control AS SELECT 1 AS x;
DROP TABLE temp_control;
RESET ROLE;

Three details carry weight. PostgreSQL grants TEMPORARY on databases and EXECUTE on functions to PUBLIC by default, which is why the script revokes TEMPORARY explicitly. The final block is the positive control: the owner holds TEMPORARY and creates a temp table, while the reader's privilege check returned false. And ALTER ROLE settings become the session default "only at login time", so the probes must use a real login. SET ROLE would skip these settings.

The forgeable policy is deliberately bad. It lets the test show what happens when isolation trusts a value the session controls.

4. Send model SQL through a one-statement envelope

Your application runs these statements in this order and substitutes the model's text for the query. This excerpt is the L4 probe. Nothing else from the model reaches the connection.

BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SELECT status, sum(total_cents) FROM orders GROUP BY status ORDER BY status \bind \g
FETCH 101 FROM model_query;
ROLLBACK;

The grammar does most of the work. The query in DECLARE is "A SELECT or VALUES command", so SET and DELETE fail as syntax errors in the envelope. A Parse message "cannot include more than one SQL statement" in the extended protocol, which is what \bind \g makes psql use here. Your driver must use that protocol for the DECLARE. Check this yourself, because this fixture exercised psql only.

Rules that follow from the results: send one FETCH, roll back every time, and read 101 rows so that a full result tells you the answer was cut at 100. E5 covers a single FETCH only. A second one would start after set_config has already run, and that case is untested, so treat it as unsafe.

5. Save and run the probes

Save the next two files beside the setup script. check.sql compares each outcome with its expected value, and 02-probes.sql holds 40 labeled probes with 42 checks. The run raises many errors on purpose, so judge it by the PASS and FAIL lines. A mismatch ends the script with a nonzero exit code through a deliberate division by zero.

SELECT :'got' = :'want' AS ok \gset
\if :ok
\echo PASS got=:got
\else
\echo FAIL got=:got want=:want
SELECT :fails + 1 AS fails \gset
\endif

-- Run as the login role t2s_reader, without ON_ERROR_STOP: errors are expected.
-- Each probe stores an outcome in got, and check.sql compares it with want.
\set ON_ERROR_STOP off
\set fails 0
SELECT session_user, current_user, current_setting('default_transaction_read_only') AS ro,
       current_setting('statement_timeout') AS st, current_setting('temp_file_limit') AS tfl;

\echo === L1 legitimate aggregate returns tenant a groups only
SELECT tenant, status, count(*) FROM orders GROUP BY 1, 2 ORDER BY 1, 2;
\set got :ROW_COUNT
\set want 2
\ir check.sql
\echo === L2 legitimate CTE
WITH open_orders AS (SELECT * FROM orders WHERE status = 'open')
SELECT count(*) AS got FROM open_orders \gset
\set want 3333
\ir check.sql
\echo === L3 envelope caps a large legitimate result at 101 rows
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SELECT id, status FROM orders ORDER BY id \bind \g
FETCH 101 FROM model_query \g (tuples_only=on format=unaligned)
\set got :ROW_COUNT
ROLLBACK;
\set want 101
\ir check.sql
\echo === L4 envelope returns a legitimate aggregate
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SELECT status, sum(total_cents) FROM orders GROUP BY status ORDER BY status \bind \g
FETCH 101 FROM model_query;
\set got :ROW_COUNT
ROLLBACK;
\set want 2
\ir check.sql
\echo === L5 legitimate short query inside the timeout
SELECT pg_sleep(0.1);
\set got :SQLSTATE
\set want 00000
\ir check.sql

\echo === H1 write under the read-only default
DELETE FROM orders;
\set got :SQLSTATE
\set want 25006
\ir check.sql
\echo === H2 role switches off the read-only default
SET default_transaction_read_only = off;
SELECT current_setting('default_transaction_read_only') AS got \gset
\set want off
\ir check.sql
\echo === H3 write after the override, stopped by the missing grant
DELETE FROM orders;
\set got :SQLSTATE
\set want 42501
\ir check.sql
\echo === H3b explicit read-write transaction, stopped by the missing grant
BEGIN READ WRITE;
INSERT INTO notes VALUES (3, 'a', 'hostile');
\set got :SQLSTATE
ROLLBACK;
\set want 42501
\ir check.sql
\echo === H7 temp table after the override, stopped by the revoked privilege
CREATE TEMP TABLE scratch AS SELECT 1 AS x;
\set got :SQLSTATE
\set want 42501
\ir check.sql
\echo === H7b new table after the override
CREATE TABLE app.exfil (x int);
\set got :SQLSTATE
\set want 42501
\ir check.sql
RESET default_transaction_read_only;
\echo === H4 flip transaction_read_only after the first query
BEGIN READ ONLY;
SELECT 1;
SELECT set_config('transaction_read_only', 'off', true);
\set got :SQLSTATE
ROLLBACK;
\set want 25001
\ir check.sql
\echo === H4b flip to read-write before the first query
BEGIN READ ONLY;
SET TRANSACTION READ WRITE;
\set got :SQLSTATE
SHOW transaction_read_only;
ROLLBACK;
\set want 00000
\ir check.sql
\echo === H5 data-modifying CTE that a starts-with-WITH filter would pass
WITH d AS (DELETE FROM orders RETURNING id) SELECT count(*) FROM d;
\set got :SQLSTATE
\set want 25006
\ir check.sql
\echo === H6 DDL under the read-only default
DROP TABLE orders;
\set got :SQLSTATE
\set want 25006
\ir check.sql
\echo === H7c temp table under the read-only default
CREATE TEMP TABLE scratch AS SELECT 1 AS x;
\set got :SQLSTATE
\set want 25006
\ir check.sql
\echo === H8 slow query under the role default
SELECT pg_sleep(3);
\set got :SQLSTATE
\set want 57014
\ir check.sql
\echo === H9 role disables the timeout, and the sleep completes
SET statement_timeout = 0;
SELECT pg_sleep(3);
\set got :SQLSTATE
\set want 00000
\ir check.sql
RESET statement_timeout;
\echo === H10 expensive cross join
SELECT count(*) FROM orders a CROSS JOIN orders b CROSS JOIN orders c;
\set got :SQLSTATE
\set want 57014
\ir check.sql
\echo === H11 forged setting exposes tenant b through the weak policy
SELECT count(*) AS got FROM notes \gset
\set want 0
\ir check.sql
SET app.tenant = 'b';
SELECT id AS got FROM notes \gset
\set want 2
\ir check.sql
RESET app.tenant;
\echo === H12 wrong tenant under the fixed policy
SELECT count(*) AS got FROM orders WHERE tenant = 'b' \gset
\set want 0
\ir check.sql
\echo === H13 row_security off raises an error
SET row_security = off;
SELECT count(*) FROM orders;
\set got :SQLSTATE
\set want 42501
\ir check.sql
RESET row_security;
\echo === H14 role switch to the owner
SET ROLE t2s_owner;
\set got :SQLSTATE
\set want 42501
\ir check.sql
\echo === H15 raise temp_file_limit
SET temp_file_limit = -1;
\set got :SQLSTATE
\set want 42501
\ir check.sql
\echo === H16 server file read
SELECT pg_read_file('postgresql.conf');
\set got :SQLSTATE
\set want 42501
\ir check.sql
\echo === H17 password hashes
SELECT rolname, rolpassword FROM pg_authid;
\set got :SQLSTATE
\set want 42501
\ir check.sql
\echo === H18 advisory lock through a plain SELECT is held
SELECT pg_advisory_lock(42);
SELECT count(*) AS got FROM pg_locks WHERE locktype = 'advisory' AND pid = pg_backend_pid() \gset
\set want 1
\ir check.sql
SELECT pg_advisory_unlock_all();
\echo === H19 large sort hits temp_file_limit
SET statement_timeout = '60s';
SET work_mem = '1MB';
SELECT count(*) FROM (SELECT md5(g::text) AS m FROM generate_series(1, 3000000) AS g ORDER BY m) AS s;
\set got :SQLSTATE
\set want 53400
\ir check.sql
RESET work_mem;
RESET statement_timeout;

\echo === E1 envelope refuses SET as model SQL
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SET statement_timeout = 0 \bind \g
\set got :SQLSTATE
ROLLBACK;
\set want 42601
\ir check.sql
\echo === E2 envelope refuses DELETE as model SQL
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR DELETE FROM orders \bind \g
\set got :SQLSTATE
ROLLBACK;
\set want 42601
\ir check.sql
\echo === E3 envelope refuses a data-modifying CTE
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR WITH d AS (DELETE FROM orders RETURNING id) SELECT * FROM d \bind \g
\set got :SQLSTATE
ROLLBACK;
\set want 0A000
\ir check.sql
\echo === E4 extended protocol refuses a stacked SET
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SELECT 1 \; SET LOCAL statement_timeout = 0 \; SELECT pg_sleep(3) \bind \g
\set got :SQLSTATE
ROLLBACK;
\set want 42601
\ir check.sql
\echo === E4c control: the same text through simple protocol disables the timeout
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SELECT 1 \; SET LOCAL statement_timeout = 0 \; SELECT pg_sleep(3);
\set got :SQLSTATE
ROLLBACK;
\set want 00000
\ir check.sql
\echo === E5 set_config inside the single fetch does not lift the running timeout
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SELECT set_config('statement_timeout', '0', true), pg_sleep(3) \bind \g
FETCH 101 FROM model_query;
\set got :SQLSTATE
ROLLBACK;
\set want 57014
\ir check.sql
\echo === E6 session-level set_config disappears at ROLLBACK
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SELECT set_config('statement_timeout', '0', false) \bind \g
FETCH 101 FROM model_query;
ROLLBACK;
SELECT current_setting('statement_timeout') AS got \gset
\set want 1s
\ir check.sql
\echo === E7 read-write flip inside the fetch
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SELECT set_config('transaction_read_only', 'off', true) \bind \g
FETCH 101 FROM model_query;
\set got :SQLSTATE
ROLLBACK;
\set want 25001
\ir check.sql
\echo === E8 advisory lock survives ROLLBACK, DISCARD ALL releases it
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SELECT pg_advisory_lock(42) \bind \g
FETCH 101 FROM model_query;
ROLLBACK;
SELECT count(*) AS got FROM pg_locks WHERE locktype = 'advisory' AND pid = pg_backend_pid() \gset
\set want 1
\ir check.sql
DISCARD ALL;
SELECT count(*) AS got FROM pg_locks WHERE locktype = 'advisory' AND pid = pg_backend_pid() \gset
\set want 0
\ir check.sql
\echo === E9 row flood from a set-returning function hits temp_file_limit before any row
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SELECT g FROM generate_series(1, 100000000) AS g \bind \g
FETCH 101 FROM model_query;
\set got :SQLSTATE
ROLLBACK;
\set want 53400
\ir check.sql
\echo === E10 row flood from a cross join stops at the fetch count
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SELECT a.id FROM orders a CROSS JOIN orders b \bind \g
FETCH 101 FROM model_query \g (tuples_only=on format=unaligned)
\set got :ROW_COUNT
ROLLBACK;
\set want 101
\ir check.sql
\echo === E11 sorted cross join needs temp files before the first row
BEGIN READ ONLY;
SET LOCAL statement_timeout = '1s';
DECLARE model_query NO SCROLL CURSOR FOR SELECT a.id FROM orders a CROSS JOIN orders b ORDER BY a.id + b.id \bind \g
FETCH 101 FROM model_query;
\set got :SQLSTATE
ROLLBACK;
\set want 53400
\ir check.sql

SELECT :fails = 0 AS all_ok \gset
\if :all_ok
\echo PASS: all probes matched
\else
\echo FAIL: :fails probes differed
\set ON_ERROR_STOP on
SELECT 1 / 0;
\endif

The teardown removes the database and both roles, then reports any leftover role.

-- Run as postgres. Removes everything 01-setup.sql created.
\set ON_ERROR_STOP on
DROP DATABASE t2s_guardrails_fixture;
DROP ROLE t2s_reader;
DROP ROLE t2s_owner;
SELECT count(*) AS leftover_roles FROM pg_roles WHERE rolname LIKE 't2s\_%';

psql -X -U postgres --dbname=postgres --file=01-setup.sql
psql -X -U t2s_reader --dbname=t2s_guardrails_fixture --file=02-probes.sql
psql -X -U postgres --dbname=postgres --file=03-teardown.sql

Add your host and port. The expected result is PASS: all probes matched, exit code 0, and leftover_roles at 0.

6. Read what held

The error codes mean: 25006 read-only transaction, 42501 insufficient privilege, 25001 active transaction, 57014 query canceled, 53400 configuration limit exceeded, 42601 syntax error and 0A000 feature not supported.

7. Read what got through

These attempts succeeded. The suite passes because it expects them to. Removing them would hide the reason for the envelope.

Read-only is a default. The parameter "controls the default read-only status of each new transaction," and H2 shows the reader can change it. After that, only the missing grant stopped the writes in H3. H4b goes further: BEGIN READ ONLY could be undone before the first query, while H4 and E7 show the flip is refused afterward. The documentation calls this "a high-level notion of read-only that does not prevent all writes to disk." Keep it as a second layer and rely on grants.

A role-level timeout is not a hard limit. H8 shows it working and H9 shows the role removing it. It holds only when the model cannot issue SET, and E1 and E4 are the mechanism that refuses SET. E4c is the warning: the same text over the simple protocol lifted the timeout, and the sleep completed. Building the envelope by string concatenation on a simple-protocol call loses the protection. E6 relies on the rollback, since the effects of SET "disappear when the transaction is rolled back."

A keyword filter would have missed these. H8, E5 and E8 start with SELECT and contain no write keyword. They were stopped by limits or got through, whatever their wording. The fixture ran no filter, so this shows only what such a filter would see in the text. Do not use one as your boundary.

A policy is only as trustworthy as its inputs. H11 returned tenant B's row after one SET, and H12 and H13 held under the fixed policy. PostgreSQL says row_security off "does not in itself bypass row security," which matches H13. The envelope refuses SET, but E6 shows set_config stays callable inside a query, and the fixture did not try forging a tenant that way. Bind tenants to roles or to values the model cannot set.

A SELECT can hold a resource. Session-level advisory locks "do not honor transaction semantics", and E8 confirms the lock outlived the ROLLBACK. Since DISCARD ALL includes pg_advisory_unlock_all, run it before reusing a connection, or close the connection. pg_advisory_lock stays callable by this role, and the fixture did not try revoking it.

A row cap limits output, not work. FETCH with a count returns that many rows. E10 stopped at 101 from a cross join of 100 million rows, while E9 and E11 failed on temporary disk before any row arrived. In both of those, temp_file_limit fired before the one-second timeout. A different machine could reverse that order, and either error stops the query. The limit applies per process, and explicit temporary tables do not count against it.

8. Know what this does not cover

Passing these probes does not prove that no bypass exists. They cover the listed statements, on one server, with synthetic data. The fixture does not test your driver or a connection pooler, lock_timeout, idle_in_transaction_session_timeout, transaction_timeout, the connection limit, or two sessions competing for one advisory lock. It says nothing about the semantic correctness of a query the model writes, or about what the reader's SELECT grant exposes to the user who sees the answer. Referential integrity checks "always bypass row security," and PostgreSQL warns of covert channels there; the reader here cannot write, and the fixture does not probe that path.

Superusers and BYPASSRLS roles bypass row security, and table owners normally do. Confirm in your own transcript that your application's reader is none of these, then rerun the suite under its real credentials whenever a grant, policy or driver changes.

Execution record

On November 6, 2026 the final fixture ran on PostgreSQL 18.6 on x86_64-windows, compiled by msvc-19.44.35229, 64-bit, on a temporary local instance created for the run, reachable only on 127.0.0.1, and deleted afterward. All 42 checks passed with exit code 0, and the teardown reported 0 leftover roles. Earlier runs of the suite failed on the row-flood probes, because we had assumed a cursor would stop a generate_series flood at 101 rows and that the timeout would fire before the disk limit. We rewrote those probes to record what the server did. Authentication on the instance was trust, so the run says nothing about password login, and the password in the setup script was never exercised.