Read-Only SQL Analyst Agent for Postgres

A SQL analyst agent is useful the day someone asks "how many orders failed since noon" and dangerous the day it invents an UPDATE. The design is not a clever prompt. It is a Postgres role that cannot write, a table allowlist, a statement timeout, a row cap, and a runner that executes only after those checks pass. The model proposes SQL. The runner decides. Production data stays on the VPS; the agent never gets a superuser URL. On a Lucknow box with Intel Xeon Platinum and High-Speed SSD, a bounded SELECT is cheap. An unbounded join is how you take the primary down.

Create a role that cannot write, then ignore the prompt about writes

In Postgres, GRANT SELECT on a schema is not enough if the role can still lock tables or call a function that writes. Create a dedicated role agent_ro, set default_transaction_read_only, set statement_timeout to 5s, set idle_in_transaction_session_timeout to 5s, and REVOKE CREATE on the database and public schema. Grant SELECT only on the tables the analyst is allowed to see. Do not grant pg_read_all_data unless you mean it. Connect with a URL that uses that role and a password stored in a 0640 env file the model never reads.

The runner still parses the SQL. Read-only at the server is defense in depth, not a license to send whatever the model typed. If pg_dump lives on the same VPS, the agent does not get that binary either.

Parse, allowlist, wrap, then run

Accept a single statement. Reject multiple statements split by semicolons. Reject anything whose first keyword is not SELECT, WITH, or EXPLAIN. Walk the FROM and JOIN identifiers and compare them to the allowlist. Wrap the statement: SET statement_timeout, SET default_transaction_read_only, then SELECT * FROM (original) AS agent_q LIMIT 200. That outer LIMIT is how you survive a missing one. Log the SQL, the duration, and the row count. Never log the result set if it may contain emails; log hashes or aggregates instead.

CREATE ROLE agent_ro LOGIN PASSWORD 'set-in-env-not-here';
ALTER ROLE agent_ro SET default_transaction_read_only = on;
ALTER ROLE agent_ro SET statement_timeout = '5s';
ALTER ROLE agent_ro SET idle_in_transaction_session_timeout = '5s';
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO agent_ro;
GRANT SELECT ON TABLE public.orders, public.order_events, public.payments TO agent_ro;

Put the allowlist in the runner too, not only in GRANT. Tables you should usually withhold: users (email, password hashes), sessions, tokens, anything with a national ID. If a question needs a user count, expose a view that returns counts by day, not rows.

import re

ALLOW = {"orders", "order_events", "payments"}
WRITE = re.compile(r"(INSERT|UPDATE|DELETE|DROP|ALTER|TRUNCATE|COPY|GRANT|REVOKE|CREATE)", re.I)

def gate(sql):
    s = sql.strip().rstrip(";")
    if ";" in s:
        raise ValueError("one statement only")
    if WRITE.search(s):
        raise ValueError("write keyword")
    head = s.split()[0].upper()
    if head not in ("SELECT", "WITH", "EXPLAIN"):
        raise ValueError("not a read")
    tables = set(re.findall(r"(?i)(?:from|join)s+([a-z_][a-z0-9_]*)", s))
    extra = tables - ALLOW
    if extra:
        raise ValueError("table not allowed: " + ",".join(sorted(extra)))
    return "SELECT * FROM (" + s + ") AS agent_q LIMIT 200"

String concatenation in the wrapper avoids template literals and keeps the original statement visible in logs. If EXPLAIN is requested, skip the wrapper and require ANALYZE only on a replica. A primary on High-Speed SSD still feels EXPLAIN ANALYZE; do not let the agent sprinkle it on every question.

Answers are numbers plus the SQL you ran

The model should return a short English summary, the SQL, and a caveat. The UI should show the SQL above the summary so a human can spot a bad join. If the runner errors on timeout, tell the user the query was killed, not that there were zero rows. If the allowlist blocked a table, say which table, not "I cannot help." Cache identical questions for a minute so a Slack storm does not open fifty backends.

Schema context belongs in the prompt as a stub: column names and types for allowlisted tables, plus one example query per table. Do not dump information_schema wholesale. Do not include other hosts or other regions. This database is on this VPS in Lucknow. Connection pooling through PgBouncer is fine; the agent still uses agent_ro.

Replica if you have one, primary only if you do not

If you can stand a replica, point the agent there. If you cannot, keep the timeout aggressive and run only during working hours until you trust the evals. L3/L4 DDoS filtering on the public NIC is unrelated to Postgres; do not expose 5432. The agent talks to localhost or a private listen address. Fixture the dangerous prompts: "delete failed orders," "drop the events table," "copy users to /tmp." Those must fail the gate in CI before you ever connect this to Slack.

Takeaway

A SQL analyst agent is a SELECT role, a parser, a table allowlist, and a LIMIT. The model writes queries; the database user cannot write rows. Stand it up next to Postgres on a Netbay Lucknow VPS — netbayhosts.in will give you the Ubuntu 24.04 host in under 60 seconds.