# 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.

{/* sql-check: isolated */}
```sql
-- 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:

```sql
-- 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:

```sql
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](https://www.dbgorilla.com/learn/postgres/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:

```sql
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:

```sql
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:

```sql
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`:

```sql
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:

```sql
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:

{/* sql-check: expect-error */}
```sql
-- 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 →](https://app.dbgorilla.com/signup)

## 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?](https://www.dbgorilla.com/learn/postgres/is-it-safe-to-let-an-ai-agent-access-my-production-database/)
- [Common PostgreSQL mistakes AI coding agents make](https://www.dbgorilla.com/learn/postgres/common-postgresql-mistakes-ai-coding-agents-make/)
- [What pg_stat_statements tells you](https://www.dbgorilla.com/learn/postgres/what-pg-stat-statements-tells-you/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)