Read-Only Agent Access Patterns for Postgres

Startups connecting AI agents to their production database are reaching for the same quick fix: create a read-only user, hand the agent a connection string, and move on. The pressure behind that move is real. Agents need live data to be useful, and for most startups that data sits in Postgres. The Model Context Protocol has turned what used to be a deliberate engineering decision into a one-line config choice: an agent connected over MCP can list schemas, inspect tables, and run SQL against whatever the connected role can reach. A dedicated Postgres user with SELECT-only grants is the correct first move, because it enforces the no-writes rule at the database level rather than in application code or a system prompt, and regex-based hooks that try to catch writes in application code fail against CTEs, writable views, and inventive SQL that slips past pattern matching. But the read-only role is a floor. It stops writes, and that is the only thing it stops. Everything else, a read-only grant leaves wide open.

It also matters which server is doing the connecting, because there is no single canonical Postgres MCP server. The community reference server wraps every query in a read-only transaction. AWS's official Aurora and RDS for PostgreSQL MCP server supports both read-only and read/write modes, toggled by the --allow_write_query flag, with AWS recommending a least-privilege database role as a separate guardrail. Which one a team runs changes the blast radius of a mistake or an attack by an order of magnitude. None of that changes the underlying argument: a read-only role is necessary, and the rest of this piece is about what has to sit on top of it.

What a read-only role does not protect against

A read-only grant leaves four distinct attack surfaces open, and each one needs a different, purpose-built control.

