PGAudit: Postgres Auditing
PGAudit is a PostgreSQL extension for logging session and object auditing over the standard PostgreSQL logging utility.
PGAudit grants fine grain control over which statements and objects are emitted to logs.
Enable the extension#
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "pgaudit" and enable the extension.
pgaudit.log setting controls which statements to log. Available values include:
COPYwhen the source is a relation or a query.
COPYwhen the destination is a relation.
function: Function calls and
role: Statements related to roles and privileges:
DDLthat is not included in the
misc: Miscellaneous commands, e.g.
all: Include all of the above.
For a full list of available settings see settings docs. Be aware that the
all setting will generate a very large volume of logs.
Given a pgaudit setting
set pgaudit.log = 'read, ddl';
The following create table, insert and select statements
create table account ( id int primary key, name text, description text ); insert into account (id, name, description) values (1, 'Foo Barsworth', 'Customer account'); select * from account;
Results in the log output
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account( id int, name text, description text );,<not logged> AUDIT: SESSION,2,1,READ,SELECT,,,select * from account,,<not logged>
Note that the insert statement is not logged because we did not include the
write option for