How to check what an AI agent can do in your PostgreSQL database

Why audit the role

An AI agent connected to your database can run whatever SQL the model decides to write. The prompt, the agent's rules and an MCP server's “read-only” switch are all requests, not limits. In 2026 there were public cases of both failures: a coding agent that deleted a production database despite rules telling it not to, and an MCP server whose restricted mode, which filtered SQL text, was bypassed by calling a file-reading function in a FROM clause.

What PostgreSQL enforces is the privileges of the role the agent logs in as. So the question is not “what did we tell the agent?” but “what can this role do?” The answer is often more than you granted directly, because privileges also come from inherited roles, from owning objects and from PUBLIC.

Every query below takes the role name in place of ai_agent. Run them as an administrator on the database the agent connects to; they only read the catalog. They were tested on PostgreSQL 18 and work on 12 and later unless a step says otherwise.

1. Role attributes

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolbypassrls,
       rolreplication, rolconnlimit, rolvaliduntil, rolconfig
FROM pg_roles
WHERE rolname = 'ai_agent';

• rolsuper = true: stop here. A superuser bypasses every check in this guide, can read server files and run programs as the database's operating system user.
• rolcreaterole, rolcreatedb, rolreplication and rolbypassrls should all be false for an agent. rolbypassrls ignores row-level security policies.
• rolconnlimit = -1 means unlimited connections. rolconfig shows per-role settings such as statement_timeout; empty means none.

2. Roles it inherits

A role gets the privileges of every role it is a member of, directly or through a chain. This is the most common way an agent ends up with far more than intended, for example when it was added to the application's own role “to make it work”.

SELECT r.rolname                AS member_of,
       r.rolname LIKE 'pg\_%'  AS predefined
FROM pg_roles AS r
WHERE pg_has_role('ai_agent', r.oid, 'MEMBER')
  AND r.rolname <> 'ai_agent'
ORDER BY predefined DESC, r.rolname;

Predefined roles that an agent should not have:

• pg_read_server_files, pg_write_server_files, pg_execute_server_program: read or write files and run programs on the server through COPY. They bypass in-database permission checks and can lead to superuser access.
• pg_write_all_data (PostgreSQL 14+): INSERT, UPDATE and DELETE on every table.
• pg_signal_backend: cancel queries and end other users' sessions.
• pg_maintain (PostgreSQL 17+): VACUUM, REINDEX, CLUSTER and LOCK TABLE on every table.

pg_read_all_data (PostgreSQL 14+) is read-only but reads every table, including ones you may want to keep from an AI provider.

3. What it owns

An owner can DROP and ALTER its objects whatever has been granted or revoked, and by default holds every other privilege on them too. Members of the owning role can do the same. If the agent shares a role with the application that ran the migrations, it can drop the application's tables.

SELECT n.nspname                   AS schema,
       c.relname                   AS object,
       c.relkind,
       pg_get_userbyid(c.relowner) AS owner
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE pg_has_role('ai_agent', c.relowner, 'MEMBER')
  AND c.relkind IN ('r', 'p', 'v', 'm', 'S', 'f')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

For an agent this list should be empty. relkind is r for a table, p partitioned table, v view, m materialized view, S sequence and f foreign table.

4. Where it can write

Grants listed in information_schema.role_table_grants show only direct grants. has_table_privilege answers the real question, including privileges that come through roles, ownership and PUBLIC.

SELECT n.nspname AS schema,
       c.relname AS table_name,
       has_table_privilege('ai_agent', c.oid, 'INSERT')   AS can_insert,
       has_table_privilege('ai_agent', c.oid, 'UPDATE')   AS can_update,
       has_table_privilege('ai_agent', c.oid, 'DELETE')   AS can_delete,
       has_table_privilege('ai_agent', c.oid, 'TRUNCATE') AS can_truncate
FROM pg_class AS c
JOIN pg_namespace AS 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', c.oid, 'INSERT')
    OR has_table_privilege('ai_agent', c.oid, 'UPDATE')
    OR has_table_privilege('ai_agent', c.oid, 'DELETE')
    OR has_table_privilege('ai_agent', c.oid, 'TRUNCATE'))
ORDER BY 1, 2;

For a read-only agent this should return no rows. If the agent is meant to write somewhere, every row here should be a table you chose on purpose.

5. Where it can create objects

