How to Connect an AI Agent to Your Database Without Handing It the Keys

Connecting an AI agent to your database is four pieces of setup, and only one of them is the connector. Create a dedicated read-only database role, expose a small set of views instead of raw tables, put a query gate between the model and the connection, and log every statement it runs. The MCP server is the easy part. The role is the part that decides whether a bad afternoon costs you a slow query or a customer table.

TL;DR

• Enforce read-only in the database with a GRANT, not in the tool with a flag. Tool-level enforcement has already been bypassed in the wild.
• The reference Postgres MCP server wrapped model SQL in BEGIN TRANSACTION READ ONLY. A payload beginning COMMIT; escaped it. Datadog Security Labs documented the whole chain; Anthropic archived the server on 29 May 2025.
• Accuracy falls off a cliff as schemas grow. The Spider 2.0 authors report GPT-4o at 10.1% on enterprise tasks against 86.6% on the original single-table Spider, with o1-preview at 17.1%. Fewer exposed tables is a quality decision, not just a security one.
• Row-level security does nothing if the agent connects as a table owner or a superuser. PostgreSQL's docs say those roles bypass row security unless you set FORCE ROW LEVEL SECURITY.
• Ship a statement_timeout, a hard row cap, and a query log before you ship the agent. Those three take an hour and prevent the outage that actually happens.

The read-only flag is not read-only

Start here because it reframes everything else. The most widely copied way to give a model database access was the reference Postgres MCP server, and its read-only guarantee looked airtight in the source:

BEGIN TRANSACTION READ ONLY;
-- the model's SQL runs here
ROLLBACK;

The problem was not the SQL, it was the client. The node-postgres query() call accepts multiple statements separated by semicolons. So a model output, or a poisoned row of data the model was summarizing, that began with COMMIT; DROP SCHEMA public CASCADE; closed the protective transaction on its first statement and executed the rest with the session's full privileges. The trailing ROLLBACK had nothing left to roll back.

The timeline is worth knowing because it is typical. Zed Industries shipped a patched fork on 9 April 2025. Anthropic archived the server on 29 May 2025 rather than patch it, and the vulnerable package stayed published on npm for anyone who ran npx against a copied config. If you inherited a database MCP config from a blog post in 2025, check which package it points at today.

The general rule this teaches: a guardrail written in the same layer the model can influence is a suggestion. The database does not negotiate. That is why the role comes first.

Step 1: build the role before you build the agent

This is ten lines of SQL and it is the highest-leverage work in the whole project. On Postgres:

CREATE ROLE agent_ro LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE app TO agent_ro;
GRANT USAGE ON SCHEMA analytics TO agent_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO agent_ro;

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 = '10s';

Four things are happening. The role can only reach one schema, so a query naming public.users fails at the parser. It can only SELECT, so the injection above dies before it does anything. default_transaction_read_only makes read-only the session default rather than something the caller opts into. And statement_timeout is the one that saves you on an ordinary Tuesday, when the model writes a correct query with a missing join condition and asks the database for a cross product.

Point the connection string at a read replica if you have one. A five second timeout on a replica means the worst case is a slow replica.

If you are using row-level security to scope the agent to one tenant, check which role it connects as. PostgreSQL's documentation states that superusers and BYPASSRLS roles always bypass row security, and that table owners bypass it unless you run ALTER TABLE ... FORCE ROW LEVEL SECURITY. Handing the agent the same credentials your migrations use is a common way to write a policy that never fires.

Step 2: give it a schema, not a database

The instinct is to expose everything so the agent can answer anything. It backfires, and the benchmark data is blunt about why.

Spider 2.0 rebuilt text-to-SQL evaluation around real warehouses: hundreds of tables, multiple dialects, multi-step workflows. Its authors report GPT-4o solving 10.1 percent of those tasks against 86.6 percent on the original academic Spider, with o1-preview at 17.1 percent. Purpose-built agents have since pushed the Spider 2.0-Lite leaderboard into the seventies, which is real progress and still means roughly one in four answers is wrong on a realistic schema.

