Postgresql: list users and groups

Users and groups are stored in 2 different tables (in views, really, I think): pg_user and pg_group. For some reason, I had a hard time today to find how to combine them.

This post suggesting

SELECT groname,usename from pg_group,pg_user where usesysid = any(grolist);

looked promising, but it didn't work for me. Probably requires a later Postgresql version.

pg_group lists the user id's in each group. Besides being id's and not names, the problem is that they are in an array field. To get a list of groups with the users they contain, or a list of users with the groups they are a member of, this is what I needed with my postgres 7.2:

Array operators

We need the array operator *= which may not be installed by default. It can be found in the contrib directory of the source.

With this, we can write things like

SELECT groname from pg_group WHERE grolist *= 100;

to list group names of which user 100 is a member.

List aggregate

then we need a function and an aggregate:

CREATE OR REPLACE FUNCTION comma_cat(text, text) RETURNS text AS
'SELECT CASE
 WHEN $2 is null OR $2 = '''' THEN $1
 WHEN $1 is null OR $1 = '''' THEN $2
 ELSE $1 || '', '' || $2
END'
LANGUAGE 'sql';

DROP AGGREGATE list text;
CREATE AGGREGATE list(
 sfunc1=comma_cat,
 basetype=text,
 stype1=text,
 initcond1=''
);

Now we can do things like:

SELECT g.groname, list(u.usename) FROM pg_user u, pg_group g
WHERE g.grolist *= u.usesysid GROUP BY g.groname;

SELECT u.usename, list(g.groname) FROM pg_user u, pg_group g
WHERE g.grolist *= u.usesysid GROUP BY u.usename;

etc.