Let's now take a look at the complete example of an event trigger that creates an audit trail of some DDL commands in the database:
CREATE TABLE track_ddl ( event text, command text, ddl_time timestamptz, usr text ); CREATE OR REPLACE FUNCTION track_ddl_function() RETURNS event_trigger AS $$ BEGIN INSERT INTO track_ddl values(tg_tag, tg_event, now(), session_user); RAISE NOTICE 'DDL logged'; END $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE EVENT TRIGGER track_ddl_event ON ddl_command_start WHEN TAG IN ('CREATE TABLE', 'DROP TABLE', 'ALTER TABLE') EXECUTE PROCEDURE track_ddl_function(); CREATE TABLE event_check(i int); SELECT * FROM track_ddl; -[ RECORD 1 ]------------------------ event | CREATE TABLE command | ddl_command_start ddl_time | 2014-04-13 16:58:40.331385 usr | testusr
The example is actually quite simple. Here's what we have done in the example:
EVENT_TRIGGER
. It logs the DDL information in the audit trail table. The function created is SECURITY DEFINER
. The reason why this is done is because other users in the database don't have any privileges on the audit trail table and we don't actually want them to know it is there. This function is executed as the definer (which is a superuser), and the use of session_user
ensures that we log the user who logged in to the database, and not the one whose privileges are used to execute the function.CREATE
, DROP
, or ALTER TABLE
are executed by a user.We then create an example table and note that the command is indeed logged in the audit trail table.
3.22.249.220