Thread: Users and groups

Hi,

Using PostgreSQL 7.2.3 on a Linux box and facing some minor issues
with determining which groups users belong to.

I have created a PgSQL group called `administrators'.  Now when I need
to check whether user X is a member of that group, I use the
contrib/array_iterator operator to do something like,

select groname from pg_group where grolist **=
       ( select usesysid from pg_user where usename = 'X' ) ;

This works just fine.  However, the complementary process, determining
all the users in group administrators, isn't so clean.  Currently I'm
brute-forcing it, getting each user in turn and then checking whether
she belongs to group administrators using the construct above.

Is there a simpler way?

Also, are there any caveats attached to using the pg_ system tables
directly?  I prefer to do that to prevent duplication of user status:
let PgSQL store whether the user is an administrator or not rather
than have another table keeping that information.  However, I'd change
that if the structure of the pg_ tables is subject to change from time
to time.

Regards,

-- Raju
--
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/
                      It is the mind that moves

Raj Mathur <raju@linux-delhi.org> writes:
> ...  However, the complementary process, determining
> all the users in group administrators, isn't so clean.

Doesn't
    select grolist from pg_group where groname = 'administrators'
do it for you?  I suppose you might want the thing to map sysids
back to usernames ...

            regards, tom lane

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Raj Mathur <raju@linux-delhi.org> writes:
    >> ...  However, the complementary process, determining all the
    >> users in group administrators, isn't so clean.

    Tom> Doesn't select grolist from pg_group where groname =
    Tom> 'administrators' do it for you?  I suppose you might want the
    Tom> thing to map sysids back to usernames ...

Ah, maybe I should have been more explicit: need a list of usename's
of users who are admins.  Any simple command (set) to do that?

Regards,

-- Raju
--
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/
                      It is the mind that moves