Thread: Help with a query

Hi,

Im trying to get a list of all groups that a certain user is a member
of. Can anyone help me with the sql to get that?

thanks!


--
Alexander Cohen
http://www.toomuchspace.com
(819) 348-9237
(819) 432-3443

Hi,

Without further information, this is rather difficult, but assuming that you
have a table with userdata with a primary key UID and a unique UserName, and
that you have a table of usergroups with a primary key GID and unique
GroupName, the best way is to make a third table Users_Usergroups e.g.
where you put the couples (UIDX, GIDX) for a user with UID=UIDX and who is in
group GIDX.
The query would then be:
SELECT GroupName FROM Users_Usergroups JOIN Users ON Users.UID =
Users_Usergroups.UID JOIN Usergroups ON Usergroups.GID = Users_Usergroups.GID
WHERE UserName Like 'The User';

Greetings,
Erwin Van de Velde
Student of University of Antwerp
Belgium


On Thursday 26 February 2004 22:47, Alexander Cohen wrote:
> Hi,
>
> Im trying to get a list of all groups that a certain user is a member
> of. Can anyone help me with the sql to get that?
>
> thanks!

On Thu, 26 Feb 2004 16:47:10 -0500, Alexander Cohen <alex@toomuchspace.com> wrote:
> Im trying to get a list of all groups that a certain user is a member
> of. Can anyone help me with the sql to get that?

select groname
>from pg_group
where (select usesyside from pg_shadow where usename = 'postgres') =
any(grolist);

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

If the user/groups you are talking about are postgres users and groups,
this is it:

select * from pg_catalog.pg_group where (select usesysid from
pg_catalog.pg_user where usename = 'user') = any(grolist)

The place to find this kind of thing is the Postgres Internals section
(system catalogs) that desribes the system tables. "any" is an array
function which is needed as the users that belong to a group are an array
of INT user ids.

Hope that helps,
Bas.

Klint,

> select groname from pg_group
> where (select usesyside from pg_shadow where usename = 'postgres') =
> any(grolist);
Unless you are lgged in as superuser (and applications other than pgAdmin
et al shouldn't be) you will get access denied on pg_shadow. (because it
contains passwords) Selecting on pg_user gives you the same result and can
be done by any user.

Bas.