F.39. pgAudit — detailed session and/or object audit logging#

F.39. pgAudit — detailed session and/or object audit logging
F.39. pgAudit — detailed session and/or object audit logging
Prev UpAppendix F. Additional Supplied Modules and ExtensionsHome Next

F.39. pgAudit — detailed session and/or object audit logging #

F.39.1. Overview
F.39.2. Why pgAudit?
F.39.3. Usage Considerations
F.39.4. Tantor SE Version Compatibility
F.39.5. Settings
F.39.6. Session Audit Logging
F.39.7. Object Audit Logging
F.39.8. Event Marking
F.39.9. Format
F.39.10. Caveats

Version: 18.0

F.39.1. Overview #

The pgAudit provides detailed session and/or object audit logging via the standard Tantor SE logging facility.

In addition to standard audit logging, pgAudit now includes an advanced event marking functionality that allows selective marking and logging of specific database events based on configurable rules. This enables fine-grained control over which events are tracked and how they are processed.

F.39.2. Why pgAudit? #

Basic statement logging can be provided by the standard logging facility with log_statement = all. This is acceptable for monitoring and other usages but does not provide the level of detail generally required for an audit. It is not enough to have a list of all the operations performed against the database. It must also be possible to find particular statements that are of interest to an auditor. The standard logging facility shows what the user requested, while pgAudit focuses on the details of what happened while the database was satisfying the request.

For example, an auditor may want to verify that a particular table was created inside a documented maintenance window. This might seem like a simple job for grep, but what if you are presented with something like this (intentionally obfuscated) example: 

DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

Standard logging will give you this: 

LOG:  statement: DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

It appears that finding the table of interest may require some knowledge of the code in cases where tables are created dynamically. This is not ideal since it would be preferable to just search on the table name. This is where pgAudit comes in. For the same input, it will produce this output in the log: 

AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)

Not only is the DO block logged, but substatement 2 contains the full text of the CREATE TABLE with the statement type, object type, and full-qualified name to make searches easy.

When logging SELECT and DML statements, pgAudit can be configured to log a separate entry for each relation referenced in a statement. No parsing is required to find all statements that touch a particular table. In fact, the goal is that the statement text is provided primarily for deep forensics and should not be required for an audit.

F.39.3. Usage Considerations #

Depending on settings, it is possible for pgAudit to generate an enormous volume of logging. Be careful to determine exactly what needs to be audit logged in your environment to avoid logging too much.

For example, when working in an OLAP environment it would probably not be wise to audit log inserts into a large fact table. The size of the log file will likely be many times the actual data size of the inserts because the log file is expressed as text. Since logs are generally stored with the OS this may lead to disk space being exhausted very quickly. In cases where it is not possible to limit audit logging to certain tables, be sure to assess the performance impact while testing and allocate plenty of space on the log volume. This may also be true for OLTP environments. Even if the insert volume is not as high, the performance impact of audit logging may still noticeably affect latency.

To limit the number of relations audit logged for SELECT and DML statements, consider using object audit logging (see Object Auditing). Object audit logging allows selection of the relations to be logged allowing for reduction of the overall log volume. However, when new relations are added they must be explicitly added to object audit logging. A programmatic solution where specified tables are excluded from logging and all others are included may be a good option in this case.

When using the event marking functionality, consider that marking rules are evaluated for each auditable event, which may add some performance overhead. Use specific rules targeting only the events of interest to minimize this impact.

F.39.4. Tantor SE Version Compatibility #

pgAudit supports PostgreSQL 12 or greater.

In order to support new functionality introduced in each Tantor SE release, pgAudit maintains a separate branch for each Tantor SE major version which will be maintained in a manner similar to the PostgreSQL project.

Aside from bug fixes, no further development is allowed for stable branches. New development, if any, will be strictly for the next unreleased major version of PostgreSQL.

F.39.5. Settings #

Settings may be modified only by a superuser. Allowing normal users to change their settings would defeat the point of an audit log.

Settings can be specified globally (in postgresql.conf or using ALTER SYSTEM ... SET), at the database level (using ALTER DATABASE ... SET), or at the role level (using ALTER ROLE ... SET). Note that settings are not inherited through normal role inheritance and SET ROLE will not alter a user’s pgAudit settings. This is a limitation of the roles system and not inherent to pgAudit.

