Re: allowing for control over SET ROLE

On Wed, Jan 11, 2023 at 03:13:29PM -0500, Robert Haas wrote:
> On Wed, Jan 11, 2023 at 10:16 AM Noah Misch <noah(at)leadboat(dot)com> wrote:
> > I still think docs for the SET option itself should give a sense of the
> > diversity of things it's intended to control. It could be simple. A bunch of
> > the sites you're modifying are near text like "These restrictions enforce that
> > altering the owner doesn't do anything you couldn't do by dropping and
> > recreating the aggregate function." Perhaps the main SET doc could say
> > something about how it restricts other things that would yield equivalent
> > outcomes. (Incidentally, DROP is another case of something one likely doesn't
> > want the WITH SET FALSE member using. I think that reinforces a point I wrote
> > upthread. To achieve the original post's security objective, the role must
> > own no objects whatsoever.)
>
> I spent a while on this. The attached is as well I was able to figure
> out how to do. What do you think?

I think this is good to go modulo one or two things:

> Subject: [PATCH v2] More documentation update for GRANT ... WITH SET OPTION.
>
> Update the reference pages for various ALTER commands that
> mentioned that you must be a member of role that will be the
> new owner to instead say that you must be able to SET ROLE
> to the new owner. Update ddl.sgml's generate statement on this

s/generate/general/

> --- a/doc/src/sgml/ref/grant.sgml
> +++ b/doc/src/sgml/ref/grant.sgml
> @@ -298,6 +298,20 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
> This option defaults to <literal>TRUE</literal>.
> </para>
>
> + <para>
> + To create an object owned by another role or give ownership of an existing
> + object to another role, you must have the ability to <literal>SET
> + ROLE</literal> to that role; otherwise, commands such as <literal>ALTER
> + ... OWNER TO</literal> or <literal>CREATE DATABASE ... OWNER</literal>
> + will fail. However, a user who inherits the privileges of a role but does
> + not have the ability to <literal>SET ROLE</literal> to that role may be
> + able to obtain full access to the role by manipulating existing objects
> + owned by that role (e.g. they could redefine an existing function to act
> + as a Trojan horse). Therefore, if a role's privileges are to be inherited
> + but should not be accessible via <literal>SET ROLE</literal>, it should not
> + own any SQL objects.
> + </para>

I recommend deleting the phrase "are to be inherited but" as superfluous. The
earlier sentence's mention will still be there. WITH SET FALSE + NOINHERIT is
a combination folks should not use or should use only when the role has no
known privileges.