数据库只读设计: 让AI安全查询生产环境

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:

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.

-- 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:

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.

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).