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

> 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.

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.

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

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.

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