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.
Privileged Role
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.
The privilege escalation problem
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:
_11create or replace function become_super()_11 returns event_trigger_11 language plpgsql as_11$$_11begin_11 alter role malicious SUPERUSER;_11end;_11$$;_11_11create event trigger bad_event_trigger on ddl_command_end_11execute procedure become_super();
And once a superuser trips on the event trigger, it will fire with its privileges. Making the malicious user a superuser.
Skipping Event Triggers
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).
User Event Triggers
This now allows users to safely create event triggers, without superuser access:
_26-- use the privileged role, which is configured to be "postgres"_26set role postgres;_26select 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_26create function show_current_user()_26returns event_trigger as $$_26begin_26 raise notice 'the event trigger is executed for %', current_user;_26end;_26$$ language plpgsql;_26_26create event trigger myevtrig on ddl_command_end_26execute procedure show_current_user();_26_26-- check it succeeds_26create table foo();_26NOTICE: the event trigger is executed for postgres_26_26set role myrole;_26create table bar();_26NOTICE: 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.
Try it out
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 guys, which kept pushing us to release this feature.
Footnotes
-
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. ↩