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

Greetings,

* Mark Dilger (mark(dot)dilger(at)enterprisedb(dot)com) wrote:
> > On Oct 25, 2021, at 11:30 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> > Consider instead:
> >
> > CREATE ROLE X;
> > CREATE ROLE Y;
> > CREATE ROLE Z;
> >
> > GRANT Y to X;
> > GRANT Z to X;
> >
> > SET ROLE X;
> > CREATE EVENT TRIGGER FOR Y do_stuff();
> >
> > Now, X has explicitly said that they want the event trigger to fire for
> > role Y and if the event trigger fires or not is no longer based on
> > membership in the role creating the trigger but instead depends on being
> > the role which the event trigger was explicitly defined to fire on.
>
> I don't think your proposal quite works, because the set of users you'd like to audit with an event trigger based auditing system may be both large and dynamic:
>
> CREATE ROLE batman;
> CREATE ROLE robin;
>
> SET ROLE batman;
> CREATE ROLE gotham_residents NOLOGIN;
> CREATE ROLE riddler IN ROLE gotham_residents LOGIN;
> -- create millions of other Gotham residents....
> CREATE EVENT TRIGGER FOR gotham_residents audit_criminal_activity();
>
> Batman is not superuser, but he's pretty powerful, and he wants to audit all the criminal activity in Gotham. How should he expect this example to work?
>
> Having the "FOR gotham_residents" clause mean anybody with membership in role gotham_residents is problematic, because it means that being granted into a role both increases and decreases your freedoms, rather than merely giving you more freedoms. If Batman covets privileges that Robin has, but wants not to be subjected to any event triggers that fire for Robin, he both wants into and out of role Robin.

The privileges afforded to 'robin' could be GRANT'd to another role
created for that purpose which is then GRANT'd to 'batman' though.
Indeed, that role could be used as the role which GRANT's 'robin' those
rights in the first place too. This kind of permission management is
largely the point of the role-based system we have.

> Having "FOR gotham_residents" mean that only actions performed by role "gotham_residents" should fire the trigger is useless, since Gotham residents don't log in as that, but as themselves. Batman won't catch anybody this way.

Naturally. That doesn't mean that there couldn't be some other role
which all of those roles are made a member of though. Either way,
there's a big list of "roles this event trigger runs for" and that has
to be managed. That it happens to be "roles owned by batman", if we
went with your suggested approach, instead of other role membership
doesn't really 'fix' that because there'll be other roles in the system
which 'batman' doesn't own. One nice thing of using roles for this is
that you end up being able to use the same role multiple ways- consider
this: I want to audit all roles who login to database mydb. Ah-hah, now
I can say:

CREATE DATABASE mydb;
CREATE EVENT TRIGGER FOR gotham_residents audit_stuff();
REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;
GRANT CONNECT ON DATABASE mydb TO gotham_residents;

Now the two are connected- if you can connect to that database, then
you're going to get audited, and if you manage access to the 'mydb'
database using membership in that role then there's no way for a role to
be able to connect to that database without being audited (except for a
true superuser, but that's always going to be an exception).

> Having to list each new resident to the trigger is tedious and error-prone. Batman may not be able to pass a compliance audit.

Agreed. Also wouldn't be great since eventually the role list might
have to get TOAST'd and then you're doing an extra lookup to pull back
the list, yuck.

> Having Batman *own* all residents in Gotham city would work, if we can agree on a role ownership system. It has the downside that only a role's (direct or indirect) owner can do the auditing, though. That's more flexible than what we have today, where only superuser can do it, but maybe some people would want to argue for a different solution with even more flexibility? A grantable privilege perhaps? But whatever it is, the reasoning about who gets audited and who does not must be clear enough that Batman can pass a compliance audit.

What about roles which Batman owns but which he *doesn't* want the event
trigger to fire for?

Note that event triggers are not strictly limited to the auditing case.
Viewing them through that lense masks other quite common use-cases which
are also importnat to consider (like preventing many users, but not all,
from being able to DROP objects as a clear example).

Thanks,

Stephen