Re: Allow database owners to CREATE EVENT TRIGGER

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 Wed, 5 Mar 2025 at 08:54, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Wednesday, March 5, 2025, Aleksander Alekseev <aleksander(at)timescale(dot)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.
>
>