How to Audit a Model's SQL Before It Runs on Production Tables

Before you configure anything, write down what the agent will touch: the tables that matter, the columns that matter, and which of those are sensitive. You cannot check what you have not defined, which is the same point data validation makes in general: validation begins with deciding what "valid" means for each field (Teradata on data validation).

Start with a schema inventory that never reads business rows. In PostgreSQL, the catalog holds table sizes, column types, and indexes.

SELECT
    c.relname            AS table_name,
    c.reltuples::bigint  AS estimated_rows,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'app'
  AND c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC;

Then record each object with an owner, a sensitivity class, and a permitted access mode.

That table makes the rest of the process fast. The reviewer's first question is "which of these does the query touch?" If the answer includes a Restricted table, the review stops there. For PII, prefer masked views over column grants, because grants on base tables widen quietly and audit badly. PostgreSQL's privilege model lets you combine schema usage with table-level SELECT, and the safe default is to deny the public schema and grant only what is needed, as Citus Data's read-only Postgres walkthrough shows. That walkthrough also reminds you that GRANT and REVOKE apply to objects that exist when they run, so default privileges matter for objects created later.

The agent's connection is more than a user; it carries session defaults. Pinning the search path so a stray table name cannot shadow a real one belongs in the same hardening step, and the PostgreSQL documentation on client connection defaults lists exactly which settings you can pin per role.

The most effective control is the one you set once and the agent cannot change: a role that can read and nothing else.

-- 1. The login role the agent uses.
CREATE ROLE agent_reader LOGIN PASSWORD 'replace-with-secret';

-- 2. Deny by default, then grant one schema and one table class.
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA app TO agent_reader;
GRANT SELECT ON app.orders, app.events, app.metrics_daily TO agent_reader;

-- 3. Future objects stay readable and nothing more.
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT ON TABLES TO agent_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON TABLES FROM agent_reader;

-- 4. Cap duration for every session this role opens.
ALTER ROLE agent_reader SET statement_timeout = '15s';

-- 5. Pin the search path.
ALTER ROLE agent_reader SET search_path = app, pg_catalog;

Two details matter. ALTER ROLE ... SET statement_timeout applies to every session the role opens, so the agent cannot forget it; a per-session SET statement_timeout TO '15s' at connection setup works too, and this Stack Overflow thread covers the millisecond unit and the fact that .psqlrc is skipped for -c invocations. Second, a timeout bounds duration but not result size. Enforce the row cap server-side, in a view or function that hard-codes the limit, so a missing limit in agent SQL cannot get around it. If your engine supports row-level security, you can also restrict which rows a role sees at all; the Database Administrators Stack Exchange discussion on limiting a user's SELECT to a fixed number of rows surveys the realistic combinations of row-level security, fetch counts, and timeouts.

A timeout does not make a query cheap; it makes it stop. A scan that burns 2 GB inside 15 seconds has already consumed the I/O. That is why the next gate exists.

The first four are pure configuration and belong in place before any agent connects. The connection limit is the forgotten one: an agent that opens 200 connections in a retry loop needs no SQL to hurt you.

The plan tells you what the query intends. EXPLAIN shows the chosen plan, including scan type, join algorithm, and estimated cost, as described in the PostgreSQL EXPLAIN documentation. Reading it takes about thirty seconds and catches most ways an agent can be expensive.

1. Scan types. Every sequential scan on a table over roughly a gigabyte is a red flag. Ask whether the query should touch that much of the table and whether an index exists on the filter column.
2. Filter placement. Look at the filter line under each scan node. If a query joins five tables and the only filter sits on the smallest, the other four are read whole. That is the missing-predicate problem, the most common defect in agent-written SQL.
3. Estimates versus reality. Estimated rows come from statistics. If a node estimates 100 rows and the query returns 2 million, the plan is likely wrong. EXPLAIN (ANALYZE) executes the statement for actual counts, and the documentation is explicit that side effects happen; on a clone that is fine, on production wrap it in a transaction you will roll back.
4. Plan shape. Nested loop joins suit small outer sets and punish large ones; a hash join with a large build side is a memory spike. You are not optimizing the plan, only noticing when its shape does not match the data.

Worked comparison. Two agent queries answer "customers who ordered last month." The first:

Seq Scan on orders  (cost=0.00..182000.00 rows=100000 width=120)
  Filter: (created_at > '2024-01-01'::date)

