Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group