# Connecting an AI Agent to Databases: Safe Text-to-SQL

> An AI agent answers database questions safely when it connects through its own read-only role limited to approved views, every query runs under a time and row limit, and permissions are enforced by the database rather than the prompt. Free-form text-to-SQL is only one of three possible designs.

Canonical URL: https://www.ruebstudio.de/en/guides/ai-agent-integrations/connect-ai-agent-to-database  
Author: Dr.-Ing. Marcus Rüb, CEO  
Published: 2026-10-31

Most day-to-day business questions already have an answer sitting in a database: which orders are overdue, how often a spare part was replaced, which suppliers missed their delivery dates. An AI agent can take these questions in plain language and answer them from the data. Whether that is safe depends far less on the model than on permissions, limits and the access pattern you choose.

## What does connecting an AI agent to a database actually mean?

A database-connected AI agent turns a question into a query, runs it through a tool and phrases the result as an answer. The model never touches the database directly. It calls a tool that your application exposes, and that tool defines what is possible at all.

Questions where this pays off:

- Sales: "Which quotes to customers in the Midwest are older than 30 days and still open?"
- Field service: "How many times was part 4711 replaced this year, broken down by machine model?"
- Procurement: "Which suppliers exceeded their agreed lead time last quarter?"
- Management: "How does this month's order intake split across product lines?"

