From: | Muhammad Imtiaz <imtiazpg712(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Role Granting Issues in PostgreSQL: Need Help |
Date: | 2024-09-05 04:05:05 |
Message-ID: | CACW1Ly01_qc4fC8huQAiQRSyhwzXGac6x6eU+m9s-+B-T56VRA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Junwang Zhao | 2024-09-05 04:06:46 | Re: BUG #18598: AddressSanitizer detects use after free inside json_unique_hash_match() |
Previous Message | Amit Kapila | 2024-09-05 04:00:16 | Re: Introduce XID age and inactive timeout based replication slot invalidation |