Date: Thu, 7 Nov 2024 14:55:30 +0100
From: Norbert Poellmann <np@ibu.de>
To: pgsql-admin@postgresql.org
Subject: GRANT and predefined role
Message-ID: <ZyzG0g9JNxM2Wku1@mail.ibu.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
"From: np@ibu.de"
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/ZyzG0g9JNxM2Wku1%40mail.ibu.de>
Precedence: bulk

Admins,

a strange situation with grants and predefined roles:

In postgresql server v14.x.

First I, as superuser, do it correctly.  I will assign a predefined role to 
some normal user (here 'homer'):

1. start with:
------------------

postgres=# select g.groname, array_agg(r.oid) as role_id, array_agg(r.rolname) as role_name 
     from pg_roles r join pg_group g on r.oid=any(g.grolist)  
     where g.groname in ('pg_read_all_data', 'homer') group by g.groname;
     groname      |    role_id    |      role_name
------------------+---------------+---------------------
 pg_read_all_data | {16390,37943} | {norbert,jra1_e_ro}


2. Then assign the predefined role pg_read_all_data to homer:
---------------------------------------------------------
postgres=# grant pg_read_all_data to homer;
GRANT ROLE


3.  We get (same query as in (1.):
-----------------------------------

     groname      |    role_id    |      role_name
------------------+---------------+---------------------
 pg_read_all_data | {16390,37943} | {norbert,homer,jra1_e_ro}

-- FINE, that's what is to be expected.

 
4. revoke the predefined role pg_read_all_data from homer:
postgres=# revoke pg_read_all_data from homer;
REVOKE ROLE


5. State is the original current state again. Everything still fine.


6. Now make some admin mistake by swapping names:
------------------------------------------------

postgres=# grant homer to pg_read_all_data;
GRANT ROLE
-- no error!


7. Same query as in (1.): No visible effect of statement (6.)

     groname      |    role_id    |      role_name
------------------+---------------+---------------------
 pg_read_all_data | {16390,37943} | {norbert,jra1_e_ro}


As far as I can tell, there is no chance to make 
the role assigned from (6.)  visible (for example by pg_roles, \du, \dg)

We now have seem to have  a role 'pg_read_all_data', 
which is somehow a "child" of role 'homer'.

The only way to make the strange role assignment visible, 
is to re-apply the correct order:

postgres=# grant pg_read_all_data to homer;
ERROR:  role "pg_read_all_data" is a member of role "homer"

-- fix it:
postgres=# revoke homer from pg_read_all_data;
REVOKE ROLE

-- apply the correct statement:
postgres=# grant pg_read_all_data to homer;
GRANT ROLE


-- check it, query from (1):

     groname      |       role_id       |         role_name
------------------+---------------------+---------------------------
 pg_read_all_data | {16390,16431,37943} | {norbert,homer,jra1_e_ro}


So, my question is: Some mistakenly given GRANT like in Step (6.) - shouldn't postgresql throw an error,
if one assigns a user to a predefined role? Or is it just superuser's freedom, to do strange things?

Thanks,

cheers

Norbert Poellmann

--
Norbert Poellmann EDV-Beratung             email  : np@ibu.de
Severinstrasse 5                           telefon: +49 89 38469995  
81541 Muenchen, Germany                    telefon: +49 179 2133436