# 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

```sql
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:

<!-- sql-check: skip app_owner stands for whichever role owns your tables, so this names an object that only exists in your database -->
```sql
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:

```sql
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

```sql
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](https://www.postgresql.org/docs/17/monitoring-stats.html)
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

```sql
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 `SELECT`s 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](https://www.postgresql.org/docs/17/pgstatstatements.html):

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

<!-- sql-check: skip pg_stat_statements_info comes with the extension, which the check container does not preload -->
```sql
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:

```ini
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
```

<!-- sql-check: skip needs shared_preload_libraries set before the server starts, which the check container does not do -->
```sql
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:

<!-- sql-check: skip pgaudit is not installed in the check container -->
```sql
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](https://github.com/pgaudit/pgaudit), 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:

<!-- sql-check: skip pgaudit is not installed in the check container -->
```sql
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:

```sql
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:

<!-- sql-check: skip log_line_prefix is a server setting and takes effect on reload, so running it here would prove only that the syntax parses -->
```sql
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`:

```sql
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:

```sql
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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgaudit.html),
[Cloud SQL](https://cloud.google.com/sql/docs/postgres/pg-audit),
[Azure Flexible Server](https://learn.microsoft.com/en-us/azure/postgresql/security/security-audit)
and [Supabase](https://supabase.com/docs/guides/database/extensions/pgaudit).
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 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.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [How to set up a read-only Postgres role for an AI agent](https://www.dbgorilla.com/learn/postgres/how-to-set-up-a-read-only-postgres-role-for-an-ai-agent/)
- [What pg_stat_statements tells you](https://www.dbgorilla.com/learn/postgres/what-pg-stat-statements-tells-you/)