Database

PGAudit: Postgres Auditing

PGAudit extends Postgres's built-in logging abilities. It can be used to selectively track activities within your database.

This helps you with:

  • Compliance: Meeting audit requirements for regulations
  • Security: Detecting suspicious database activity
  • Troubleshooting: Identifying and fixing database issues

Enable the extension

Configure the extension

PGAudit can be configured with different levels of precision.

PGAudit logging precision:

  • Session: Logs activity within a connection, such as a psql connection.
  • User: Logs activity by a particular database user (for example, anon or postgres).
  • Global: Logs activity across the entire database.
  • Object: Logs events related to specific database objects (for example, the auth.users table).

Although Session, User, and Global modes differ in their precision, they're all considered variants of Session Mode and are configured with the same input categories.

Session mode categories

These modes can monitor predefined categories of database operations:

CategoryWhat it LogsDescription
readData retrieval (SELECT, COPY)Tracks what data is being accessed.
writeData modification (INSERT, DELETE, UPDATE, TRUNCATE, COPY)Tracks changes made to your database.
functionFUNCTION, PROCEDURE, and DO/END block executionsTracks routine/function executions
roleUser management actions (CREATE, DROP, ALTER on users and privileges)Tracks changes to user permissions and access.
ddlSchema changes (CREATE, DROP, ALTER statements)Monitors modifications to your database structure (tables, indexes, etc.).
miscLess common commands (FETCH, CHECKPOINT)Captures obscure actions for deeper analysis if needed.
allEverything aboveComprehensive logging for complete audit trails.

Below is a limited example of how to assign PGAudit to monitor specific categories.


_10
-- log all CREATE, ALTER, and DROP events
_10
... pgaudit.log = 'ddl';
_10
_10
-- log all CREATE, ALTER, DROP, and SELECT events
_10
... pgaudit.log = 'read, ddl';
_10
_10
-- log nothing
_10
... pgaudit.log = 'none';

Session logging

When you are connecting in a session environment, such as a psql connection, you can configure PGAudit to record events initiated within the session.

Inside a session, by default, PGAudit will log nothing:


_10
-- returns 'none'
_10
show pgaudit.log;

In the session, you can set the pgaudit.log variable to record events:


_10
-- log CREATE, ALTER, and DROP events
_10
set pgaudit.log = 'ddl';
_10
_10
-- log all CREATE, ALTER, DROP, and SELECT events
_10
set pgaudit.log = 'read, ddl';
_10
_10
-- log nothing
_10
set pgaudit.log = 'none';

User logging

There are some cases where you may want to monitor a database user's actions. For instance, let's say you connected your database to Zapier and created a custom role for it to use:


_10
create user "zapier" with password '<new password>';

You may want to log all actions initiated by zapier, which can be done with the following command:


_10
alter role "zapier" set pgaudit.log to 'all';

To remove the settings, execute the following code:


_10
-- disables role's log
_10
alter role "zapier" set pgaudit.log to 'none';
_10
_10
-- check to make sure the changes are finalized:
_10
select
_10
rolname,
_10
rolconfig
_10
from pg_roles
_10
where rolname = 'zapier';
_10
-- should return a rolconfig path with "pgaudit.log=none" present

Global logging

The below SQL configures PGAudit to record all events associated with the "postgres" role. Since it has extensive privileges, this effectively monitors all database activity.


_10
alter role "postgres" set pgaudit.log to 'all';

To check if the postgres role is auditing, execute the following command:


_10
select
_10
rolname,
_10
rolconfig
_10
from pg_roles
_10
where rolname = 'postgres';
_10
-- should return a rolconfig path with "pgaudit.log=all" present

To remove the settings, execute the following code:


_10
alter role "postgres" set pgaudit.log to 'none';

Object logging

To fine-tune what object events PGAudit will record, you must create a custom database role with limited permissions:


_10
create role "some_audit_role" noinherit;

No other Postgres user can assume or login via this role. It solely exists to securely define what PGAudit will record.

Once the role is created, you can direct PGAudit to log by assigning it to the pgaudit.role variable:


_10
alter role "postgres" set pgaudit.role to 'some_audit_role';

You can then assign the role to monitor only approved object events, such as select statements that include a specific table:


_10
grant select on random_table to "some_audit_role";

With this privilege granted, PGAudit will record all select statements that reference the random_table, regardless of who or what actually initiated the event. All assignable privileges can be viewed in the Postgres documentation.

If you would no longer like to use object logging, you will need to unassign the pgaudit.role variable:


_10
-- change pgaudit.role to no longer reference some_audit_role
_10
alter role "postgres" set pgaudit.role to '';
_10
_10
-- view if pgaudit.role changed with the following command:
_10
select
_10
rolname,
_10
rolconfig
_10
from pg_roles
_10
where rolname = 'postgres';
_10
-- should return a rolconfig path with "pgaudit.role="

Interpreting Audit Logs

PGAudit was designed for storing logs as CSV files with the following headers:

Referenced from the PGAudit official docs

