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.coek88@gmail.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.

On Thu, Jan 9, 2025 at 12:31 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

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.

This doesn't seem great, but it's not clear to me what we should do
about it. It doesn't really seem reasonable to me to change the role
grants that point to userB to make them point to userA instead. After
all, there could be multiple sets of role grants pointing to userB and
there could be multiple sets of role grants from userB pointing
elsewhere and they could all have different options (admin, set,
inherit). It doesn't feel right to have DROP ROLE make a bunch of
arbitrary decisions about what to do about that. We could make DROP
ROLE userB fail, perhaps, and tell the user they need to sort it out
first, but I'm not entirely sure that we have the right tools to allow
the user to do that in a convenient way. If userC were instead tableC,
DROP OWNED or REASSIGN OWNED could be used.

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

Hi Robert,

On Tue, Jan 21, 2025 at 10:22 PM Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jan 9, 2025 at 12:31 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

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.

This doesn't seem great, but it's not clear to me what we should do
about it. It doesn't really seem reasonable to me to change the role
grants that point to userB to make them point to userA instead. After
all, there could be multiple sets of role grants pointing to userB and
there could be multiple sets of role grants from userB pointing
elsewhere and they could all have different options (admin, set,
inherit). It doesn't feel right to have DROP ROLE make a bunch of
arbitrary decisions about what to do about that. We could make DROP
ROLE userB fail, perhaps, and tell the user they need to sort it out
first, but I'm not entirely sure that we have the right tools to allow
the user to do that in a convenient way. If userC were instead tableC,
DROP OWNED or REASSIGN OWNED could be used.

Thanks for sharing your thoughts and inputs. I'm also not quite clear
about the fix. Some of the solutions/changes you've mentioned above
seem quite complex and may not be reasonable, as you pointed out. How
about introducing a new predefined role, perhaps something like
pg_admin_all, which, when granted to an admin user in the system,
would allow them to manage all non-superusers on the server?

--
With Regards,
Ashutosh Sharma.

On 1/22/25 12:07, Ashutosh Sharma wrote:

Hi Robert,

On Tue, Jan 21, 2025 at 10:22 PM Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jan 9, 2025 at 12:31 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

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.

This doesn't seem great, but it's not clear to me what we should do
about it. It doesn't really seem reasonable to me to change the role
grants that point to userB to make them point to userA instead. After
all, there could be multiple sets of role grants pointing to userB and
there could be multiple sets of role grants from userB pointing
elsewhere and they could all have different options (admin, set,
inherit). It doesn't feel right to have DROP ROLE make a bunch of
arbitrary decisions about what to do about that. We could make DROP
ROLE userB fail, perhaps, and tell the user they need to sort it out
first, but I'm not entirely sure that we have the right tools to allow
the user to do that in a convenient way. If userC were instead tableC,
DROP OWNED or REASSIGN OWNED could be used.

Thanks for sharing your thoughts and inputs. I'm also not quite clear
about the fix. Some of the solutions/changes you've mentioned above
seem quite complex and may not be reasonable, as you pointed out. How
about introducing a new predefined role, perhaps something like
pg_admin_all, which, when granted to an admin user in the system,
would allow them to manage all non-superusers on the server?

If this stopped working in PG16, then how/why did it work in PG15? Is
that intentional change?

I agree DROP ROLE shouldn't be doing decisions about which roles should
remain responsible for managing the orphaned roles. After all, if a role
has this privilege "indirectly" then why should it suddenly get it
"directly" after the intermediate role gets dropped? That would be quite
surprising/confusing,

If we could make the DROP ROLE fail if it causes some other role to
become orphaned, that'd be a solution too, I think. How difficult is it
to check that, though? I imagine we'd have to list which roles the
to-be-dropped role has ADMIN privilege on, and then check there's at
least one other role with the ADMIN privilege. I'm not very familiar
with this part of the code, so maybe I'm entirely wrong.

So it seems to me having a predefined role that allows managing all
roles (including orphaned ones) might be the good alternative. I
initially wrote "cleaner", but it feels a bit wrong to allow orphaned
roles and then have to "fix" this by having this predefined role. Not
allowing orphaned roles seems cleaner, but it's not a bug either.