The pgAudit extension must be loaded in shared_preload_libraries. Otherwise, an error will be raised at load time and no audit logging will occur.

In addition, CREATE EXTENSION pgaudit must be called before pgaudit.log is set to ensure proper pgaudit functionality. The extension installs event triggers which add additional auditing for DDL. pgAudit will work without the extension installed but DDL statements will not have information about the object type and name.

If the pgaudit extension is dropped and needs to be recreated then pgaudit.log must be unset first otherwise an error will be raised.

F.39.5.1. pgaudit.log #

Specifies which classes of statements will be logged by session audit logging. Possible values are:

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

  • WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.

  • FUNCTION: Function calls and DO blocks.

  • ROLE: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.

  • DDL: All DDL that is not included in the ROLE class.

  • MISC: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.

  • MISC_SET: Miscellaneous SET commands, e.g. SET ROLE.

  • ALL: Include all of the above.

Multiple classes can be provided using a comma-separated list and classes can be subtracted by prefacing the class with a - sign (see Session Audit Logging).

The default is none.

F.39.5.2. pgaudit.log_catalog #

Specifies that session logging should be enabled in the case where all relations in a statement are in pg_catalog. Disabling this setting will reduce noise in the log from tools like psql and PgAdmin that query the catalog heavily.

The default is on.

F.39.5.3. pgaudit.log_client #

Specifies whether log messages will be visible to a client process such as psql. This setting should generally be left disabled but may be useful for debugging or other purposes.

Note that pgaudit.log_level is only enabled when pgaudit.log_client is on.

The default is off.

F.39.5.4. pgaudit.log_level #

Specifies the log level that will be used for log entries (see Message Severity Levels for valid levels) but note that ERROR, FATAL, and PANIC are not allowed). This setting is used for regression testing and may also be useful to end users for testing or other purposes.

Note that pgaudit.log_level is only enabled when pgaudit.log_client is on; otherwise the default will be used.

The default is log.

F.39.5.5. pgaudit.log_parameter #

Specifies that audit logging should include the parameters that were passed with the statement. When parameters are present they will be included in CSV format after the statement text.

The default is off.

F.39.5.6. pgaudit.log_parameter_max_size #

Specifies that parameter values longer than this setting (in bytes) should not be logged, but replaced with <long param suppressed>. This is set in bytes, not characters, so does not account for multi-byte characters in a text parameters's encoding. This setting has no effect if log_parameter is off. If this setting is 0 (the default), all parameters are logged regardless of length.

The default is 0.

F.39.5.7. pgaudit.log_relation #

Specifies whether session audit logging should create a separate log entry for each relation (TABLE, VIEW, etc.) referenced in a SELECT or DML statement. This is a useful shortcut for exhaustive logging without using object audit logging.

The default is off.

F.39.5.8. pgaudit.log_rows #

Specifies that audit logging should include the number of rows retrieved or affected by a statement. When enabled the rows field will be included after the parameter field.

The default is off.

F.39.5.9. pgaudit.log_statement #

Specifies whether logging will include the statement text and parameters (if enabled). Depending on requirements, an audit log might not require this and it makes the logs less verbose.

The default is on.

F.39.5.10. pgaudit.log_statement_once #

Specifies whether logging will include the statement text and parameters with the first log entry for a statement/substatement combination or with every entry. Enabling this setting will result in less verbose logging but may make it more difficult to determine the statement that generated a log entry, though the statement/substatement pair along with the process id should suffice to identify the statement text logged with a previous entry.

The default is off.

F.39.5.11. pgaudit.role #

Specifies the master role to use for object audit logging. Multiple audit roles can be defined by granting them to the master role. This allows multiple groups to be in charge of different aspects of audit logging.

There is no default.

F.39.5.12. pgaudit.marking_rules_enabled #

Enables the event marking functionality. When enabled, audit events will be matched against configured marking rules and selectively logged to a separate marking log file in CSV format.

This setting requires pgaudit.marking_log_directory and pgaudit.marking_log_filename to be configured.

The default is off.

F.39.5.13. pgaudit.marking_rules_max #

