psql \du and \dg is the same - pg 8.4

From: ANdreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: psql \du and \dg is the same - pg 8.4
Date: 2009-07-21 19:44:52
Message-ID: 4A661AB4.70209@netzmeister-st-pauli.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2009-07-21 19:52:48 Re: WIP patch for TODO Item: Add prompt escape to display the client and server versions
Previous Message Alexey Klyukin 2009-07-21 17:54:29 Re: errcontext support in PL/Perl