Postgres default privileges and an AI agent's role: new tables, granted or not

The short answer

GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent covers only tables that exist when you run it. ALTER DEFAULT PRIVILEGES sets privileges for objects created in the future, and doesn’t touch existing ones.¹ It applies only to objects created by the role it names (or the current role), so tables created by another role don’t get it.¹

For an AI agent, the default that saves a migration step is also the one that hands it every future table, including the sensitive ones. We ran all three cases on PostgreSQL 18.6. For agents, prefer explicit grants on named tables, and accept that new tables need a deliberate grant.

Case 1: the grant that missed a new table

app_owner owns the app’s tables; agent_ro2 is the agent’s login role with USAGE on the schema.

Code A database command in SQL. The text around it says what it does.

set role app_owner;  create table invoices(id int);  reset role;
grant select on all tables in schema public to agent_ro2;
set role app_owner;  create table payments(id int);  reset role;

set role agent_ro2;
select 'invoices' t, count(*) from invoices;
select 'payments' t, count(*) from payments;

Code What the screen or file shows, copied as is.

 invoices |     0
ERROR:  permission denied for table payments

ON ALL TABLES meant “all tables now.” payments, created a moment later, isn’t included. In practice, this is the agent that worked yesterday and fails today after a migration, and the person who fixes it under time pressure is the one most likely to over-grant.

Case 2: the default that granted too much

The usual fix:

Code A database command in SQL. The text around it says what it does.

alter default privileges for role app_owner in schema public
  grant select on tables to agent_ro2;

\ddp then showed:

Code What the screen or file shows, copied as is.

   Owner   | Schema | Type  |   Access privileges
-----------+--------+-------+-----------------------
 app_owner | public | table | agent_ro2=r/app_owner

A new table, refunds, was readable straight away. So was the next one:

Code A database command in SQL. The text around it says what it does.

set role app_owner;
create table api_tokens(token text);
insert into api_tokens values ('tok_live_example');
reset role;

set role agent_ro2;
select token from api_tokens;

Code What the screen or file shows, copied as is.

      token
------------------
 tok_live_example

Nobody decided the agent should read api_tokens. The default decided, a month earlier, for every table app_owner would ever create. That’s the failure mode worth designing against: sensitive tables are usually added later than the grant.

Case 3: a table created by another role

Default privileges follow the role that creates the object.¹ We created chargebacks as the superuser instead of app_owner:

Code What the screen or file shows, copied as is.

ERROR:  permission denied for table chargebacks

So defaults are both too broad and not broad enough: they cover everything one role creates, and nothing another role creates. If migrations sometimes run as postgres and sometimes as app_owner, the agent’s access depends on who ran which migration. The docs add that at creation time only the current role’s default privileges apply, not those of roles it’s a member of.¹

Which to use for an agent

For agents we’d use explicit grants, written into each migration that creates a table the agent needs, reviewed with the migration. PostgreSQL grants no table privileges to PUBLIC by default², so a new table stays invisible to the agent until someone decides otherwise. That’s the safe failure.

If you want defaults, put the tables the agent may read in their own schema (reporting, say), set defaults only IN SCHEMA reporting, and keep sensitive tables in another. Per-schema defaults add to global ones, and you can’t revoke per schema what’s granted globally, so keep global defaults empty.¹

Column grants narrow things further: PostgreSQL can grant SELECT on single columns³, which our column grants post tests.

Audit what the agent can read today

Run these as an admin. Existing default privileges:

Code A database command in SQL. The text around it says what it does.

\ddp

Every table the agent’s role can read right now (substitute your role name):

Code A database command in SQL. The text around it says what it does.

select table_schema, table_name
from information_schema.role_table_grants
where grantee = 'agent_ro2' and privilege_type = 'SELECT'
order by 1, 2;

On our test database it returned:

Code What the screen or file shows, copied as is.

 table_schema | table_name
--------------+------------
 public       | api_tokens
 public       | invoices
 public       | refunds

api_tokens is on the list without anyone having granted it by name. If your list contains anything with “token,” “secret,” “key,” “password,” “session” or personal data in its name, the agent can read it. Also check the role’s group memberships; a membership in pg_read_all_data reads everything regardless of grants.² Our post on what rds_superuser and neon_superuser carry has a query for memberships.

A checklist

• The agent’s role has grants on named tables, not schema-wide defaults.
• Each migration that creates an agent-visible table includes its grant.
• If defaults are used, they’re per schema, on a schema with nothing sensitive, and global defaults are empty.
• Migrations always run as the same owning role.
• \ddp reviewed; no default grants to the agent’s role on general schemas.
• The readable-tables query reviewed after each migration.
• No membership in pg_read_all_data or admin groups.

For the whole setup in one place, see safe Postgres access for agents and read-only database access. To grade the role your agent connects with, run npx deedglass scan --postgres-dsn-env DATABASE_URL, which lists the tables that role can write (how the grade works), or paste an MCP config into the check page; to try tool-call rules in your browser, open the playground.

Written with AI assistance and checked against our claims register.