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

On Mon, Oct 25, 2021 at 2:30 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Does membership in role Y cause the event trigger to fire for that role?
> I'd argue that the answer is probably 'yes', but at least it's no longer
> tied back to membership in X (the owner of the trigger). That Noah
> explicitly mentioned 'login' roles vs. 'non-login' roles makes me think
> this is more in line with what the argument was about- the owner of the
> trigger would almost certainly be a 'login' role. All that said, this
> is definitely a complex area and there's certainly a lot of different
> ways we could go.

I mean I get all this. I am not convinced that it's a big problem,
because it seems a bit hypothetical, but if it is a problem, then
introducing some explicit mechanism to control which triggers fire for
which users is a solution. I'm a bit concerned that it's just going to
make it complicated to configure your event triggers to no real
benefit. Suppose that, as a master tenant, have 10 event triggers and
100 users and all the users are supposed to run all the event
triggers. When I add user #101, if I have to say, yes, I want that
user to fire the same 10 event triggers, running a separate SQL
command for each of one, that's kind of annoying. If I can just create
the new user and I automatically gain membership in that user and it
therefore fires all my event triggers, I get the behavior I wanted
anyway without having to do any special steps.

But also, Noah writes: "Also, it's currently a best practice for only
non-LOGIN roles to have members. The proposed approach invites folks
to abandon that best practice."

The kind of mechanism you're proposing here doesn't seem to make that
any less likely.

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