Event Triggers in Postgres are powerful, but only a superuser can create them. In cloud environments, granting superuser access isn't an option.
But thanks to Postgres' extensibility, we can allow regular users to create Event Triggers, in a safe way.
In this blog post, we’ll explain how we do this in the supautils extension, using a combination of the Utility Hook and the Function Manager Hook.
The core of supautils is the “privileged role”, which is a role that serves as proxy to superuser. It provides a safe subset of superuser capabilities and it’s accessible to regular users.
When the privileged role does a create event trigger, we intercept the statement with a Utility Hook (ProcessUtility_hook).
Here we elevate the role to a superuser, continuing the usual flow and allowing the creation on Postgres core. As a last step, we downgrade to the privileged role and make
it the event trigger owner 1.
Creating an event trigger like this is not safe though, as it would allow privilege escalation.
Here, a problem arises. Once an Event Trigger is created:
- It targets every role.
- It runs using the target role privileges 2.
This means that a malicious user can create an Event Trigger like:
_11 create or replace function become_super() _11 returns event_trigger _11 language plpgsql as _11 $$ _11 begin _11 alter role malicious SUPERUSER; _11 end; _11 $$; _11 _11 create event trigger bad_event_trigger on ddl_command_end _11 execute procedure become_super();
And once a superuser trips on the event trigger, it will fire with its privileges. Making the malicious user a superuser.
A solution would be skipping user Event Triggers for superusers.
The Function Manager hook (fmgr_hook) allows us to intercept and modify functions’ execution.
We can intercept the Event Trigger function and replace it with a “noop”. Postgres doesn’t provide a noop function, but we can use the existing version() function for the same purpose.
Besides superusers, we also want to skip user event triggers for “reserved roles” 3. These are used for managed services (like pgbouncer).
This now allows users to safely create Event Triggers, without superuser access:
_26 -- use the privileged role, which is configured to be "postgres" _26 set role postgres; _26 select current_setting('is_superuser'); -- prove it's not a superuser _26 current_setting _26 ----------------- _26 off _26 (1 row) _26 _26 -- now create the event trigger _26 create function show_current_user() _26 returns event_trigger as $$ _26 begin _26 raise notice 'the event trigger is executed for %', current_user; _26 end; _26 $$ language plpgsql; _26 _26 create event trigger myevtrig on ddl_command_end _26 execute procedure show_current_user(); _26 _26 -- check it succeeds _26 create table foo(); _26 NOTICE: the event trigger is executed for postgres _26 _26 set role myrole; _26 create table bar(); _26 NOTICE: the event trigger is executed for myrole
Future in Postgres core#
We would also like to allow regular user event triggers in Postgres core. To this end, we’ve submitted some patches which are already generating fruitful discussion.
Note that user Event Triggers in Postgres core will likely be more restricted than the supautils version.
User Event Triggers are now available for new projects on the Supabase platform.
You can also git clone the supautils repo and make install it in your own deployment.
Finally, we want to give a special shout out to the Zero Sync team, who pushed us to release this feature.
-
This is so the event trigger can be altered or dropped by end users. ↩
-
This is not true if you mark the event trigger function as
security definer, then it will run with the privileges of the function owner. But this is not a usual practice on event triggers, as they usually want to preserve the context of the current user. ↩ -
These are configurable. You can read more about reserved roles here. ↩