Allow using event triggers without SUPERUSER

[steve-chavez]

Problem

Currently there's no way to use event triggers. RDS allows it though, see https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Concepts.General.FeatureSupport.EventTriggers.html. Caveat: the user must delete event triggers when migrating to a new major postgres version.

References:

• https://dba.stackexchange.com/questions/182501/create-event-trigger-as-non-superuser-in-postgres
• https://github.com/orgs/supabase/discussions/9314#discussioncomment-7085076

Solution

Allow event triggers for non superusers.

[steve-chavez]

I gave this a shot but I was surprised to find privileged_role is not used for creating privileged extensions. There's a privileged_extensions_superuser that's used for switching to superuser.

@soedirgo Which role should we switch to create the event trigger? Why privileged_role wasn't used for extensions?

[steve-chavez]

Here it says privileged_role but privileged_extensions_superuser is used for the switch:

supautils/src/supautils.c

Lines 284 to 286 in fa39431

	// Allow `privileged_role` (in addition to superusers) to
	// set bypassrls & replication attributes.
	switch_to_superuser(privileged_extensions_superuser, &already_switched_to_superuser);

@soedirgo What's the relationship between these 2 roles?

[soedirgo]

privileged_role is named as such because from the user's perspective it's the role that can do certain superuser-only actions (hence "privileged"). But under the hood some of it is achieved by switching to a superuser when performing the action.

Agree that privileged_extensions_superuser is confusing though - we should rename them to supautils.superuser in the future, since it's not only used for creating privileged_extensions.

[soedirgo]

Before you go ahead with the implementation, keep in mind that simply switching to a superuser to perform the CREATE EVENT TRIGGER, similar to how we do it for FDWs etc., wouldn't work since it opens a door for privilege escalation.