Thread: Role Granting Issues in PostgreSQL: Need Help

I need to assign role permissions from one role to another. However, after granting the role, I see that the permission list for the target role has not been updated. For this process, I followed the PostgreSQL documentation available at PostgreSQL Role Membership. Please let me know if I've missed anything.

I am using PostgreSQL version 16 and I have followed these steps.

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit
(1 row)

1. Create a role with specific permissions

CREATE ROLE rep_admin WITH LOGIN CREATEDB CREATEROLE REPLICATION;

2.Create another role named replication_expert:

CREATE ROLE replication_expert;

3.Grant the rep_admin role to the replication_expert role with inheritance:

 GRANT rep_admin TO replication_expert with INHERIT true;
GRANT ROLE

4.Attempt to log in using the replication_expert role:

postgres=# \c postgres replication_expert
connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "replication_expert" is not permitted to log in

5.Check the role attributes to see if they have been reflected:

postgres=# \du+
                                         List of roles
     Role name      |                         Attributes                         | Description
--------------------+------------------------------------------------------------+-------------
 postgres           | Superuser, Create role, Create DB, Replication, Bypass RLS |
 rep_admin          | Create role, Create DB, Replication                        |
 replication_expert | Cannot login  

6.Examine the pg_roles table to confirm that the permissions for replication_expert have not been updated:

postgres=# SELECT rolname,rolinherit, rolcreaterole, rolcreatedb, rolcanlogin,rolreplication
FROM pg_roles where rolname in('rep_admin','replication_expert');;
      rolname       | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication
--------------------+------------+---------------+-------------+-------------+----------------
 rep_admin          | t          | t             | t           | t           | t
 replication_expert | t          | f             | f           | f           | f
(2 rows)

postgres=#

Regards,

Muhammad Imtiaz