BUG #18183: Unable to grant similar roles after changing user from No inheritance to Inheritance.

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: riyaz489(dot)rk(at)gmail(dot)com
Subject: BUG #18183: Unable to grant similar roles after changing user from No inheritance to Inheritance.
Date: 2023-11-06 10:30:49
Message-ID: 18183-2d377c717b5cfcce@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 18183
Logged by: Riyaz Khan
Email address: riyaz489(dot)rk(at)gmail(dot)com
PostgreSQL version: 16.0
Operating system: Windows
Description:

DB_name = test

I have created a new user 't1' and Now I want to grant all permission of my
existing user 'test' to this new user 't1'.
To do So I ran the mentioned command:
grant test to t1;

but even after that, I noticed 't1' didn't get any required permissions.
So After that, I ran \du command and checked roles table to verify t1
permissions, and I got this output

List of roles
Role name | Attributes
-----------+------------------------------------------------------------
t1 | No inheritance
test | Superuser, Create role, Create DB, Replication, Bypass RLS

test=# select rolname from pg_user join pg_auth_members on
(pg_user.usesysid=pg_auth_members.member) join pg_roles on
(pg_roles.oid=pg_auth_members.roleid) where pg_user.usename='t1';

rolname
---------
test

After this, I ran the below command to provide inherit access to user t1;

test=# alter user t1 with inherit;
ALTER ROLE

this command successfully ran and non inherit was removed.

test=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
t1 |
test | Superuser, Create role, Create DB, Replication, Bypass RLS

test=# select rolname from pg_user join pg_auth_members on
(pg_user.usesysid=pg_auth_members.member) join pg_roles on
(pg_roles.oid=pg_auth_members.roleid) where pg_user.usename='t1';
rolname
---------
test

So as you can notice now 't1' has inherit access and it is already a member
of 'test' role.
Even if I try to grant command again I get this warning:

test=# grant test to t1;
NOTICE: role "t1" has already been granted membership in role "test" by
role "test"
GRANT ROLE

That means now 't1' user has all the permission the 'test' user has.

but still when logged in with 't1' user and tries to create table in same
database.
I got this error:
test=> create table t1 (id int);
ERROR: permission denied for schema public.

So to resolve this I found 2 solutions :
1. delete the user and create a new user with the inherit option and then
grant all privileges.
2. provide inherit privilege to 't1' user. then revoke the 'test' role from
't1' and assign it again.

But Ideally, It should be able to use the role which is already assigned to
it after giving inherit access.
Instead of explicitly removing and adding the same roles again.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2023-11-06 12:54:30 BUG #18184: ERROR: wrong varnullingrels (b) (expected (b 3)) for Var 2/2
Previous Message Magnus Hagander 2023-11-06 10:06:33 Re: BUG #18182: apt distribution is missing for Bionic