SELECT n.nspname AS schema_with_create
FROM pg_namespace AS n
WHERE has_schema_privilege('ai_agent', n.oid, 'CREATE')
  AND n.nspname NOT LIKE 'pg\_%'
  AND n.nspname <> 'information_schema';

SELECT has_database_privilege('ai_agent', current_database(), 'CREATE') AS can_create_schema,
       has_database_privilege('ai_agent', current_database(), 'TEMP')   AS can_create_temp;

CREATE on a schema lets the role add tables, views and functions there. Before PostgreSQL 15, every role had CREATE on the public schema by default, so databases created on older versions and upgraded often still have it. TEMP is granted to PUBLIC by default and is usually harmless.

6. Risky functions

Functions are executable by PUBLIC unless revoked. Two kinds matter here. First, the server file functions. They are restricted by default, and membership in pg_read_server_files does not grant them; only an explicit GRANT EXECUTE does.

SELECT p.oid::regprocedure AS function_name
FROM pg_proc AS p
WHERE p.proname IN ('pg_read_file', 'pg_read_binary_file', 'pg_ls_dir',
                    'pg_stat_file', 'lo_import', 'lo_export')
  AND has_function_privilege('ai_agent', p.oid, 'EXECUTE')
ORDER BY 1;

Second, SECURITY DEFINER functions. They run with their owner's privileges, so a function owned by the application role that deletes rows lets the agent delete rows without any table privilege.

SELECT p.oid::regprocedure         AS function_name,
       pg_get_userbyid(p.proowner) AS runs_as
FROM pg_proc AS p
JOIN pg_namespace AS n ON n.oid = p.pronamespace
WHERE p.prosecdef
  AND has_function_privilege('ai_agent', p.oid, 'EXECUTE')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1;

7. Prove it with a test

Catalog queries tell you what should happen. A test tells you what does. Switch to the agent's role inside a transaction, try the statements you are worried about, and roll back.

BEGIN;
SET LOCAL ROLE ai_agent;
SELECT current_user;           -- should print ai_agent

DROP TABLE app.orders;         -- expect: must be owner of table orders
ROLLBACK;

An error such as must be owner of table or permission denied for table is the result you want. After the first error the transaction is aborted, so test one statement per transaction.

8. Read-only mode is not a boundary

Setting default_transaction_read_only for the role, or connecting with a “read-only” flag, is useful as a guard against mistakes. It is not a security control, because the session can turn it off:

ALTER ROLE ai_agent SET default_transaction_read_only = on;

-- later, inside the agent's own session:
BEGIN READ WRITE;
SHOW transaction_read_only;    -- off

The same applies to SQL filters in front of the database: they inspect text, and SQL has many ways to say the same thing. Treat both as a first line, and make privileges the line that holds.

9. Close the gaps

Undo what the audit found. Adjust names to your results.

REVOKE app_owner FROM ai_agent;
REVOKE pg_write_server_files FROM ai_agent;
REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM ai_agent;
REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA app FROM ai_agent;
REVOKE EXECUTE ON FUNCTION app.reset_orders() FROM PUBLIC, ai_agent;

Then limit what one runaway session can cost, and record what it ran:

ALTER ROLE ai_agent CONNECTION LIMIT 3;
ALTER ROLE ai_agent SET statement_timeout = '30s';
ALTER ROLE ai_agent SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE ai_agent SET log_statement = 'all';

Role settings apply to new sessions. log_statement can only be set by a superuser (or, on PostgreSQL 15+, a role granted SET on it), so the agent cannot turn it off. Run steps 1 to 7 again until each returns what you expect, and repeat the audit after migrations and role changes.

Beyond the database role

The database role only covers SQL. An agent with your cloud provider's API token, the database host's credentials or a shell can delete a volume, a snapshot or the backups without running a single statement. Give the agent its own credentials for each system, scoped to what it needs, and keep backups somewhere it cannot reach.

The same rules in Quorum

Quorum puts your connections behind MCP tools for Claude, OpenAI, Cursor or your own agent, and keeps writes behind a person.

• MCP review gate: an agent's write waits for a person; no response means no write.
• Budgets for tools, time, rows and cost on every agent connection.
• Schema and safe summaries to AI by default, with classified columns masked in MCP replies and AI prompts, and failing closed.
• Safe changes: preview before change, single-use confirmation and a WHERE-guard, with outcomes reported as confirmed, failed or unknown.