Many thanks for the feedback.
> an attribute of the trigger and allow both superusers and non-superusers to create them.
The above is the crux of the issue. Superuser evtrigs can target every role but non-superusers evtrigs must apply only to a restricted set of roles to avoid privilege escalation.
With an explicit attribute, I guess the SQL syntax should be like:
> Seems better to make “execute_for” an attribute of the trigger
CREATE EVENT TRIGGER name ... FOR role1, role2;
Now say a new role is created and has usage/create on this database and we want the evtrig to apply to it. We would need to manually update the list of roles, it won't be done automatically. That is a problem if, for example, we need to enforce an audit trail through event triggers.
This is why I thought the database owner is the right role to allow evtrig creation since it won't need an explicit list of roles.
How about requiring explicit non-superuser execution for the database owner evtrig? It would be like:
CREATE EVENT TRIGGER name ... FOR NOSUPERUSER;
I welcome any alternative ideas.
Best regards,
Steve Chavez
On Wednesday, March 5, 2025, Aleksander Alekseev <aleksander@timescale.com> wrote: > Unlike superuser event triggers, which execute functions for every role, database owner event triggers are only executed for non-superusers.
All this doesn't strike me as a great UI.
Yeah. Seems better to make “execute_for” an attribute of the trigger and allow both superusers and non-superusers to create them. Then enforce that non-superusers must specify the more limited value.
Though it would seem nice to be able to exclude the pseudo-admin roles these service providers create as well.
David J.
