How do I set up a read-only PostgreSQL role for an AI agent?
Quick answer: Create a dedicated
LOGINrole, thenGRANT CONNECTon the database,GRANT USAGEon each schema, andGRANT SELECTon the tables it needs, plusALTER DEFAULT PRIVILEGESso future tables are covered. Addstatement_timeoutandidle_in_transaction_session_timeouton the role. PostgreSQL enforces grants. It does not enforce session settings, MCP query parsing, or prompt instructions.
The complete setup
Section titled “The complete setup”Run this as a superuser or the database owner. Step 5 also requires that you be app_owner or a member of it. ALTER DEFAULT PRIVILEGES FOR ROLE can only be run by that role, a member of it, or a superuser. Substitute your own database,
schema, and owning role.
{/* sql-check: isolated */}
-- 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 to 5 for every schema the agent needs. Anything you do not name stays invisible.
What each step is actually doing
Section titled “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. 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. Without USAGE, table grants give no access.
SELECT ON ALL TABLES IN SCHEMA is easy to misread. It means “on all tables
that exist at the moment I press enter.” It applies to the current catalog, not as
a standing rule. If you create a table tomorrow, the role cannot read it.
ALTER DEFAULT PRIVILEGES is the standing rule, with an important limitation.
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. You will
then need to run GRANT SELECT ON ALL TABLES by hand after every deploy.
Always pass FOR ROLE explicitly and use the role that owns your migrations.
There is a legitimate argument for not setting default privileges: a newly added table, which might be the one holding your new sensitive field, remains invisible to the agent until someone grants access deliberately. That adds friction but improves security. Pick one on purpose rather than by accident.
The pg_read_all_data shortcut, and why to think twice
Section titled “The pg_read_all_data shortcut, and why to think twice”PostgreSQL 14 added a predefined role that covers all of steps 3 to 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 convenient and fine for a
throwaway analysis role.
For an agent, it is the wrong default. It grants access to every table that exists
and every table anyone adds later, including 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 still apply because the role is not
BYPASSRLS, but that is a narrow consolation if you are not already using RLS.
Use explicit grants. They take twenty extra seconds and are auditable.
Where is “read-only” actually enforced?
Section titled “Where is “read-only” actually enforced?”There are five different things people call “read-only,” and they differ widely. 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
Section titled “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
Section titled “2. Statement parsing in an MCP server or client is best-effort”Many database MCP servers have a read-only mode that inspects SQL before sending it. Turn it on; it catches honest mistakes cheaply. Do not treat it as a boundary:
- CTEs write.
WITH d AS (DELETE FROM sessions RETURNING *) SELECT * FROM d;starts withWITHand is aSELECTstatement. It deletes rows. - Functions have side effects.
SELECT my_function(1);is aSELECT. What the function does is up to the function. EXPLAIN ANALYZEexecutes. On aSELECTyou pay the full cost; on anINSERT/UPDATE/DELETEthe write happens. PlainEXPLAINdoes 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
Section titled “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 roleSo it is a good default that catches accidents, but it is ineffective against
deliberate changes. The same is true of statement_timeout and
idle_in_transaction_session_timeout: useful, but defaults rather than ceilings.
4. Grants are the real boundary
Section titled “4. Grants are the real boundary”If the role has no INSERT privilege on a table, no statement it writes itself,
whether nested in a CTE or typed directly, lets it insert. No session setting
raises its own privileges. This is the layer to build on. The exception is code
someone else wrote and allowed it to 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 DEFINERfunction 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. CheckEXECUTEprivileges as carefully asSELECTones. - Views behave the same way by default: a view runs with the privileges of its
owner, not its caller, so granting
SELECTon a view grants effective access to the base tables it reads. That is useful because it is how masking views work, and dangerous because it is how a permissive view leaks a table you carefully revoked. PostgreSQL 15 added asecurity_invokeroption on views to change this, if you want caller-privilege behaviour instead.
5. A replica is the strongest and simplest option
Section titled “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 no session setting 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, which does not matter for schema, plans, index definitions and pg_stat_statements, and those are 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
Section titled “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.usersBecause 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. 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?
Section titled “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=matviewFROM pg_class cJOIN pg_namespace n ON n.oid = c.relnamespaceWHERE 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 delFROM pg_class cJOIN pg_namespace n ON n.oid = c.relnamespaceWHERE 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 because it resolves
inheritance and PUBLIC grants. Reading information_schema.table_privileges
instead shows direct grants and misses those that come through inheritance.
Finally, sanity-check as the role itself:
{/* sql-check: expect-error 42501 at 1,2 as ai_agent_ro */}
-- connected as ai_agent_roCREATE TABLE should_fail (id int); -- expect: permission deniedINSERT INTO users (id) VALUES (1); -- expect: permission deniedSELECT current_setting('transaction_read_only');Why do AI agents make this configuration harder than it used to be?
Section titled “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. This is why the boundary has to be in PostgreSQL rather than in the client that is doing the reasoning.
Neither changes the mechanics. The GRANT statements above are the same ones you
would write for any least-privilege reader. The difference is that you have to
actually mean them.
How do I create a read-only user in PostgreSQL?
Create a login role, then grant it CONNECT on the database, USAGE on each schema it needs, and SELECT on the tables in those schemas. Add ALTER DEFAULT PRIVILEGES so tables created later by the owning role are also readable. The role should own nothing and have no CREATE privileges.
Why does my new read-only role lose access when I add a table?
GRANT SELECT ON ALL TABLES IN SCHEMA is a one-time operation over the tables that exist at that moment; it does not apply to tables created afterwards. Use ALTER DEFAULT PRIVILEGES FOR ROLE owner IN SCHEMA public GRANT SELECT ON TABLES TO the read-only role. Note that default privileges are keyed to the role that creates the object, so the FOR ROLE clause must name the role your migrations run as.
Is default_transaction_read_only enough to make a role read-only?
No. default_transaction_read_only is a USERSET parameter, so a connected session can turn it off with a plain SET statement. Setting it with ALTER ROLE gives you a sensible default and catches accidents, but it is not a privilege boundary. Only the grants themselves, or connecting to a hot standby replica, are enforced in a way the session cannot override.
Does read-only mode in an MCP server actually prevent writes?
Not reliably. It inspects statement text before sending it, which is a useful guardrail but not a security boundary: a CTE can contain INSERT, UPDATE or DELETE inside a statement that begins with WITH, functions called from a SELECT can have side effects, EXPLAIN ANALYZE executes, and any other client using the same credential bypasses the check entirely. Put role grants underneath it.
How do I hide sensitive columns from a read-only role?
Do not grant table-level SELECT on that table. Grant column-level SELECT on only the columns you want exposed, or expose a view that omits or masks the sensitive columns and grant SELECT on the view instead of the base table. If table-level SELECT was already granted you must REVOKE it first, because a table-level grant supersedes column-level restrictions.
Should I just use pg_read_all_data?
It works on PostgreSQL 14 and later 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.