Read-Only by Design: Letting AI Explore Your Database Without the Risk of Writes

A developer outlines a defense-in-depth approach to giving AI assistants read-only access to production databases, using database permissions, read replicas, and query parsers to make writes structurally impossible. The post emphasizes that prompt instructions are insufficient and provides PostgreSQL and MySQL examples for enforcing read-only access at the database level.

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. 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 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 https://docs.draxlr.com/docs/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 .