Horton
Menu

Re: CREATEROLE users vs. role properties

Robert Haas <robertmhaas@gmail.com>

View thread
From: Robert Haas <robertmhaas@gmail.com>
To: tushar <tushar.ahuja@enterprisedb.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2023-01-23T16:57:52Z
Lists: pgsql-hackers 
On Mon, Jan 23, 2023 at 10:25 AM tushar <tushar.ahuja@enterprisedb.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

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Improve several permission-related error messages.

  2. Integrate superuser check into has_rolreplication()

  3. Small code simplification

  4. Adjust interaction of CREATEROLE with role properties.

  5. Add new GUC reserved_connections.

  6. Rename ReservedBackends variable to SuperuserReservedConnections.

  7. Update docs and error message for superuser_reserved_connections.

  8. Add new GUC createrole_self_grant.

  9. Restrict the privileges of CREATEROLE users.

  10. Add a SET option to the GRANT command.