Natural Language to SQL: Building a Guarded Postgres Agent

Natural language to SQL turns a plain-English question into a database query an AI agent can run. It is genuinely useful and frequently wrong, and that combination is exactly what makes it risky to drop in front of your production database. This article explains how the technique works, where it breaks, and how to build a Postgres query agent your team can actually trust.

Key Takeaways Natural language to SQL hits roughly 85 to 95 percent accuracy on standard queries and degrades sharply on complex joins and ratios. The real design problem is containment, not accuracy. A 95-percent-accurate generator is still unsafe if the other 5 percent can run a DROP or an unbounded scan. Split the system in two: the agent reasons and writes SQL, and a deterministic, permission-checked layer decides what actually runs. Hard guardrails: read-only role, blocked DML, allowed views only, forced LIMIT, EXPLAIN cost ceiling, and full query plus result logging. Guardrails reduce risk. They do not erase it. Sensitive schemas still need column masking and human review.

What natural language to SQL is (and what it is not)

Natural language to SQL is a technique where a large language model reads a question written in ordinary language and produces a SQL query that answers it. Ask "how many paying customers signed up last month" and the model returns a SELECT with the right COUNT, WHERE clause, and date math. The model is not querying your data directly. It is writing text that happens to be SQL.

The quality of that text depends almost entirely on context. A model with no knowledge of your tables will guess column names and invent joins. A model handed the relevant schema, a few example queries, and notes on what each view means will produce far better SQL. This is why production systems feed the model a curated schema for the allowed tables and views, sample rows, and a handful of known-good queries before it writes anything.

Here is what natural language to SQL is not. It is not a guarantee of correctness, and it is not an execution engine. The model generates a candidate query. Whether that query runs, and what it is allowed to touch, is a separate decision that belongs to your application, not the model.

Accuracy reality: On clean, simple schemas, general-purpose tools land around 85 to 95 percent. On the BIRD benchmark, which uses messy real-world databases, GPT-4 with curated knowledge evidence reached only 54.89 percent execution accuracy, against human performance of 92.96 percent (BIRD benchmark analysis). Leading 2026 systems reach about 81.95 percent, but only through elaborate multi-step pipelines. This is the performance cliff: accuracy looks great on toy queries and falls off on the complex joins and ratios real analysts ask about.

When an NL-to-SQL agent over Postgres actually helps

An NL-to-SQL agent earns its keep when many people need answers from the same Postgres database and most of them cannot or should not write SQL by hand. These are the situations where it pays off:

Use cases worth building for

• Self-serve internal data Q&A: a support lead asks "how many tickets stayed open more than 48 hours last week" in Slack and gets a number back, without filing a request to the data team.
• Customer cohort analysis: a growth PM segments users by signup month and plan tier to see retention, iterating in plain language instead of waiting on a dashboard change.
• Anomaly checks: an on-call engineer asks whether error rates by region spiked in the last hour, using read-only views over operational tables.
• Scheduled report generation: a daily metrics digest runs a fixed set of questions every morning and posts the results to a channel.
• Ad-hoc metric lookups: a finance analyst checks gross revenue for a date range without opening a SQL client.

Notice what these share. The reader needs a read, not a write. Nobody in this list should be issuing an UPDATE through an LLM. That observation drives the entire build.

What you need to build one

Two pieces do the heavy lifting: a locked-down Postgres setup, and a deterministic execution layer that sits between the agent and the database. Get the Postgres side right first.

The Postgres side

Create a dedicated role that can read and nothing else. Grant it SELECT on a small set of curated views rather than raw tables, so the agent never sees columns it should not. Use EXPLAIN to estimate query cost before execution and reject anything above a ceiling. Connect the agent to Postgres only through this role. The role, not the prompt, is your hard boundary.

-- 1. A read-only role the agent connects as
CREATE ROLE nlsql_readonly NOLOGIN;
REVOKE ALL ON DATABASE app FROM nlsql_readonly;
GRANT CONNECT ON DATABASE app TO nlsql_readonly;
GRANT USAGE ON SCHEMA analytics TO nlsql_readonly;

