Re: Orphaned users in PG16 and above can only be managed by Superusers

Hi All,

On Thu, Jan 9, 2025 at 11:01 AM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com>
wrote:
>
> Hi All,
>
> Starting from PG16, it seems that orphaned users can only be managed
> by superusers. For example, if userA creates userB, and userB creates
> userC, then both userB (the parent of userC) and userA (the
> grandparent of userC) would typically have the ability to
> manage/administer userC. However, if userB is dropped, userA (the
> grandparent of userC) loses the ability to administer userC as well.
> This leads to a situation where only superusers can manage userC.
>
> Shouldn't userA retain the permission to manage userC even if userB is
> removed? Otherwise, only superusers would have the authority to
> administer userC (the orphaned user in this case), which may not be
> feasible for cloud environments where superuser access is restricted.
>

Here's a simple test-case to clarify the issue raised here:

\c postgres adminusr

create user userA createdb createrole;

\c postgres userA

create user userB createdb createrole;

\c postgres userB

create user userC createdb createrole;

Here userA creates userB and userB creates userC. This means userB inherits
the privileges of userC as it has created it and userA inherits the
privileges of userB which also includes the privileges that the userB
inherited from userC. When all the users are present, userA can administer
userC, see below:

postgres=> \c postgres usera
You are now connected to database "postgres" as user "usera".

postgres=> alter user userC nocreatedb;
ALTER ROLE

However, when userB (the creator of userC) is dropped, userA can no longer
administer userC. See below:

postgres=> drop user userB;
DROP ROLE

postgres=> alter user userC createdb;
ERROR: 42501: permission denied to alter role
DETAIL: Only roles with the CREATEROLE attribute and the ADMIN option on
role "userc" may alter this role.

This results in a situation where only superusers can administer userC.

--
With Regards,
Ashutosh Sharma.