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.
Why "just prompt it to be careful" fails
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."
Layer 1: A dedicated read-only database user
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.
Layer 2: Point AI at a read replica
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.
# Analytics / AI connection string points at the replica
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
Layer 3: A broker that parses SQL before it runs
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.
Common mistakes and gotchas
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.
Key takeaways
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:
- Create a dedicated role with
SELECT-only grants, scoped to the tables that matter — enforced by the database engine. - Route AI traffic to a read replica or a read-only transaction so writes can't reach a writable node.
- Put a broker in front that parses each statement, allows only single reads, caps rows, masks sensitive columns, and logs everything.
- Never treat the system prompt as a security control.
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).
Top comments (1)
This is a solid baseline. One PostgreSQL edge case worth adding: “single SELECT” is not identical to “no side effects.” A SELECT can call user-defined functions, including SECURITY DEFINER or volatile functions, and extensions may reach external systems. Parser classification alone cannot prove those functions are harmless.
I’d combine the role with an allowlist of schemas/views and revoke EXECUTE on non-approved functions (including future defaults where appropriate). Curated security-barrier views can also hide sensitive columns and enforce tenant/row scope; read-only prevents mutation, not exfiltration.
The other production risk is availability. Add statement_timeout, lock_timeout, idle-in-transaction timeout, row/byte limits, concurrency quotas, and perhaps estimated-cost gates. A read-only cross join can still exhaust CPU, memory, replica lag, or network.
Finally, test the effective role continuously: canary attempts for writes, forbidden tables/functions, multi-statements, long queries, and replica routing. Configuration drift is the failure mode these layers otherwise share.