Giving an AI agent database access without losing sleep: roles, sandboxes, audit

Published November 10, 2025

The fear is legitimate. An agent with a database connection and a shell can run DROP TABLE just as easily as it can run SELECT. It doesn't have to be malicious to cause damage — it just has to be wrong once, confidently, at 2am, with nobody watching. If your reaction to "should I give my agent DB access" is caution, that's the correct starting reaction.

But "no access" isn't really an option either, not if you want the agent to be useful. An agent that can't see your actual schema will guess column names, invent tables that don't exist, and write code against a mental model of your database that's slightly wrong in ways that only surface at runtime. The answer isn't all-or-nothing. It's layered — the same way you'd think about any other principal with partial trust.

The layered answer

Treat the agent like you'd treat a new hire's laptop on day one: useful access, no destructive capability, everything logged. Concretely:

  1. Read-only role by default. The agent's normal connection can SELECT. Nothing else.
  2. A separate sandbox schema or database for write experiments. If the agent needs to try something destructive, it does it somewhere that isn't production.
  3. Row-level security where your database supports it, so "read-only" can also mean "read-only, and only these rows."
  4. Query allowlists and timeouts, so even read access can't run an unbounded query that takes down a table.
  5. Audit logging on every query the agent runs, so "what did it actually do" is answerable after the fact, not a mystery.
  6. Human approval gates for migrations. Schema changes are the one category I don't let happen unsupervised, full stop.

None of these individually is bulletproof. Together, they mean the worst realistic outcome is a slow query or a rejected proposal, not lost data.

Read-only role as the default

This is the floor, not a nice-to-have. The connection string an agent uses in day-to-day work should point at a role that physically cannot write.

CREATE ROLE agent_readonly LOGIN PASSWORD '...';
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;

-- Make sure new tables inherit the same restriction automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO agent_readonly;

That last line matters more than it looks. Without it, every new table you create needs a manual grant, and it's exactly the kind of thing that gets forgotten — at which point the agent either can't see a table it needs, or worse, someone "temporarily" grants it broader access to unblock themselves and forgets to revoke it.

GRANT SELECT and nothing else means there's no INSERT, UPDATE, DELETE, TRUNCATE, or DROP available to this role, at the database level, regardless of what the agent decides to attempt. This isn't about trusting the agent's judgment — it's that the judgment call is moot because the permission doesn't exist.

A separate sandbox for write experiments

Read-only is right for the agent working against your real application. It's too restrictive for an agent that's supposed to prototype a schema change or test a migration idea. For that, give it a database that isn't production — a sandbox schema or a genuinely separate database, seeded with realistic-but-not-real data, where it has real write privileges.

-- A schema the agent can read and write freely, isolated from the real data
CREATE SCHEMA agent_sandbox AUTHORIZATION agent_sandbox_role;
GRANT ALL PRIVILEGES ON SCHEMA agent_sandbox TO agent_sandbox_role;

The key property of a sandbox isn't that it's less important — it's that destroying it is cheap. If the agent truncates every table in agent_sandbox, you re-seed it and move on. If it did the same thing in public, you're restoring from backup and explaining an incident.

Row-level security where it's supported

Postgres row-level security lets you scope SELECT down further than "this table" to "these rows in this table." If your agent only ever needs to see one tenant's data, or needs to be blocked from a table of API keys and secrets even under a read-only grant, RLS is the tool.

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

CREATE POLICY agent_readonly_customers ON customers
  FOR SELECT
  TO agent_readonly
  USING (is_sensitive = false);

This is worth doing selectively, not everywhere — RLS adds real complexity to query planning and to your mental model of who sees what. I'd reach for it specifically for tables that hold secrets or PII, where "just don't grant SELECT on this table" is too blunt (because the agent legitimately needs some columns or some rows, just not all of them).

Query allowlists and timeouts

Read-only doesn't mean safe by itself. A read-only role can still run a full table scan against your largest table with no LIMIT, at the worst possible time, and cause a real production problem. Two cheap mitigations:

-- Cap how long any single query from this role can run
ALTER ROLE agent_readonly SET statement_timeout = '5s';

-- Cap idle-in-transaction time so a hung session doesn't hold locks
ALTER ROLE agent_readonly SET idle_in_transaction_session_timeout = '10s';

A five-second statement timeout sounds aggressive, but an agent exploring schema and sampling data doesn't need long-running queries — if a query needs longer than that, it's usually a sign the agent should be asking a narrower question, not that the timeout is wrong. Tune it to your actual query patterns, but start restrictive and loosen only if you hit real false positives.

On top of the database-level timeout, whatever MCP server or tool layer mediates the agent's queries should allowlist query shapes — SELECT only, no pg_terminate_backend, no access to pg_ administrative functions, no COPY to arbitrary file paths. Defense in depth: the role restricts what's possible at the database, the tool layer restricts what's offered to the agent in the first place.

Audit logging every agent query

If something goes wrong, or even if you're just trying to understand what the agent has been doing all week, you need a log. Postgres can log statements from a specific role:

ALTER ROLE agent_readonly SET log_statement = 'all';

That gets you the queries in your Postgres logs. In practice I'd rather have this at the tool layer too — the MCP server or proxy that mediates agent access logging each query with a timestamp, the agent session it came from, and the result row count, somewhere queryable and retained on purpose rather than mixed into general Postgres logs that rotate out in a few days. The database log is the backstop; the application-level audit log is what you'll actually look at.

Human approval gates for migrations

Everything above is about queries. Migrations are a different category, and I treat them differently on principle, not just as an extension of "be careful." A migration changes the shape of the database for everyone, permanently, and a bad one can be much harder to undo than a bad query.

The agent can propose a migration — write the SQL, explain what it does, flag anything destructive. It does not run it. A human reads the migration, runs it against a staging environment first, and applies it to production the same way any other migration gets applied: reviewed, and by a person.

-- Agent-proposed migration, reviewed before it runs anywhere real
BEGIN;

ALTER TABLE orders ADD COLUMN fulfillment_status text NOT NULL DEFAULT 'pending';
CREATE INDEX CONCURRENTLY idx_orders_fulfillment_status ON orders (fulfillment_status);

COMMIT;

Even a migration that looks obviously safe — adding a nullable column, adding an index — deserves this gate. The cost of the gate is small (a few minutes of review). The cost of skipping it, on the one migration that wasn't as safe as it looked, is not small.

The short version

Read-only by default. A real sandbox for anything that needs to write. Row-level security for anything sensitive. Timeouts so read-only can't still hurt you. Audit logs so you're never guessing what happened. Migrations reviewed by a human, always. None of this is exotic — it's the same access model you'd design for a contractor you trust but don't fully trust yet. The agent doesn't need more than that to be useful, and you don't need to give it more than that to sleep fine.