FWIW I'm assuming we're not looking for a "fix" for already released
versions, right?

regards

--
Tomas Vondra

Tomas Vondra <tomas@vondra.me> writes:

So it seems to me having a predefined role that allows managing all
roles (including orphaned ones) might be the good alternative. I
initially wrote "cleaner", but it feels a bit wrong to allow orphaned
roles and then have to "fix" this by having this predefined role. Not
allowing orphaned roles seems cleaner, but it's not a bug either.

IMO, there is not any such thing as an orphaned role. You can't drop
the bootstrap superuser, and a superuser can always manage any role.

The subtext of the current discussion, as near as I can tell, is
that certain service providers don't want to give their customers
superuser, and thus those customers would prefer not to get into
situations where superuser privileges are needed to clean things up.
That's fine, but it's a poor argument for making DROP ROLE far more
complicated and non-intuitive.

That line of reasoning leads to the same conclusion, that another
built-in role might be a suitable solution --- unless said role is
so powerful that the service providers might want to block access
to it too. Probably limiting it to manage non-superuser roles is
good enough for that, but I'm not quite sure.

regards, tom lane

Hi,

On 2025-01-23 20:55:25 +0100, Tomas Vondra wrote:

If this stopped working in PG16, then how/why did it work in PG15? Is
that intentional change?

Yes, it was intentional:

Restrict the privileges of CREATEROLE and its ability to modify other roles (Robert Haas)

Previously roles with CREATEROLE privileges could change many
aspects of any non-superuser role. Such changes, including
adding members, now require the role requesting the change to
have ADMIN OPTION permission. For example, they can now change
the CREATEDB, REPLICATION, and BYPASSRLS properties only if they
also have those permissions.

If we could make the DROP ROLE fail if it causes some other role to
become orphaned, that'd be a solution too, I think. How difficult is it
to check that, though?

I don't think it should be too difficult. DropRole() already scans
pg_auth_members to see if the to-be-dropped role has members or is a
member. Doing yet another scan in case we're removing a role membership that
has admin_option set shouldn't be too hard.

One slight difficulty might be that may need to be a bit more aggressive about
locking roles, otherwise two concurrent sessions dropping two different roles
could still result in an orphaned role.

So it seems to me having a predefined role that allows managing all
roles (including orphaned ones) might be the good alternative. I
initially wrote "cleaner", but it feels a bit wrong to allow orphaned
roles and then have to "fix" this by having this predefined role. Not
allowing orphaned roles seems cleaner, but it's not a bug either.

Both seem somewhat weird in different ways. Not allowing orphaned roles has
the issue of the order of drop roles determining which role can be dropped and
which can't. An owner-of-last-resort seems somewhat dangerous to have,
e.g. dropping superusers could result in a role with superuser being granted
to the owner-of-last-resort.

I wonder if it's a mistake that a role membership that has WITH ADMIN on
another role is silently removed if the member role is removed. We e.g. do
*not* do that for pg_auth_members.grantor:

ERROR: 2BP01: role "r1" cannot be dropped because some objects depend on it
DETAIL: privileges for membership of role r2 in role r3

That's not *really* comparable, because the role membership in question isn't
being dropped, but still.

FWIW I'm assuming we're not looking for a "fix" for already released
versions, right?

I suspect that changing it in a minor version would cause trouble for another
set of users...

Greetings,

Andres Freund

On Wed, Jan 22, 2025 at 6:08 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

Thanks for sharing your thoughts and inputs. I'm also not quite clear
about the fix. Some of the solutions/changes you've mentioned above
seem quite complex and may not be reasonable, as you pointed out. How
about introducing a new predefined role, perhaps something like
pg_admin_all, which, when granted to an admin user in the system,
would allow them to manage all non-superusers on the server?

IMHO, this is a hack. Let's suppose the superuser creates roles A and
X with CREATEROLE. A creates B, who creates C. X creates Y, who
creates Z. Now A drops B. We want A to retain the ability to
administer C, but we do not want X to suddenly acquire the ability to
administer C. If A and C both had pg_admin_all, that's what would
happen.

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

