Can an event trigger capture DDL events for role objects?
Event triggers are global to a particular database and are capable of capturing DDL events occured in the database. In Redrock Postgres, roles are database-level objects. Event triggers also support DDL commands for role objects. For details, see Event Trigger Firing Matrix.
In the example below, we create an event trigger that captures DDL events that occur in the database at the end of a command.
CREATE OR REPLACE FUNCTION ddl_trigger_func() RETURNS event_trigger AS $$ DECLARE ddl record; BEGIN FOR ddl IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP RAISE NOTICE 'ddl command: %', pg_ddl_command_deparse(ddl.command); END LOOP; END $$ LANGUAGE plpgsql; CREATE EVENT TRIGGER ddl_event_trigger ON ddl_command_end EXECUTE PROCEDURE ddl_trigger_func();
Let’s create, change, and delete a role
CREATE USER david WITH PASSWORD 'jw8s0F4'; ALTER ROLE david WITH PASSWORD 'hu8jmn3'; DROP USER david;
In the example above, we will see that when creating, changing, and deleting the role
david, the event trigger captures the corresponding DDL event.