Re: allowing for control over SET ROLE

On Fri, Sep 30, 2022 at 04:34:32PM -0400, Robert Haas wrote:
> That thread has not reached an entirely satisfying conclusion.
> However, the behavior that was deemed outright buggy over there has
> been fixed. The remaining question is what to do about commands that
> allow you to give objects to other users (like ALTER <whatever> ..
> OWNER TO) or commands that allow you to create objects owned by other
> users (like CREATE DATABASE ... OWNER). I have, in this version,
> adopted the proposal by Wolfgang Walther on the other thread to make
> this controlled by the new SET option. This essentially takes the view
> that the ability to create objects owned by another user is not
> precisely a privilege, and is thus not inherited just because the
> INHERIT option is set on the GRANT, but it is something you can do if
> you could SET ROLE to that role, so we make it dependent on the SET
> option. This logic is certainly debatable, but it does have the
> practical advantage of making INHERIT TRUE, SET FALSE a useful
> combination of settings for predefined roles. It's also 100%
> backward-compatible, whereas if we made the behavior dependent on the
> INHERIT option, users could potentially notice behavior changes after
> upgrading to v16.

I'm not sure about tying the ownership stuff to this new SET privilege.
While you noted some practical advantages, I'd expect users to find it kind
of surprising. Also, for predefined roles, I think you need to be careful
about distributing ADMIN, as anyone with ADMIN on a predefined role can
just GRANT SET to work around the restrictions. I don't have a better
idea, though, so perhaps neither of these things is a deal-breaker. I was
tempted to suggest using ADMIN instead of SET for the ownership stuff, but
that wouldn't be backward-compatible, and you'd still be able to work
around it to some extent with SET (e.g., SET ROLE followed by CREATE
DATABASE).

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com