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

On Mon, Jul 26, 2021 at 4:58 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> After further thought, I can't poke a hole in that concept.
> We'd keep the rule that the trigger executes as the calling user.
> Therefore, the trigger cannot perform any action that the calling
> user couldn't do if she chose. Conversely, since the trigger
> owner could become a member of that role and then do whatever the
> trigger intends to do, this scheme does not give the trigger owner
> any new abilities either. All we've done is provide what some
> programming languages call an observer or annotation.
>
> I also like the fact that with this rule, superusers' ability to
> create event triggers that fire for everything is not a special case.

I think this has potential. In a managed services environment, you can
imagine the provider as the super-duper user, having the ability to do
anything - because they control the box, so there's really no stopping
it - but presumably very little interest in what happens within the
database. Then you have the tenant, who is a semi-super-user,
authorized by the provider to do anything internal to the database
that the provider doesn't think will cause them problems. With the
setup you're proposing here, I suppose what the provider needs to do
is have a role like 'tenant' and make all the other tenant role
members of that master role. Then the tenant can log in as 'tenant' as
set up event triggers that will apply to all of those users, but
there's no security compromise for the provider because the role (or
roles) that they use to log in are not members of 'tenant'.

I thought for a while there might be a problem with tenant users
creating event triggers and then altering the owner to 'tenant' but I
think now that was backwards thinking. 'tenant' is a member of all of
the tenant users but not the other way around, so they can't give
their event triggers away to 'tenant'.

Do I have that right?

I agree with you that it's really nice that this eliminates the
special case for superusers.

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