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

> On Oct 19, 2021, at 12:28 PM, Jeff Davis <pgsql@j-davis.com> wrote:
>
> On Mon, 2021-09-27 at 11:15 -0700, Mark Dilger wrote:
>> Allow non-superusers to create event triggers.  The logic already
>> existed and is unchanged to handle event triggers owned by non-
>> superusers and conditioning those triggers firing on the (trigger-
>> owner, role-performing-event) pair.  The v7 patch set didn't go quite
>> so far as allowing non-superusers to create event triggers, but that
>> undercuts much of the benefit of the changes for no obvious purpose.
>
> The thread on role self-administration seems like a dependency here.
> And it doesn't look like there's consensus that we should be
> conditioning event trigger firing on role membership:
>
> https://postgr.es/m/20211005043438.GB314685@rfd.leadboat.com

I have noticed the lack of consensus.  The resistance to having roles own other roles should get more attention, I
think.

Stephen and I went into the weeds on what DROP ROLE rolename CASCADE should mean, but I don't think that should hold up
theidea of role ownership.  He wanted a different command to do the work rather than this command, but I don't see
anythingin what he wrote to suggest that the idea is unacceptable, only a different preference on how that
functionalitygets spelled. 

There was also some difference in interpretation on what exact differences there are between "ownership" and
"dependency". To me, "ownership" is a subtype of dependency, just as "is indexed by" and "is contained in" are subtypes
ofdependency.  Indexes are dependent on the tables they index, tables are dependent on schemas that contain them,
objectsare dependent on roles that own them, and so forth.  Stephen seemed to have a different view.  I'm not at all
clearon whether his different view is a showstopper. 

Before redesigning the way we fix up event triggers for v15, I'd like to have a sense of how contentious all this is.
Ifit's just a matter of definitions and command spellings, we can work around it. 

Thanks for participating in this thread, BTW.

> Instead, how about:
>
> * make a predefined role pg_event_trigger that allows creating event
> triggers
> * make it an error for a superuser to fire an event trigger created by
> a non-superuser

I think blocking superuser actions is a non-starter, but you address that below....

> It doesn't solve the problem hierarchically, but we don't solve other
> predefined role privileges hierarchically, either (and for many of them
> it makes no sense).
>
> A downside is that the privileged event trigger creator could
> accidentally make life annoying for a superuser that's trying to issue
> DDL: the superuser would need to disable the event trigger, perform the
> action, then re-enable it. But that shouldn't be a practical problem in
> sane setups -- superusers shouldn't be performing a lot of DDL, and if
> they are, it's good to be explicit that they are bypassing something
> configured by their pseudo-admin.

I'd prefer not to assume much about the sanity of the setup, and I agree the superuser should be able to
unconditionallydisable the offending event trigger, but I think it is a pretty poor solution that a superuser would
needto disable and then re-enable a trigger.  Other commands in other sessions would be able to sneak through during
thewindow of time when the trigger is disabled.  Wouldn't it be much cleaner to have superuser bypass the trigger? 

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company