The fix is not a better model. It is a smaller surface. Build an analytics schema containing five to fifteen views that answer the questions people actually ask, with the joins already resolved and the columns named in business language:

CREATE VIEW analytics.monthly_revenue AS
SELECT date_trunc('month', o.created_at) AS month,
       p.name  AS plan,
       sum(o.amount_cents) / 100.0 AS revenue_usd,
       count(*) AS order_count
FROM orders o JOIN plans p ON p.id = o.plan_id
WHERE o.status = 'paid'
GROUP BY 1, 2;

Now the model does not need to know that status = 'paid' is the filter that excludes refunds, or that amounts are stored in cents. That knowledge lives in the view, where it is reviewed once and tested in CI, instead of in a system prompt where it degrades silently. This is the same principle as designing narrow, opinionated tools for an agent, which I go through in how to design tools for an AI agent.

Ship a schema card alongside the views: a short text block listing each view, its grain, and one example question it answers. That block belongs in the tool description, not the system prompt, so it travels with the connector. The MCP Big Three walks through wiring the connector itself if you have not set one up before, and what is an MCP server covers the protocol underneath it.

Step 3: put a gate between the SQL and the connection

Even with a read-only role, you want a thin function that every query passes through. Five checks, none of them clever:

1. Parse it. Reject anything that is not a single SELECT or WITH statement. Use a real SQL parser, not a regex, and reject on multiple statements outright.
2. Cap the rows. Wrap the query in an outer SELECT ... LIMIT 1000 so a forgotten filter returns a page instead of a table dump.
3. Cost it. Run EXPLAIN first and refuse plans above a cost threshold. This catches the accidental cross product before it starts, not after five seconds of it.
4. Time it. Enforce the timeout in your client too, so a hung network call cannot pin a worker.
5. Redact. Strip columns you never want in a transcript, because whatever the query returns is going straight into a context window and probably into a log.

Check three matters more than it sounds. statement_timeout limits the damage; EXPLAIN prevents it. On a large table the difference is whether your read replica lags by five seconds or by five minutes.

Step 4: log the query, not just the answer

Every call should write one row: timestamp, the natural language request, the generated SQL, the plan cost, the row count, the duration, and the trace id. Not because you will read them, but because the first time someone says the number looked wrong, the only useful question is which SQL produced it.

That log is also your eval set. After two weeks you will have a few hundred real questions with real queries attached, which is a far better test suite than anything you would have invented up front. Sample fifty, have someone who knows the data mark each query right or wrong, and you have a baseline you can rerun every time you change a view or a model. The mechanics are in how to test an AI agent.

Turn on pg_stat_statements too. It answers the question a text log cannot: which agent-generated query shapes are actually costing you database time.

When the agent genuinely needs to write

Sometimes reading is not the job. Updating a record, tagging a lead, closing a ticket. The pattern that holds up is not a writable role, it is a set of named procedures.

Give the agent a tag_lead(lead_id, tag) function instead of UPDATE on the leads table. Grant EXECUTE on that function to agent_ro and nothing else. Now the blast radius is defined by the function body, the change is auditable by name, and no amount of clever prompting produces an UPDATE the function does not implement. Free-form generated SQL is fine for questions and a bad idea for mutations.

For writes that are expensive to reverse, add a human confirmation step and show the diff before it commits, not a summary of the diff. Human in the loop AI agent covers where those gates belong. And since query results and database rows are untrusted input flowing back into the model, read how to prevent prompt injection before you let an agent summarize a table that customers can write to.

The bottom line

Connecting AI to your database is mostly a permissions exercise wearing an AI costume. The connector takes twenty minutes. The role, the views, and the gate take an afternoon, and they are what determines whether this is a useful internal tool or an incident report. Do them in that order: role first, then a narrow schema of views, then the gate, then the log. Give the model less to reach and it will be both safer and more accurate, which is a rare thing to get in the same trade.

If you want the next one of these when it lands, the newsletter is where I send the builds that worked and the ones that did not.