Is it safe to let an AI coding agent access my production PostgreSQL database?
Quick answer: It can be, but not with your app’s credentials. Give the agent a dedicated login role with
SELECT-only grants, astatement_timeout, and anidle_in_transaction_session_timeout, and point it at a read replica instead of the primary where you have one. Read-only is not harmless: a read-only agent can still run a query that saturates the database or read data that should never leave your network.
What are the actual risks?
Section titled “What are the actual risks?”“Is it safe” is too coarse a question. There are four distinct risk classes, and they need four different controls. Conflating them makes answers to this question unsatisfying.
| Risk | What goes wrong | Where you control it |
|---|---|---|
| Prompt injection | Untrusted text in the database is read as instructions | Credentials and tool scope, not text filtering |
| Over-privileged credentials | The agent reuses the app’s read-write login | Role grants in PostgreSQL |
| Unbounded queries | A scan or join saturates CPU, I/O, or memory | statement_timeout, replica, work_mem |
| Data exfiltration to a model | Rows land in a model provider’s context window | Which tables and columns you grant SELECT on |
Risk 1: prompt injection reaching the database
Section titled “Risk 1: prompt injection reaching the database”An agent connected to your database reads rows. Rows contain text your users wrote: support tickets, profile fields, product reviews, log lines, and webhook payloads. Any of that text can carry instructions aimed at the model, and the model has no reliable way to tell “content I was asked to summarise” from “instructions I was given.”
This happens in practice, and there is no general fix for it. The practical consequence is a design rule:
Assume the model will, at some point, be persuaded to attempt anything its tools allow. Design so that “anything its tools allow” is not damaging.
Injected text does not have to come from a person targeting you. A scraped page stored in a table, a copied stack trace, or an email body is untrusted input the moment an agent reads it.
Filtering the text does not work reliably. Constraining the credentials does.
Risk 2: over-privileged credentials
Section titled “Risk 2: over-privileged credentials”The most common failure mode is the least exotic: someone pastes the
application’s DATABASE_URL into an agent config because it is the connection
string already in the .env file. That role can usually INSERT,
UPDATE, DELETE, TRUNCATE, and run DDL because the application needs to.
Now every risk above has a write path attached to it, and you have no way to tell agent traffic apart from application traffic in your logs.
The fix is a separate role, covered in detail in
how to set up a read-only PostgreSQL role for an AI agent.
Separate credentials also provide attribution: with application_name set on the
agent’s connection, pg_stat_activity identifies the agent’s sessions.
Risk 3: unbounded and expensive queries
Section titled “Risk 3: unbounded and expensive queries”This is the risk that read-only does not fix.
A SELECT with no write privilege can still:
- Sequentially scan a table that does not fit in memory, evicting your buffer cache and pushing read I/O through the roof.
- Produce an accidental cross join. A missing join condition between two million-row tables is a syntactically valid query with a catastrophic plan.
- Spill a large sort or hash to disk, consuming temp space.
- Sit in an open transaction, pinning the
xminhorizon so autovacuum cannot clean up dead tuples anywhere in the database (see idle in transaction connections).
There is a subtler risk too: EXPLAIN ANALYZE executes the statement. On a
SELECT, that means you pay the query’s real cost, not a planner estimate. On an
INSERT, UPDATE, or DELETE, the write actually happens. Plain
EXPLAIN (without ANALYZE) does not execute, which makes “explain this plan
for me” a much cheaper request than “run this and show me the timings.” A
read-only role blocks the write case outright. Only a timeout limits the
expensive-read case.
Bound it:
ALTER ROLE ai_agent_ro SET statement_timeout = '30s';ALTER ROLE ai_agent_ro SET idle_in_transaction_session_timeout = '60s';Both settings are USERSET parameters, so a connected session can raise its own
limit with a plain SET. ALTER ROLE ... SET
gives you a default, which handles the accidental case, but it is not a hard
ceiling against a determined client. A hard ceiling has to come from somewhere
the session cannot reach: a resource-limited replica, a connection pooler that
rewrites or rejects statements, or the cgroup limits of the instance itself.
Risk 4: data flowing to a model provider
Section titled “Risk 4: data flowing to a model provider”Whatever the agent reads goes into a context window. Depending on your setup,
that context may be processed by a third-party API and may be retained. Before
you grant SELECT on a table, ask: am I comfortable with the contents of this
table being sent to my model provider?
For most schema and performance work, you do not need row data at all. Table
definitions, index definitions, row-count estimates, EXPLAIN output, and
pg_stat_statements (which stores normalised query text with constants
stripped out) answer most “why is this slow” questions without a single
production row leaving the database. Grant accordingly.
For tables you do need to expose that mix sensitive and non-sensitive columns, column-level grants or a masking view are the right tool. The read-only role article covers both.
What does the safe pattern look like?
Section titled “What does the safe pattern look like?”You can work through this checklist in about twenty minutes.
Credentials and privileges
- The agent has its own login role, not the application’s, not a superuser, and not the database owner.
- The role has
CONNECTon exactly one database,USAGEon exactly the schemas it needs, andSELECTon exactly the tables it needs.PUBLICholdsCONNECTon every database by default, so making the first part literally true needsREVOKE CONNECT ON DATABASE <other_db> FROM PUBLIC. -
ALTER DEFAULT PRIVILEGESis set so tables created later are covered, or deliberately not set, so new tables are invisible until you review them. - Sensitive tables and columns are excluded by grant, not by instruction.
- The role owns nothing and can create nothing.
Where it connects
- It points at a read replica, not the primary, if you have one. On a hot standby, write transactions are rejected by the server regardless of the role’s grants. This is the strongest read-only guarantee available, and it also moves the load off the primary.
- The credential is stored where your other secrets live, not in a config file committed to the repo. Agent config files get shared, screenshotted, and pasted into issues.
-
application_nameis set on the connection so you can identify agent sessions inpg_stat_activity. - Network reachability is restricted the same way it is for any other client
(
pg_hba.conf, security groups, private networking).
Blast radius
-
statement_timeoutset on the role. -
idle_in_transaction_session_timeoutset on the role. - You know how to find and terminate an agent session that misbehaves:
pg_stat_activity, thenpg_terminate_backend(pid).
Awareness
- You know exactly which tables the role can read, not roughly. Run the permission audit query rather than trusting your memory of what you granted.
- You have thought about which of those tables contain user-controlled text, and accepted that reading them puts untrusted content into the agent’s context.
If you can tick all of those, the residual risk is expensive reads and data you
chose to expose being seen by the model. Those risks are real, and the controls
above reduce them without bounding them.
ALTER ROLE ... SET statement_timeout sets a default, not a hard limit. Any
client can raise it with SET in its own session, so a compromised or simply
over-eager agent can opt out of your timeout.
If you want a real ceiling, it has to come from something the session cannot change: a pooler that pins the setting, a resource-limited replica the agent is confined to, or OS-level limits on the backend. Set the role default anyway, because it catches the common case, but do not treat it as a guarantee.
Even so, this is different from handing over DATABASE_URL.
Why is this specifically an aI-agent problem?
Section titled “Why is this specifically an aI-agent problem?”The agent is fast, tireless, and reasoning about a database it cannot
see. A human running an exploratory query against production hesitates before
SELECT * on a big table. They have been burned. The agent has no such instinct
and no sense of scale: it does not know whether orders has 400 rows or 400
million, so it cannot know whether the query it just wrote is a ten-millisecond
blink or a ten-minute scan. That is the class of mistake covered in
common PostgreSQL mistakes AI coding agents make.
It also iterates. Where a human runs one query and thinks, an agent may run twenty in a loop while chasing an answer. A mildly expensive query can create a sustained load when it runs continuously.
The agent’s behaviour also depends on its context, which now includes data from your database. That feedback loop, in which database content influences what the agent does next, makes prompt injection a database concern rather than purely an application concern.
What about the guardrails in MCP servers and agent tools?
Section titled “What about the guardrails in MCP servers and agent tools?”Most database MCP servers offer a “read-only mode.” These are useful and worth
turning on. Each one is a client-side or server-side check on the
statement text before it is sent, usually parsing or pattern-matching the SQL to
reject anything that is not a SELECT.
That is a useful ergonomic guardrail, but it is not a security boundary:
- It is on the wrong side of the trust line. It runs in a process the agent is driving, not in PostgreSQL.
- SQL is hard to reject reliably from text alone. CTEs can contain
INSERT,UPDATE, andDELETE(WITH x AS (DELETE FROM ... RETURNING *) SELECT ...is aSELECTstatement that writes). Functions called from aSELECTcan have side effects.EXPLAIN ANALYZEexecutes. - It only covers the path it wraps. Another tool, a shell command, or a different client using the same credential bypasses it entirely.
Use it as a seatbelt. Put the grants underneath it as the actual structure. If the credential itself cannot write, it does not matter what any parser missed.
One gap remains before you trust that: SECURITY DEFINER routines run with the
owner’s privileges, not the caller’s. A role with no write grants anywhere can
still write by calling one, and PostgreSQL grants EXECUTE on newly created
functions to PUBLIC by default, so this is the default state, not an exotic
misconfiguration. Audit what your read-only role can reach:
SELECT n.nspname, p.proname, pg_get_userbyid(p.proowner) AS ownerFROM pg_proc pJOIN pg_namespace n ON n.oid = p.pronamespaceWHERE p.prosecdef AND has_function_privilege('ai_agent_ro', p.oid, 'EXECUTE') AND n.nspname NOT IN ('pg_catalog', 'information_schema');Anything that comes back is a write path your grants do not cover. REVOKE EXECUTE ... FROM PUBLIC on those routines, then grant back only to the roles
that genuinely need them. Grants are the structure after this is done, not
before.
Is it safe to give an AI coding agent access to a production database?
It can be made reasonably safe, but not by default. Give the agent its own login role with SELECT-only grants, its own credentials separate from the application, a statement_timeout and idle_in_transaction_session_timeout, and point it at a read replica rather than the primary where one exists. Assume anything the agent can query may end up in a model provider context window.
Can an AI agent with read-only database access still cause an outage?
Yes. Read-only prevents data modification, not resource consumption. A read-only session can run an unbounded sequential scan or an accidental cross join that saturates CPU and I/O, spill large sorts to disk, or hold a snapshot open (a long-running statement, an open cursor, or a REPEATABLE READ transaction) long enough to pin the xmin horizon and block autovacuum cleanup. Bound it with statement_timeout and a replica.
What is prompt injection in the context of database access?
Prompt injection is when text the agent reads as data gets treated as instructions. If an agent queries a table containing user-submitted content, support tickets, profile bios, log messages, a row can contain text aimed at the model. The defence is not to filter the text; it is to make sure the credentials and tools available to the agent cannot do damage even if the model is fully persuaded.
Does telling the AI agent to be read-only make it read-only?
No. Instructions in a system prompt, a rules file, or a tool description are guidance, not enforcement. They can be overridden by later context, including untrusted text pulled out of the database itself. Read-only must be enforced by PostgreSQL role grants, and ideally by connecting to a hot standby replica, which rejects write transactions at the server level.
How do I stop production data from reaching a model provider?
Assume any row the agent can SELECT may be sent to whatever model backs the agent. Control it at the grant level: withhold SELECT on tables holding regulated or sensitive data, use column-level grants or a masked view for tables that mix sensitive and non-sensitive columns, and prefer schema, statistics, and query plans over raw row data wherever they answer the question.
Should the agent connect to the primary or a replica?
A replica, where you have one. Reads do not compete with production traffic on the primary, and a hot standby rejects any write transaction outright, independent of what the role was granted. The trade-off is replication lag, which matters for the current value of a row and not for schema, plans, or pg_stat_statements work.
What if the agent needs to write, run a migration, or fix a row?
Then a human runs it. Keep the agent's credential read-only and let it produce the statement for review. You lose very little: the hard part of a migration is deciding what is safe, not typing it.