-- 2. Grant SELECT on curated VIEWS only, never base tables
GRANT SELECT ON analytics.customer_metrics TO nlsql_readonly;
GRANT SELECT ON analytics.ticket_summary TO nlsql_readonly;

-- 3. Make the read-only intent enforced at the session level
ALTER ROLE nlsql_readonly SET default_transaction_read_only = on;
ALTER ROLE nlsql_readonly SET statement_timeout = '5s';

-- 4. Estimate cost before running anything (no execution)
EXPLAIN SELECT count(*) FROM analytics.customer_metrics WHERE plan = 'pro';

The read-only role, the view-only grants, and the session-level read-only flag are defaults from the PostgreSQL documentation on roles and EXPLAIN. They cost almost nothing and remove the worst failure mode entirely. A connection through nlsql_readonly physically cannot write.

Pros and cons, and the honest gotchas

Be clear-eyed about what you are getting. The table below sets a guarded agent against the common alternatives so you can decide what fits.

• Raw LLM with a write-capable connection
• • Who it suits: Nobody. Avoid.
  • Production safety: None; one bad query can DROP or UPDATE.
  • Main gotcha: A hallucinated statement runs against live data.
• IDE or MCP query tool (Cursor, DBHub)
• • Who it suits: A single developer querying their own data
  • Production safety: Fine for personal use, weak for shared prod
  • Main gotcha: No central governance or audit across a team.
• Hosted NL-to-SQL SaaS
• • Who it suits: Teams wanting a fast off-the-shelf start
  • Production safety: Varies by vendor, often opaque
  • Main gotcha: Your schema and data leave your boundary.
• Guarded agent plus deterministic executor
• • Who it suits: Teams giving non-developers prod access
  • Production safety: Strong; reads only, every query logged.
  • Main gotcha: You build and own the guardrail layer.
• Hand-written SQL by the data team
• • Who it suits: Low-volume, high-stakes questions
  • Production safety: Highest, but slow
  • Main gotcha: Becomes a request queue and a bottleneck.

The recurring gotchas across every row are the same three. Accuracy falls off a cliff on complex joins and ratios. Generic column names create schema ambiguity the model resolves by guessing. And any path that hands the model a write connection is one hallucination away from data loss. The guarded pattern is the only row that neutralizes the third.

A worked example: the Guarded Postgres Query Agent

What it does

A teammate asks a question in Slack: "how many pro-plan customers signed up in May." The agent reads the allowed schema and a few example queries, writes a candidate SELECT, and hands that SQL to a separate app that enforces the guardrails before anything touches Postgres. The answer comes back in the channel, with an option to export the rows to Google Sheets. The agent never holds a database connection of its own.

How the steps wire together

1. Trigger. A message in a Slack channel, or an optional scheduled daily-metrics digest, kicks off the run.
2. Context read. The agent pulls the schema for the allowed views and a short set of example queries from a Notion page, so it writes SQL against real column names instead of guessing.
3. Generate. The agent produces one candidate SQL query targeting the read-only views.
4. Guarded execution. The agent passes the SQL to a Major-built "Run Read-Only Query" app with typed inputs (sql, max_rows). This app, not the agent, talks to Postgres.
5. Deliver. Formatted rows post back to Slack. A button exports the full result set to Google Sheets for anyone who wants to pivot on it.

The connectors in play are PostgreSQL, Slack, Notion, and Google Sheets. Slack is where the question and answer live. Notion holds the schema and example context. Google Sheets is the export target. Postgres is reached only through the typed app. In Pipedream terms this mirrors an Execute Custom Query action on a Postgres connection, except here the query is fixed to read-only and gated.

What governance the agent needs

The guardrail layer is the whole point. The deterministic app applies these checks server-side, in order, and refuses the query if any one fails:

• Read-only database role. The app connects as nlsql_readonly, which cannot write even if asked to.
• Block all DML and DDL. Reject any statement that is not a single SELECT. No INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, or GRANT.
• Allowed tables and views only. The query may reference the curated analytics views and nothing else.
• Forced LIMIT. If the SQL has no LIMIT, the app injects one capped at max_rows so a broad query cannot stream the whole table.
• EXPLAIN cost ceiling. The app runs EXPLAIN first and aborts if the planner's estimated total cost exceeds a set threshold, stopping runaway scans before they start.
• Query and result logging. Every submitted query, its decision (allowed or rejected), and its result size is logged for audit.

