psql \du and \dg is the same - pg 8.4
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(SELECT b.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(SELECT b.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(SELECT b.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
ANdreas Wenk <a.wenk@netzmeister-st-pauli.de> writes:
actually I discovered that using \du and \dg in psql is providing the
same result:
Yup. The psql documentation says as much.
I am wondering why there is \dg at all.
Users and groups used to be distinct kinds of objects. They aren't
anymore, but people might still be used to the old commands.
regards, tom lane
Tom Lane wrote:
ANdreas Wenk <a.wenk@netzmeister-st-pauli.de> writes:
actually I discovered that using \du and \dg in psql is providing the
same result:Yup. The psql documentation says as much.
ok - got it ;-)
Should have read the psql docu ...
I am wondering why there is \dg at all.
Users and groups used to be distinct kinds of objects. They aren't
anymore, but people might still be used to the old commands.
Yeah I know - but I was wondering if it would make sense to have \dg
with a different result because here
http://www.postgresql.org/docs/8.4/interactive/role-membership.html
the people still read about group roles. I am not sure what is confusing
here - in psql or in the docu.
regards, tom lane
Cheers
Andy
Cheers
Andy