On a table of 4 million rows, that reads the whole table for one month of data. The revised version filters on an indexed column:

Index Scan using orders_created_at_idx on orders  (cost=0.43..8200.00 rows=86000 width=120)
  Index Cond: (created_at > '2024-01-01'::date)

The estimated cost falls by more than an order of magnitude here, and the plan now responds to the date filter rather than table size. The exact numbers depend on statistics and data, which is why you copy the plan into the review record instead of describing it in chat.

One limitation to plan around: EXPLAIN estimates costs in arbitrary units, not seconds or dollars. Use them to compare candidate queries and spot scans; use EXPLAIN (ANALYZE, BUFFERS) on the clone when you want a real number. Never turn plan cost into an SLA.

The last gate is a rehearsal against a clone or shadow schema with the same table structure, indexes, and statistics as production, holding either anonymized rows or a representative sample. Two workable approaches:

• Structure-only clone. Recreate the schema and indexes on a fresh instance and load a known sample. Fast and cheap, but plans on unpopulated tables are often wrong because the planner has no statistics.
• Statistical clone. Restore a recent backup, anonymize PII before the agent connects, and run. Plans are realistic and results predict production closely, which is the only way to catch estimate errors and missing-index effects at production scale.

Either way, the clone carries the same role settings as production: same timeout, same row cap, same search path. A dry run that passes because the clone is more permissive is a false positive.

The protocol, in five steps. Check the query out of the review record and run it in the sandbox runner, never in psql against production. Run EXPLAIN (COSTS, VERBOSE) and compare against the approved plan if one exists. Execute with the row cap enforced and capture the count and first twenty rows. Run EXPLAIN (ANALYZE, BUFFERS) inside a transaction, then roll back; the documentation covers this pattern explicitly. Finally, record the query, plan, row count, elapsed time, and reviewer in the review record.

A small harness makes it repeatable.

function dry_run(sql, reviewer):
    plan  = explain(sql, costs=true, verbose=true)
    rows  = execute(sql, limit=ROW_CAP, timeout=TIMEOUT)
    stats = explain_analyze(sql, buffers=true, in_transaction=true)
    rollback()
    record = { query: sql, plan: plan, row_count: rows.count,
               sample: rows.first_20, analyzed: stats,
               reviewer: reviewer, timestamp: now() }
    review_store.append(record)
    return record

The record is the deliverable. It lets the next reviewer spend two minutes instead of twenty, and it turns a stream of one-off gambles into a library of vetted queries. Engines with native auditing can write most of it automatically; SQL Server's audit feature records event time, principal, object, and statement text, as the SQL Server audit records reference lists. Postgres has pgaudit and log capture. The tool matters less than the fact that the record exists outside the chat session that produced the query.

Cohort retention is a good test case because it is a common agent task with a recognizable shape: bucket users into signup month, map each activity to a month number, join the two into a retention matrix. Holistics' SQL walkthrough lays out those steps, Codefinity's cohort lesson covers the retention-rate variant, and StrataScratch's retention guide explains the definitional trap: aggregate retention mixes old and new customers, which is why cohorts exist.

Now run the gates. Gate 1: the query joins users and activities, so grant SELECT on exactly those two tables. Gate 2: activities is the large one, so a 15-second timeout and a 10,000-row cap apply. Gate 3: the plan should show an index or bitmap scan keyed on the cohort month or signup date, with activity filters pushed down; two full scans joined together ends the review. Gate 4: dry run on the clone and confirm the first cohort sizes against a manual count. This is also where agents slip: joining activities to cohorts by user without restricting activity dates to the cohort window inflates early-month retention. No plan catches that. A sample of the first three cohorts does, which is why Gate 4 asks you to look at rows, not just plans.

JSON plans diff in version control, so the next review of the same query is a code review rather than a fresh read; the EXPLAIN documentation lists the supported formats. Keep a short deny list of shapes that always need a second reviewer regardless of the agent's confidence: mass updates, DDL, unlimited selects on large tables, and anything touching a Restricted table.

If you are wiring this pipeline up for a team, routing the agent through a single gateway with per-key scoping is often simpler than one integration per provider. Quanta Inference (https://quantaintelligence.ai/inference) puts text, image, and embedding models behind one OpenAI-compatible key, which keeps the blast radius to the credentials you issue. Teams that want the review process designed with them rather than bolted on later can book a call (https://quantaintelligence.ai/calendar).