Specifies the maximum number of marking rules that can be defined. This setting determines the amount of shared memory allocated for the marking rules system at server startup.

Increasing this value requires more shared memory but allows more granular control over event marking.

The default is 1000.

F.39.5.14. pgaudit.marking_log_directory #

Specifies the directory where marking log files will be created. This directory must exist and be writable by the PostgreSQL server process. The path can be absolute or relative to the PostgreSQL data directory.

This setting is required when pgaudit.marking_rules_enabled is on.

There is no default.

F.39.5.15. pgaudit.marking_log_filename #

Specifies the filename pattern for marking log files. The pattern can include strftime-style escapes which will be replaced based on the current time, similar to PostgreSQL's standard log filename patterns.

For example, pgaudit_marking_%Y%m%d.csv will create daily log files like pgaudit_marking_20240101.csv.

This setting is required when pgaudit.marking_rules_enabled is on.

There is no default.

F.39.6. Session Audit Logging #

Session audit logging provides detailed logs of all statements executed by a user in the backend.

F.39.6.1. Configuration #

Session logging is enabled with the pgaudit.log setting.

Enable session logging for all DML and DDL and log all relations in DML statements: 

set pgaudit.log = 'write, ddl';
set pgaudit.log_relation = on;

Enable session logging for all commands except MISC and raise audit log messages as NOTICE: 

set pgaudit.log = 'all, -misc';
set pgaudit.log_level = notice;

F.39.6.2. Example #

In this example session audit logging is used for logging DDL and SELECT statements. Note that the insert statement is not logged since the WRITE class is not enabled

SQL: 

set pgaudit.log = 'read, ddl';

create table account
(
    id int,
    name text,
    password text,
    description text
);

insert into account (id, name, password, description)
             values (1, 'user1', 'HASH1', 'blah, blah');

select *
    from account;

Log Output: 

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account
(
    id int,
    name text,
    password text,
    description text
);,<not logged>
AUDIT: SESSION,2,1,READ,SELECT,,,select *
    from account,,<not logged>

F.39.7. Object Audit Logging #

Object audit logging logs statements that affect a particular relation. Only SELECT, INSERT, UPDATE and DELETE commands are supported. TRUNCATE is not included in object audit logging.

Object audit logging is intended to be a finer-grained replacement for pgaudit.log = 'read, write'. As such, it may not make sense to use them in conjunction but one possible scenario would be to use session logging to capture each statement and then supplement that with object logging to get more detail about specific relations.

F.39.7.1. Configuration #

Object-level audit logging is implemented via the roles system. The pgaudit.role setting defines the role that will be used for audit logging. A relation (TABLE, VIEW, etc.) will be audit logged when the audit role has permissions for the command executed or inherits the permissions from another role. This allows you to effectively have multiple audit roles even though there is a single master role in any context.

Set pgaudit.role to auditor and grant SELECT and DELETE privileges on the account table. Any SELECT or DELETE statements on the account table will now be logged: 

set pgaudit.role = 'auditor';

grant select, delete
   on public.account
   to auditor;

F.39.7.2. Example #

In this example object audit logging is used to illustrate how a granular approach may be taken towards logging of SELECT and DML statements. Note that logging on the account table is controlled by column-level permissions, while logging on the account_role_map table is table-level.

SQL: 

create role auditor;

set pgaudit.role = 'auditor';

create table account
(
    id int,
    name text,
    password text,
    description text
);

grant select (password)
   on public.account
   to auditor;

select id, name
  from account;

select password
  from account;

grant update (name, password)
   on public.account
   to auditor;

update account
   set description = 'yada, yada';

update account
   set password = 'HASH2';

create table account_role_map
(
    account_id int,
    role_id int
);

grant select
   on public.account_role_map
   to auditor;

select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id

Log Output: 

AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,select password
  from account,<not logged>
AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,update account
   set password = 'HASH2',<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account_role_map,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>

F.39.8. Event Marking #

Event marking is an advanced feature that provides fine-grained control over which audit events are tracked and how they are processed. Unlike standard audit logging which captures all events based on class or role configuration, event marking allows you to selectively mark specific events based on configurable rules that match database, event type, object type, object name, and roles.

