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

Hi Nathan,

On Mon, Mar 10, 2025 at 8:31 PM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
>
> On Mon, Mar 10, 2025 at 11:15:04AM +0530, Ashutosh Sharma wrote:
> > On Fri, Mar 7, 2025 at 10:55 PM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
> >> I noticed that much of this code is lifted from DropRole(), and the new
> >> check_drop_role_dependency() function is only used by DropRole() right
> >> before it does the exact same scans. Couldn't we put the new dependency
> >> detection in those existing scans in DropRole()?
> >
> > It can be done, but mixing the code that checks for the drop role
> > dependency with the code that removes entries for the role being
> > dropped from pg_auth_members could reduce clarity and precision. This
> > is more of a sanity check which I felt was necessary before we proceed
> > with actually dropping the role, starting with the deletion of drop
> > role entries from the system catalogs. I’m aware there’s some code
> > duplication, but I think it should be fine.
>
> Looking closer, we probably need to move this check to the second pass,
> anyway:
>

I think moving the check to the second pass won’t work in this case.
The reason is that we rely on entries in the pg_auth_members table. By
the time the check occurs in the second pass, the first pass will have
already removed all entries related to the roles being dropped from
pg_auth_members and incremented the command counter. As a result, when
check_drop_role_dependency() is called in the second pass, it won’t
find any entries in the table and won't be able to detect any ADMIN
privilege-related dependencies. Let me illustrate this with an example
(similar to yours, but with b creating an additional role d):

CREATE ROLE a CREATEROLE;
SET ROLE a;
CREATE ROLE b CREATEROLE;
SET ROLE b;
CREATE ROLE c;
CREATE ROLE d;
RESET ROLE;

At this point, the pg_auth_members table will contain the following entries:

ashu(at)postgres=# SELECT oid, roleid::regrole, member::regrole,
grantor::regrole, admin_option, xmin, xmax FROM pg_auth_members WHERE
roleid::regrole::text NOT LIKE 'pg_%';
oid | roleid | member | grantor | admin_option | xmin | xmax
-------+--------+--------+---------+--------------+------+------
16394 | b | a | ashu | t | 756 | 0
16396 | c | b | ashu | t | 757 | 0
16398 | d | b | ashu | t | 758 | 0
(3 rows)

Now, when we run DROP ROLE b, c, the first pass in DropRole() will
remove all the entries from pg_auth_members for these roles, as
expected. So when check_drop_role_dependency() is called in the second
pass, it won’t find any entries in the table, and thus won’t identify
the dependency of role 'a' on role 'b' for role 'd'. As a result, the
drop would succeed, even though it should fail due to the dependency.

So, we need to explore alternative approaches to handle this better.
I’ll spend some more time today to investigate other possibilities for
addressing this issue.

--
With Regards,
Ashutosh Sharma.