Give an AI Coding Agent Read-Only Production Access

An AI coding agent that can see production data earns its keep on one job: debugging an incident that only reproduces against real data. An agent that can also write to production is a different, riskier tool. Giving an agent read only access to production data means creating a database credential technically incapable of INSERT, UPDATE, DELETE, or TRUNCATE, not an agent you have politely asked not to touch anything. A prompt is not a permission system, and Postgres does not read it before deciding whether to execute a statement.

This is the production-access version of a question this blog has already covered for staging environments and for terminal access, two of the capability decisions that come up once you are using an AI coding agent beyond autocomplete. Those two are about letting an agent act. This one is about letting it look, in the one environment where a mistake is the most expensive kind. What follows is what you would hand a contractor debugging a live incident: a scoped credential, not a stern warning.

Why "just don't tell it to write" isn't a boundary

The easy version is to hand the agent your existing database credentials and add a line to its instructions: read-only, do not modify anything. It works until it doesn't.

A system prompt is context the model weighs against everything else it reads: the incident ticket, the error logs, text embedded in the data itself. If a ticket contains a line that reads like an instruction, or the agent decides fixing the row is more helpful than reporting it, nothing in a prompt stops the attempt, and if the credential can write, the write happens. This is not mainly about malicious injection, it is often the agent being helpful. A boundary living only in English is a request, not a boundary. The real one lives where Postgres enforces it, in the role's grants.

Step 1: create a dedicated read-only role

Do not reuse an existing role. Not your app's connection user, not a "reporting" role some past project quietly over-granted. Create one role whose only job is being handed to the agent.

-- Run as a superuser or a role with CREATEROLE
create role agent_readonly with
  login
  password 'use a generated secret, not this one'
  connection limit 3
  valid until '2026-08-23 00:00:00+00';

grant connect on database production to agent_readonly;
grant usage on schema public to agent_readonly;

connection limit caps concurrent sessions, useful if the agent fires off parallel queries mid-investigation and you would rather it get throttled than hammer the primary. valid until is the time-box, covered in step 4.

Step 2: grant select, then revoke everything else explicitly

Granting SELECT is the easy part. The part people skip is revoking write privileges explicitly instead of assuming a role that was never granted them does not have them, when inherited memberships and stale defaults can hand a role more than intended.

grant select on all tables in schema public to agent_readonly;
alter default privileges in schema public
  grant select on tables to agent_readonly;

revoke insert, update, delete, truncate, references, trigger
  on all tables in schema public
  from agent_readonly;

revoke create on schema public from agent_readonly;

The alter default privileges line matters as much as the grant. It makes SELECT the default for tables created after this point, so a new table from tomorrow's migration does not end up ungoverned. Confirm it worked. Query information_schema.role_table_grants for agent_readonly and check the privilege column reads SELECT only. Do not trust that the statement did what you assume.

Step 3: handle PII with column grants or row-level security

Read-only access to a customers table with email, phone, and payment fields is still a data exposure, just not a modification one. An agent debugging application behavior rarely needs the actual PII values, it needs to know a row exists, its shape, and the non-sensitive fields around it. Column-level grants are the lightest fix:

revoke select on customers from agent_readonly;
grant select (id, created_at, status, plan, last_login_at)
  on customers to agent_readonly;

select * against that table now fails. An explicit column list that excludes email and payment fields succeeds. If the restriction is about which rows rather than which columns, for example scoping a multi-tenant schema to the one tenant being debugged, row-level security is the right tool instead:

alter table orders enable row level security;

create policy agent_tenant_scope on orders
  for select
  to agent_readonly
  using (tenant_id = current_setting('app.debug_tenant_id')::uuid);

Set app.debug_tenant_id for the session before the agent connects, and it only ever sees the one tenant it is actually debugging. Postgres's own row security documentation covers the policy syntax in full.

Step 4: time-box the credential

A read-only credential that never expires eventually gets pasted into a chat thread, committed to a config file, or left sitting in an agent's tool config months after the incident that justified it. Expire it. valid until on the role handles this directly:

alter role agent_readonly valid until '2026-08-23 12:00:00+00';

Past that timestamp the role cannot log in, though its grants stay intact, so rotating it breaks nothing downstream. For a session that should last hours, set it that tight and re-expire the role when the incident closes. Where available, a provider's native short-lived credentials, AWS RDS IAM authentication or Vault's database secrets engine, are worth preferring over a hand-rolled expiry, since both issue tokens that expire in minutes and never touch a shared password.

