Thread: mapping user name to group name

Hey guys... first time caller, long time listener.

Don't ask me why, but I would like a quick way to map a user name to the
group name that the user is part of. Using pg_user and pg_group makes
this a little tricky. You can obtain usesysid from pg_user, and then get
the groname from pg_group based on it. However each group has its own
usesysids within an array of integers. I have no clue how to search
within an array using a select statement.

Am I making it harder than it should be? Is there a sys table already
out there that is doing this mapping for me? How do I do this (with
minimal hastle)

thanks much
matthew

On Monday 10 Mar 2003 5:47 pm, Matthew Phillips wrote:
> Hey guys... first time caller, long time listener.
>
> Don't ask me why, but I would like a quick way to map a user name to the
> group name that the user is part of. Using pg_user and pg_group makes
> this a little tricky. You can obtain usesysid from pg_user, and then get
> the groname from pg_group based on it. However each group has its own
> usesysids within an array of integers. I have no clue how to search
> within an array using a select statement.

By default - you don't.

> Am I making it harder than it should be? Is there a sys table already
> out there that is doing this mapping for me? How do I do this (with
> minimal hastle)

Nope - PG is making it harder than it should be. Having said that there are
some array-handling functions in contrib/intarray (sorry - don't know what,
never used them).

Also, I'm sure this must have been dealt with before. I'd recommend checking
techdocs.postgresql.org and especially Roberto's plpgsql cookbook. If that
comes up with nothing, it shouldn't be too difficult to write a small plpgsql
function to do what you want. If you're not familiar with plpgsql get back to
me and I'll run something up for you.

--
  Richard Huxton

> On Monday 10 Mar 2003 5:47 pm, Matthew Phillips wrote:
>>
>> to map a user name to
>> the group name
>> has its own usesysids within an array of integers. I have no clue how
>> to search within an array using a select statement.
>
> there
> are  some array-handling functions in contrib/intarray
>


SELECT
 pg_group.groname,
 pg_user.usename
FROM pg_user, pg_group
WHERE (pg_group.grolist *= pg_user.usesysid)
ORDER BY pg_group.groname, pg_user.usename;

works for me, after installing the array-handing functions referred to by
Huxton.

~Berend Tober