Today these questions often end up as a ticket for IT or a spreadsheet export handled by whoever knows SQL. The agent does not replace your dashboards. It covers the long tail of one-off questions that never justify a report of their own. How such an agent fits into the wider application landscape is covered on the [AI agents overview](https://www.ruebstudio.de/en/ai-agents).

## How does an AI agent query a database?

An agent that queries a database has four building blocks: a model that interprets the question, context describing the schema or metrics, a tool that executes the query, and a database connection with restricted privileges. Security comes from the last two, not from the prompt.

**1. The model** reads the question, picks a tool and produces arguments: a full SQL statement, the name of a predefined query, or a set of metrics and dimensions, depending on the design.

**2. Context.** The model knows nothing about your data model. It needs table and column names, what they mean, sample values and business rules such as "revenue means net invoices excluding credit notes". The more cryptic the column names (`CUSTNO2`, `FLG_X`), the more this description matters.

**3. The tool** is a function in your application that receives the call, validates it and runs it. Limits, logging and human approval live here.

**4. The connection** uses a dedicated service account that can only read, and only what it is meant to see. This is the one layer that still holds when the model is wrong or has been manipulated.

The OWASP Top 10 for LLM applications makes the same point: [implement authorisation in downstream systems rather than relying on an LLM](https://genai.owasp.org/llmrisk/llm062025-excessive-agency/), and apply database permissions to the identity the LLM extension uses.

## Text-to-SQL, predefined queries or a semantic layer: which design fits?

There are three basic designs. With free-form text-to-SQL the model writes any query it likes. With predefined queries it only chooses from parameterised statements you wrote. With a semantic layer it asks for centrally defined metrics. They trade flexibility against error risk and review effort.

| Criterion | Free-form text-to-SQL | Predefined queries | Semantic layer |
|---|---|---|---|
| Flexibility | very high | low, only anticipated questions | medium, any combination of defined metrics |
| Risk of wrong numbers | high (joins, filters, definitions) | low | low to medium |
| Risk of expensive queries | high | low | medium |
| Upfront effort | low | high per question | high, once |
| Auditability | hard | easy | good |

### Free-form text-to-SQL

The model receives the schema and writes the SQL itself. It handles unexpected questions and suits analysts who can judge the result. The failure modes are well known: a wrong join, a forgotten status filter, a definition of "revenue" that differs from finance. The query runs without error and the number is still wrong. Free-form SQL therefore needs every safeguard described below.

### Predefined queries as tools

For recurring questions you write the SQL yourself and expose it as a tool with parameters, for example `open_orders(customer_id, since_date)`. The model only picks the tool and supplies values, which are passed as bound parameters and never concatenated into the SQL text. You get reviewed logic and predictable runtime. The cost is that the agent can only answer what you anticipated.

### Semantic layer

A model of your business metrics sits between the agent and the tables: what counts as revenue, what counts as an open order, which dimensions you may slice by. The agent requests metrics and dimensions, and the layer generates the SQL. One example is the [dbt Semantic Layer](https://docs.getdbt.com/docs/use-dbt-semantic-layer/dbt-sl), where metrics are defined in the modelling layer. According to dbt, AI tools such as Claude can connect to it through the dbt MCP server so that answers use governed metrics instead of guessing at raw tables (as of October 2026, available on the Starter and Enterprise plans).

Many real deployments mix all three: predefined queries for the ten most common questions, a semantic layer for KPIs, and free-form SQL only for a small group of data-literate users.

## What do you need before you start?

You need a documented data foundation, a dedicated service account for the agent, a query target that will not disturb operations, and a set of test questions with known answers. Without these four, every design is guesswork.

**Data.** Identify which tables are really needed; it is usually a few dozen, not hundreds. Write short descriptions for them: the meaning of each column, valid values for status fields, and traps such as cancelled documents that are flagged rather than deleted.

**Systems.** ERP and CRM products often store data in relational databases whose structure was never meant for direct querying. Check whether the vendor permits direct read access or whether an API, a data warehouse or a read replica is the right route. For analytics, a replica or a dedicated reporting schema almost always beats the production database. SAP S/4HANA comes with its own access paths and authorisation model, covered in [connecting an AI agent to SAP S/4HANA](https://www.ruebstudio.de/en/guides/ai-agent-integrations/connect-ai-agent-sap-s4hana). To expose a database tool to several agents in a standard way, see the guide on [MCP servers](https://www.ruebstudio.de/en/guides/ai-agent-integrations/mcp-servers-connect-ai-agents); this article focuses on permissions and limits inside the database itself.

**Organisation.** Decide who may see what. If a sales rep currently only sees their own accounts, the agent must respect that too. HR, payroll and health data do not belong in a general-purpose agent's view. Where personal data is involved, the setup can be operated in line with the GDPR if access, purpose and logging are properly defined; that does not replace a review by your data protection officer.

**Test questions.** Collect 30 to 50 real questions from the business and their correct answers. This list is your yardstick for every change to prompt, model or schema.

## How do you set up read-only roles and query limits?

Create a dedicated database user with SELECT on approved views only, set a per-statement timeout and cap the number of returned rows. The example below uses PostgreSQL 18. Other database systems offer comparable mechanisms; check the details in their own documentation.

### Step 1: dedicated role and approved views

The agent gets no access to base tables, only to views in a separate schema. The view decides which columns are visible and can already translate status codes into readable values.

```sql
-- Separate schema with approved views
CREATE SCHEMA agent_read;

CREATE VIEW agent_read.open_orders AS
SELECT o.order_id, o.customer_id, c.name AS customer, o.order_date, o.net_total
FROM erp.orders o
JOIN erp.customers c ON c.customer_id = o.customer_id
WHERE o.status = 'OPEN';

-- Service account used only by the agent
CREATE ROLE ai_agent LOGIN PASSWORD 'from-your-secret-store';
GRANT USAGE ON SCHEMA agent_read TO ai_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA agent_read TO ai_agent;
```

PostgreSQL also supports column-level privileges, but the [GRANT documentation](https://www.postgresql.org/docs/current/sql-grant.html) warns that revoking a single column does not narrow an existing table-level grant. Granting only the views from the start is cleaner.

### Step 2: read-only mode and timeouts as role defaults

[ALTER ROLE … SET](https://www.postgresql.org/docs/current/sql-alterrole.html) defines defaults that apply whenever this role opens a new session.

```sql
ALTER ROLE ai_agent SET default_transaction_read_only = on;
ALTER ROLE ai_agent SET statement_timeout = '15s';
ALTER ROLE ai_agent SET idle_in_transaction_session_timeout = '30s';
```

Per the [PostgreSQL documentation](https://www.postgresql.org/docs/current/runtime-config-client.html), `statement_timeout` aborts any statement that runs longer than the given time, and the default of zero disables the limit. A [read-only transaction](https://www.postgresql.org/docs/current/sql-set-transaction.html) rejects INSERT, UPDATE, DELETE, MERGE, TRUNCATE and all CREATE, ALTER and DROP commands, among others. Keep in mind that these are defaults a session can change. The real barrier is that the role from step 1 holds no write privileges; read-only mode is a second line of defence.

### Step 3: row-level security when users see different data

If users may only see their own records, use row-level security. According to the [PostgreSQL documentation](https://www.postgresql.org/docs/current/ddl-rowsecurity.html), once you run `ALTER TABLE … ENABLE ROW LEVEL SECURITY` with no policy defined, a default-deny applies and no rows are visible. Superusers and roles with BYPASSRLS always bypass policies, and table owners do so by default. The agent must therefore never connect as an owner or admin account. For policies to work per user, the agent has to query on behalf of the signed-in user, for instance by having your application set the user identity for each session and checking it in the policy.

### Step 4: validate in the tool before execution

With free-form text-to-SQL, the tool checks every statement before it reaches the database:

- exactly one statement, starting with SELECT or WITH;
- always a row limit, added by the tool if missing;
- a plain `EXPLAIN` first, which per the [documentation](https://www.postgresql.org/docs/current/sql-explain.html) returns estimated cost and rows without running the query; above a threshold the tool refuses or asks the user to narrow the question;
- never `EXPLAIN ANALYZE` on unvetted SQL, because that actually executes the statement.

```python
# Simplified pre-execution check (psycopg 3)
import json

MAX_COST = 50_000   # in PostgreSQL planner cost units; calibrate per database
MAX_ROWS = 500

def safe_query(conn, sql: str):
    sql = sql.strip().rstrip(";")
    if ";" in sql or not sql.lower().startswith(("select", "with")):
        raise ValueError("Only single SELECT statements are allowed")
    with conn.cursor() as cur:
        cur.execute("EXPLAIN (FORMAT JSON) " + sql)
        plan = cur.fetchone()[0]
        if isinstance(plan, str):
            plan = json.loads(plan)
        if plan[0]["Plan"]["Total Cost"] > MAX_COST:
            raise ValueError("Query too expensive, please narrow it down")
        cur.execute(f"SELECT * FROM ({sql}) AS t LIMIT {MAX_ROWS}")
        return cur.fetchall()
```

This check does not replace steps 1 to 3. String checks can be bypassed; their job is to stop accidentally expensive or pointless queries. Planner cost is an estimate in arbitrary units, so tune the threshold against your own data.

## What risks remain?

Even with tight permissions, three risks remain: wrong numbers from syntactically valid queries, data leaking through answers, and manipulation of the agent through content stored in the database. Permissions protect the data from damage, not you from wrong conclusions.

**Wrong numbers.** The biggest practical risk is not a dropped table but a plausible, incorrect answer. Countermeasures: always show the executed query and the data it used, source critical KPIs from predefined queries or the semantic layer, and rerun the test questions after every change.

**Data leakage.** Anything the agent can read, it can also output, possibly to an external model provider. Decide which data a hosted model may see. For sensitive data a self-hosted model can make sense; the trade-offs are covered on the [sovereign AI](https://www.ruebstudio.de/en/sovereign-ai) page.

**Prompt injection.** To the model, database content is just text. If a free-text field, say a complaint description, contains instructions aimed at the model, it may try to follow them. OWASP calls this [indirect prompt injection](https://genai.owasp.org/llmrisk/llm01-prompt-injection/) and recommends least privilege and human approval for privileged operations, among other measures. A read-only agent with no other tools limits the blast radius; if the same agent can also send emails or create tickets, the exposure grows considerably.

**Writes.** If the agent should change data, such as entering a delivery date, give it a narrow, dedicated tool with human approval, never free-form SQL. OWASP's own example is an agent that should only read a single products table and must neither see other tables nor insert, update or delete records.

**When this approach does not fit.** If the data is so inconsistent that people already argue about the right number, an agent will not settle it. And for KPIs needed in the same shape every week, a conventional report is often the more robust answer.

## How do you get started?

Start with one department, ten to twenty real questions and a read-only role on a replica. Begin with predefined queries, track which questions stay unanswered, and only then add a semantic layer or free-form SQL for selected users.

A proven five-step sequence:

1. **Collect questions.** Ask one team to log every data question they send to IT or finance for two weeks.
2. **Define the data foundation.** Pick the tables you need, build views in a separate schema, and document columns and business rules.
3. **Set up the role and limits.** Service account, SELECT on views only, read-only mode, timeout, row cap and row-level security where needed.
4. **Build the tools.** Implement the most common questions as predefined queries and log every execution: who, when, which query, how many rows.
5. **Test and roll out.** Run the test questions, resolve discrepancies, launch with a small user group and review what remains unanswered.

In our own operations, always displaying the executed query has proved its worth: users quickly learn whether the data basis is right, and mistakes get reported instead of silently accepted. If the agent should combine numbers with knowledge from documents, see [knowledge management agents](https://www.ruebstudio.de/en/ai-agents/knowledge-management). For questions around customers and quotes there are dedicated building blocks for [sales](https://www.ruebstudio.de/en/ai-agents/sales). The underlying concepts are explained in the glossary entries on [tool use and function calling](https://www.ruebstudio.de/en/glossary/tool-use-function-calling) and [prompt injection](https://www.ruebstudio.de/en/glossary/prompt-injection).

## Frequently asked questions

### Can I just tell the model in the system prompt not to modify data?

No. Prompt instructions can be bypassed by prompt injection or simply misread. Enforce it in the database with a role that holds SELECT on approved objects and nothing else.

### Should the agent query the production database?

For read-only questions, a read replica or a dedicated reporting schema is usually the better target. Long-running queries then cannot slow down transactional workloads, and the agent only sees tables designed for analysis.

### Do I need a frontier cloud model for text-to-SQL?

Not necessarily. With predefined queries or a semantic layer, the model only picks a tool or a metric and fills in parameters, which smaller self-hosted models can handle. Free-form SQL across many tables is more demanding.

### How do I catch wrong answers?

Have the agent always show the SQL it ran and the data it used, and test it against a set of questions with known answers before rollout. Wrong joins and missing filters surface quickly this way.

## Sources

- [OWASP Top 10 for LLM Applications 2025: LLM06 Excessive Agency](https://genai.owasp.org/llmrisk/llm062025-excessive-agency/)
- [OWASP Top 10 for LLM Applications 2025: LLM01 Prompt Injection](https://genai.owasp.org/llmrisk/llm01-prompt-injection/)
- [PostgreSQL 18 Documentation: Client Connection Defaults](https://www.postgresql.org/docs/current/runtime-config-client.html)
- [PostgreSQL 18 Documentation: SET TRANSACTION](https://www.postgresql.org/docs/current/sql-set-transaction.html)
- [PostgreSQL 18 Documentation: Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)
- [PostgreSQL 18 Documentation: GRANT](https://www.postgresql.org/docs/current/sql-grant.html)
- [PostgreSQL 18 Documentation: ALTER ROLE](https://www.postgresql.org/docs/current/sql-alterrole.html)
- [PostgreSQL 18 Documentation: EXPLAIN](https://www.postgresql.org/docs/current/sql-explain.html)
- [dbt Developer Hub: dbt Semantic Layer](https://docs.getdbt.com/docs/use-dbt-semantic-layer/dbt-sl)
