There's a moment every developer hits the first time they connect an AI assistant to a real database: it works beautifully, the model writes a clean SELECT
, you get your answer in seconds — and then a small, cold thought arrives. What if it had written DELETE instead?
That worry is healthy. An AI agent that can query your production database is also, by default, an AI agent that can UPDATE
, DROP
, and TRUNCATE
it. Large language models are probabilistic. They hallucinate. They misread a vague prompt like "clean up the test users" as an instruction to actually delete rows. You don't want the only thing standing between a confused model and your orders
table to be good intentions.
The fix isn't to keep AI away from your data. It's to make write operations structurally impossible — read-only by design, enforced at layers the model can't talk its way past. This post walks through how to do that properly, from the database grant all the way up to query-level guardrails.
The tempting shortcut is to add "only run SELECT queries, never modify data" to your system prompt and call it a day. Don't rely on this. Prompt instructions are suggestions, not enforcement. A cleverly worded user request, an injected instruction hidden in some data the model reads, or a plain misunderstanding can all lead the model to generate a destructive statement anyway.
Real read-only access is enforced below the model — in places where no amount of clever text can override it. Think of it as defense in depth, with at least three independent layers:
| Layer | What it stops | Enforced by |
|---|---|---|
| Database permissions | Any write reaching the engine | SQL GRANT /REVOKE |
| Connection / replica | Writes even being routed to a writable node | Read replica, read-only transaction |
| Query parser / broker | Non-SELECT statements before they run | SQL parsing, allowlists |
Any one of these is decent. All three together mean a write has to defeat your database engine, your routing, and your parser simultaneously — which is a very different threat model than "the model promised."
Start at the bottom. Create a database role whose entire vocabulary is SELECT
. This is the single most important step, because it's enforced by the database engine itself and applies no matter what SQL arrives.
In PostgreSQL:
-- Create a login role with no inherited privileges
CREATE ROLE ai_readonly WITH LOGIN PASSWORD 'use-a-secret-manager';
-- Let it see the schema, but nothing more
GRANT CONNECT ON DATABASE app_production TO ai_readonly;
GRANT USAGE ON SCHEMA public TO ai_readonly;
-- Read-only on existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_readonly;
-- And on tables created later
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO ai_readonly;
Now prove it. Connected as ai_readonly
, a write simply bounces:
DELETE FROM orders WHERE created_at < '2025-01-01';
-- ERROR: permission denied for table orders
That error is the whole point. The model can generate the most confident DELETE
in the world and Postgres will refuse it. The equivalent in MySQL is GRANT SELECT ON app_production.* TO 'ai_readonly'@'%';
— same idea, same guarantee.
A subtle but important detail: grant SELECT
on specific tables or schemas rather than handing over a blanket "read everything" role. Your AI assistant probably doesn't need to read password_resets
or internal_audit_log
. Scope the grant to the tables that answer real questions.
Permissions stop writes, but you can also stop writes from ever reaching a writable machine. If you run a read replica — standard on managed Postgres and MySQL — send all AI traffic there.
DATABASE_URL=postgres://ai_readonly@replica.db.internal:5432/app_production
This buys you two things. First, a replica is physically read-only; even a superuser can't write to it. Second, you isolate the load. An AI assistant exploring data with a few accidental full-table scans won't compete with your production write path. If you're on SQL Server Always On, the ApplicationIntent=ReadOnly
connection property routes the session to a secondary and refuses to promote it to the primary — a nice belt-and-suspenders check.
For a single-node database with no replica, you can still force each session into a read-only transaction:
-- Postgres: this session cannot write, full stop
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
INSERT INTO events (name) VALUES ('test');
-- ERROR: cannot execute INSERT in a read-only transaction
The top layer is where the Model Context Protocol (MCP) and similar "broker" architectures shine. Instead of the AI holding a database connection directly, it talks to an intermediary that holds the credentials, inspects every query, and executes only what's allowed.
A good broker parses the SQL — not with a fragile regex, but with a real SQL grammar — and rejects anything that isn't a plain SELECT
. That catches the sneaky cases a keyword blocklist misses:
| Query | Naive keyword check | Parser-based check |
|---|---|---|
SELECT * FROM users |
||
| allow | allow | |
DELETE FROM users |
||
| block | block | |
SELECT * FROM users; DROP TABLE users |
||
| may allow (starts with SELECT) | block (two statements) | |
WITH x AS (DELETE FROM users RETURNING *) SELECT * FROM x |
||
| may allow | block (writable CTE) |
Those last two are exactly the tricks that get past hand-rolled string checks. A broker that parses the statement, confirms it's a single read, caps the row count, and logs the whole thing gives you enforcement the model can't argue with. This is the model that managed MCP servers use — Draxlr's MCP server, for instance, exposes a database over OAuth as SELECT-only, so an AI client can list schemas and run queries but never issue a write. The broker holds the connection; the AI never sees the credentials.
The bigger win of the broker pattern is that read-only stops being one setting you hope everyone remembers and becomes a property of the gateway every AI client shares.
Relying on the prompt. Worth repeating because it's the most common error: a system prompt is not a security boundary. Enforce read-only at the database and connection layers first, always.
Forgetting DEFAULT PRIVILEGES. Grant
SELECT ON ALL TABLES
today and a table created next week won't be readable — or worse, your migration grants it broader access. The ALTER DEFAULT PRIVILEGES
line above handles future tables cleanly.Read-only isn't the same as private. A read-only role can still read everything it's granted, including PII and secrets. "Can't write" says nothing about "should see." Scope table grants, and mask sensitive columns (email, tokens, card numbers) before results leave the broker.
Ignoring resource exhaustion. A model can't corrupt your data with a SELECT
, but SELECT * FROM events
on a billion-row table can still take your database down. Cap returned rows, set a statement_timeout
, and prefer a replica so read load stays off the primary.
No audit trail. If you can't answer "what did the AI query last Tuesday," you have a blind spot. Log every query the broker runs, with the identity behind it. This is also what turns an incident review from guesswork into a five-minute grep.
Giving an AI assistant access to your database doesn't have to be a leap of faith. Make writes structurally impossible instead of merely discouraged:
SELECT
-only grants, scoped to the tables that matter — enforced by the database engine.Do those, and you get the upside — an AI that explores your data, answers questions, and drafts queries in seconds — without the 2 a.m. worry that it might rewrite history instead of reading it.
How do you hand database access to AI tools on your team — a read replica, a scoped role, a broker, or something else? I'd love to hear what's working (and what's bitten you) in the comments.
Sources: Model Context Protocol for Databases (AI2SQL), Safely connecting AI tools to your database (Daymark), Protecting production SQL from agentic query risks (Rietta), safedb-mcp (GitHub), Configure read-only access on an availability replica (Microsoft Learn).