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

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #18183: Unable to grant similar roles after changing user from No inheritance to Inheritance.
Дата
Msg-id 18183-2d377c717b5cfcce@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #18183: Unable to grant similar roles after changing user from No inheritance to Inheritance.  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: BUG #18183: Unable to grant similar roles after changing user from No inheritance to Inheritance.  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      18183
Logged by:          Riyaz Khan
Email address:      riyaz489.rk@gmail.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.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Magnus Hagander
Дата:
Сообщение: Re: BUG #18182: apt distribution is missing for Bionic
Следующее
От: PG Bug reporting form
Дата:
Сообщение: BUG #18184: ERROR: wrong varnullingrels (b) (expected (b 3)) for Var 2/2