> On Oct 25, 2021, at 11:30 AM, Stephen Frost <sfrost@snowman.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
auditingsystem 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
shouldhe expect this example to work?
Having the "FOR gotham_residents" clause mean anybody with membership in role gotham_residents is problematic, because
itmeans 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
complianceaudit.
Having Batman *own* all residents in Gotham city would work, if we can agree on a role ownership system. It has the
downsidethat only a role's (direct or indirect) owner can do the auditing, though. That's more flexible than what we
havetoday, where only superuser can do it, but maybe some people would want to argue for a different solution with even
moreflexibility? A grantable privilege perhaps? But whatever it is, the reasoning about who gets audited and who does
notmust be clear enough that Batman can pass a compliance audit.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