Step 5: scope the connection string in the agent's tool config

Everything above is worthless if the connection string in the agent's MCP server config or .env still points at the URL your application uses to write. This step closes the loop. The agent needs its own connection string, built from agent_readonly's credentials, and that should be its only database access, not a shared file that also holds write credentials where a misconfigured tool call could reach the wrong one.

# .env.agent-debug, used only by the agent's database tool
DATABASE_URL=postgresql://agent_readonly:REDACTED@db-read-replica.internal:5432/production?sslmode=require

Point it at a read replica where you have one. That does not add security by itself, but it keeps a badly written query from contending with the primary your application depends on. Confirm the agent's database tool reads its connection string from this isolated file, not from a shared environment where write credentials also live.

Prove the boundary holds before you trust it

Do not take the grants on faith. Connect as agent_readonly, or ask the agent to attempt a write in the same session it will use for debugging, and confirm it fails:

-- Connected as agent_readonly
update orders set status = 'cancelled' where id = 1;
-- ERROR: permission denied for table orders

That error is the actual safety mechanism. Everything before this step was configuration, this is the test that it does what you think. Run it when the role is created, and again any time the grants change, because a grant all on all tables in schema public typed at 2am while fixing an unrelated permissions issue is exactly how boundaries like this quietly get undone.

A worked example: a bad row in production

An order stopped showing up in a customer's dashboard. The agent, connected as agent_readonly through the read replica, finds the row and notices shipping_address is null on an order the write path assumes never happens. It checks the audit log, also SELECT-only, and traces the null to an import job that ran three days earlier, not the checkout flow. It pulls a few more rows with the same pattern to confirm it is not a one-off, and reproduces the crash locally against that shape.

What it does not do, because the credential does not allow it, is fix the row. It writes up what it found, which rows, which job caused it, and a proposed backfill for a person to review and run with write access. Agent investigates and proposes, a person with write access executes, is the entire point of this setup.

Checklist: read-only access for an AI agent

1. Create a dedicated role for the agent. Never hand it the app's write credentials or your own login.

2. Grant SELECT explicitly, revoke writes explicitly. Do not assume a role lacks privileges just because you never granted them.

3. Restrict PII with column grants or row-level security, scoped to column sensitivity or tenant depending on the exposure.

4. Time-box the credential with valid until, or a provider's short-lived credential system. It should expire when the incident does.

5. Give the agent its own connection string, pointed at a read replica where possible, never a file that also holds write credentials.

6. Test the boundary yourself. Attempt a write with the agent's credential and confirm Postgres rejects it first.

7. Log what the credential is used for. log_statement or pgAudit still matters for access that can see everything.

Where this fits with staging and terminal access

Production read access, staging access, and terminal access are three different trust decisions, worth keeping separate. This one is about visibility into real data without the ability to change it. Give an AI coding agent safe staging access covers the environment where you do want the agent to write, because staging is built to absorb that. Give an AI coding agent access to your terminal covers what shell commands it can run at all, which matters even for a read-only database session, since a broad terminal grant could still expose a connection string it should not see.

For a team rather than one agent session, how to set permissions for AI coding agents on a team covers keeping these grants consistent across engineers. The database role is one layer. Whether an AI system should see the data at all is the broader question in is it safe to give AI access to your data.

Frequently asked questions

Can an AI agent with read-only database access still leak data?

Yes. Read-only stops it changing your database, not from repeating a customer's email in a chat response or a bug report. If the data is sensitive, restrict the columns the credential can see rather than relying on the agent's judgment.

Is a read replica necessary, or is the role restriction enough?

The role restriction is what makes the access safe from writes. The replica just keeps a slow query from competing with your application for primary resources. Skip it and you risk a bad SELECT slowing production reads, not a write.

How is this different from giving the agent an API endpoint instead?

An internal API can enforce the same restrictions and is often better if one exists, since you can log and rate-limit at that layer too. A direct credential earns its place when the agent needs to write its own queries to investigate something you did not anticipate.

Should the agent ever write to production, even for a fix it's confident about?

Not unattended. Have it propose the exact statement and let a person with write access run it. This setup covers investigation specifically, executing its own fix is a separate, higher-trust decision, closer to the staging access model than to this one.