On Thu, Jan 23, 2025 at 4:02 PM Robert Haas <robertmhaas@gmail.com> wrote:

On Wed, Jan 22, 2025 at 6:08 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

Thanks for sharing your thoughts and inputs. I'm also not quite clear
about the fix. Some of the solutions/changes you've mentioned above
seem quite complex and may not be reasonable, as you pointed out. How
about introducing a new predefined role, perhaps something like
pg_admin_all, which, when granted to an admin user in the system,
would allow them to manage all non-superusers on the server?

IMHO, this is a hack. Let's suppose the superuser creates roles A and
X with CREATEROLE. A creates B, who creates C. X creates Y, who
creates Z. Now A drops B. We want A to retain the ability to
administer C, but we do not want X to suddenly acquire the ability to
administer C. If A and C both had pg_admin_all, that's what would
happen.

Sorry, correction: if A and X both had pg_admin_all, that's what would happen.

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

On Thu, Jan 23, 2025 at 3:51 PM Andres Freund <andres@anarazel.de> wrote:

I wonder if it's a mistake that a role membership that has WITH ADMIN on
another role is silently removed if the member role is removed. We e.g. do
*not* do that for pg_auth_members.grantor:

ERROR: 2BP01: role "r1" cannot be dropped because some objects depend on it
DETAIL: privileges for membership of role r2 in role r3

Yeah, I'm not sure about this either, but this is the kind of thing I
was thinking about when I replied before, saying that maybe dropping
role B shouldn't just succeed. Maybe dropping a role that doesn't have
privileges to administer any other role should be different than
dropping one that does.

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

On 2025-01-23 Th 4:06 PM, Robert Haas wrote:

On Thu, Jan 23, 2025 at 3:51 PM Andres Freund <andres@anarazel.de> wrote:

I wonder if it's a mistake that a role membership that has WITH ADMIN on
another role is silently removed if the member role is removed. We e.g. do
*not* do that for pg_auth_members.grantor:

ERROR: 2BP01: role "r1" cannot be dropped because some objects depend on it
DETAIL: privileges for membership of role r2 in role r3

Yeah, I'm not sure about this either, but this is the kind of thing I
was thinking about when I replied before, saying that maybe dropping
role B shouldn't just succeed. Maybe dropping a role that doesn't have
privileges to administer any other role should be different than
dropping one that does.

That seems reasonable and consistent with what we do elsewhere, as
Andres noted.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On Fri, Jan 24, 2025 at 09:53:09AM -0500, Andrew Dunstan wrote:

On 2025-01-23 Th 4:06 PM, Robert Haas wrote:

On Thu, Jan 23, 2025 at 3:51 PM Andres Freund <andres@anarazel.de> wrote:

I wonder if it's a mistake that a role membership that has WITH ADMIN on
another role is silently removed if the member role is removed. We e.g. do
*not* do that for pg_auth_members.grantor:

ERROR: 2BP01: role "r1" cannot be dropped because some objects depend on it
DETAIL: privileges for membership of role r2 in role r3

Yeah, I'm not sure about this either, but this is the kind of thing I
was thinking about when I replied before, saying that maybe dropping
role B shouldn't just succeed. Maybe dropping a role that doesn't have
privileges to administer any other role should be different than
dropping one that does.

That seems reasonable and consistent with what we do elsewhere, as Andres
noted.

+1, if this is doable, I would prefer that over a new predefined role. A
pg_admin_all role might still be useful, but IMHO it's a rather big hammer
for this particular problem.

--
nathan

On Thu, Jan 23, 2025 at 03:10:16PM -0500, Tom Lane wrote:

That line of reasoning leads to the same conclusion, that another
built-in role might be a suitable solution --- unless said role is
so powerful that the service providers might want to block access
to it too. Probably limiting it to manage non-superuser roles is
good enough for that, but I'm not quite sure.

IMHO it's reasonable to expect service providers to adjust the predefined
roles if the stock limitations are not sufficient for them.

