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
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for
pgaudit
and 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
orpostgres
). - 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:
Category | What it Logs | Description |
---|---|---|
read | Data retrieval (SELECT, COPY) | Tracks what data is being accessed. |
write | Data modification (INSERT, DELETE, UPDATE, TRUNCATE, COPY) | Tracks changes made to your database. |
function | FUNCTION, PROCEDURE, and DO/END block executions | Tracks routine/function executions |
role | User management actions (CREATE, DROP, ALTER on users and privileges) | Tracks changes to user permissions and access. |
ddl | Schema changes (CREATE, DROP, ALTER statements) | Monitors modifications to your database structure (tables, indexes, etc.). |
misc | Less common commands (FETCH, CHECKPOINT) | Captures obscure actions for deeper analysis if needed. |
all | Everything above | Comprehensive logging for complete audit trails. |
Below is a limited example of how to assign PGAudit to monitor specific categories.
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.
The Dashboard is a transactional environment and won't sustain a session.
Inside a session, by default, PGAudit will log nothing:
In the session, you can set
the pgaudit.log
variable to record events:
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:
You may want to log all actions initiated by zapier
, which can be done with the following command:
To remove the settings, execute the following code:
Global logging
Use global logging cautiously. It can generate many logs and make it difficult to find important events. Consider limiting the scope of what is logged by using session, user, or object logging where possible.
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.
To check if the postgres
role is auditing, execute the following command:
To remove the settings, execute the following code:
Object logging
To fine-tune what object events PGAudit will record, you must create a custom database role with limited permissions:
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:
You can then assign the role to monitor only approved object events, such as select
statements that include a specific table:
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:
Interpreting Audit Logs
PGAudit was designed for storing logs as CSV files with the following headers:
Referenced from the PGAudit official docs
header | Description |
---|---|
AUDIT_TYPE | SESSION or OBJECT |
STATEMENT_ID | Unique statement ID for this session. Sequential even if some statements are not logged. |
SUBSTATEMENT_ID | Sequential 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_TYPE | TABLE, INDEX, VIEW, etc. Available for SELECT, DML, and most DDL statements. |
OBJECT_NAME | The fully qualified object name (for example, public.account). Available for SELECT, DML, and most DDL. |
STATEMENT | Statement executed on the backend. |
PARAMETER | If 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:
Generates the following log in the Dashboard's Postgres Logs:
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
Practical examples
Monitoring API events
API requests are already recorded in the API Edge Network logs.
To monitor all writes initiated by the PostgREST API roles:
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:
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:
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 ''
FAQ
Using 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.
Downloading database logs
In the Logs Dashboard you can download logs as CSVs.
Logging observed table rows
By default, PGAudit records queries, but not the returned rows. You can modify this behavior with the pgaudit.log_rows
variable:
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.
Logging 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:
Scope | Description | Configuration File/Command |
---|---|---|
System | Entire server | ALTER SYSTEM commands |
Database | Specific database | ALTER DATABASE commands |
Role | Specific user/role | ALTER 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.