VineScale

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

By · · 8 min read

DatabasesPermissionsGuardrails

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

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

GRANT pg_read_all_data TO agent_readonly;

Per the predefined roles docs, 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:

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). 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). But any session can switch it off. In our test, the read-only role ran:

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

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

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). 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 note the hint "is permitted only for SELECT statements":

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 has the full config. The permissions docs 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 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 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.

Related articles