Hi,
actually I discovered that using \du and \dg in psql is providing the
same result:
book=# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolconnlimit,
ARRAY(SELECTb.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
ORDER BY 1;
**************************
List of roles Role name | Attributes | Member of
---------------+--------------+----------- postgres | Superuser | {} : Create role
:Create DB ps_buch_group | | {} psbuch | | {} psbuch_role | Cannot login | {}
roundcube | | {}
book=# \dg
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolconnlimit,
ARRAY(SELECTb.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
ORDER BY 1;
**************************
List of roles Role name | Attributes | Member of
---------------+--------------+----------- postgres | Superuser | {} : Create role
:Create DB ps_buch_group | | {} psbuch | | {} psbuch_role | Cannot login | {}
roundcube | | {}
Commonly a grouprole is defined as a role with no right to login. As of
this, the following statement should list all grouproles:
SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolconnlimit,
ARRAY(SELECTb.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE r.rolcanlogin = 'f'
ORDER BY 1;
rolname |rolsuper|rolinherit|rolcreaterole|rolcreatedb|rolcanlogin|
rolconnlimit|memberof
-----------+--------+----------+-------------+-----------+-----------+
-----------+---------
psbuch_role| f | t | f | f | f | -1 | {}
(1 row)
On the other hand a group role can also have the login privilege as of
all roles can have members or not with the privilege login or not.
I am wondering why there is \dg at all. I am not sure what the intention
is to have it. And also I am not sure if the definition of a group role
(having no login privilege) is really correct.
Any ideas on this? If there is a clear solution and the implementation
of \dg is wanted but not correctly implemented, I could try to provide a
patch.
By the way. It's also possible to use \dg+ . This is missing in the psql
help (\dg[+]) in the same way as for \du (see my small patch).
If I missed something please lend me a hand to the right way.
Cheers
Andy