When event marking is enabled, marked events are logged to a separate CSV file specified by pgaudit.marking_log_directory and pgaudit.marking_log_filename. This separation allows for easier processing and analysis of security-relevant events.

F.39.8.1. Overview #

The event marking system consists of three main components:

  • Marking Rules: Configurable rules that define which events should be marked based on database, event type, object type, object name, and roles.

  • Rule Management SQL Functions: Functions to add, remove, and view marking rules at runtime. All functions are located in the pgaudit_marking schema.

  • Marking Log: A separate CSV log file containing only marked events with additional context information.

F.39.8.2. Supported Event Types #

The event marking system supports the following event types. These represent specific SQL commands that can be matched by marking rules:

Authentication and Session Events: AUTHENTICATE, DISCONNECT

DDL Operations: ALTER AGGREGATE, ALTER COLLATION, ALTER CONVERSION, ALTER DATABASE, ALTER DEFAULT PRIVILEGES, ALTER DOMAIN, ALTER EVENT TRIGGER, ALTER EXTENSION, ALTER FOREIGN DATA WRAPPER, ALTER FOREIGN TABLE, ALTER FUNCTION, ALTER INDEX, ALTER LANGUAGE, ALTER LARGE OBJECT, ALTER MATERIALIZED VIEW, ALTER OPERATOR, ALTER OPERATOR CLASS, ALTER OPERATOR FAMILY, ALTER POLICY, ALTER PROFILE, ALTER ROLE, ALTER USER, ALTER GROUP, ALTER RULE, ALTER SCHEMA, ALTER SEQUENCE, ALTER SERVER, ALTER SYSTEM, ALTER TABLE, ALTER TABLESPACE, ALTER TEXT SEARCH CONFIGURATION, ALTER TEXT SEARCH DICTIONARY, ALTER TEXT SEARCH PARSER, ALTER TEXT SEARCH TEMPLATE, ALTER TRIGGER, ALTER TYPE, ALTER USER MAPPING, ALTER VIEW

CREATE Operations: CREATE ACCESS METHOD, CREATE AGGREGATE, CREATE CAST, CREATE COLLATION, CREATE CONVERSION, CREATE DATABASE, CREATE DOMAIN, CREATE EVENT TRIGGER, CREATE EXTENSION, CREATE FOREIGN DATA WRAPPER, CREATE FOREIGN TABLE, CREATE FUNCTION, CREATE INDEX, CREATE LANGUAGE, CREATE MATERIALIZED VIEW, CREATE OPERATOR, CREATE OPERATOR CLASS, CREATE OPERATOR FAMILY, CREATE POLICY, CREATE PROFILE, CREATE ROLE, CREATE USER, CREATE GROUP, CREATE RULE, CREATE SCHEMA, CREATE SEQUENCE, CREATE SERVER, CREATE TABLE, CREATE TABLE AS, SELECT INTO, CREATE TABLESPACE, CREATE TEXT SEARCH CONFIGURATION, CREATE TEXT SEARCH DICTIONARY, CREATE TEXT SEARCH PARSER, CREATE TEXT SEARCH TEMPLATE, CREATE TRANSFORM, CREATE TRIGGER, CREATE TYPE, CREATE USER MAPPING, CREATE VIEW

DROP Operations: DROP ACCESS METHOD, DROP AGGREGATE, DROP CAST, DROP COLLATION, DROP CONVERSION, DROP DATABASE, DROP DOMAIN, DROP EVENT TRIGGER, DROP EXTENSION, DROP FOREIGN DATA WRAPPER, DROP FOREIGN TABLE, DROP FUNCTION, DROP INDEX, DROP LANGUAGE, DROP MATERIALIZED VIEW, DROP OPERATOR, DROP OPERATOR CLASS, DROP OPERATOR FAMILY, DROP OWNED, DROP POLICY, DROP PROFILE, DROP ROLE, DROP USER, DROP GROUP, DROP RULE, DROP SCHEMA, DROP SEQUENCE, DROP SERVER, DROP TABLE, DROP TABLESPACE, DROP TEXT SEARCH CONFIGURATION, DROP TEXT SEARCH DICTIONARY, DROP TEXT SEARCH PARSER, DROP TEXT SEARCH TEMPLATE, DROP TRANSFORM, DROP TRIGGER, DROP TYPE, DROP USER MAPPING, DROP VIEW

