How to audit which SQL an AI agent ran

Quick answer: Give the agent a login role that nothing else uses, and make it log in as that role rather than reaching it with SET ROLE. The other tools use that distinction because pg_stat_activity, pg_stat_statements and the server log can all filter by it. Then turn on pgaudit for individual statements and keep pg_stat_statements for what they cost.

What is the problem?

The question “what did the agent run?” is usually asked after something has happened, and by then the answer is fixed. Either the database recorded enough to separate the agent from everything else, or it did not.

What decides it is whether the agent had its own authenticated identity, not the logging configuration. An agent sharing the application’s connection string is the same database user as the application, so the one field the server vouches for cannot tell them apart.

Some evidence is left when that happens. Sessions still differ by backend PID, client address and port, and application_name. Those separate two well-behaved tools perfectly well. What they cannot do is prove anything about a badly behaved one, because every one of them is chosen by the client. No PostgreSQL setting turned on afterwards creates an authenticated distinction that was never authenticated.

Step one: a role of its own

CREATE ROLE ai_agent LOGIN PASSWORD 'use-a-generated-secret'
    NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS
    CONNECTION LIMIT 5;

GRANT CONNECT ON DATABASE app TO ai_agent;
GRANT USAGE ON SCHEMA public TO ai_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_agent;

Everything below filters on that role. Without it, none of it means anything.

The agent must log in as that role. A shared login that reaches ai_agent with SET ROLE looks equivalent and is not, because the per-role settings the rest of this page depends on are applied at login. Measured on PostgreSQL 17.11: after ALTER ROLE ai_agent SET log_statement = 'all', a session that logged in as ai_agent reported log_statement as all, and a superuser session that ran SET ROLE ai_agent reported none. The statements would not have been logged.

That GRANT covers the tables that exist right now. Measured on the same server: a table created after the GRANT ON ALL TABLES line gave ERROR: permission denied for table later to the agent. Tables created later need a default:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
    GRANT SELECT ON TABLES TO ai_agent;

Repeat it for every role that owns objects, because the default is attached to the creating role rather than to the schema.

NOBYPASSRLS does less than it looks like it does. It removes the privilege to ignore row-level security, and row-level security is off until somebody enables it on the table. It also does not apply to a table the role owns. Measured: ai_agent with rolbypassrls false read all 2 rows of an RLS-enabled table carrying a policy that should have shown it 1, because it owned the table. Owners need FORCE ROW LEVEL SECURITY, and an agent role should not own application tables in the first place.

Add application_name as a second axis, so two tools sharing a role are still separable:

SET application_name = 'claude-code';

It is set by the client, so treat it as correlation metadata rather than as evidence. It is worth carrying a per-run identifier in it, so a log line can be traced back to one agent invocation rather than to the agent in general.

What each tool gives you

pg_stat_activity: what is running now

SELECT pid,
       usename,
       application_name,
       state,
       now() - query_start AS running_for,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE usename = 'ai_agent'
ORDER BY query_start;

This view keeps one query per backend, and that query is not always a running one. From the cumulative statistics documentation on the query column:

Text of this backend’s most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that was executed. By default the query text is truncated at 1024 bytes; this value can be changed via the parameter track_activity_query_size.

Measured on PostgreSQL 17.11: a session that ran one query and then sat idle was still showing that query, with state of idle, four seconds later. So a query that has finished is not necessarily gone.

The next query on that backend replaces it, and disconnecting the backend removes it. In the same test the row vanished the moment the client exited. The view holds one query per connection, keeps no history and truncates at 1024 bytes. That makes it useful for catching something in the act or for seeing what a stuck session did last, and useless as an audit trail.

pg_stat_statements: what things cost

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       left(query, 80) AS query
FROM pg_stat_statements
WHERE userid = 'ai_agent'::regrole
ORDER BY total_exec_time DESC
LIMIT 20;

The view has one row per combination of database, user, query identifier and top-level status, with literal values normally replaced by parameter symbols. Measured on 17.11, two SELECTs differing only in a constant came back as one row, select count(*) from early where id = $1, with calls of 2.

“Normally” is the documentation’s own hedge, and it matters for anyone reaching for this as evidence. From F.30. pg_stat_statements:

Queries on which normalization can be applied may be observed with constant values in pg_stat_statements, especially when there is a high rate of entry deallocations.

The same page notes the reverse: two queries with identical text can land in separate entries when they mean different things, for instance under a different search_path.

It answers “what is this agent costing me”. It is weaker than it looks at “is it reading tables it should not”, because the text of a statement is not an inventory of what the statement touched: a view, a function or dynamic SQL reaches tables the query text never names. That question belongs to pgaudit below. It does not answer “what did it run at 14:32” at all, because that is not what it stores.

It is not a complete history either. Counters run from the last reset, and entries are evicted once more distinct statements than pg_stat_statements.max have been seen, which can happen when an agent explores a schema. Both facts are readable:

SELECT dealloc, stats_reset FROM pg_stat_statements_info;

