A read-only Postgres role for AI agents is the right first step. It is also not enough on its own.
Most guides stop at GRANT SELECT. We ran the usual setup on PostgreSQL 16, tried to get around it the way a manipulated agent or a careless query would, and found seven places where "read-only" doesn't mean what people assume. Each one comes with the SQL to reproduce it and the fix.
Quick answer
A read-only Postgres role for an AI agent should be a dedicated login role with no superuser or BYPASSRLS, SELECT granted only on the columns the agent needs, and timeouts set. But default_transaction_read_only and statement_timeout are defaults that a session can change, a SECURITY DEFINER function can still write, and anything you grant can still be read by a manipulated agent. The real barrier is what the role is allowed to do, so grant as little as possible and never give the agent a raw SQL session.
In this post
- How we tested
- The baseline setup
- Gap 1: the read-only setting is a default, not a lock
- Gap 2: your timeout can be switched off
- Gap 3: a SECURITY DEFINER function can write for you
- Gap 4: table names stay visible
- Gap 5: SELECT * fails, and the error blames the table
- Gap 6: row-level security with no policy returns nothing
- Gap 7: everything you grant can be read
- The hardened setup in one block
- FAQ How we tested Everything below ran on PostgreSQL 16.15 in a clean database, with each statement sent from a normal login role. The results are copied from the terminal. Nothing needs an extension, and you can paste each block into psql and compare. The sample schema has three tables: customers, orders and secrets. The agent should reach only some columns of the first two, and nothing in the third. The baseline setup This is the setup most guides describe, plus the settings people add to make it "read-only". CREATE TABLE public.customers (id serial PRIMARY KEY, name text, email text); CREATE TABLE public.orders ( id serial PRIMARY KEY, customer_id int REFERENCES customers(id), status text, total numeric, internal_notes text ); CREATE TABLE public.secrets (id serial PRIMARY KEY, token text);
CREATE ROLE claix_agent WITH LOGIN PASSWORD 'use-a-secret-manager'
NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOREPLICATION NOBYPASSRLS;
GRANT CONNECT ON DATABASE mydb TO claix_agent;
GRANT USAGE ON SCHEMA public TO claix_agent;
GRANT SELECT (id, name) ON public.customers TO claix_agent;
GRANT SELECT (id, customer_id, status, total) ON public.orders TO claix_agent;
ALTER ROLE claix_agent SET default_transaction_read_only = on;
ALTER ROLE claix_agent SET statement_timeout = '2s';
Two things to avoid at this stage. Don't use GRANT SELECT ON ALL TABLES, which also covers tables you didn't mean to expose. And don't use the built-in pg_read_all_data role for an agent: it grants read access to every table in the database, which is far more than an agent needs.
Gap 1: the read-only setting is a default, not a lock
The assumption: default_transaction_read_only = on means the role can't write.
What happens: it means new transactions start read-only. The session can change that. To see it, we made a second role that does have INSERT and the same read-only default:
INSERT INTO orders(customer_id, status, total) VALUES (1, 'x', 1);
ERROR: cannot execute INSERT in a read-only transaction
SET default_transaction_read_only = off;
INSERT INTO orders(customer_id, status, total) VALUES (1, 'x', 1);
-- succeeded
The same happened with BEGIN READ ONLY; followed by SET TRANSACTION READ WRITE; as the first statement: the write went through.
What stopped the write in the real agent role was something else. After SET default_transaction_read_only = off;, an INSERT from claix_agent failed with:
ERROR: permission denied for table orders
The fix: treat the setting as a safety net and the privileges as the barrier. Grant SELECT only. If you can, connect the agent to a read replica, because a standby server rejects writes whatever the session sets (we didn't test this one).
Gap 2: your timeout can be switched off
The assumption: ALTER ROLE ... SET statement_timeout = '2s' caps every query.
What happens: it sets the starting value. The session can raise it or remove it:
SHOW statement_timeout;
2s
SELECT pg_sleep(3);
ERROR: canceling statement due to statement timeout
SET statement_timeout = 0;
SELECT pg_sleep(3);
-- ran to completion
The fix: the timeout protects you only if the agent can't send a SET. So never give the agent a raw SQL session. Your code should run a single validated SELECT, set the limits itself for each transaction (SET LOCAL statement_timeout = '5s') and add its own timeout around the call. A pooler or proxy that cancels long queries is a second layer, and you should check its documentation for the exact setting.
Gap 3: a SECURITY DEFINER function can write for you
The assumption: a role with only SELECT can't change data.
What happens: functions declared SECURITY DEFINER run with the privileges of their owner, and by default Postgres lets everyone execute functions. We created one that inserts into secrets, a table claix_agent can't touch:
CREATE FUNCTION public.archive_orders() RETURNS void
LANGUAGE sql SECURITY DEFINER AS $$
INSERT INTO public.secrets(token) VALUES ('written-by-function')
$$;
With the read-only default on, the call failed. After the session switched it off (gap 1), it worked, and secrets went from one row to two.
The fix: find these functions and remove public execute access from the ones that write:
SELECT p.oid::regprocedure
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public' AND p.prosecdef;
REVOKE EXECUTE ON FUNCTION public.archive_orders() FROM PUBLIC;
After the revoke, the same call returned permission denied for function archive_orders. Give EXECUTE back to the roles that need it. You can also run ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; so that future functions aren't public by default, but that affects every function created afterwards by the same role, so check your application first.
Gap 4: table names stay visible
The assumption: if the role can't read a table, it can't see it.
What happens: it can't read the contents, but the catalog still lists the name:
SELECT tablename FROM pg_tables WHERE schemaname = 'public';
customers
orders
secrets
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
customers
orders
SELECT * FROM secrets;
ERROR: permission denied for table secrets
information_schema shows only what the role has privileges on. pg_catalog shows everything.
The fix: don't put anything sensitive in a table or column name. And when you build the description of your schema for an agent, read it with the agent's own role through information_schema, so the agent is told about the tables it can use and nothing else.
Gap 5: SELECT * fails, and the error blames the table
The assumption: with a column-level grant, SELECT * returns the allowed columns.
What happens: it fails, and the message names the table, not the column:
SELECT * FROM orders;
ERROR: permission denied for table orders
SELECT id, status, total FROM orders ORDER BY id LIMIT 2;
1|shipped|10
2|pending|20
SELECT internal_notes FROM orders;
ERROR: permission denied for table orders
That is useful: an agent can't get a column you didn't grant by accident. It is also confusing when you debug, because the table looks denied even though only one column is.
The fix: make your agent list its columns, and reject SELECT * in your validator so the failure is a clear message from your code and not a database error. A view with only the safe columns is a simple alternative:
CREATE VIEW public.order_status WITH (security_invoker = true) AS
SELECT id, status FROM public.orders;
GRANT SELECT ON public.order_status TO claix_agent;
The security_invoker option (PostgreSQL 15 and later) makes the view use the privileges and row-level security of the caller, not the view's owner.
Gap 6: row-level security with no policy returns nothing
The assumption: enabling row-level security on a table limits what the agent sees.
What happens: if the role is not the owner and no policy applies to it, it sees zero rows. That fails safe, and it looks like an empty table:
ALTER TABLE public.customers ENABLE ROW LEVEL SECURITY;
SELECT count(*) FROM customers; -- as claix_agent
0
CREATE POLICY agent_read ON public.customers FOR SELECT TO claix_agent USING (true);
SELECT count(*) FROM customers;
2
The fix: if you use row-level security, write a policy for the agent role with the narrowest condition that works, and check that the role is not the table owner and does not have BYPASSRLS. If an agent suddenly "finds nothing", look at the policies first.
Gap 7: everything you grant can be read
The assumption: once the role is read-only, the data is safe.
What happens: a read-only role protects what it can't reach. It doesn't protect what you gave it. Instructions hidden in a user message, a document or a database field can change what an agent asks for, and the role will answer any query for data it was granted. Supabase's own documentation names prompt injection as the main risk of connecting an agent to your data, and says its MCP server is designed for development and testing.
A role also can't tell a useful query from a pointless or expensive one, or a correct query from one that is valid and wrong, and it doesn't separate one person's rows from another's.
The fix: grant less, and add layers outside the database:
• Validate every query with a real SQL parser before it runs: one SELECT, approved tables, an allowlist of functions.
• Add a row limit, a timeout and a cost check.
• Treat query results as untrusted data.
• Log every request: the question, the query and the result.
• Keep per-user rules in row-level security or in your application.
The hardened setup in one block
This is the baseline plus the fixes that belong in the database. We ran the whole block on a clean cluster and checked that granted columns work and others don't.
-- 1. A dedicated role, not your app user and not postgres
CREATE ROLE claix_agent WITH LOGIN PASSWORD 'use-a-secret-manager'
NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOREPLICATION NOBYPASSRLS
CONNECTION LIMIT 10;
GRANT CONNECT ON DATABASE mydb TO claix_agent;
GRANT USAGE ON SCHEMA public TO claix_agent;
-- 2. Columns, not whole tables
GRANT SELECT (id, name) ON public.customers TO claix_agent;
GRANT SELECT (id, customer_id, status, total) ON public.orders TO claix_agent;
-- 3. Guardrails (defaults the session can change)
ALTER ROLE claix_agent SET default_transaction_read_only = on;
ALTER ROLE claix_agent SET statement_timeout = '5s';
ALTER ROLE claix_agent SET idle_in_transaction_session_timeout = '10s';
-- 4. Find functions that run with their owner's privileges
SELECT p.oid::regprocedure AS security_definer_function
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public' AND p.prosecdef;
-- 5. Remove public execute access from any that write
REVOKE EXECUTE ON FUNCTION public.archive_orders() FROM PUBLIC;
-- GRANT EXECUTE ON FUNCTION public.archive_orders() TO app_user;
Then add what a database role can't: validation, limits, logging, and a controlled way for the agent to ask.
Where Claix fits
Claix is a managed service between your agent and your database. You connect Postgres or Supabase with a read-only role like the one above, choose the tables and columns each agent can use, and write a short guide with your rules. Your agent sends questions to its own endpoint and gets typed answers back, so it never holds a database connection or sends raw SQL. Every request is traced.
It doesn't replace the role. Claix connects with a role like this one, and what that role can read is still the ceiling. You can try it in the Sandbox and read the docs at claix.dev.
FAQ
Is a read-only user enough to secure a database for an AI agent? No. It is the first layer. A session can change settings such as default_transaction_read_only and statement_timeout, a SECURITY DEFINER function can still write, and a manipulated agent can read anything you granted. Add query validation, limits and logging.
How do I create a read-only user in Postgres? Create a login role without superuser or BYPASSRLS, grant CONNECT on the database and USAGE on the schema, and grant SELECT only on the tables or columns it needs. The block in this post does that.
Can a read-only Postgres user still modify data? It can if it can call a SECURITY DEFINER function that writes, or if the session lifts the read-only default and the role has write privileges. With only SELECT privileges and no public execute access to writing functions, it can't.
How do I limit an AI agent to certain columns in Postgres? Use column-level grants, such as GRANT SELECT (id, status) ON orders TO claix_agent, or expose a view with only the safe columns.
Should an AI agent connect to a read replica? It is a good extra layer, because a standby server rejects writes whatever the session sets, and it keeps heavy queries away from your main database. It doesn't replace limiting what the role can read.
Does this work on Supabase? Yes. Supabase is Postgres, so the same SQL works in its SQL editor. Supabase's MCP server has a read-only option that runs queries as a read-only Postgres user, and its documentation says the server is designed for development and testing.
How do I stop an AI agent's long-running queries? Set a statement_timeout for the role, set it again in your code for every transaction, and make sure the agent can't send SET statements. A pooler or proxy that cancels long queries adds another layer.
Sources and further reading
• PostgreSQL documentation, GRANT: https://www.postgresql.org/docs/current/sql-grant.html
• PostgreSQL documentation, CREATE FUNCTION and SECURITY DEFINER: https://www.postgresql.org/docs/current/sql-createfunction.html
• PostgreSQL documentation, client connection defaults: https://www.postgresql.org/docs/current/runtime-config-client.html
• PostgreSQL documentation, row security policies: https://www.postgresql.org/docs/current/ddl-rowsecurity.html
• PostgreSQL documentation, predefined roles: https://www.postgresql.org/docs/current/predefined-roles.html
• Supabase documentation, MCP server: https://supabase.com/docs/guides/getting-started/mcp
SEO notes (not part of the post)
Why this angle. I searched the main phrase, how to connect an AI agent to a database safely, and the results are crowded: a dev.to post from September 2026 on giving an agent safe, read-only access, a hoop.dev guide, an Airbyte guide on connection patterns, Microsoft's SQL MCP Server documentation and several open-source projects. A generic how-to would compete with all of them. This post targets a narrower phrase with the same intent, read-only Postgres role for AI agents, and brings something those results don't: tested results, with the SQL to reproduce them. That is your best chance to rank and to be linked. I can't give you search volumes, so check the phrase in a keyword tool before you commit.
Keyword placement
Element
What it contains
Title
Read-Only Postgres Role for AI Agents (primary keyword first)
First sentence
The primary keyword, with a clear claim
Description (front matter)
Primary keyword and the numbers 7 and PostgreSQL 16
Quick answer
A direct answer in about 90 words, for featured snippets and answer engines
H2 headings
Each gap, plus FAQ questions in the words people use
Secondary keywords
AI agent database access, Postgres read-only user, default_transaction_read_only, statement_timeout, SECURITY DEFINER, row-level security, Supabase, MCP, prompt injection
Tags
postgres, ai, security, database (dev.to allows four
Top comments (2)
This is the distinction teams often miss: a read-only transaction default is not a capability boundary. The SECURITY DEFINER example is especially important because it shows how a role can stay “read-only” on paper while an exposed function writes on its behalf. I would put the final point—no raw SQL session for an agent—at the top of the hardened pattern: narrow task-specific queries are easier to authorize, log, and revoke.
Gap 1 is the one we'd stress most, since a session can simply SET default_transaction_read_only back to off. For an agent that really must never write, we'd also point it at a streaming replica, because a hot standby rejects writes no matter what the session sets. On the primary, a column-level GRANT plus a role with no ownership of anything covers the rest. Did you test whether pg_stat_activity or the catalogs leak anything sensitive, such as query text from other roles?