Re: CREATEROLE users vs. role properties

On Mon, Jan 23, 2023 at 10:25 AM tushar <tushar(dot)ahuja(at)enterprisedb(dot)com> wrote:
> Please refer to this scenario where I am able to give createrole privileges but not replication privilege to role
>
> postgres=# create role t1 createrole;
> CREATE ROLE
> postgres=# create role t2 replication;
> CREATE ROLE
> postgres=# create role t3;
> CREATE ROLE
> postgres=# grant t3 to t1,t2 with admin option;
> GRANT ROLE
> postgres=# set session authorization t1;
> SET
> postgres=> alter role t3 createrole ;
> ALTER ROLE
> postgres=> set session authorization t2;
> SET
> postgres=> alter role t3 replication;
> ERROR: permission denied
>
> This same behavior was observed in v14 as well but why i am able to give createrole grant but not replication?

In previous releases, you needed to have CREATEROLE in order to be
able to perform user management functions. In master, you still need
CREATEROLE, and you also need ADMIN OPTION on the role. In this
scenario, only t1 meets those requirements with respect to t3, so only
t1 can manage t3. t2 can SET ROLE to t3 and grant membership in t3,
but it can't set role properties on t3 or change t3's password or
things like that, because the ability to make user management changes
is controlled by CREATEROLE.

The patch is only intended to change behavior in the case where you
possess both CREATEROLE and also ADMIN OPTION on the target role (but
not SUPERUSER). In that scenario, it intends to change whether you can
give or remove the CREATEDB, REPLICATION, and BYPASSRLS properties
from a user.

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