A non-zero dealloc means the view has already forgotten things.

It needs the extension, and the extension needs the library loaded before the server starts, which means a restart:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Query identifier calculation also has to be on, and compute_query_id defaults to auto, which means on whenever the module is loaded. Measured on a 17.11 container with nothing set but shared_preload_libraries: compute_query_id read auto and the view recorded normally. If somebody has set it to off, the module is inert.

pgaudit: individual statements, with their objects

This is PostgreSQL’s audit extension, and the caveats below limit how much its output proves. Like pg_stat_statements, pgaudit has to be in shared_preload_libraries before the server starts, and then CREATE EXTENSION pgaudit in the database. Only a superuser can change its settings.

It has two separate mechanisms, and the common mistake is to turn on the first and describe it as the second.

Session audit logging selects by statement class, with pgaudit.log. Scope it to the agent’s role rather than logging the whole server:

ALTER ROLE ai_agent SET pgaudit.log = 'read, write, ddl, role';
ALTER ROLE ai_agent SET pgaudit.log_parameter = on;

Because that is an ALTER ROLE, it applies to a new session that logs in as ai_agent, which is the same reason the role has to be a login role rather than something reached with SET ROLE.

From pgAudit’s own settings reference, the READ class is:

READ: SELECT and COPY when the source is a relation or a query.

“A relation or a query”, not a table: a SELECT through a view is in the class. This includes the case where an agent has been given views rather than tables.

log_parameter “specifies that audit logging should include the parameters that were passed with the statement”. With it, the log shows the values a parameterized statement ran with, and it is also the setting most likely to put customer data in a file with different access controls from the database. Decide it deliberately, and note that pgaudit.log_parameter_max_size only suppresses oversized values, not sensitive ones. Values embedded as SQL literals rather than passed as parameters are in the statement text either way.

Object audit logging selects by relation instead, with pgaudit.role, and it is configured by granting privileges to an audit role:

ALTER SYSTEM SET pgaudit.role = 'auditor';
SELECT pg_reload_conf();

GRANT SELECT ON customers TO auditor;

Setting pgaudit.log does not turn this on, and granting to the audit role does not turn session logging on. pgAudit describes object logging as “a finer-grained replacement for pgaudit.log = 'read, write'” and the right tool when the point is to keep the volume down on a small set of sensitive tables. Its own warning is that new relations have to be added to it explicitly.

If you want the objects without the object-auditing setup, pgaudit.log_relation writes a separate entry per relation referenced in a SELECT or DML statement, which is exhaustive and correspondingly loud.

What pgaudit cannot prove

This is in the extension’s own README, under Caveats, and it determines how much weight the output can carry:

Audit logging is best-effort and not transactional. pgAudit writes audit entries through the standard PostgreSQL logging facility, which does not flush each entry to disk synchronously with the transaction that produced it, nor does it propagate write errors back to the session. There is no guarantee that a committed transaction will have a corresponding audit log entry.

This has two consequences. A statement is logged when it executes, so an entry can exist for a transaction that later rolled back: the log says what was attempted, not what committed. And entries can be lost if the server crashes or the log volume fills after the commit. If the question is “what changed”, the answer has to come from the data or from an application-side record of approved actions, with the audit log as corroboration.

The README is equally blunt about the other gap:

It is not possible to reliably audit superusers with pgAudit.

That is an argument about the role design rather than about the logging: if the agent can reach a superuser, none of this counts.

Volume is the last caveat, and the first one you are likely to hit. An agent that explores a schema issues many small queries, and read plus log_parameter writes a line for each. Measure the log volume on a representative run before turning it on everywhere.

log_statement: the blunt instrument

It is built in, needs no extension, and its output has no structure:

ALTER ROLE ai_agent SET log_statement = 'all';

Set per role, it is useful: every statement submitted in a session that logged in as that role goes to the server log, except ones with syntax errors, and nothing else changes. Server-wide it produces a volume nobody reads. What it logs is what the client submitted, so a function body or dynamic SQL is one line here and several under pgaudit.

Give it a log_line_prefix carrying the fields you will need to correlate on, since the default carries almost none of them:

ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d app=%a ';
SELECT pg_reload_conf();

%m is the timestamp, %p the backend PID, %u the user, %d the database and %a the application name. If your log destination is csvlog or jsonlog those fields are already there as columns and the prefix is not doing the work, so check log_destination before rewriting the prefix. Either way the application name is still whatever the client said it was.

How do I confirm the audit trail works?

Test it the way you would test a backup: produce the event and then go and find it in the place you would actually look during an incident. That is the retained destination, not the file on the database host, and the acceptance question is whether it arrived there rather than how quickly. Export pipelines, rotation and ingestion delay all sit between the statement and the search box.

Run the list, as the agent’s role, in one session:

• a normal SELECT, which should appear with the user, database, PID and application name
• a statement that is denied by privileges, which should appear as an error with the same attribution
• a statement inside a transaction that you then roll back, which will still appear, and which is the difference between “attempted” and “committed”
• a statement with bound parameters, which tells you whether log_parameter is doing what you think and whether it is writing customer data
• if you use object auditing, a SELECT through a view over an audited table

Then check the settings are actually in force for that role, because ALTER ROLE only takes effect on new connections and is not picked up by SET ROLE:

SELECT rolname, rolconfig
FROM pg_roles
WHERE rolname = 'ai_agent';

And check where the log is going, since a log nobody collects is not an audit trail:

SELECT name, setting
FROM pg_settings
WHERE name IN ('log_destination', 'logging_collector', 'log_directory',
               'log_statement', 'log_min_duration_statement');

On a managed service, half of this is somebody else’s to set

Everything above assumes you can edit postgresql.conf, restart the server and read the log directory. On RDS and Aurora, Cloud SQL, Azure Database for PostgreSQL and Supabase you generally cannot: shared_preload_libraries is a parameter-group setting, the extension list is an allow-list, ALTER SYSTEM may be blocked, and the log goes to the provider’s own destination rather than to a file you can tail. All four publish their own pgaudit instructions: RDS, Cloud SQL, Azure Flexible Server and Supabase. Follow those rather than the ALTER SYSTEM lines above.

The part that is yours everywhere is the role design, which also cannot be retrofitted. Do that first.

Why does AI-generated code cause this?

The relevant failure is in the setup, not the SQL. Asked to connect an agent to a database, a model produces a connection string using the credentials that already exist, because that is what works. Nothing about it suggests creating a role, and nothing goes wrong until the day somebody asks what the agent did.

The second pattern is an agent that runs many small queries where a person would run one. That is fine for correctness and it means your audit volume is decided by the agent’s strategy, not by your workload.

How do I stop it coming back?

Never share a credential with an agent. One login role per agent, per environment, authenticated directly rather than reached with SET ROLE. It costs one CREATE ROLE, and everything else on this page depends on it.

Check what the pooler does to the session. Under transaction pooling one server backend serves many logical callers, so a role-level setting and an application_name are only as good as the pooler’s handling of them. If agent traffic shares a pool with application traffic, the role separation you set up is not reaching the database.

Decide the parameter-logging question before the incident, not during it. Logging bound values makes the trail complete and puts customer data in a file with different access controls from the database.

Ship the logs somewhere with retention. The default is a file on the database host that rotates.

Review what the agent read, not only what it wrote. For a read-only agent the disclosure question is the main concern, and SELECT is not a low-risk privilege. Scope it through views and column-level grants rather than granting the schema, and pair it with default_transaction_read_only, a statement_timeout and an idle_in_transaction_session_timeout on the role. Those settings limit damage but do not record activity.

Read the log as a record of attempts. pgAudit logs a statement when it executes, so the trail tells you what was run, not what committed. When the question is what changed, the data and an application-side record of approved actions are the evidence, and the log corroborates them.

How DBGorilla helps

DBGorilla can help

DBGorilla connects read-only and gives your AI coding agent the query workload attributed to the database user that ran it, over time rather than since the last counter reset: invocations, total and average duration, rows, buffers read and written, temporary blocks written and WAL bytes, per query fingerprint. Verified in a live session, the top entries come back with the user beside them, which is what makes an agent’s activity separable from the application’s. It surfaces and explains; it does not write to your database.

Start free

FAQ

• How do I see which queries an AI agent ran?

  Give it a login role nobody else uses, then filter on that role. pg_stat_statements has a userid column, pg_stat_activity has usename, and the server log can include the user in every line. Without a separate role you are left with client-supplied evidence like application_name, which separates two tools that behave but proves nothing about one that does not.

• Is pg_stat_statements enough for an audit trail?

  No. It aggregates by query shape with the literal values normally replaced by parameter symbols, so it tells you what kinds of query ran and what they cost, not what a specific one did or when. Entries are also evicted once more distinct statements than pg_stat_statements.max have been seen, and pg_stat_statements_info.dealloc counts that happening. It is the right tool for cost and the wrong tool for accountability.

• What is the difference between log_statement and pgaudit?

  log_statement is built in and logs statement text by category, which is blunt: 'all' means every statement including the noisy ones, and the output has no structure. pgaudit is an extension with two separate mechanisms: session audit logging, which you turn on with pgaudit.log and which selects by statement class, and object audit logging, which you turn on with pgaudit.role and which selects by relation. Both tag their lines so they can be parsed. pgaudit needs installing and shared_preload_libraries.

• Can I use application_name instead of a separate role?

  It helps and it does not replace one. application_name is set by the client and appears in pg_stat_activity and in the log, so it is a good way to separate two tools that share a role. It is also self-declared, so a client can set it to anything. Use it for attribution, not for evidence.

• Does read-only access mean I do not need an audit trail?

  No. Read-only removes the risk of damage, not the risk of disclosure. The audit question for a read-only agent is which tables it selected from and how much it returned, which is exactly what pgaudit's READ class records.

Related

• How to set up a read-only Postgres role for an AI agent
• What pg_stat_statements tells you