Don't parse SQL to make a query runner read-only

Say you're building a tool that lets people run ad-hoc SQL against a database, and you want a read-only by default mode — a safety net so a fat-fingered UPDATE doesn't nuke a table.

The tempting first instinct is to look at the SQL:

FORBIDDEN = ("insert", "update", "delete", "drop", "truncate", "alter", "create")

def is_read_only(sql: str) -> bool:
    lowered = sql.strip().lower()
    return not any(lowered.startswith(word) for word in FORBIDDEN)

Please don't ship this. It's a sieve:

WITH x AS (DELETE FROM orders RETURNING *) SELECT * FROM x;   -- starts with WITH
update orders set total = 0;                                  -- leading whitespace, casing
SELECT do_evil();                                             -- a function that writes
/* comment */ DELETE FROM orders;                             -- starts with a comment

You're now writing a SQL parser to play whack-a-mole with a language designed to be extensible. Every CTE, comment style, and side-effecting function is a new bypass. This is the same losing game as sanitizing HTML with regex.

Let the database do it

Postgres already has the exact feature you want. A transaction can be declared read only, and the server — not your code — refuses any write inside it:

BEGIN;
SET TRANSACTION READ ONLY;
-- now any INSERT/UPDATE/DELETE/DDL raises:
--   ERROR:  cannot execute DELETE in a read-only transaction

This catches everything: the CTE trick, the writing function, DDL, SELECT ... FOR UPDATE, all of it. You're not guessing what the SQL does — you're telling the engine "whatever this is, don't let it write," and letting the executor enforce it where it actually knows.

Here's the whole thing in Python (psycopg2), which is roughly what I run in cli2ui's SQL runner:

def run_query(self, sql_text, *, max_rows=1000, timeout_ms=15000, read_only=True):
    with self._connect() as conn:
        conn.autocommit = False          # we need a real transaction
        with conn.cursor() as cur:
            if read_only:
                # Must be the FIRST statement in the transaction.
                cur.execute("SET TRANSACTION READ ONLY")
            cur.execute("SET LOCAL statement_timeout = %s", [timeout_ms])
            cur.execute(sql_text)        # the user's SQL, unparsed
            rows = cur.fetchmany(max_rows + 1) if cur.description else []
        conn.rollback()                  # read-only path never persists anything
    ...

Three things doing real work here:

1. SET TRANSACTION READ ONLY must be the first statement in the transaction — Postgres rejects it once the transaction has touched data. So set it before anything else.
2. statement_timeout stops SELECT pg_sleep(99999) or an accidental cross join from pinning a backend forever.
3. rollback() on the way out, even for read-only. There's no write to commit, and it cleanly releases any snapshot/locks the query took. (If you ever flip read_only=False, that's where a commit() goes — ideally after a safety backup.)

Notice what's not here: any inspection of sql_text. It goes to the server verbatim. That's the point.

"But I want to show a nice error / block it earlier"

You still can — but as UX, not security. Surface the server's cannot execute X in a read-only transaction message nicely, or grey out a "write" toggle. Just don't let a string check be the thing standing between a user and their data. Defense belongs where the executor is.

The caveat

Read-only transactions block writes, not resource abuse. A read-only query can still be a monstrous cartesian join. That's what statement_timeout (and a row cap on the fetch) are for — they're the other half of "ad-hoc SQL, but safe." And of course, read-only is per-transaction: it does nothing if you forget to start a transaction, or if autocommit silently wraps each statement in its own.

Stop parsing SQL. The database already knows what's a write — ask it.

This is one piece of cli2ui — a local-only web UI over the psql commands you keep half-remembering. No AI, no SaaS. It's MIT-licensed on GitHub. What command do you reach for that should be a button?