Thread: Event-Triggers for DB owners instead of just SUPERUSER
Hi. I'd like to use CREATE EVENT TRIGGER, but they are SUPERUSER only. In the past, CREATE EXTENSION was also SUPERUSER-only, but is now also available to DB owners. Which is great! Can't event-triggers also be available to DB owbers, instead of just SUPERUSER? Having CREATEDB does not imply having SUPERUSER, and I find it very limiting that the DB owner cannot setup things like event-triggers (for "local" auditing purposes) on its own DB. Is this something that has been considered before? Any technical issues to enable / add it, in the future? In general, can't the DB owner be considered a mini-SUPERUSER for anything related to that one DB? Thanks, --DD [1]: https://www.postgresql.org/docs/current/sql-createeventtrigger.html
Dominique Devienne:
Can't event-triggers also be available to DB owners, instead of just SUPERUSER?
There's a recent -hackers thread exactly about this:
Best,
Wolfgang
On Mon, Apr 14, 2025 at 12:09 PM Wolfgang Walther <walther@technowledgy.de> wrote: > > From Dominique Devienne: > > Can't event-triggers also be available to DB owners, instead of just SUPERUSER? > > There's a recent -hackers thread exactly about this: > https://www.postgresql.org/message-id/flat/CAGRrpzbtYDkg7_xwfzrqByYgCJQbbL38tADyuN%2B6tAkbA-Pnkg%40mail.gmail.com Hi. Thanks for the pointer Wolfgang. I've perused that thread, and Tom seems pretty against it, bummer... (see extract below). From Tom: > Here is the real problem: database owners are not specially > privileged in Postgres. Yeah, they can drop their DB, but they > don't have automatic permissions to mess with other people's > objects inside the DB. (Much the same can be said of schema > owners.) So any proposal that effectively gives DB owners > such privileges is going to fail. I realize that some other > DBMSes assign more privileges to schema or DB owners, but we > don't and I don't think we're open to changing that. Tom, I don't want to mess with other people's data. I want to track DDL changes in the schemas the "app" manages in that DB, for auditing reasons. I don't want the DB owner to do DML changes, except to the tables it can INSERT into, as usual, per the normal access rules. And the DB Owner will NOT have SUPERUSER. Not even the ROLE that created the DB or its owner. > I think you need to be thinking of this in terms of "what sort > of feature can we add that can be allowed to any SQL user?" > The notion I proposed earlier that an event trigger only fires > on queries executed by roles the trigger's owner belongs to > is (AFAICS) safe to allow to anyone. If that's not good enough > for your notion of what a DB owner should be able to do, the > answer is to grant the DB owner membership in every role that > uses her database. That's effectively what the feature you're > suggesting would do anyway. In my case, the DB Owner is not a MEMBER per-se (no set_option or inherit_option), BUT it will be the one who did create the ROLEs that will own the DB objects (SCHEMAs and TABLEs and co.), so it will have admin_option. So if admin_option is "good enough" to generate ROLE cycles, with the mess that created in "my app" against v16+, then it should also be "good enough" to fire event triggers on too. Like someone on that thread, I'm not sure to see where the privilege escalation is coming from. It sounds as-if the event trigger executes with SUPERUSER. But does it? In my case, the trigger would execute as the DB owner, based on the SECURITY DEFINER of the trigger proc, and that ROLE can't write (or mess) nilly willy in the normal access rules. So where's the problem exactly? In practice, as I wrote, in my case, the DB Owner is the one which created all ROLEs that owns any of the SCHEMAs, so it could mess with those, since it has ADMIN OPTION on all those ROLEs. But I don't see how that's any different for event-triggers compared to non-trigger SQL. I'm sure my view is naive, from not knowing PostgreSQL enough. But that's why I'm asking questions here, to get educated. And presenting my use-cases, to inform future devs. Thanks, --DD