# 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, a `statement_timeout`, and an
> `idle_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?

"Is it safe" is too coarse a question to answer.  There are four distinct risk
classes, and they need four different controls.  Conflating them is why most
answers to this question are 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

An agent connected to your database reads rows.  Rows contain text your users
wrote, support tickets, profile fields, product reviews, log lines, 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 is not hypothetical and it is not solved.  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.

Note that injected text does not have to come from a person targeting you.  A
scraped page stored in a table, a copied stack trace, an email body, anything
that made it into a row is untrusted input the moment an agent reads it.

Filtering the text is a losing game.  Constraining the credentials is not.

### 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 that is already in the `.env` file.  That role can usually `INSERT`,
`UPDATE`, `DELETE`, `TRUNCATE`, and run DDL, because the application needs to.

Now every one of the risks 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](https://www.dbgorilla.com/learn/postgres/how-to-set-up-a-read-only-postgres-role-for-an-ai-agent/).
Separate credentials also buy you attribution: with `application_name` set on the
agent's connection, `pg_stat_activity` tells you exactly which sessions are the
agent's.

### Risk 3: unbounded and expensive queries

**This is the risk that read-only does not fix, and it is the one people miss.**

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 `xmin` horizon so autovacuum cannot
  clean up dead tuples anywhere in the database (see
  [idle in transaction connections](https://www.dbgorilla.com/learn/postgres/how-to-fix-idle-in-transaction-connections/)).

There is a subtler one 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` it means the write actually happens.  Plain
`EXPLAIN` (without `ANALYZE`) does not execute, which is why "explain this plan
for me" is a much cheaper request than "run this and show me the timings." A
read-only role blocks the write case outright; nothing blocks the expensive-read
case except a timeout.

Bound it:

```sql
ALTER ROLE ai_agent_ro SET statement_timeout = '30s';
ALTER ROLE ai_agent_ro SET idle_in_transaction_session_timeout = '60s';
```

Be aware of what that is and is not.  Both settings are `USERSET` parameters, a
connected session can raise its own limit with a plain `SET`. `ALTER ROLE ... SET`
gives you a **default**, which handles the accidental case (the overwhelming
majority) but is not a hard ceiling against a determined one.  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

Whatever the agent reads goes into a context window.  Depending on your setup that
context may be processed by a third-party API, and it may be retained.  Before you
grant `SELECT` on a table, the question is: *am I comfortable with the contents of
this table being sent to my model provider?*

For most schema and performance work the answer is that 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 the large majority of "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, again, see the
[read-only role article](https://www.dbgorilla.com/learn/postgres/how-to-set-up-a-read-only-postgres-role-for-an-ai-agent/).

## What does the safe pattern look like?

A checklist you can work through 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 `CONNECT` on exactly one database, note `PUBLIC` holds `CONNECT` on every database by default, so making that literally true needs `REVOKE CONNECT ON DATABASE <other_db> FROM PUBLIC`, `USAGE` on exactly the
      schemas it needs, and `SELECT` on exactly the tables it needs.
- [ ] `ALTER DEFAULT PRIVILEGES` is 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, the strongest read-only guarantee available, and it moves the
      load off the primary at the same time.
- [ ] 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_name` is set on the connection so you can identify agent
      sessions in `pg_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_timeout` set on the role.
- [ ] `idle_in_transaction_session_timeout` set on the role.
- [ ] You know how to find and terminate an agent session that misbehaves.`pg_stat_activity`, then `pg_terminate_backend(pid)`.

**Awareness**

- [ ] You know which tables the role can read.  Not roughly, exactly.  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 are real, and they are *reduced*, not automatically bounded.  Be honest about where the ceiling comes from:
`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, it catches the common case, but do not file it as a guarantee.

Even so.  This is a different situation from handing over `DATABASE_URL`.

## Why is this specifically an aI-agent problem?

Because 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 that the query it just wrote is a ten-millisecond blink
or a ten-minute scan.  That is exactly the class of mistake covered in
[common PostgreSQL mistakes AI coding agents make](https://www.dbgorilla.com/learn/postgres/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 chasing an answer.  A pattern that is mildly expensive once is a
sustained load when it runs continuously.

And critically: the agent's behaviour depends on its context, and its context now
includes data from your database.  That feedback loop, database content
influencing what the agent does next, is what makes prompt injection a database
concern rather than purely an application one.

## 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.  Understand what they are: 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 good ergonomic guardrail.  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`, and `DELETE` (`WITH x AS (DELETE FROM ... RETURNING *) SELECT ...` is
  a `SELECT` statement that writes).  Functions called from a `SELECT` can have
  side effects. `EXPLAIN ANALYZE` executes.
- 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 to close 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:

```sql
SELECT n.nspname, p.proname, pg_get_userbyid(p.proowner) AS owner
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE 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.

## How DBGorilla helps

DBGorilla is built around the pattern described above: it connects to your
database **read-only** and works through the AI coding agent you already use
(Claude Code, Cursor) over MCP. It surfaces and explains the real operational
data.`pg_stat_statements`, query plans, index definitions, row-count estimates,
schema, locks, so the agent reasons about your actual database instead of
guessing at it, which is where most of the expensive-query risk comes from in the
first place.

To be clear about what it does not do: it does not write to your database, run
migrations, kill sessions, or change configuration.  And it cannot enforce safety on
a connection you set up yourself, the read-only role, the replica, and the
timeouts are yours to configure.  This article and its
[companion on role setup](https://www.dbgorilla.com/learn/postgres/how-to-set-up-a-read-only-postgres-role-for-an-ai-agent/)
are how we'd suggest doing it.
[Get started free →](https://app.dbgorilla.com/signup)

## FAQ

**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 it its own login role
with `SELECT`-only grants, its own credentials, a `statement_timeout` and
`idle_in_transaction_session_timeout`, and point it at a read replica where you
have one.  Then assume anything it can query may reach your model provider.

**Can a read-only agent still cause an outage?**
Yes.  Read-only prevents modification, not resource consumption.  An unbounded
sequential scan, an accidental cross join, a large disk spill, or a long-held
open transaction that blocks autovacuum are all available to a `SELECT`-only
role. `statement_timeout` and a replica are what bound them.

**Does telling the agent "you are read-only" make it read-only?**
No.  Prompt and tool-description instructions are guidance, not enforcement, and
they can be overridden by later context, including untrusted text the agent read
out of your own database.  Enforce it with role grants, and preferably with a hot
standby replica, which rejects write transactions at the server level.

**Should the agent connect to the primary or a replica?**
A replica, where you have one.  Two benefits: 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 "what is the current value of X" and not for schema, plans, or
`pg_stat_statements` work.

**How do I stop production data reaching the model?**
Control it with grants, not instructions.  Withhold `SELECT` on regulated tables,
use column-level grants or a masked view where a table mixes sensitive and
non-sensitive columns, and prefer schema, statistics, and plans over raw rows, they answer most performance questions without exposing any customer data.

**What if the agent needs to write, run a migration, 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.  See
[how to handle database migrations safely](https://www.dbgorilla.com/learn/postgres/how-to-handle-database-migrations-safely/).

## Related

- [How do I set up a read-only PostgreSQL role for an AI agent?](https://www.dbgorilla.com/learn/postgres/how-to-set-up-a-read-only-postgres-role-for-an-ai-agent/)
- [Common PostgreSQL mistakes AI coding agents make](https://www.dbgorilla.com/learn/postgres/common-postgresql-mistakes-ai-coding-agents-make/)
- [How to fix idle in transaction connections in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-fix-idle-in-transaction-connections/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)