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

On Wed, Oct 20, 2021 at 1:20 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> A downside is that with my suggestion, event triggers would still be
> for the highly-privileged roles only. Allowing unprivileged users to
> create event triggers that have limited scope might allow some really
> interesting use cases. There might be some options here, like allowing
> any user to create an event trigger that only affects that user.

I think that's basically giving up the important part of this idea,
which is to allow meaningful administration without superuser
privileges. "highly-privileged roles only" sounds like in practice it
would amount to the superuser or someone who can become the superuser
-- and thus probably wouldn't include the "master tenant" role in a
service provider environment.

I don't really see what the problem is with Tom's proposal[1,2], or
why the role self-administration thread is necessarily a blocker. It's
true that if X creates an event trigger and it fires for Y because X
can become Y, then Y might be able to revoke membership in Y from X
and thus circumvent the event trigger firing. But that is a severable
problem. We can fail to solve that problem and still be better off
than today, because at least with the proposed change a cooperating
group of users (or one whose ability to execute GRANT and REVOKE is
restricted by some other means) can benefit from event triggers
without any of them being superuser. If we make this change *and also*
resolve the role self-administration problem, then it can also work in
cases where a more privileged user needs to enforce event trigger
firing against a less-privileged user.

--
Robert Haas
EDB: http://www.enterprisedb.com

[1] http://postgr.es/m/214052.1627331086@sss.pgh.pa.us
[2] http://postgr.es/m/216038.1627333077@sss.pgh.pa.us