Read-Only by Default: Agent Tasks Without Writes

Notice that the table never asks whether the agent is "trusted." Trust is not a property you can measure per run, and agents drift: the same model that behaves well on Tuesday can be talked into something else by a malicious document it reads. OpenAI's safety guidance argues the reliable strategy is to constrain the impact of manipulation rather than to detect every attempt (Penligent's summary of OpenAI agent safety guidance). A read-only role is that constraint expressed as a grant.

The grant itself is small. In PostgreSQL, CONNECT and USAGE are separate from table privileges, so a read-only role typically needs three grants: connect to the database, usage on the schema, and select on the tables or views, plus default privileges so tables created later are covered (Stack Overflow's read-only user recipe).

-- Run as the object owner, not as a superuser shared with the agent
CREATE ROLE agent_reader LOGIN PASSWORD :'pw';
GRANT CONNECT ON DATABASE appdb TO agent_reader;
GRANT USAGE ON SCHEMA public TO agent_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO agent_reader;

The ALTER DEFAULT PRIVILEGES line is the one people forget. AWS's guidance on PostgreSQL roles recommends exactly this shape: named roles per use case (readonly, readwrite), least privilege, and never letting the application use the master user (AWS Database Blog). If your migrations run as a different role than the one that issued the default-privilege statement, new tables will not be readable, and the agent will fail on a permission error that looks like a bug.

Lookups and enrichment. "What tier is account A-1029 on, and have they opened a support ticket in the last 30 days?" The agent reads, joins, and returns an answer. Nothing needs to persist because the answer goes back into the conversation, and the transcript or the agent's log is the record, not a database row.

Reporting and anomaly detection. Daily revenue reconciliations, backlog aging, cohort counts, duplicate-record hunts. These are naturally read-only, and the output (a report, a chart, a list of exceptions) is a deliverable a human consumes. If the report must be stored, store it outside the operational database, in an object store or a reporting warehouse fed by a separate job.

Triage and classification. Route an inbound ticket to a queue, label an alert as noise or signal, score a lead, pick the right runbook. The decision is the output. The executing system (the ticket tracker, the alerting tool) applies it under its own rules and usually keeps its own audit trail. The agent never touches the operational tables.

Reconciliation. Compare two sources: the payment processor's settlement file against your ledger, the CRM against the billing system, the carrier manifest against the order table. The agent produces a list of mismatches with evidence. Fixing the mismatches is a second task with a different scope, and often a different owner.

Cache warmers and retrievers. Anything that builds a read model (an index, an embedding set, a materialized view) can be run by a builder job with a write grant to its own schema, and the query-facing agent connects to that read model with SELECT only. Keep the two roles distinct even when the same person owns both.

A worked example with numbers makes the boundary concrete. Suppose reconciliation runs nightly against 1.2 million orders and finds 340 mismatches, about 0.03 percent. A read-only agent flags all 340 in a report. A write-enabled agent that "corrects" them in place will touch 340 rows, produce 340 audit events you did not design, and give you no clean way to tell a good correction from a hallucinated one. The read-only version gives a human or a separate apply service the list and the evidence, and the correction becomes a reviewed operation with an idempotency key and a log line.

Three requirements break pure read-only scope, and they are the ones that matter.

The task must persist state across runs. Agents are stateless between invocations unless something remembers. If a job polls an inbox every ten minutes and must know which messages it already handled, that memory has to live somewhere durable. The good news is that the memory does not need to live in your operational database: a sandbox with a persistent volume synced to object storage can carry agent state between executions, with read-only or read-write access assigned per user or group (Windmill's AI sandbox documentation). Keep the agent's scratch memory in its own volume, and keep the operational database read-only.

The task must retry safely after a crash. This is where naive write-enabled agents go wrong: the tool call times out but succeeds server-side, the agent retries, and you get two invoices (Duckweave on tool calling and retries). Idempotency is the fix, and idempotency is a server-side property: the API must accept a key and return the original outcome when the same key arrives again (WSO2 on idempotent tool design). A read-only role sidesteps the problem entirely because a repeated SELECT is harmless.

The task must leave an audit trail. If an auditor later asks who changed a record and why, the change must have been made by something that records it. PostgreSQL's pgAudit extension exists to capture exactly that level of detail, including the user who made each change, and it is designed for regulatory audit needs (AWS documentation for pgAudit). A SELECT leaves no such trail, which is fine when the deliverable is a report and not fine when the deliverable is a change in the system of record.

There is a fourth signal worth naming even though it is not strictly a write: schema changes. Migrations should never be run by the same identity that reasons about the task. Database branching gives each task its own copy-on-write fork of schema and data, so a botched ALTER TABLE affects only the branch and the parent is structurally unchanged (PingCAP on TINE and database branching). Specstory's walkthrough shows the operational discipline: create the branch when the work starts, point the task's connection string at it, delete it when the pull request closes (Specstory on database branching). That is a write path, but it is a write path to a disposable copy, which is a different risk class from a write to production.

Once a task genuinely needs to change state, do not hand the agent an UPDATE grant. Split the task in two.

The propose step runs under a role with SELECT plus INSERT on exactly one table: an intent table. The agent reads whatever it needs, decides what should change, and writes a row describing the intended change: the target row(s), the before-value, the after-value, the reason, a correlation id and a hash of the evidence it used. This role can insert into one table and read the rest. It cannot update, delete, or touch anything in the operational schema.

The apply step is a separate service with the actual write grants. It validates each intent against current data (does the target row still have the before-value?), enforces business rules, and executes the change inside a transaction. It records the outcome, accepts an idempotency key so a retry does not double-apply, and writes an audit row. If validation fails, the intent is marked rejected with a reason and nothing changes.

The shape of the intent table matters more than the schema details:

CREATE TABLE agent_intent (
  id            bigserial PRIMARY KEY,
  correlation_id text NOT NULL,
  task          text NOT NULL,
  target_table  text NOT NULL,
  target_key    text NOT NULL,
  before_value  jsonb,
  after_value   jsonb,
  reason        text NOT NULL,
  evidence_hash text NOT NULL,
  status        text NOT NULL DEFAULT 'proposed',
  created_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (correlation_id, target_table, target_key)
);

That UNIQUE constraint is doing real work. It makes the propose step idempotent at the database level: a retried proposal with the same correlation id and target becomes a no-op instead of a duplicate. The apply service then only has to be careful about its own execution, and that is a much smaller problem than making a language model careful.

This pattern also gives you the audit trail for free. Every change is preceded by a proposed row that names the agent task, the evidence hash and the reason, and every change is followed by a status transition. When an auditor asks why the shipping address for order 4417 changed, the answer is a row, not a reconstruction from logs.

A practical note on transaction semantics: if you ever prototype the apply step inside a test harness, remember that test-managed transactions and preemptive timeouts do not mix well, because work performed on a different thread escapes the transaction and commits even though the harness rolls back (Spring Framework transaction management guide). The same class of bug applies to any async apply service: if the write happens outside the transaction you think wraps it, your rollback is a lie.

A SELECT-only grant is the strongest single control, but it is one layer. Agents run with more access than the grant suggests, because the process also has environment variables, credentials, network reach and a filesystem. Sandboxing is what bounds all of that at once: the filesystem boundary limits the agent to its mounted working tree, and process isolation limits what it can reach outside it (Penligent on coding agent sandboxes). A proper sandbox is a bundle of boundaries, not just a separate container, and hardware-level isolation (microVMs) is a different risk class from software isolation (Solo.io's agent sandbox guide).

For database access specifically, the checks that pay for themselves are:

• The agent's connection string points at a role with SELECT, or SELECT plus INSERT on the intent table. Nothing else.
• The role is not a member of any role with write grants, and default privileges do not accidentally grant more.
• The database user the agent uses is distinct per environment, so a staging credential cannot reach production.
• The apply service's credentials live in a different secret store and are never injected into the agent's process.

If you run agents that generate or test code against a database, database branching is the complementary control: branch per task, point the task at the branch, and drop the branch when the task ends (cole-medin knowledge base on database branching). The agent can then run migrations and reset fixtures freely, because the blast radius stops at its own copy. That is read-write scope against a disposable target, which is safe in a way that read-write scope against production never is.

Readers building agent platforms with model routing across text, image and embedding endpoints often find that the database scope decision is the easy half; the harder half is running the same agent task against several model providers without rewriting the tool layer. That is the case Quanta Inference is built for: one OpenAI-compatible API key across model types, priced in US dollars with prepaid billing (https://quantaintelligence.ai/inference). For teams that want an expert to own the propose-then-apply build rather than assemble it, Quanta Experts hands back finished deliverables (https://quantaintelligence.ai/agents).

Before you grant the agent anything, answer these in writing:

1. Write the success sentence. Can it be verified with a SELECT? If yes, stop at read-only.
2. Does the task need to remember anything between runs? If yes, put that memory in a sandbox volume, not the operational database.
3. Can the task be retried without side effects? If not, you need an idempotency key at the tool boundary.
4. Does anyone need to reconstruct the change later? If yes, you need an intent row or an audit extension, not just a log line.
5. Does the task touch schema? If yes, run it against a database branch and never against production.
6. Is the agent's credential separate from every human and service credential? If not, fix that before anything else.

The decision rule survives contact with real tasks because it maps to something a reviewer can check. Read-only scope is not a limitation you tolerate; it is the default you earn the right to leave. Every task that fits it gets a cheaper review, a smaller blast radius and a shorter incident report.