DML Operations: SELECT, INSERT, UPDATE, DELETE, TRUNCATE TABLE, COPY

Other Operations: CLUSTER, COMMENT, DEALLOCATE, DO, EXECUTE, GRANT, PREPARE, REASSIGN OWNED, REFRESH MATERIALIZED VIEW, REINDEX, RESET, REVOKE, SECURITY LABEL, SET

The following security event classes are supported:

ALL_DDL: CREATE, ALTER, DROP for any database object, excluding stored procedures and functions.

ALL_DML: SELECT, INSERT, UPDATE, DELETE, TRUNCATE for any table types; EXECUTE for functions and stored procedures.

ALL_MOD: INSERT, UPDATE, DELETE, TRUNCATE for any table types.

ALL_PROC: CREATE, ALTER, DROP for any functions and stored procedures.

ALL_ROLE: CREATE, ALTER, DROP for USER, ROLE, GROUP, PROFILE.

F.39.8.3. Supported Object Types #

The following object types can be specified in marking rules to filter events by the type of database object involved:

TABLE, VIEW, MATERIALIZED VIEW, INDEX, SEQUENCE, SCHEMA, DATABASE, TABLESPACE, ROLE, FUNCTION, AGGREGATE, PROCEDURE, OPERATOR, OPERATOR CLASS, OPERATOR FAMILY, CAST, TRANSFORM, COLLATION, CONVERSION, LANGUAGE, DOMAIN, COMPOSITE TYPE, EVENT TRIGGER, EXTENSION, FOREIGN DATA WRAPPER, FOREIGN TABLE, SERVER, USER MAPPING, POLICY, RULE, TRIGGER, TEXT SEARCH CONFIGURATION, TEXT SEARCH DICTIONARY, TEXT SEARCH PARSER, TEXT SEARCH TEMPLATE, ACCESS METHOD, STATISTICS, PROFILE, CATALOG RELATION, CATALOG FUNCTION

F.39.8.4. SQL Interface #

The event marking system provides several SQL functions for managing marking rules at runtime. All functions are located in the pgaudit_marking schema and require superuser privileges.

pgaudit_marking.set_rule( db_name text, event_type text, object_type text, object_name text, role_name text, comment text DEFAULT '' ) #

Adds a new marking rule. The rule matches audit events based on the specified database name, SQL command type, database object type, object name, and role name.

Each parameter accepts the special string values 'ALL' and 'NULL'. These values are equivalent and indicate that the parameter is not used for matching.

Parameters:

  • db_name — Name of the database to match.

  • event_type — SQL command type to match (for example, CREATE TABLE, INSERT).

  • object_type — Database object type to match (for example, TABLE, VIEW).

  • object_name — Object name, optionally schema-qualified.

  • role_name — Role name.

  • comment — Optional descriptive comment for the rule.

The function returns true if the rule is successfully added.

Examples:

-- Mark all CREATE TABLE events in the current database
SELECT pgaudit_marking.set_rule(
    current_database(),
    'CREATE TABLE',
    'TABLE',
    'NULL',
    'ALL',
    'Track table creation'
);

-- Mark DROP TABLE operations in a specific database for a specific role
SELECT pgaudit_marking.set_rule(
    'mydb',
    'DROP TABLE',
    'TABLE',
    'NULL',
    'admin',
    'Admin drops in sensitive schema'
);

-- Mark all operations on a specific table for all roles
SELECT pgaudit_marking.set_rule(
    'mydb',
    'NULL',
    'TABLE',
    'public.audit_log',
    'ALL',
    'All operations on audit log'
);
pgaudit_marking.remove_rule( db_name text, event_type text, object_type text, object_name text, role_name text, comment text DEFAULT '' ) #

Removes a marking rule by its specification. All parameters must match exactly with an existing rule for it to be removed. Returns true if the rule was found and removed, false otherwise.

Examples: 

-- Remove specific rule
SELECT pgaudit_marking.remove_rule(
    'mydb',
    'CREATE TABLE',
    'TABLE',
    'ALL',
    'ALL',
    'Track table creation'
);

