Re: allowing for control over SET ROLE

On Thu, Jan 12, 2023 at 12:09 AM Noah Misch <noah(at)leadboat(dot)com> wrote:
> 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/

Oops, yes.

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

I don't think I agree with this suggestion. If the privileges aren't
going to be inherited, it doesn't matter whether the role owns SQL
objects or not. And I think that there are two notable use cases for
SET FALSE + NOINHERIT (or SET FALSE + INHERIT FALSE). First, the a
grant with SET FALSE, INHERIT FALSE, ADMIN TRUE gives you the ability
to administer a role without inheriting its privileges or being able
to SET ROLE to it. You could grant yourself those abilities if you
want, but you don't have them straight off. In fact, CREATE ROLE
issued by a non-superuser creates such a grant implicitly as of
cf5eb37c5ee0cc54c80d95c1695d7fca1f7c68cb. Second, SET FALSE, INHERIT
FALSE could be used to set up groups for pg_hba.conf matching without
conferring privileges.

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