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@xxxxxx
Severinstrasse 5 telefon: +49 89 38469995
81541 Muenchen, Germany telefon: +49 179 2133436
