How do I set up a read-only PostgreSQL role for an AI agent?

Quick answer: Create a dedicated LOGIN role, then GRANT CONNECT on the database, GRANT USAGE on each schema, and GRANT SELECT on the tables it needs, plus ALTER DEFAULT PRIVILEGES so future tables are covered. Add statement_timeout and idle_in_transaction_session_timeout on the role. Grants are the layer PostgreSQL enforces; session settings, MCP query parsing and prompt instructions are not.

The complete setup

Run this as a superuser or the database owner. Step 5 additionally requires that you be app_owner or a member of it.ALTER DEFAULT PRIVILEGES FOR ROLE can only be run by that role or a superuser. Substitute your own database, schema, and owning role.

-- 1. The role itself. It logs in, and (after step 3b) can do nothing else.
CREATE ROLE ai_agent_ro WITH
  LOGIN
  PASSWORD 'use-a-generated-secret'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS
  CONNECTION LIMIT 5;

-- 2. Connect to exactly one database.
GRANT CONNECT ON DATABASE app TO ai_agent_ro;

-- 3. See into exactly the schemas it needs. USAGE alone reveals nothing;
--    it is the prerequisite for using anything inside the schema.
GRANT USAGE ON SCHEMA public TO ai_agent_ro;

-- 3b. On PG14 and earlier, and on ANY cluster upgraded from one, PUBLIC still
--     holds CREATE on schema public. PG15 removed this for new databases only;
--     an upgraded database keeps it, so this role could create tables.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 4. Read the tables and views that exist right now.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_agent_ro;

-- 5. Read the tables that do not exist yet.
--    FOR ROLE must name the role that CREATES the tables, usually the role
--    your migrations run as. This is the step everyone gets wrong.
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO ai_agent_ro;

-- 6. Bound the blast radius of any single query or transaction.
ALTER ROLE ai_agent_ro SET statement_timeout = '30s';
ALTER ROLE ai_agent_ro SET idle_in_transaction_session_timeout = '60s';

-- 7. A sensible default, not a boundary. See the enforcement section below.
ALTER ROLE ai_agent_ro SET default_transaction_read_only = on;

Then remove anything the role should not see:

-- Whole tables.
REVOKE ALL ON TABLE payment_methods, auth_tokens FROM ai_agent_ro;

-- Individual columns: revoke the table-level grant first, then grant
-- column by column. A table-level SELECT supersedes column-level grants,
-- so leaving it in place makes the column grants meaningless.
REVOKE SELECT ON TABLE users FROM ai_agent_ro;
GRANT SELECT (id, created_at, status, country_code) ON users TO ai_agent_ro;

Repeat steps 3–5 for every schema the agent needs. Anything you do not name stays invisible.

What each step is actually doing

CONNECT on the database is per-database. Without it the login fails at connection time, before any query runs. Note that PUBLIC, the implicit group every role belongs to, holds CONNECT on a new database by default, so your role can probably connect to other databases on the same cluster too. Revoking that (REVOKE CONNECT ON DATABASE other_db FROM PUBLIC) affects every role, so check what else depends on it before you do.

USAGE on a schema is a gate, not a grant. It does not confer read access to anything; it makes the objects inside the schema usable by a role that also has privileges on them. No USAGE, no access, regardless of table grants.

SELECT ON ALL TABLES IN SCHEMA is the one that surprises people. Read it as "on all tables that exist at the moment I press enter." It is a loop over the current catalog, not a standing rule. Create a table tomorrow and the role cannot read it.

ALTER DEFAULT PRIVILEGES is the standing rule, with a sharp edge. Default privileges are recorded per creating role. If you run it as postgres but your migrations create tables as app_owner, the rule never fires and you will be back running GRANT SELECT ON ALL TABLES by hand after every deploy, wondering why. Always pass FOR ROLE explicitly and make it the role that owns your migrations.

There is a legitimate argument for not setting default privileges at all: it means a newly added table, which might be exactly the one holding your new sensitive field, is invisible to the agent until someone grants it deliberately. That is more friction and better security. Pick one on purpose rather than by accident.

The pg_read_all_data shortcut, and why to think twice

PostgreSQL 14 added a predefined role that covers all of steps 3–5 at once:

GRANT pg_read_all_data TO ai_agent_ro;

That grants read access across the cluster's databases as though the role had SELECT on everything, plus schema USAGE. It is genuinely convenient, and it is fine for a throwaway analysis role.

For an agent it is the wrong default. It is the opposite of least privilege: it covers every table that exists and every table anyone adds later, including the one holding card tokens that someone creates next quarter. You also cannot meaningfully carve exceptions out of it, a REVOKE on a specific table does not remove access granted through the predefined role. Row-level security policies do still apply (the role is not BYPASSRLS), but that is a narrow consolation if you are not already using RLS.

Use explicit grants. They are twenty extra seconds and they are auditable.

Where is "read-only" actually enforced?

This is the part most guides skip, and it is the part that matters. There are five different things people call "read-only," and they have wildly different strength. From weakest to strongest:

#	Mechanism	Enforced by	Can the agent get around it?
1	Prompt / rules-file instruction ("you are read-only")	Nothing	Trivially, it is text
2	MCP or client statement parsing	The agent's own tool process	Yes, several ways
3	default_transaction_read_only on the role	PostgreSQL, per transaction	Yes, one SET
4	Role grants (SELECT only, no INSERT/UPDATE/DELETE)	PostgreSQL, per object	No
5	Hot standby replica	PostgreSQL, per cluster	No

1. Prompt instructions are not enforcement

Telling the agent "only run SELECT statements" in a system prompt, a CLAUDE.md, a .cursorrules, or a tool description is guidance. It usually works, because the model is usually cooperative. It is not a control, because:

• Instructions compete with everything else in the context window, and later context can win.
• The context includes data read out of the database, which can contain instructions of its own (see is it safe to let an AI agent access my production database).
• A model that misreads its instructions produces exactly the same SQL as a model that was persuaded to ignore them.

This is the layer people most often rely on, and it is the only one on this list with zero enforcement behind it.

2. Statement parsing in an MCP server or client is best-effort

Many database MCP servers have a read-only mode that inspects the SQL before sending it. Turn it on, it catches honest mistakes cheaply. Do not mistake it for a boundary:

• CTEs write. WITH d AS (DELETE FROM sessions RETURNING *) SELECT * FROM d; starts with WITH and is a SELECT statement. It deletes rows.
• Functions have side effects. SELECT my_function(1); is a SELECT. What the function does is up to the function.
• EXPLAIN ANALYZE executes. On a SELECT you pay the full cost; on an INSERT/UPDATE/DELETE the write happens. Plain EXPLAIN does not execute.
• It only covers one path. The same credential used from psql, a script, or a different tool never touches the parser.

It runs on the agent's side of the trust boundary. Assume it can be bypassed and put layer 4 underneath it.

3. default_transaction_read_only is real, but overridable in-session

This one is enforced by PostgreSQL. In a read-only transaction the server rejects INSERT, UPDATE, DELETE, most DDL, and other data-changing commands outright.

The catch is that it is a USERSET parameter. Setting it with ALTER ROLE ... SET establishes the value a new session starts with, and a session can change it:

SET default_transaction_read_only = off;   -- allowed for an ordinary role

So it is a good default that catches accidents, and worthless against anything deliberate. The same is true of statement_timeout and idle_in_transaction_session_timeout: valuable, but defaults rather than ceilings.

4. Grants are the real boundary

If the role has no INSERT privilege on a table, no statement it writes itself, nested in a CTE or typed directly, lets it insert, and there is no session setting that raises its own privileges. This is the layer to build on. The one exception is code someone else already wrote and let it call: a SECURITY DEFINER function, or a view, runs as its owner, not as the caller (see below).

Two things to keep straight:

• A SECURITY DEFINER function executes with its owner's privileges. If such a function exists and the role can call it, the role can do whatever the function does. Check EXECUTE privileges as carefully as SELECT ones.
• Views behave the same way by default: a view runs with the privileges of its owner, not its caller, so granting SELECT on a view grants effective access to the base tables it reads. That is useful (it is how masking views work) and dangerous (it is how a permissive view leaks a table you carefully revoked). PostgreSQL 15 added a security_invoker option on views to flip this, if you want caller-privilege behaviour instead.

5. A replica is the strongest and simplest option

Point the agent at a hot standby. A standby refuses write transactions at the server level, independent of grants, and there is no session setting that changes that. You also move the agent's read load off the primary, which addresses the "read-only query melts the database" risk at the same time.

The trade-off is replication lag, irrelevant for schema, plans, index definitions, and pg_stat_statements, which is most of what an agent needs.

Practical recommendation: replica + explicit SELECT grants (4 and 5), with 1, 2 and 3 layered on as convenience. Never 1 or 2 alone.

Restricting what the agent can see

Beyond grants, three things shape the agent's view:

Search path. Set the role's default so it does not stumble into schemas by accident:

ALTER ROLE ai_agent_ro SET search_path = 'app_public';

This is ergonomics, not security, the role can still schema-qualify anything it has privileges on, and it can change its own search_path. It shapes the default view; it does not restrict it.

Masking views. For a table that mixes sensitive and safe columns, a view in a dedicated schema is cleaner than a pile of column grants:

CREATE SCHEMA agent_views;
GRANT USAGE ON SCHEMA agent_views TO ai_agent_ro;

CREATE VIEW agent_views.users AS
  SELECT id, created_at, status, country_code
  FROM public.users;

GRANT SELECT ON agent_views.users TO ai_agent_ro;
-- and no grant at all on public.users

Because the view runs with its owner's privileges, the role reads the view without needing access to the base table.

Catalog metadata is not hidden. Worth knowing: pg_catalog is readable by default, so a role can see that tables and columns exist, names, types, relationships, even where it has no SELECT on the data. You can remove access to the rows; removing knowledge of the schema is a much bigger project. In practice this is usually fine, but do not assume a revoked table is invisible.

How do I verify what I actually granted?

Do not trust the GRANT statements you think you ran. Ask the database. This returns every relation the role can read, accounting for direct grants, role membership, and PUBLIC:

SELECT n.nspname AS schema,
       c.relname AS object,
       c.relkind AS kind          -- r=table, p=partitioned, v=view, m=matview
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p', 'v', 'm', 'f')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND has_table_privilege('ai_agent_ro', c.oid, 'SELECT')
ORDER BY 1, 2;

Then confirm it holds nothing else:

SELECT c.relname,
       has_table_privilege('ai_agent_ro', c.oid, 'INSERT') AS ins,
       has_table_privilege('ai_agent_ro', c.oid, 'UPDATE') AS upd,
       has_table_privilege('ai_agent_ro', c.oid, 'DELETE') AS del
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND (has_table_privilege('ai_agent_ro', c.oid, 'INSERT')
    OR has_table_privilege('ai_agent_ro', c.oid, 'UPDATE')
    OR has_table_privilege('ai_agent_ro', c.oid, 'DELETE'));

That second query should return zero rows. If it does not, you have almost certainly inherited something through PUBLIC or a group role.

has_table_privilege() is the right function here precisely because it resolves inheritance and PUBLIC grants. Reading information_schema.table_privileges instead will show you direct grants and quietly miss the ones that come in sideways.

Finally, sanity-check as the role itself:

-- connected as ai_agent_ro
CREATE TABLE should_fail (id int);          -- expect: permission denied
INSERT INTO users (id) VALUES (1);          -- expect: permission denied
SELECT current_setting('transaction_read_only');

Why do AI agents make this configuration harder than it used to be?

Read-only roles are not new, analysts and BI tools have used them for decades. Two things change with an agent.

First, the agent generates its own queries. A BI tool issues a fixed set of statements you reviewed once. An agent composes new SQL every turn, including SQL nobody anticipated, against tables you may have forgotten it can reach. "It only runs the queries we wrote" stops being true, so the grant list becomes the only description of what it can do.

Second, the agent's behaviour is influenced by what it reads. If a row contains text that reads like an instruction, that text is now in the loop deciding what query comes next. No previous class of read-only client had that property. It is why the boundary has to be in PostgreSQL rather than in the client that is doing the reasoning.

The good news is that neither changes the mechanics. The GRANT statements at the top of this article are the same ones you would write for any least-privilege reader. The difference is that you have to actually mean them.

How DBGorilla helps

DBGorilla connects to your database read-only and works through the AI coding agent you already use (Claude Code, Cursor) over MCP, so the pattern in this article is exactly how it is meant to be pointed at a database. Give it a role like ai_agent_ro above, ideally on a replica, and it surfaces and explains the real operational data (pg_stat_statements, query plans, index definitions, row-count estimates, schema, locks) to your agent.

Honestly: DBGorilla does not create this role for you, and it cannot enforce least privilege on a database it connects to, the grants above are yours to run and yours to verify. What it does is make a read-only connection genuinely useful, so you are not tempted to hand over a broader one. Get started free →

FAQ

How do I create a read-only user in PostgreSQL? Create a LOGIN role, then GRANT CONNECT on the database, GRANT USAGE on each schema, and GRANT SELECT on the tables. Add ALTER DEFAULT PRIVILEGES for future tables. The role should own nothing and have no CREATE privileges.

Why does my read-only role lose access when someone adds a table? GRANT SELECT ON ALL TABLES IN SCHEMA only covers tables that existed when you ran it. Use ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO ai_agent_ro, and make sure FOR ROLE names the role your migrations create tables as, or the rule never fires.

Is default_transaction_read_only enough? No. It is enforced by PostgreSQL per transaction, but it is a USERSET parameter, so a session can turn it off with a plain SET. It is a good default that stops accidents; it is not a privilege boundary. Grants are.

Does an MCP server's read-only mode prevent writes? Not reliably. It checks statement text before sending, and text checks miss writing CTEs (WITH ... AS (DELETE ...) SELECT ...), functions with side effects, and EXPLAIN ANALYZE. It also does not cover any other client using the same credential. Keep it on, but put grants underneath it.

How do I hide specific columns? Do not grant table-level SELECT on that table, a table-level grant supersedes column-level ones. REVOKE SELECT ON users, then GRANT SELECT (id, status) ON users. Or expose a view that omits the sensitive columns and grant on the view instead.

Should I just use pg_read_all_data? It works (PostgreSQL 14+) and it is convenient, but it grants read access to everything now and everything added later, and you cannot carve exceptions out of it with REVOKE. For an agent, write the explicit grants.

Related

• Is it safe to let an AI coding agent access my production PostgreSQL database?
• Common PostgreSQL mistakes AI coding agents make
• What pg_stat_statements tells you
• Product: DBGorilla docs