Run-as-invoker identity ties each query to the person who asked, so their own data permissions apply. The agent cannot escalate. To make this concrete, here is a guarded executor that rejects a destructive query and forces a LIMIT before it ever reaches the read-only role.

import re

BLOCKED = re.compile(
r"\b(insert|update|delete|drop|alter|truncate|grant|revoke|create|copy)\b",
re.IGNORECASE,
)
MAX_COST = 50000.0 # EXPLAIN total-cost ceiling

def run_read_only_query(conn, sql: str, max_rows: int = 1000):
stripped = sql.strip().rstrip(";")

# 1. Single statement only
if ";" in stripped:
raise ValueError("rejected: multiple statements not allowed")

# 2. Must be a SELECT, must contain no DML/DDL keywords
if not stripped.lower().startswith("select"):
raise ValueError("rejected: only SELECT queries are permitted")
if BLOCKED.search(stripped):
raise ValueError("rejected: write/DDL keyword detected")

# 3. Force a LIMIT if the model did not add one
if " limit " not in stripped.lower():
stripped = f"{stripped} LIMIT {max_rows}"

# 4. Cost check via EXPLAIN before executing
with conn.cursor() as cur:
cur.execute(f"EXPLAIN (FORMAT JSON) {stripped}")
plan = cur.fetchone()[0][0]["Plan"]
if plan["Total Cost"] > MAX_COST:
raise ValueError(
f"rejected: estimated cost {plan['Total Cost']} exceeds ceiling"
)

# 5. Execute against the read-only role and return rows
cur.execute(stripped)
return cur.fetchall()


# A hallucinated destructive query is stopped at the gate:
# run_read_only_query(conn, "DROP TABLE customers")
# -> ValueError: rejected: only SELECT queries are permitted

What this doesn't cover. This guardrail set blocks writes and runaway scans. It does not handle column-level sensitivity. A read-only role can still SELECT a salary or an email if the view exposes it, so sensitive schemas need column masking and, for anything regulated, a human review step before results leave the system. A regex DML block is a backstop, so the read-only role and view-only grants remain the real boundary. Guardrails reduce risk. They do not remove it.

How do you stop an AI from running destructive SQL?

You stop it in three layers, and you do not rely on the model for any of them. First, connect through a read-only Postgres role, so a write is impossible at the database level regardless of what the model generates. Second, put a deterministic execution layer in front of the database that rejects any statement that is not a single SELECT and refuses known DML and DDL keywords. Third, log every query the agent runs so a bad pattern is caught and reviewed. The model is allowed to suggest. It is never allowed to execute.

Build this in Major

The pattern above is a deterministic app paired with an AI agent, which is the split Major is designed around. The agent reasons over the schema and writes the SQL. The typed "Run Read-Only Query" app holds the only Postgres connection and enforces the guardrails before a single row is read.

To assemble it, you connect four resources and let the agent orchestrate them. Slack carries the question and the answer. Notion supplies the schema and example queries the model needs for context. PostgreSQL is reached only through the read-only app. Google Sheets receives the export.

1. Connect PostgreSQL with a read-only role, Slack, Notion, and Google Sheets.
2. Build the "Run Read-Only Query" app with typed inputs (sql, max_rows) and the guardrail checks from the worked example.
3. Then build the query agent so it reads context from Notion, writes a candidate query, and can only execute through the typed app.
4. Add the Slack trigger and the optional daily-metrics schedule, and confirm every run is logged.

The result gives your team self-serve answers from Postgres without handing an LLM a write connection. The agent drafts. The app decides. The role enforces.

Related articles

• Jira Automation for Engineering-Ops AI Agents
• AI Agents for Notion: 5 Workflows That Use Notion as the Data Layer
• AI Agents for HubSpot: 5 Workflows That Actually Earn Their Keep