I've done up a patch per Tom's idea of combining the binary role
attributes into a single column.
Each attribute which differs from the default is listed on a separate
line, like so:
List of roles
Role name | Attributes | Member of
-------------+----------------+-------------------
bob | | {readers,writers}
brendanjurd | Superuser | {}
: Create role
: Create DB
harry | No inherit | {}
jim | 10 connections | {readers}
readers | No login | {}
writers | No login | {}
(6 rows)
Notes:
* The patch relies on array_to_string's current treatment of NULL
values in the array; they are ignored. If that behaviour changes in
the future, the \du output will become very ugly indeed.
* I'm not sure whether "No login" and "No inherit" are the best
phrases to use. I took my cue from the SQL setting names NOLOGIN and
NOINHERIT, but maybe something more grammatically sensible with
"Cannot login" and "No inheritance" would be preferable.
* If accepted, this patch would supercede the earlier patch mentioned
by Bernd Helmle upthread, which adds LOGIN to the output as a new
column: http://archives.postgresql.org/pgsql-patches/2007-11/msg00014.php
Cheers,
BJ
*** src/bin/psql/describe.c
--- src/bin/psql/describe.c
***************
*** 1611,1638 **** describeRoles(const char *pattern, bool verbose)
PQExpBufferData buf;
PGresult *res;
printQueryOpt myopt = pset.popt;
! static const bool trans_columns[] = {false, true, true, true, true,
false, false};
initPQExpBuffer(&buf);
printfPQExpBuffer(&buf,
"SELECT r.rolname AS \"%s\",\n"
! " CASE WHEN r.rolsuper THEN '%s' ELSE '%s' END
AS \"%s\",\n"
! " CASE WHEN r.rolcreaterole THEN '%s' ELSE '%s' END AS
\"%s\",\n"
! " CASE WHEN r.rolcreatedb THEN '%s' ELSE '%s' END AS
\"%s\",\n"
! " CASE WHEN r.rolconnlimit < 0 THEN CAST('%s' AS
pg_catalog.text)\n"
! " ELSE CAST(r.rolconnlimit AS
pg_catalog.text)\n"
! " END AS \"%s\", \n"
" 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 \"%s\"",
gettext_noop("Role name"),
- gettext_noop("yes"),
gettext_noop("no"),
gettext_noop("Superuser"),
! gettext_noop("yes"),
gettext_noop("no"),
gettext_noop("Create role"),
- gettext_noop("yes"),
gettext_noop("no"),
gettext_noop("Create DB"),
! gettext_noop("no limit"),
! gettext_noop("Connections"),
gettext_noop("Member of"));
if (verbose)
--- 1611,1639 ----
PQExpBufferData buf;
PGresult *res;
printQueryOpt myopt = pset.popt;
! static const bool trans_columns[] = {false, true, false, false};
initPQExpBuffer(&buf);
printfPQExpBuffer(&buf,
"SELECT r.rolname AS \"%s\",\n"
! " array_to_string(ARRAY[\n"
! " CASE WHEN r.rolsuper THEN '%s' ELSE NULL
END,\n"
! " CASE WHEN NOT r.rolinherit THEN '%s' ELSE NULL END,\n"
! " CASE WHEN r.rolcreaterole THEN '%s' ELSE NULL END,\n"
! " CASE WHEN r.rolcreatedb THEN '%s' ELSE NULL
END,\n"
! " CASE WHEN NOT r.rolcanlogin THEN '%s' ELSE NULL END,\n"
! " CASE WHEN r.rolconnlimit >= 0 THEN
r.rolconnlimit::pg_catalog.text || ' %s' ELSE NULL END\n"
! " ], chr(10)) AS \"%s\",\n"
" 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 \"%s\"",
gettext_noop("Role name"),
gettext_noop("Superuser"),
! gettext_noop("No inherit"),
gettext_noop("Create role"),
gettext_noop("Create DB"),
! gettext_noop("No login"),
! gettext_noop("connections"),
! gettext_noop("Attributes"),
gettext_noop("Member of"));
if (verbose)
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
