Thread: Allow database owners to CREATE EVENT TRIGGER

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.

Steve Chavez <steve@supabase.io> writes:
> This is why I thought the database owner is the right role to allow evtrig
> creation since it won't need an explicit list of roles.

> How about requiring explicit non-superuser execution for the database owner
> evtrig? It would be like:
> CREATE EVENT TRIGGER name ... FOR NOSUPERUSER;

Well, no.  That still allows the database owner to commandeer any
non-superuser role.  Even if we tightened "nosuperuser" to mean
"not superuser and not any built-in role", I don't think it will fly.

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.

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.

                        regards, tom lane

Hi Tom,

> Well, no.  That still allows the database owner to commandeer any
> non-superuser role.  Even if we tightened "nosuperuser" to mean
> "not superuser and not any built-in role", I don't think it will fly.

Why would the predefined roles be taken into consideration here? The docs on https://www.postgresql.org/docs/current/predefined-roles.html say:

" pg_read_server_files, pg_write_server_files and pg_execute_server_program..."
" ..they could be used to gain superuser-level access, therefore great care should be taken when granting these roles to users."

If a dbowner event trigger does `GRANT pg_read_server_files TO current_user;` inside it will fail with `ERROR:  permission denied to grant role "pg_read_server_files"`.
The only way for that to succeed is for a superuser to explicitly grant access to `pg_read_server_files` before, and that would have to be a conscious operation.

I would appreciate any clarification here.

> 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?

The role membership approach would work but it seems insufficient. For example consider `pgaudit` which installs event triggers and requires superuser.

Let's assume `pgaudit` would try to adopt this new feature. Then it would need to provide some special role like `pgaudit_admin`, create the event triggers under this role,
and users of this extension would have to manually grant membership to `pgaudit_admin` for the audit event triggers to fire.
That is a problem because that's easy to forget when creating new roles and the audit event triggers won't be "enforced".
So in that case I guess `pgaudit` developers would keep requiring superuser and not bother to adopt this new feature.

From a PoLP perspective it would be a desirable side-effect of this feature to stop requiring superuser for certain extensions too.

Best regards,
Steve Chavez

On Fri, 7 Mar 2025 at 15:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Steve Chavez <steve@supabase.io> writes:
> This is why I thought the database owner is the right role to allow evtrig
> creation since it won't need an explicit list of roles.

> How about requiring explicit non-superuser execution for the database owner
> evtrig? It would be like:
> CREATE EVENT TRIGGER name ... FOR NOSUPERUSER;

Well, no.  That still allows the database owner to commandeer any
non-superuser role.  Even if we tightened "nosuperuser" to mean
"not superuser and not any built-in role", I don't think it will fly.

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.

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.

                        regards, tom lane

Sorry, attached the output file.