The first is resource exhaustion. SELECT pg_sleep([3600](https://adaptive.live/blog/safe-ai-agent-database-access)) is a completely valid read query that holds a connection open for an hour, and running it a handful of times is enough to drain a connection pool and start returning errors to every real user of the application. A cartesian join across two large tables is just as valid and just as damaging, and nothing about either query violates read-only semantics. This is the failure mode teams most consistently miss: the agent isn't doing anything wrong by database-permission standards, because the query it ran is valid SQL. The role allows it. The database executes it. The outage happens anyway.

The second is sensitive data exposure. A join across a users table and an oauth_tokens table can be entirely legal SQL, permitted by the role, and still return credentials that the application was never designed to surface to anyone, let alone to a language model. PII, credentials, and financial data routinely sit in plain-text varchar columns in production schemas, and a SELECT * returns all of it, verbatim, straight into the agent's context window. The query is valid, the role has permission, and the result is still a security incident.

The third is prompt injection carried by stored content. A row in a support_messages table can contain text engineered to look like an instruction, and when an agent reads that row as part of ordinary work, the adversarial text becomes part of what the model is reasoning over. Models have no reliable built-in way to tell data from instructions embedded inside that data. The attacker in this scenario can act without any current access to the system. The attacker's words might already be sitting in a table, waiting for an agent to read them.

The fourth undoes the very mechanism teams think is protecting them: both statement_timeout and default_transaction_read_only, when set through ALTER ROLE, are USERSET parameters in PostgreSQL. That classification means the session itself can change them. An agent's own connection can issue SET default_transaction_read_only = off and lift the constraint in a single statement. This is the kill-switch myth: from the operator's side, the role looks locked down, configured, done. But the boundary was never enforced by the database engine the way an actual privilege grant is enforced. A grant is binding. A role-level default is a starting value the session is free to override.

Statement classification as the first wall above the floor

The gap exposed by USERSET parameters, a session quietly undoing its own constraints, is what makes statement classification necessary: every statement an agent tries to run has to be parsed and judged before it ever reaches the database, rather than trusted because the connecting role is read-only.

Static SQL parsing catches the obvious cases: a DROP TABLE, an UPDATE, an ALTER ROLE buried in a batch. But SQL has enough dialects, extensions, and corner-case syntax that a parser can't be assumed to catch every unsafe pattern on its own, so classification functions as a first wall, not a final one. The more durable pattern is to stop exposing raw SQL access to the agent at all, and instead expose intent-specific tools: get_customer_order_summary(customer_id, start_date, end_date), list_overdue_invoices(account_id, limit). Each tool carries a strict input schema, an authorization check, a row limit, a timeout, and a documented, predictable output shape. Values passed into these tools should be parameterized, and any selectable column, table, or sort field should come from an allow-list, because SQL parameters protect against malicious values, not against an untrusted identifier standing in for a table or column name.

Classification should reject writes, DDL statements, and dangerous functions before a query reaches Postgres, and the query should still execute inside a read-only transaction as a second, independent wall, so that anything the classifier misses still can't mutate data. That second wall isn't theoretical caution. The archived official Postgres MCP server shipped a documented SQL injection that escaped its own read-only transaction, which is exactly the kind of gap that pre-execution classification exists to close. Multi-statement queries deserve the same scrutiny: a single approved SELECT bundled together with a second, unapproved statement in the same request is not a single approved SELECT, and a classifier that only checks the first statement in a batch has checked nothing.

None of this should be delegated to a system prompt that tells the model to "only run SELECT statements." A prompt shapes what a model is inclined to generate. PostgreSQL privileges determine what the connecting identity is actually capable of doing once a query reaches the engine. These operate at entirely different layers of the stack, and only one of them is authoritative: the database enforces its grants regardless of what the prompt says, while the prompt enforces nothing at all once a motivated or confused agent decides to generate something else.

Resource limits and query bounds as operational safeguards

Resource exhaustion survives both the read-only role and the statement classifier, because a query like pg_sleep([3600](https://adaptive.live/blog/safe-ai-agent-database-access)) or an unbounded join is a syntactically fine, fully authorized read. Closing that gap takes query bounds, which function less as a security feature in the traditional sense and more as operational necessity.

Timeouts set at the application layer can fail to fire if a network call stalls at the wrong point in the stack, so a database-level timeout has to exist alongside the application-level one. An application or a connecting client can override statement_timeout within the session with SET statement_timeout = 0, because like its ALTER ROLE counterpart it is still a USERSET parameter at the database level. Neither placement is immune to that override on its own; the protection comes from layering both.

Result-size limits belong at this same layer. A SELECT * against a billion-row table with no LIMIT clause is valid SQL, passes every read-only check, and can still exhaust memory, CPU, or disk, or simply dump an entire table's worth of rows into the model's context window. Large result sets are as much a data-governance problem as a performance one: whatever comes back from the database becomes part of what the agent sees, logs, and potentially passes along to other tools. The practical fix is to rewrite queries at the AST level to inject a LIMIT clause before execution, so the cap holds structurally and doesn't depend on the model remembering to ask for one.

Rate limits and connection-pool accounting sit as a distinct sub-layer underneath all of this. Rate limits don't help if the single query that gets through reads an entire table or locks up the database on its own; they protect the pool as a whole, not the behavior of any one query. Query bounds and rate limits are complementary controls, and neither can substitute for the other.

Per-agent identity and revocable keys as the control plane

Sharing one database role across every agent in a system means there's only one available response when something goes wrong: cut off every agent at once. Per-agent identity turns that into a surgical response: a single agent can be cut off while the rest keep running. Giving each agent its own revocable key means a single misbehaving or compromised agent can be shut off without disrupting the others, and the database password itself should never be something the agent holds or sees. The key maps to a database identity; the MCP server or query layer owns the actual connection string and the secrets behind it, keeping credential storage, rotation, and revocation entirely outside the model's reach.

Views over base tables are the scoping mechanism that makes this identity layer meaningful. A customer_health view can expose an account ID, a plan tier, a renewal date, and a usage trend while excluding password hashes, payment tokens, private notes, and other personal data the agent has no business reading. The agent gets the business context it needs to do its job without a column-by-column path into everything else in the schema. Granting SELECT on a reporting schema or a small set of approved views is what makes a leaked key containable rather than catastrophic: the blast radius of a compromised credential is bounded by what that credential was ever allowed to see.

For systems carrying higher risk, read and write paths should be separated entirely at the server level. A read server can run against a read replica, scoped to a role limited to reporting views; a write server, where one exists at all, can expose a small number of reviewed procedures gated behind explicit approval and policy checks. That separation means the read path has no structural route to mutation, even in a scenario where the identity layer itself is compromised.

Audit logging as the layer that makes everything else accountable

Every control described so far, the role, the classifier, the bounds, the per-agent keys, can be working exactly as designed and still leave a team unable to answer the one question that eventually gets asked: what has the agent actually touched? Without an audit trail, that question has no answer, and a team is left assuming its controls are holding rather than knowing it.

Every query should be logged against the specific key that ran it, because the record of who did what is what turns "we think this is safe" into "here is exactly what happened." A useful log captures the tool call that was invoked, the SQL that was actually generated and sent to the database, the identity of the key behind it, execution timing, the number of rows returned, and whether any limit or classification rule fired along the way. That last detail closes a loop the earlier sections opened: when a row in a table carries injected instructions and an agent responds by issuing an unexpected query, the audit trail surfaces that anomaly and lets a team tell an ordinary model mistake apart from a deliberate injection attempt.

The log itself has to be handled carefully, because a logging system that records full query output can become a second copy of exactly the sensitive data the rest of the stack is trying to contain. Sensitive values should be redacted at the point of logging while the surrounding security metadata, the identity, the timing, the row count, the rule that fired, is retained in full. Column-level redaction in the logging layer mirrors what the query layer already does for the model itself: the shape of what ran is preserved in full, and the regulated values inside it are not. For a founder or a small engineering team, this layer is what turns the access pattern from a one-time setup decision into something that can be trusted over time, because confidence here comes from a growing record of normal behavior, not from a control that was configured once and never checked again.

Routing agent queries away from the production primary

Every layer described above governs what an agent is allowed to ask and what happens to the answer. None of it changes where that query actually runs, and even a fully governed, correctly scoped, properly logged agent query is still analytical load landing on transactional compute, competing for the same connections and the same CPU cycles as the application's own user-facing writes.

Pointing any agent, or any BI tool, directly at the same Postgres instance serving production traffic brings a specific, well-known set of costs with it: friction from row-level security policies written for application access patterns, pressure on connection pooler limits, poor handling of semi-structured analytical queries, and the general strain of analytical load sitting on OLTP-tuned compute. For teams running on managed platforms, those costs don't stay abstract. They show up directly on the infrastructure bill.

A read replica resolves this cleanly. The read server built around a replica and a role scoped to reporting views carries no connection at all to the primary database handling user writes, so agent query volume, however well-governed, has nowhere to compete with production traffic. Every control covered in this piece, the role, the classifier, the bounds, the identity, the logging, holds regardless of where the query physically executes. Routing that execution off the primary is what keeps the whole defense-in-depth pattern from becoming, in practice, a performance problem for the application it was built to protect.