User/group association

I'm working on a users view for my database (what I have so far can be found
below), and I was wondering how postgres associates users with groups, as
there is no grosysid column in pg_user. I would like to display information
like so:

Username: Primary Group: Other Groups:
-----------------------------------------------------------------------
cshobe Administrators Security Operations, Development
[etc.]

See the question marks below for an in-context request.

Any help is greatly appreciated,

- Casey

create view "users" as
select ps."usesysid" as "UID",
ps."usename" as "Username",
ps."passwd" as "Password",
coalesce (
(
select cast (p."position_name" as varchar)
from only "positions" as "p"
where p."position_id" = (
select cast (ui."info_value" as integer)
from only "user_info" as "ui"
inner join only "user_info_types" as "uit"
on uit."type_id" = ui."info_type_id"
where uit."info_type" = 'Position ID'
and uit."info_type_name" = 'Default'
and ui."user_id" = ps."usesysid"
)
),
'Undefined'
) as "Position",
coalesce (
(
select cast (pg."groname" as varchar)
from only "pg_group" as "pg"
where pg."grosysid" = (
????????where do I get this from?????????
)
),
'Undefined'
) as "Group",
coalesce (
(
select cast (ui."info_value" as integer)
from only "user_info" as "ui"
inner join only "user_info_types" as "uit"
on uit."type_id" = ui."info_type_id"
where uit.info_type = 'Creator ID'
and uit.info_type_name = 'Default'
and ui."user_id" = ps."usesysid"
),
0
) as "Creator ID",
coalesce (
(
select cast (ui."info_value" as varchar)
from only "user_info" as "ui"
inner join only "user_info_types" as "uit"
on uit."type_id" = ui."info_type_id"
where uit.info_type = 'E-Mail'
and uit.info_type_name = 'Work'
and ui."user_id" = ps."usesysid"
),
'None'
) as "Work E-Mail Address",
coalesce (
(
select cast (ui."info_value" as varchar)
from only "user_info" as "ui"
inner join only "user_info_types" as "uit"
on uit."type_id" = ui."info_type_id"
where uit.info_type = 'Extension'
and uit.info_type_name = 'Work'
and ui."user_id" = ps."usesysid"
),
'None'
) as "Extension",
coalesce (
(
select cast (ui."info_value" as varchar)
from only "user_info" as "ui"
inner join only "user_info_types" as "uit"
on uit."type_id" = ui."info_type_id"
where uit.info_type = 'Telephone'
and uit.info_type_name = 'Home'
and ui."user_id" = ps."usesysid"
),
'None'
) as "Home Telephone",
coalesce (
(
select cast (ui."info_value" as varchar)
from only "user_info" as "ui"
inner join only "user_info_types" as "uit"
on uit."type_id" = ui."info_type_id"
where uit.info_type = 'Telephone'
and uit.info_type_name = 'Mobile'
and ui."user_id" = ps."usesysid"
),
'None'
) as "Mobile Telephone"
from only "pg_shadow" as "ps"
where ps."usesysid" > 100
order by "UID";

--
Casey Allen Shobe / Network Security Analyst & PHP Developer
SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144
cshobe(at)secureworks(dot)net / http://www.secureworks.net
Content is my own and does not necessarily represent my company.