Re: Delegating superuser tasks to new security roles (Was: Granting control of SUSET gucs to non-superusers)

Greetings,

* Alvaro Herrera (alvherre(at)alvh(dot)no-ip(dot)org) wrote:
> On 2021-Jul-26, Tom Lane wrote:
>
> > What if we allow event triggers owned by non-superusers, but only fire
> > them on commands performed by the trigger's owner? This sidesteps all
> > the issues of who has which privileges and whether Alice is malicious
> > towards Bob or vice versa, because there is no change of privilege
> > domain. Admittedly, it fails to cover some use-cases, but I think it
> > would still handle a lot of interesting cases. The impression I have
> > is that a lot of applications do everything under just one or a few
> > roles.
>
> This is similar but not quite an idea I had: have event triggers owned
> by non-superusers run for all non-superusers, but not for superusers.
> It is still the case that all non-superusers have to trust everyone with
> the event-trigger-create permission, but that's probably the database
> owner so most of the time you have to trust them already.

This sort of logic is what has caused issues with CREATEROLE, imv. It's
simply not so simple as "don't run this when the superuser flag is set"
because non-superuser roles can become superusers. We need something
better to have something like this actually be safe. Tom's suggestion
would work, of course, but it would mean having to create event triggers
for all the roles in the system, and would those roles who own those
event triggers be able to disable them..? If so, it would almost
certainly be against the point of an auditing event trigger..

Thanks,

Stephen