-- Remove rule without comment (use empty string)
SELECT pgaudit_marking.remove_rule(
    'mydb',
    'DROP TABLE',
    'TABLE',
    'test',
    'postgres',
    ''
);
pgaudit_marking.show_rules() #

Returns all currently configured marking rules as a table. The result set includes the following columns: db_name, event_type, object_type, object_name, role_name, comment.

Examples: 

-- List all marking rules
SELECT * FROM pgaudit_marking.show_rules();

-- Find rules for specific database
SELECT * FROM pgaudit_marking.show_rules() 
WHERE db_name = 'mydb';

-- Find rules for specific event type
SELECT * FROM pgaudit_marking.show_rules() 
WHERE event_type = 'DROP TABLE';

-- Find rules with comments
SELECT * FROM pgaudit_marking.show_rules() 
WHERE comment != '';
pgaudit_marking.rules_conf_save() #

Saves the current marking rules to a configuration file on disk. This allows rules to persist across server restarts. The file is written to the PostgreSQL data directory.

Example: 

-- Save current rules to configuration file
SELECT pgaudit_marking.rules_conf_save();
pgaudit_marking.rules_conf_reload() #

Reloads marking rules from the configuration file. This is useful after manually editing the rules file or after a server restart to restore previously saved rules.

Example: 

-- Reload rules from configuration file
SELECT pgaudit_marking.rules_conf_reload();
pgaudit_marking.rules_conf_reset() #

Removes all marking rules from the database table only. Deletion of all rules in the configuration file is performed manually by the database administrator.

Example: 

-- Remove all marking rules from the rules table only
SELECT pgaudit_marking.rules_conf_reset();

F.39.8.5. Marking Log Format #

Events that match marking rules are written to the marking log file in CSV format with the following columns:

  • timestamp - Event timestamp

  • session_user - Session user name

  • current_user - Current effective user

  • database - Database name

  • pid - Process ID

  • client_addr - Client IP address

  • client_port - Client port

  • application_name - Application name

  • statement_id - Statement identifier

  • substatement_id - Substatement identifier

  • event_type - Type of event (e.g., CREATE TABLE)

  • object_type - Type of object (e.g., TABLE)

  • object_name - Qualified object name

  • query_text - Full text of the query

  • status - Event status (OK, ERROR)

  • error_message - Error message if status is ERROR

  • parameters - Query parameters if logged

  • marked_by_rule - Rule specification that marked this event

The CSV format allows for easy import into analysis tools, databases, or SIEM systems for further processing and correlation.

F.39.8.6. Examples #

Example 1: Basic Event Marking Setup

Configure PostgreSQL to enable event marking: 

-- In postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.marking_rules_enabled = on
pgaudit.marking_log_directory = '/var/log/postgresql/audit'
pgaudit.marking_log_filename = 'pgaudit_marking_%Y%m%d.csv'
pgaudit.marking_rules_max = 1000

After restarting PostgreSQL, create the extension and add marking rules: 

-- Create pgaudit extension
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Mark all DDL operations on tables in current database
SELECT pgaudit_marking.set_rule(
    current_database(),
    'CREATE TABLE',
    'TABLE',
    'ALL',
    'ALL',
    'Track CREATE TABLE'
);
SELECT pgaudit_marking.set_rule(
    current_database(),
    'ALTER TABLE',
    'TABLE',
    'ALL',
    'ALL',
    'Track ALTER TABLE'
);
SELECT pgaudit_marking.set_rule(
    current_database(),
    'DROP TABLE',
    'TABLE',
    'ALL',
    'ALL',
    'Track DROP TABLE'
);

-- Mark all operations on sensitive schema
SELECT pgaudit_marking.set_rule(
    current_database(),
    'ALL',
    'ALL',
    'sensitive_data.%',
    'ALL',
    'All ops on sensitive schema'
);

-- View configured rules
SELECT * FROM pgaudit_marking.show_rules();

Example 2: Auditing Specific Tables 

