Thread: Allow database owners to CREATE EVENT TRIGGER
Hello hackers,
Currently PostgreSQL only allows creating event triggers for superusers, this prevents usage on PostgreSQL service providers, which do not grant superuser access.
This patch allows database owners to create event triggers, while preventing privilege escalation.
Unlike superuser event triggers, which execute functions for every role, database owner event triggers are only executed for non-superusers.
This is necessary to prevent privesc. i.e. a superuser tripping on an event trigger containing an `ALTER ROLE dbowner SUPERUSER`.
This is necessary to prevent privesc. i.e. a superuser tripping on an event trigger containing an `ALTER ROLE dbowner SUPERUSER`.
For skipping dbowner event triggers for superusers:
- A restriction is added for superuser event triggers, the event trigger function must be owned by a superuser.
+ While this is a breaking change, I think it's minor as the usual flow is to "login as superuser" -> "create an evtrig function" -> "create the evtrig". This is also proved by the existing tests, which barely change.
- A restriction is added for dbowner event triggers, the event trigger function must not be owned by a superuser.
This way we can filter dbowner event trigger functions inside `EventTriggerInvoke`.
Tests are included in the patch, I've added a dedicated regression file for easier review. Only a couple of error messages of the existing event trigger regression tests are changed.
Any feedback is welcomed. I haven't added docs yet but I'll gladly add them if the community thinks this patch makes sense.
(Previous thread that also discussed allowing event triggers for non-superusers: https://www.postgresql.org/message-id/flat/81C10FFB-5ADC-4956-9337-FA248A4CC20D%40enterprisedb.com#77738d12b82c9a403ea2c56ed09949a3)
Best regards,
Steve Chavez
Attachment
Hi, > Unlike superuser event triggers, which execute functions for every role, database owner event triggers are only executedfor non-superusers. Even if we forget about the security aspect for a moment, personally I have mixed feelings about the idea of adding a new type of event trigger that looks like a regular one but works differently depending on who creates them. Also what will happen if I promote a user to a superuser or vice versa? All this doesn't strike me as a great UI. Maybe you could explain your particular use case? -- Best regards, Aleksander Alekseev
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.
On Wed, Mar 5, 2025 at 7:55 AM Steve Chavez <steve@supabase.io> wrote:
How about requiring explicit non-superuser execution for the database owner evtrig? It would be like:
CREATE EVENT TRIGGER name ... FOR NOSUPERUSER;
This is more what I was thinking - which works for a boolean. The ability to have an exclusion list would make sense, or maybe just a predefined role pg_bypass_eventtriggers.
David J.
Steve Chavez <steve@supabase.io> writes:
> Currently PostgreSQL only allows creating event triggers for superusers,
> this prevents usage on PostgreSQL service providers, which do not grant
> superuser access.
> This patch allows database owners to create event triggers, while
> preventing privilege escalation.
I'm pretty down on this, at least in the form presented. While
you may have managed to keep the DB owner from sabotaging superusers,
the proposed feature still allows owning every other special role,
for example pg_write_server_files (which is something that's pretty
trivially exploitable to get superuser). Since we've generally been
working towards not requiring superuser for most routine admin tasks,
that problem is going to get worse not better over time. I don't
want to see us add a feature that creates a security reason to
avoid using those special roles in favor of using a superuser.
Or in other words: not-superuser to superuser is far from the only
type of privilege escalation that we need to prevent.
regards, tom lane
I wrote:
> Or in other words: not-superuser to superuser is far from the only
> type of privilege escalation that we need to prevent.
After reflecting on that for a moment: maybe say that an event trigger
fires for queries that are run by a role that the trigger's owning
role is a member of? That changes nothing for superuser-owned
triggers.
regards, tom lane
On Wed, 5 Mar 2025 at 10:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I wrote:
> Or in other words: not-superuser to superuser is far from the only
> type of privilege escalation that we need to prevent.
After reflecting on that for a moment: maybe say that an event trigger
fires for queries that are run by a role that the trigger's owning
role is a member of? That changes nothing for superuser-owned
triggers.
Can somebody remind me why triggers don't run as their owner in the first place?
It would make triggers way more useful, and eliminate the whole issue of trigger owners escalating to whomever tries to access the object on which the trigger is defined.