public inbox for [email protected]  

help / color / mirror / Atom feed

From: Norbert Poellmann <[email protected]>
To: [email protected]
Subject: GRANT and predefined role
Date: Thu, 7 Nov 2024 14:55:30 +0100
Message-ID: <[email protected]> (raw)

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  : [email protected]
Severinstrasse 5                           telefon: +49 89 38469995  
81541 Muenchen, Germany                    telefon: +49 179 2133436 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: GRANT and predefined role
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox