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 david.

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.