From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: riyaz489.rk@gmail.com
Subject: BUG #18183: Unable to grant similar roles after changing user from No inheritance to Inheritance.
Date: Mon, 06 Nov 2023 10:30:49 +0000
Message-ID: <18183-2d377c717b5cfcce@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, riyaz489.rk@gmail.com
Subject: Re: BUG #18183: Unable to grant similar roles after changing user from No inheritance to Inheritance.
In-Reply-To: <18183-2d377c717b5cfcce@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox