Why “give the chatbot database access” is the wrong problem statement

Text-to-SQL offers an appealing interface to management analytics: an executive asks a question in plain language, the system writes SQL, and returns a table or a short explanation. For a small business, the value is obvious—routine questions do not all have to join an analyst’s queue.

However, a language model is not an authorization system and cannot guarantee that a query is correct. It can select the wrong table, confuse order date with payment date, omit cancelled transactions, or generate an expensive join. If the model sees the full technical schema and executes arbitrary SQL with broad privileges, a wrong answer becomes a confidentiality and availability risk.

A safe design therefore does not begin with model selection. The business must first define which questions are allowed, which metric definitions are authoritative, and which data each role may see. The model is a translator from intent to a draft—not a database administrator.

Establish the boundary: a separate analytics plane

The first rule is not to connect the assistant to the primary transactional database using the application’s credentials. Give it a separate read endpoint: a replica, a data mart, or an analytics database with a known and acceptable refresh delay.

Do not expose every source table. Publish a small set of views with business-oriented names. Instead of dozens of order, payment, and refund tables, expose a `sales_daily` view with agreed definitions of revenue, paid orders, refunds, and sales channels. PostgreSQL object privileges can restrict access, while views can hide unnecessary columns and present a stable data contract.

This has three benefits:

  • the model receives a shorter, less ambiguous schema;
  • sensitive columns never enter the available catalog;
  • changes inside the ERP do not constantly break the assistant’s logic.

A view is not a replacement for authorization. Some simple PostgreSQL views can be automatically updatable, so the assistant role must still receive only the required `SELECT` privilege. `security_invoker`, `security_barrier`, and the policies on underlying tables must be tested against real scenarios.

A semantic layer matters more than a larger prompt

The model should not infer the meaning of metrics from column names. Alongside the schema, it needs a concise metric catalog containing:

  • the metric definition and formula;
  • allowed dimensions and filters;
  • time zone and business calendar;
  • rules for refunds, VAT, and cancellations;
  • metric owner and refresh timestamp;
  • examples of valid questions and queries.

If the company has two definitions of margin, the system must not pick one probabilistically. It should ask for clarification. This is an important refusal class: a good assistant can state that a question is ambiguous and explain what information is missing.

The catalog can be kept as version-controlled YAML or JSON, with only the relevant fragment supplied to the model. A pilot does not need a complex knowledge graph. Ten to twenty agreed metrics are usually more useful than automatic access to hundreds of tables.

The model proposes a query; the gateway decides whether it may run

A robust flow separates the stages:

1. The user authenticates through the company’s normal identity system; the service obtains the role, department, and allowed data scope.
2. Only descriptions of permitted views and metrics are added to the request.
3. The model returns a structured draft: intent, SQL, datasets, expected grain, and assumptions.
4. A deterministic gateway parses the SQL into an abstract syntax tree and evaluates policy.
5. The database produces a plan without executing the statement; the gateway evaluates volume, cost, and risk.
6. An approved query runs in a read-only transaction under hard limits.
7. The result is validated and reduced before the model receives it for explanation.

Filtering a SQL string with regular expressions is not enough. Comments, nested queries, functions, and dialect-specific syntax make such controls brittle. Use a real SQL parser and apply policy to the query structure.

A minimum policy should enforce:

  • exactly one statement and only `SELECT`;
  • allowlisted schemas, views, and columns;
  • no `INSERT`, `UPDATE`, `DELETE`, `MERGE`, DDL, `COPY`, or unknown functions;
  • limits on joins, subqueries, and time range;
  • a mandatory `LIMIT` for detailed output;
  • no system catalogs or technical schemas;
  • verification of user context and metric-catalog version.

The function allowlist deserves special attention. A statement may begin with `SELECT` while invoking a function with side effects. PostgreSQL manages `EXECUTE` privileges on functions separately; the assistant’s service role should not inherit broad permissions.

Three independent safeguards inside the database

The gateway reduces risk but should not be the only line of defense. The database needs independent controls.

First, create a dedicated least-privilege role. It receives `CONNECT`, access only to the analytics schema, and `SELECT` on specific objects. It owns no tables, cannot create objects, and has no `BYPASSRLS` capability.

Second, use read-only transactions. PostgreSQL documents that read-only mode blocks ordinary data-modification commands and DDL. It is protection against accidental writes, not a universal sandbox: temporary objects and functions still require separate control. Combine read-only mode with object privileges and a function allowlist.

Third, enable Row-Level Security where scopes differ by user, branch, or legal entity. RLS policies restrict rows according to a role or expression, and PostgreSQL applies default deny when RLS is enabled but no applicable policy exists. This means a branch can see only its own transactions even if the model omits a filter. Superusers, roles with `BYPASSRLS`, and usually table owners bypass RLS; the text-to-SQL service role must be none of these.

A read-only SELECT can still overload the database

A query does not need write privileges to consume CPU, memory, and I/O. Before execution, the gateway can run ordinary `EXPLAIN` without `ANALYZE`. It returns the plan, estimated cost, and estimated row counts without running the query. `EXPLAIN ANALYZE` is unsuitable as a safety pre-check because it actually executes the statement.

Planner estimates are not guarantees, so hard runtime limits are still required:

  • a session-level `statement_timeout` for the assistant;
  • small limits on returned rows and response size;
  • a separate connection pool and concurrency cap;
  • a queue with lower priority than critical workloads;
  • cancellation when the client request is abandoned;
  • monitoring of p95 latency, timeouts, and data scanned.

The strongest boundary is a separate replica or data mart with its own resource budget. A heavy question then cannot block order processing. A replica introduces lag, so the user must see the data freshness timestamp and “today” queries must account for it.

Make every answer auditable

A polished sentence alone is not enough. The user should see:

  • the period and filters applied;
  • the definition of the key metric;
  • the data freshness timestamp;
  • the row or aggregate count behind the result;
  • the SQL or a clear disclosure of its logic;
  • any truncation warning;
  • unresolved ambiguity and confidence limits.

For financial, HR, and contract metrics, use a two-step flow: show the query and expected scope first, then require a person to approve execution or publication. Any action based on the answer—changing a price, sending a campaign, writing to a CRM—must go through a separate typed tool, not the same SQL channel.

Separate technical logs from result data. A technical audit record can contain the user ID, normalized-query hash, policy version, timestamps, plan estimate, and policy outcome. Returned rows may contain personal data, so retaining them requires a separate, explicit policy for access and deletion.

A two-week pilot

Start with 20–30 recurring questions from one department instead of free-form chat over the entire database. Good candidates include weekly sales, refund share, overdue receivables, and sales-team workload.

For each question, record the reference SQL, accepted phrasings, required filters, and metric owner. Then test real variations and measure separately:

  • the share of drafts accepted by the gateway;
  • accuracy of metric, period, and filter selection;
  • result agreement with the reference answer;
  • correct refusals on ambiguous questions;
  • p95 response time and timeout rate;
  • cost per accepted answer, including human review.

Keep the first release in draft mode: the assistant prepares the query and explanation, while an analyst approves the result. Direct delivery to executives should be enabled only for question classes with stable quality and low error cost.

What leaders should take away

Text-to-SQL is not a way to replace access control with natural language. It is a governed analytics product built around a narrow metric catalog, a separate read-only plane, and several independent safeguards.

The practical next step is small: choose one data mart, create a dedicated `SELECT`-only role, define ten key metrics, and collect twenty reference questions. This pilot quickly separates model limitations from the more common problems of inconsistent metric definitions and poor data quality. Most importantly, even convincing SQL remains a draft until policy and the database authorize its execution.