Role Granting Issues in PostgreSQL: Need Help

[Muhammad Imtiaz]

Hi,

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
<https://www.postgresql.org/docs/current/role-membership.html>. 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