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
- 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
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;
audit.logged_actions includes the following columns:
||When the operation is performed|
||The user who performed the operation|
||Type of the object. Possible types are
||Name of the schema the object belongs in, if any; otherwise
||Text rendering of the object identity, schema-qualified. Each identifier included in the identity is quoted if necessary.|
||The name of the client application that performed the operation|
||The client network address from where the operation is performed|
||The client network port from where the operation is performed|
||Command tag. Possible values see Event Trigger Support by Command Tag.|
||The SQL command in textual representation|
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
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
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.