Wednesday, November 17, 2004

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.

1 Comments:

Blogger Souzace said...

Relating tables for Schemes

select nspname,tablename from pg_tables, pg_namespace where nspname not similar
to '%(pg_|information_schema)%' and nspname = schemaname;

26 October, 2009 13:12  

Post a Comment

<< Home