-- Mark all DML operations on customer table for all roles
SELECT pgaudit_marking.set_rule(
    'mydb',
    'INSERT',
    'TABLE',
    'public.customers',
    'ALL',
    'Track inserts'
);
SELECT pgaudit_marking.set_rule(
    'mydb',
    'UPDATE',
    'TABLE',
    'public.customers',
    'ALL',
    'Track updates'
);
SELECT pgaudit_marking.set_rule(
    'mydb',
    'DELETE',
    'TABLE',
    'public.customers',
    'ALL',
    'Track deletes'
);
SELECT pgaudit_marking.set_rule(
    'mydb',
    'SELECT',
    'TABLE',
    'public.customers',
    'ALL',
    'Track reads'
);

-- Mark TRUNCATE separately
SELECT pgaudit_marking.set_rule(
    'mydb',
    'TRUNCATE TABLE',
    'TABLE',
    'public.customers',
    'ALL',
    'Track truncates'
);

-- Now perform operations - they will be marked in the log
INSERT INTO public.customers (name, email) VALUES ('John Doe', 'john@example.com');
UPDATE public.customers SET email = 'john.doe@example.com' WHERE name = 'John Doe';

Example 3: Monitoring Role Operations 

-- Mark all role-related operations for specific admin role
SELECT pgaudit_marking.set_rule(
    'ALL',
    'CREATE ROLE',
    'ROLE',
    'ALL',
    'admin',
    'Admin creates roles'
);
SELECT pgaudit_marking.set_rule(
    'ALL',
    'ALTER ROLE',
    'ROLE',
    'ALL',
    'admin',
    'Admin alters roles'
);
SELECT pgaudit_marking.set_rule(
    'ALL',
    'DROP ROLE',
    'ROLE',
    'ALL',
    'admin',
    'Admin drops roles'
);

-- Mark all GRANT/REVOKE for all roles
SELECT pgaudit_marking.set_rule(
    'ALL',
    'GRANT',
    'ROLE',
    'ALL',
    'ALL',
    'Track all grants'
);
SELECT pgaudit_marking.set_rule(
    'ALL',
    'REVOKE',
    'ROLE',
    'ALL',
    'ALL',
    'Track all revokes'
);

-- These operations will now be marked
CREATE ROLE analyst LOGIN PASSWORD 'secure_pass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
ALTER ROLE analyst VALID UNTIL '2025-12-31';

Example 4: Pattern Matching 

-- Mark operations on all tables ending with _audit
SELECT pgaudit_marking.set_rule(
    current_database(),
    'ALL',
    'TABLE',
    '%_audit',
    'ALL',
    'Audit tables'
);

-- Mark operations on all tables in reporting schema
SELECT pgaudit_marking.set_rule(
    'mydb',
    'ALL',
    'TABLE',
    'reporting.%',
    'ALL',
    'Reporting schema'
);

-- Mark DROP operations in production database for specific role
SELECT pgaudit_marking.set_rule(
    'production',
    'DROP%',
    'ALL',
    'ALL',
    'dba',
    'DBA drops in production'
);

Example 5: Persistent Rules Configuration 

-- Configure rules
SELECT pgaudit_marking.set_rule(
    'mydb',
    'CREATE TABLE',
    'TABLE',
    'ALL',
    'ALL',
    'Track creates'
);
SELECT pgaudit_marking.set_rule(
    'mydb',
    'DROP TABLE',
    'TABLE',
    'ALL',
    'ALL',
    'Track drops'
);

-- Save rules to file for persistence across restarts
SELECT pgaudit_marking.rules_conf_save();

-- After server restart, reload rules
SELECT pgaudit_marking.rules_conf_reload();

-- Verify rules are restored
SELECT * FROM pgaudit_marking.show_rules();

Example 6: Analyzing Marked Events 

-- Create a table to load marking log for analysis
CREATE TABLE audit_analysis (
    timestamp timestamp,
    session_user text,
    current_user text,
    database text,
    pid integer,
    client_addr inet,
    client_port integer,
    application_name text,
    statement_id bigint,
    substatement_id bigint,
    event_type text,
    object_type text,
    object_name text,
    query_text text,
    status text,
    error_message text,
    parameters text,
    marked_by_rule text
);

-- Load marking log (adjust path as needed)
COPY audit_analysis 
FROM '/var/log/postgresql/audit/pgaudit_marking_20240101.csv' 
WITH (FORMAT csv, HEADER true);