--
nathan

On Fri, Jan 24, 2025 at 8:23 PM Andrew Dunstan <andrew@dunslane.net> wrote:

On 2025-01-23 Th 4:06 PM, Robert Haas wrote:

On Thu, Jan 23, 2025 at 3:51 PM Andres Freund <andres@anarazel.de> wrote:

I wonder if it's a mistake that a role membership that has WITH ADMIN on
another role is silently removed if the member role is removed. We e.g. do
*not* do that for pg_auth_members.grantor:

ERROR: 2BP01: role "r1" cannot be dropped because some objects depend on it
DETAIL: privileges for membership of role r2 in role r3

Yeah, I'm not sure about this either, but this is the kind of thing I
was thinking about when I replied before, saying that maybe dropping
role B shouldn't just succeed. Maybe dropping a role that doesn't have
privileges to administer any other role should be different than
dropping one that does.

That seems reasonable and consistent with what we do elsewhere, as
Andres noted.

Thank you all for your valuable inputs and suggestions. Based on the
consensus, we will move forward with this solution. I'll start working
on the coding part and share the patch for review by next week.

--
With Regards,
Ashutosh Sharma.

Hi All,

On Fri, Jan 24, 2025 at 9:34 PM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

On Fri, Jan 24, 2025 at 8:23 PM Andrew Dunstan <andrew@dunslane.net> wrote:

On 2025-01-23 Th 4:06 PM, Robert Haas wrote:

On Thu, Jan 23, 2025 at 3:51 PM Andres Freund <andres@anarazel.de> wrote:

I wonder if it's a mistake that a role membership that has WITH ADMIN on
another role is silently removed if the member role is removed. We e.g. do
*not* do that for pg_auth_members.grantor:

ERROR: 2BP01: role "r1" cannot be dropped because some objects depend on it
DETAIL: privileges for membership of role r2 in role r3

Yeah, I'm not sure about this either, but this is the kind of thing I
was thinking about when I replied before, saying that maybe dropping
role B shouldn't just succeed. Maybe dropping a role that doesn't have
privileges to administer any other role should be different than
dropping one that does.

That seems reasonable and consistent with what we do elsewhere, as
Andres noted.

Thank you all for your valuable inputs and suggestions. Based on the
consensus, we will move forward with this solution. I'll start working
on the coding part and share the patch for review by next week.

PFA WIP patch, the approach it follows is outlined below:

When a new role is created, the patch identifies all members of the
creator role (the role attempting to create the new role) who have the
admin option enabled on the creator role. If such members are found, a
shared dependency is added between the member role and the creator
role in pg_shdepend. Let me explain this with an example:

Imagine a superuser creates role u1. Since the superuser is creating
u1, it won't have membership in any role. Now, suppose u1 creates a
new role, u2. In this case, u1 automatically becomes a member of u2
with the admin option. However, at this point, there is no dependency
between u1 and u2, meaning that dropping u2 shouldn't impact u1. Now,
if u2 creates yet another role, u3, that's when u1 will start
depending on u2. This is because if u2 were dropped, u1 would lose the
ability to administer u3. At this stage, a dependency between u1 and
u2 is recorded.

To address this, the patch ensures that when a new role is created, it
identifies all members of the creator role who have the admin option.
If such members exist (e.g., u1 in this case), a dependency is
recorded between the member and the creator role (in this case, u1 and
u2). This ensures that when someone attempts to drop a role (e.g.,
u2), the shared dependencies are checked before the role can be
removed. Please take a look at the attached patch and let me know any
comments/feedback/thoughts.

Thanks,

--
With Regards,
Ashutosh Sharma.

On Thu, Jan 30, 2025 at 8:45 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

Imagine a superuser creates role u1. Since the superuser is creating
u1, it won't have membership in any role. Now, suppose u1 creates a
new role, u2. In this case, u1 automatically becomes a member of u2
with the admin option. However, at this point, there is no dependency
between u1 and u2, meaning that dropping u2 shouldn't impact u1. Now,
if u2 creates yet another role, u3, that's when u1 will start
depending on u2. This is because if u2 were dropped, u1 would lose the
ability to administer u3. At this stage, a dependency between u1 and
u2 is recorded.

