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
