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. 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