headerDescription
AUDIT_TYPESESSION or OBJECT
STATEMENT_IDUnique statement ID for this session. Sequential even if some statements are not logged.
SUBSTATEMENT_IDSequential ID for each sub-statement within the main statement. Continuous even if some are not logged.
CLASS..., READ, ROLE (see pgaudit.log).
COMMAND..., ALTER TABLE, SELECT.
OBJECT_TYPETABLE, INDEX, VIEW, etc. Available for SELECT, DML, and most DDL statements.
OBJECT_NAMEThe fully qualified object name (for example, public.account). Available for SELECT, DML, and most DDL.
STATEMENTStatement executed on the backend.
PARAMETERIf pgaudit.log_parameter is set, this field contains the statement parameters as quoted CSV, or <none>. Otherwise, it's <not logged>.

A log made from the following create statement:


_10
create table account (
_10
id int primary key,
_10
name text,
_10
description text
_10
);

Generates the following log in the Dashboard's Postgres Logs:


_10
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account(
_10
id int,
_10
name text,
_10
description text
_10
); <not logged>

Finding and filtering audit logs

Logs generated by PGAudit can be found in Postgres Logs. To find a specific log, you can use the log explorer. Below is a basic example to extract logs referencing CREATE TABLE events


_10
select
_10
cast(t.timestamp as datetime) as timestamp,
_10
event_message
_10
from
_10
postgres_logs as t
_10
cross join unnest(metadata) as m
_10
cross join unnest(m.parsed) as p
_10
where event_message like 'AUDIT%CREATE TABLE%'
_10
order by timestamp desc
_10
limit 100;

Practical examples

Monitoring API events

To monitor all writes initiated by the Postgrest API roles:


_10
alter role "authenticator" set pgaudit.log to 'write';
_10
_10
-- the above is the practical equivalent to:
_10
-- alter role "anon" set pgaudit.log TO 'write';
_10
-- alter role "authenticated" set pgaudit.log TO 'write';
_10
-- alter role "service_role" set pgaudit.log TO 'write';

Monitoring the auth.users table

In the worst case scenario, where a privileged roles' password is exposed, you can use PGAudit to monitor if the auth.users table was targeted. It should be stated that API requests are already monitored in the API Edge Network and this is more about providing greater clarity about what is happening at the database level.

Logging auth.user should be done in Object Mode and requires a custom role:


_10
-- create logging role
_10
create role "auth_auditor" noinherit;
_10
_10
-- give role permission to observe relevant table events
_10
grant select on auth.users to "auth_auditor";
_10
grant delete on auth.users to "auth_auditor";
_10
_10
-- assign auth_auditor to pgaudit.role
_10
alter role "postgres" set pgaudit.role to 'auth_auditor';

With the above code, any query involving reading or deleting from the auth.users table will be logged.

Best practices

Disabling excess logging

PGAudit, if not configured mindfully, can log all database events, including background tasks. This can generate an undesirably large amount of logs in a few hours.

The first step to solve this problem is to identify which database users PGAudit is observing:


_12
-- find all users monitored by pgaudit
_12
select
_12
rolname,
_12
rolconfig
_12
from pg_roles
_12
where
_12
exists (
_12
select
_12
1
_12
from UNNEST(rolconfig) as c
_12
where c like '%pgaudit.role%' or c like '%pgaudit.log%'
_12
);

To prevent PGAudit from monitoring the problematic roles, you'll want to change their pgaudit.log values to none and pgaudit.role values to empty quotes ''


_10
-- Use to disable object level logging
_10
alter role "<role name>" set pgaudit.role to '';
_10
_10
-- Use to disable global and user level logging
_10
alter role "<role name>" set pgaudit.log to 'none';

FAQ

Can I use PGAudit to debug database functions?

Technically yes, but it is not the best approach. It is better to check out our function debugging guide instead.

How can I download database logs?

In the Logs Dashboard you can download logs as CSVs.

Can I log observed table rows?

By default, PGAudit records queries, but not the returned rows. You can modify this behavior with the pgaudit.log_rows variable:


_10
--enable
_10
alter role "postgres" set pgaudit.log_rows to 'on';
_10
_10
-- disable
_10
alter role "postgres" set pgaudit.log_rows to 'off';

You should not do this unless you are absolutely certain it is necessary for your use case. It can expose sensitive values to your logs that ideally should not be preserved. Furthermore, if done in excess, it can noticeably reduce database performance.

Can I log function parameters?

We don't currently support configuring pgaudit.log_parameter because it may log secrets in encrypted columns if you are using pgsodium orVault.

You can upvote this feature request with your use-case if you'd like this restriction lifted.

Does PGAudit support system wide configurations?

PGAudit allows settings to be applied to 3 different database scopes:

ScopeDescriptionConfiguration File/Command
SystemEntire serverALTER SYSTEM commands
DatabaseSpecific databaseALTER DATABASE commands
RoleSpecific user/roleALTER ROLE commands

Supabase limits full privileges for file system and database variables, meaning PGAudit modifications can only occur at the role level. Assigning PGAudit to the 'postgres' role grants it nearly complete visibility into the database, making role-level adjustments a practical alternative to configuring at the database or system level.

PGAudit's official documentation focuses on system and database level configs, but its docs officially supports role level configs, too.

Resources