# How to give an AI coding agent read-only access to your production database

> Create a dedicated database role with SELECT-only grants, add timeouts, point it at a replica if you can, and keep owner credentials out of the agent's reach.

Web page: https://vinescale.com/blog/ai-agent-read-only-database-access/
By Gary Mathis, founder of VineScale (https://vinescale.com/about/). Published 2026-10-31.

To give an AI coding agent read-only access to a production database, create a **separate database user for the agent** that has only `SELECT` privileges, never the application's or the owner's credentials. Add **statement timeouts** so one bad query can't tie up the database, hide sensitive columns with column-level grants, and where you can, point the agent at a **read replica** instead of the primary. The database then refuses writes no matter what the agent decides to run.

The rest of this guide gives the SQL for PostgreSQL and MySQL, explains which settings actually enforce read-only and which only look like they do, and covers the credential mistakes that undo all of it. The PostgreSQL commands were tested on PostgreSQL 16; the docs were checked on October 31, 2026.

## Why "read-only" has to be enforced by the database

Telling an agent "only run SELECT queries" is an instruction, not a control: it can misread it, lose it in a long session or decide a `DELETE` is what you wanted.

Several of the public cases in our roundup of [AI coding agent incidents](https://vinescale.com/blog/ai-coding-agent-incidents/) follow the same pattern: the agent was working on something unrelated, found credentials with more power than the task needed, and used them. The fix that holds up is a user that *can't* write, so the worst outcome of a confused agent is an error message.

## Step 1: create a read-only role in PostgreSQL

Connect as an administrator and create a login role for the agent. This example assumes a database called `app` whose tables are owned by `app_owner`:

```sql
CREATE ROLE agent_readonly WITH LOGIN PASSWORD 'change-me'
  NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS
  CONNECTION LIMIT 5;

GRANT CONNECT ON DATABASE app TO agent_readonly;
GRANT USAGE ON SCHEMA public TO agent_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent_readonly;

-- Tables app_owner creates later are readable too
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO agent_readonly;
```

`GRANT SELECT ON ALL TABLES` only covers tables that exist now. The [ALTER DEFAULT PRIVILEGES docs](https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html) say it sets privileges "that will be applied to objects created in the future", and only for objects created by the role you name, so name the role your migrations run as. Repeat the grants for each schema the agent needs.

On PostgreSQL 14 and earlier, every role can create tables in the `public` schema by default. Run `REVOKE CREATE ON SCHEMA public FROM PUBLIC;` there so the agent can't create objects either. PostgreSQL 15 and later no longer grant that by default.

### The shortcut: pg_read_all_data

If the agent should read everything, PostgreSQL 14 and later have a built-in role for it:

```sql
GRANT pg_read_all_data TO agent_readonly;
```

Per the [predefined roles docs](https://www.postgresql.org/docs/current/predefined-roles.html), it "allows reading all data (tables, views, sequences), as if having `SELECT` rights on those objects and `USAGE` rights on all schemas." It needs no default privileges for new tables. The trade-off is that it really is everything, including tables of personal data, and you can't subtract a table from it. Use the explicit grants above when you need to hide some tables.

## Step 2: add timeouts and connection limits

Read-only doesn't mean harmless. A `SELECT` with a missing join condition on a large table can run for an hour and compete with your application for CPU and I/O. Set limits on the role so they apply to every session it opens:

```sql
ALTER ROLE agent_readonly SET statement_timeout = '30s';
ALTER ROLE agent_readonly SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE agent_readonly SET default_transaction_read_only = on;
```

`statement_timeout` will "abort any statement that takes more than the specified amount of time", and `idle_in_transaction_session_timeout` ends sessions left open inside a transaction, which otherwise hold locks and block vacuum ([client connection defaults](https://www.postgresql.org/docs/current/runtime-config-client.html)). The `CONNECTION LIMIT 5` from step 1 stops a loop of new connections from using up your pool.

These are defaults, not locks: a session can run `SET statement_timeout = 0` and remove its own limit. They protect against accidents. And `default_transaction_read_only` is the setting people most often mistake for enforcement.

## Is default_transaction_read_only enough on its own?

No. It makes each new transaction read-only by default, and a read-only transaction rejects `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` and all `CREATE`, `ALTER` and `DROP` commands ([SET TRANSACTION docs](https://www.postgresql.org/docs/current/sql-set-transaction.html)). But any session can switch it off. In our test, the read-only role ran:

```sql
DELETE FROM orders;
-- ERROR:  cannot execute DELETE in a read-only transaction
SET default_transaction_read_only = off;
DELETE FROM orders;
-- ERROR:  permission denied for table orders
```

The first error came from the setting, the second from the missing grant. Only the grant is a real boundary. Keep the setting as a second layer, but never call a role with write privileges read-only because of it.

## Step 3: hide sensitive columns and rows

An agent debugging a slow query rarely needs password hashes, tokens or customers' email addresses. PostgreSQL supports column-level privileges ([privileges docs](https://www.postgresql.org/docs/current/ddl-priv.html)):

```sql
REVOKE SELECT ON users FROM agent_readonly;
GRANT SELECT (id, name, created_at) ON users TO agent_readonly;
```

Now `SELECT id, name FROM users` works and `SELECT email FROM users` fails with `permission denied for table users`. A view that exposes only safe or masked columns works too, and is easier to keep in sync with schema changes.

If your tables use row-level security, the agent role is subject to it: we created the role with `NOBYPASSRLS`, and `pg_read_all_data` "does not bypass row-level security (RLS) policies", per the docs.

## Step 4: point the agent at a read replica if you have one

A PostgreSQL replica in hot standby mode is read-only at the server level. The [hot standby docs](https://www.postgresql.org/docs/current/hot-standby.html) say "all such connections are strictly read-only; not even temporary tables may be written." That protects you from a mistaken grant and moves heavy exploratory queries off the primary your users depend on. Use the read-only role on the replica too, so its credentials can't be reused against the primary.

## How to do the same in MySQL

The privilege model is similar. Create a dedicated user and grant `SELECT` on one database:

```sql
CREATE USER 'agent_readonly'@'%' IDENTIFIED BY 'change-me'
  WITH MAX_USER_CONNECTIONS 5;

GRANT SELECT ON app.* TO 'agent_readonly'@'%';
```

Replace `%` with the host or network the agent connects from. MySQL also supports column privileges, for example `GRANT SELECT (id, name) ON app.users TO 'agent_readonly'@'%';` ([GRANT docs](https://dev.mysql.com/doc/refman/8.4/en/grant.html)). Don't grant `SELECT` on `mysql.*`, and never grant `SUPER`, `FILE` or `PROCESS` to this user.

For timeouts, MySQL limits read-only `SELECT` statements with `max_execution_time`, in milliseconds, which you can set per session (`SET SESSION max_execution_time = 30000;`) or per query with the `MAX_EXECUTION_TIME` optimizer hint. The [optimizer hints docs](https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html) note the hint "is permitted only for SELECT statements":

```sql
SELECT /*+ MAX_EXECUTION_TIME(30000) */ COUNT(*) FROM orders WHERE status = 'pending';
```

## Keep the powerful credentials out of reach

A read-only role only helps if it's the only credential the agent can find. The most common way this fails isn't SQL at all:

- **No owner or admin credentials on the machine the agent runs on.** If `.env` holds `DATABASE_URL` for the application's own user, the agent can read it. Keep production credentials in your secrets manager and deployment environment, not in local files.
- **Watch for test and staging variables that point at production.** A `TEST_DATABASE_URL` or cached config that quietly resolves to the live database turns a routine test run into a production change.
- **Block the agent from reading secret files.** In Claude Code, deny rules such as `Read(./.env)` and `Read(./.env.*)` keep the agent from opening them; our guide to [stopping destructive commands in Claude Code](https://vinescale.com/blog/stop-claude-code-destructive-commands/) has the full config. The [permissions docs](https://code.claude.com/docs/en/permissions) note that Read deny rules don't stop other programs started by a shell command, so the safest secret is one that isn't on disk.
- **Scope cloud API tokens too.** A database protected by grants can still be deleted through the hosting provider's API by a token that allows it.

If the agent reaches the database through an MCP server, apply the same rules there. Supabase's MCP server, for example, has a `read_only=true` option that will "execute all queries as a read-only Postgres user", and a `project_ref` option to scope it to one project. Supabase's [MCP docs](https://supabase.com/docs/guides/getting-started/mcp) recommend scoping to the project and using read-only mode before connecting a production project.

## Checklist

- A dedicated `agent_readonly` user, separate from the application and owner users
- `SELECT` grants only, plus default privileges for future tables (or `pg_read_all_data`)
- `NOSUPERUSER`, `NOBYPASSRLS`, a connection limit
- `statement_timeout` and `idle_in_transaction_session_timeout` on the role
- Column grants or views for sensitive data
- A read replica where possible
- No owner credentials, production `.env` files or broad cloud tokens where the agent can read them
- Test it: connect as the agent user and try a `DELETE`, a `DROP` and a `SELECT` on a hidden column

Read-only access stops the agent from breaking the database directly. It doesn't cover an agent with write access to code, migrations or infrastructure making a change that breaks something downstream. That's what we're building VineScale for: a safety gate, now in [free beta](https://vinescale.com/beta/) for Claude Code, with beta support for Codex, Cursor, Antigravity and Grok Build, that knows a team's production databases and infrastructure and warns about or blocks an agent's action based on what it would break.

## Frequently asked questions

### Is it safe to let an AI agent query a production database?

It can be, if the agent connects as a dedicated user that has only `SELECT` privileges, with statement timeouts and no access to sensitive columns. The database then rejects any write the agent attempts. Never give the agent the application's own credentials or an owner or admin account.

### Does default_transaction_read_only make a PostgreSQL user read-only?

No. It only sets the default for new transactions, and any session can turn it off with `SET default_transaction_read_only = off`. Read-only access comes from privileges: grant the user `SELECT` and nothing else. The setting is still useful as a second layer.

### What is pg_read_all_data?

It's a predefined PostgreSQL role, available from PostgreSQL 14, that lets a user read all tables, views and sequences as if it had `SELECT` on each of them and `USAGE` on every schema. It doesn't bypass row-level security. Use explicit grants instead when some tables should stay hidden.

### Should the agent use a read replica instead of the primary?

When you have one, yes. A PostgreSQL hot standby rejects every write at the server level, and running exploratory queries on a replica keeps them from slowing down the primary your application uses. Connect with a read-only user on the replica as well.

VineScale is a safety gate for AI coding agents. The gate for Claude Code is in free beta, with beta support for Codex, Cursor, Antigravity and Grok Build: https://vinescale.com/beta/