-- Analyze: Count events by type
SELECT event_type, COUNT(*) 
FROM audit_analysis 
GROUP BY event_type 
ORDER BY COUNT(*) DESC;

-- Find all failed operations
SELECT timestamp, event_type, object_name, error_message
FROM audit_analysis
WHERE status = 'ERROR';

-- Track specific table modifications
SELECT timestamp, event_type, session_user, query_text
FROM audit_analysis
WHERE object_name = 'public.customers'
ORDER BY timestamp;

-- Analyze by database
SELECT database, event_type, COUNT(*)
FROM audit_analysis
GROUP BY database, event_type
ORDER BY database, COUNT(*) DESC;

Example 7: Database-Specific Rules 

-- The value of 'ALL' is equivalent to 'NULL'
-- Different rules for different databases
SELECT pgaudit_marking.set_rule(
    'production',
    'DROP TABLE',
    'NULL',
    'ALL',
    'ALL',
    'All drops in production'
);
SELECT pgaudit_marking.set_rule(
    'development',
    'CREATE TABLE',
    'ALL',
    'ALL',
    'ALL',
    'All creates in dev'
);
SELECT pgaudit_marking.set_rule(
    'ALL',
    'ALTER SYSTEM',
    'DATABASE',
    'ALL',
    'ALL',
    'ALTER SYSTEM in any DB'
);

-- View rules grouped by database
SELECT db_name, COUNT(*) as rule_count
FROM pgaudit_marking.show_rules()
GROUP BY db_name
ORDER BY db_name;

F.39.9. Format #

Audit entries are written to the standard logging facility and contain the following columns in comma-separated format. Output is compliant CSV format only if the log line prefix portion of each log entry is removed.

  • AUDIT_TYPE - SESSION or OBJECT.

  • STATEMENT_ID - Unique statement ID for this session. Each statement ID represents a backend call. Statement IDs are sequential even if some statements are not logged. There may be multiple entries for a statement ID when more than one relation is logged.

  • SUBSTATEMENT_ID - Sequential ID for each sub-statement within the main statement. For example, calling a function from a query. Sub-statement IDs are continuous even if some sub-statements are not logged. There may be multiple entries for a sub-statement ID when more than one relation is logged.

  • CLASS - e.g. READ, ROLE (see pgaudit.log).

  • COMMAND - e.g. ALTER TABLE, SELECT.

  • OBJECT_TYPE - TABLE, INDEX, VIEW, etc. Available for SELECT, DML and most DDL statements.

  • OBJECT_NAME - The fully-qualified object name (e.g. public.account). Available for SELECT, DML and most DDL statements.

  • STATEMENT - Statement executed on the backend.

  • PARAMETER - If pgaudit.log_parameter is set then this field will contain the statement parameters as quoted CSV or <none> if there are no parameters. Otherwise, the field is <not logged>.

Use log_line_prefix to add any other fields that are needed to satisfy your audit log requirements. A typical log line prefix might be '%m %u %d [%p]: ' which would provide the date/time, user name, database name, and process id for each audit log.

F.39.10. Caveats #

Object renames are logged under the name they were renamed to. For example, renaming a table will produce the following result: 

ALTER TABLE test RENAME TO test2;

AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,public.test2,ALTER TABLE test RENAME TO test2,<not logged>

It is possible to have a command logged more than once. For example, when a table is created with a primary key specified at creation time the index for the primary key will be logged independently and another audit log will be made for the index under the create entry. The multiple entries will however be contained within one statement ID.

Autovacuum and Autoanalyze are not logged.

Statements that are executed after a transaction enters an aborted state will not be audit logged. However, the statement that caused the error and any subsequent statements executed in the aborted transaction will be logged as ERRORs by the standard logging facility.

It is not possible to reliably audit superusers with pgAudit. One solution is to restrict access to superuser accounts and use the set_user extension to escalate permissions when required.

When using event marking, be aware that marking rules are evaluated for each event, which may add some overhead. However, the rule matching system is optimized for performance and should have minimal impact in most environments.

Prev Up Next
F.38. pg_archive — automatically archive historical data from partitioned tables Home F.40. pgauditlogtofile — pgAudit addon for redirect audit log lines to an independent file