Audit Logging

PostgreSQL provides event triggers. Event triggers are global to a particular database and are capable of capturing DDL events occured in the database. Redrock Postgres makes the following improvements to PostgreSQL so that users can audit DDL commands with event triggers:

• In Redrock Postgres, roles and tablespaces are database-level objects. Event triggers also support DDL commands for role and tablespace objects. For details, see Event Trigger Firing Matrix.
• Support capturing events of dropping objects at the end of a command (corresponding to the ddl_command_end event).
• Add a function pg_ddl_command_deparse to deparse a command from internal format of type pg_ddl_command, and returns a SQL statement in textual representation of type text.

The following example shows how to audit DDL and DML commands occured in the database. You can modify the commands in the example according to the actual situation.

Login to the database using a superuser or database owner, and create the audit log table to record events:

CREATE SCHEMA IF NOT EXISTS audit;
REVOKE ALL ON SCHEMA audit FROM public;

-- create table for audit logging
CREATE TABLE IF NOT EXISTS audit.logged_actions(
  event_time      timestamp,
  username        text,
  object_type     text,
  schema_name     text,
  object_identity text,
  application_name text,
  client_addr     inet,
  client_port     integer,
  command_tag     text,
  command         text
);

-- grant privileges to all user
GRANT INSERT ON TABLE audit.logged_actions TO PUBLIC;

The table audit.logged_actions includes the following columns:

We can use event trigger to capture DDL events that occur in the database, and record DDL commands into the audit log table.

-- create function for event trigger
CREATE OR REPLACE FUNCTION audit.ddl_trigger_func()
  RETURNS event_trigger AS $$
BEGIN
  INSERT INTO audit.logged_actions
    SELECT now(), current_user,
           object_type, schema_name, object_identity,
           current_setting('application_name'),
           inet_client_addr(), inet_client_port(),
           command_tag, pg_ddl_command_deparse(command)
      FROM pg_event_trigger_ddl_commands();
END;
$$ LANGUAGE plpgsql;

-- create ddl_command_end event trigger
CREATE EVENT TRIGGER audit_event_trigger
  ON ddl_command_end
  EXECUTE PROCEDURE audit.ddl_trigger_func();

After the preceding commands are executed, your DDL commands will be recorded in the table audit.logged_actions.

You can configure the auditing of DDL commands by modifying the DDL event trigger function definition. Referring to the definition of the table audit.logged_actions in above, you can configure the auditing of DDL commands according to the user who executed the DDL command, the type of object affected, the schema the object belongs in, the object name, the command tag, as well as the client application name, the client network address, the client network port, etc. For example, we can modify the definition of the above event trigger function audit.ddl_trigger_func to limit the object types involved in the audited DDL commands as follows:

CREATE OR REPLACE FUNCTION audit.ddl_trigger_func()
  RETURNS event_trigger AS $$
BEGIN
  INSERT INTO audit.logged_actions
    SELECT now(), current_user,
           object_type, schema_name, object_identity,
           current_setting('application_name'),
           inet_client_addr(), inet_client_port(),
           command_tag, pg_ddl_command_deparse(command)
      FROM pg_event_trigger_ddl_commands();
      WHERE object_type IN ('table', 'role');
END;
$$ LANGUAGE plpgsql;

In the example above, we limit the object types involved in DDL commands to table and role.

We can create a generic trigger function used for recording changes to tables into an audit log table. It will record the SQL statement, the table affected, the user who made the change, and a timestamp for each change.

CREATE OR REPLACE FUNCTION audit.if_modified_func()
  RETURNS TRIGGER AS $$
BEGIN
  IF TG_WHEN <> 'AFTER' OR TG_LEVEL <> 'STATEMENT' THEN
    RAISE EXCEPTION 'audit.if_modified_func() may only run as statement level AFTER trigger';
  END IF;

  INSERT INTO audit.logged_actions VALUES (
    now(), current_user, 'table', TG_TABLE_SCHEMA, TG_TABLE_NAME,
    current_setting('application_name'),
    inet_client_addr(), inet_client_port(),
    TG_OP, current_query());
END;
$$ LANGUAGE plpgsql;

Create triggers on tables that need to be audited to audit table data modification operations:

CREATE TRIGGER trigger_audit_testtbl
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON testtbl
  FOR EACH STATEMENT
  EXECUTE PROCEDURE audit.if_modified_func();

The above SQL command audits INSERT, UPDATE, DELETE, and TRUNCATE operations on the table testtbl. You need to modify the table name and trigger name in the SQL command according to your actual case. Alternatively, you can choose to audit only some kinds of operations on the table, for example, only DELETE and TRUNCATE operations on the table.