Read-only Postgres role for AI agents: step-by-step setup

What is the setup for a read-only Postgres role for AI agents?

Replace the database, table and column names with your own, and use a strong password from a secret manager.

-- 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;

-- 2. Connect and see the schema
GRANT CONNECT ON DATABASE mydb TO claix_agent;
GRANT USAGE ON SCHEMA public TO claix_agent;

-- 3. SELECT only on what the agent needs (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;

-- 4. Defaults for safety (the session can change them, see step 5)
ALTER ROLE claix_agent SET default_transaction_read_only = on;
ALTER ROLE claix_agent SET statement_timeout = '5s';
ALTER ROLE claix_agent SET lock_timeout = '2s';
ALTER ROLE claix_agent SET idle_in_transaction_session_timeout = '10s';

Why create a dedicated role for the agent?

Don't reuse your application user or the postgres superuser. A role made for the agent can be limited, monitored and revoked without touching anything else. NOINHERIT stops it from picking up privileges from other roles by accident, and NOBYPASSRLS makes sure row-level security still applies to it.

How do you grant connect and schema usage?

The role needs permission to connect to the database and to see objects inside the schema. Without USAGE on the schema, it can't query anything, even with SELECT on the table. If your tables live in a schema other than public, grant USAGE on that one instead.

How do you grant SELECT only on what the agent needs?

This is the most important step. Grant SELECT on specific tables, or better, on specific columns:

GRANT SELECT (id, status, total) ON public.orders TO claix_agent;

With column-level grants, a query that touches any other column fails with permission denied for table orders. That includes SELECT *, so the agent has to name its columns, which is what you want.

Two things to avoid:

• GRANT SELECT ON ALL TABLES. It also covers tables you didn't mean to expose and, depending on your setup, tables created later.
• The built-in pg_read_all_data role (PostgreSQL 14 and later). It grants read access to every table in the database. That's convenient for a backup tool and too broad for an agent.

When should you use views for sensitive data?

If a table mixes safe and sensitive data, expose a view with only the safe columns and grant SELECT on the view instead:

CREATE VIEW public.order_status AS
  SELECT id, status FROM public.orders;

GRANT SELECT ON public.order_status TO claix_agent;

Views are also easier for a model to use than several joined tables. By default a view runs with its owner's permissions, so the agent doesn't need access to the underlying table. If you want row-level security on the underlying table to apply to the agent, create the view with WITH (security_invoker = true) (PostgreSQL 15 and later).

Are role timeouts and read-only defaults enough?

The ALTER ROLE settings in the block above make every session start read-only with timeouts. They are defaults and not locks. In our tests, a session could run SET statement_timeout = 0 and remove the timeout, and a session can start a read-write transaction as its first statement. So treat them as guardrails:

• Set the same limits again from your own code at the start of each transaction (SET LOCAL statement_timeout = '5s').
• Add a timeout in your application as well.
• Remember that the real protection is what the role is granted. In our test, when the session switched back to read-write mode and tried to insert, Postgres still answered permission denied, because the role had no INSERT privilege.

Which functions can a read-only role still call?

By default, Postgres lets everyone execute functions. If you have a function marked SECURITY DEFINER that writes data, a read-only role can call it, and it will write with its owner's privileges. In our test, the agent role inserted a row this way after switching the session to read-write mode.

List those 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.do_write() FROM PUBLIC;

-- New functions in this schema won't be executable by everyone by default
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

How should you handle row-level security for an AI agent?

If your tables use row-level security, check three things: the agent role is not the table owner, it does not have BYPASSRLS, and there is a policy that lets it see the rows you want. With RLS enabled and no policy for the role, it sees zero rows (we confirmed this), which fails safe but looks like an empty table.

CREATE POLICY agent_read ON public.orders
  FOR SELECT TO claix_agent USING (true);

Use a narrower condition than true if the agent should only see some rows.

How do you test a read-only Postgres role like an attacker would?

Connect as claix_agent and try each of these. Every one should fail except the first.

Repeat the write tests after switching the session to read-write mode (SET default_transaction_read_only = off). They should still fail with permission denied.

How do you connect the agent safely?

• Require SSL (sslmode=require or stricter).
• Keep the password in a secret manager, never in the prompt, the repository or the agent's configuration.
• Rotate the password on a schedule and when someone leaves the team.
• Limit connections (CONNECTION LIMIT) and, if your host supports it, allow only your server's IP addresses.
• Use a connection pooler if the agent will run many short queries.

Does this work on Supabase?

Yes. Supabase is Postgres, so you can run the same SQL in the SQL editor and connect with the credentials and host from your project's connection settings. Two Supabase-specific checks: review the default privileges for its built-in roles (such as anon and authenticated) so you know what is exposed through its API, and confirm which connection method suits your agent in the current Supabase documentation, because connection options change.

What a read-only role does not protect you from

• Anything you granted. If the role can read a column, a prompt-injected agent can be tricked into reading it. Grant less.
• Expensive queries. A read-only query can still be slow or heavy. Use timeouts and row limits.
• Per-person access. The role sees everything it was granted, for every user. If each person should only see their own rows, you need row-level security or a layer in front of the database.
• Table names. In our test, the role could still list the names of tables it had no access to through pg_tables. The contents stayed protected.

Checklist: read-only Postgres role for AI agents

• A dedicated role with NOSUPERUSER and NOBYPASSRLS
• CONNECT and USAGE only
• SELECT on specific tables or columns, or on views
• No pg_read_all_data and no GRANT ON ALL TABLES
• Timeouts set in the role and again from your code
• Execute access removed from functions that write
• RLS reviewed
• SSL, a secret manager and rotation
• Write and timeout tests run and passing

Where Claix fits

Claix connects to your database with a role like the one in this guide and only reads the tables and columns you include in each route. You choose what each agent can reach, and every request is traced. A read-only role is still the foundation, so do this setup first, whether you use Claix Retrieve (https://www.claix.dev/) or build your own. Next: how to let an AI agent query Postgres safely (/blog/how-to-let-an-ai-agent-query-postgres-safely).