This seems to me to be assuming that who can administer which roles
won't change, but it can. The superuser is free to grant the ability
to administer u3 to some new user u4 or an existing user such as u1,
and is also free to remove that ability from u2.

I think if you want to legislate that attempting to drop u2 fails, you
have to do that by testing what the situation is at the time of the
drop command, not by adding dependencies in advance.

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

Hi Robert,

On Tue, Feb 4, 2025 at 10:54 PM Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jan 30, 2025 at 8:45 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

Imagine a superuser creates role u1. Since the superuser is creating
u1, it won't have membership in any role. Now, suppose u1 creates a
new role, u2. In this case, u1 automatically becomes a member of u2
with the admin option. However, at this point, there is no dependency
between u1 and u2, meaning that dropping u2 shouldn't impact u1. Now,
if u2 creates yet another role, u3, that's when u1 will start
depending on u2. This is because if u2 were dropped, u1 would lose the
ability to administer u3. At this stage, a dependency between u1 and
u2 is recorded.

This seems to me to be assuming that who can administer which roles
won't change, but it can. The superuser is free to grant the ability
to administer u3 to some new user u4 or an existing user such as u1,
and is also free to remove that ability from u2.

You're right, I'll fix that in the next patch.

I think if you want to legislate that attempting to drop u2 fails, you
have to do that by testing what the situation is at the time of the
drop command, not by adding dependencies in advance.

I agree, and I will give this some thought to see if we can find a way
forward along these lines.

--
Thanks & Regards,
Ashutosh Sharma.

Hi Robert,

On Tue, Feb 11, 2025 at 9:48 PM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

Hi Robert,

On Tue, Feb 4, 2025 at 10:54 PM Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jan 30, 2025 at 8:45 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

Imagine a superuser creates role u1. Since the superuser is creating
u1, it won't have membership in any role. Now, suppose u1 creates a
new role, u2. In this case, u1 automatically becomes a member of u2
with the admin option. However, at this point, there is no dependency
between u1 and u2, meaning that dropping u2 shouldn't impact u1. Now,
if u2 creates yet another role, u3, that's when u1 will start
depending on u2. This is because if u2 were dropped, u1 would lose the
ability to administer u3. At this stage, a dependency between u1 and
u2 is recorded.

This seems to me to be assuming that who can administer which roles
won't change, but it can. The superuser is free to grant the ability
to administer u3 to some new user u4 or an existing user such as u1,
and is also free to remove that ability from u2.

You're right, I'll fix that in the next patch.

I think if you want to legislate that attempting to drop u2 fails, you
have to do that by testing what the situation is at the time of the
drop command, not by adding dependencies in advance.

I agree, and I will give this some thought to see if we can find a way
forward along these lines.

Attached is a patch that checks for role dependencies when the DROP
ROLE command is executed. If dependencies are found, the command is
prevented from succeeding. Please review the attached patch and share
your feedback. thanks.!

--
With Regards,
Ashutosh Sharma.

Added a commitfest entry for this here:

https://commitfest.postgresql.org/patch/5608/

--
With Regards,
Ashutosh Sharma.

On Tue, Feb 18, 2025 at 02:54:46PM +0530, Ashutosh Sharma wrote:

Attached is a patch that checks for role dependencies when the DROP
ROLE command is executed. If dependencies are found, the command is
prevented from succeeding. Please review the attached patch and share
your feedback. thanks.!

Thanks for the patch. I have two questions:

* The patch alleges to only block DROP ROLE commands when there exists
_both_ admins of the target role and roles for which the target role is
an admin. However, it's not clear to me why both need to be true. I
might be able to glean the reason if I read this thread carefully or
spend more time thinking about it, but IMHO that patch itself should make
it obvious. I'd suggest expanding the comment atop
check_drop_role_dependency().

* Does this introduce any race conditions? For example, is it possible for
the new check to pass and then for a dependency to be added before the
drop completes?

--
nathan