Hi,

Is there any reason why the grolist field in the table pg_group is
implemented as an array and not as a separate table?

According to the documentation:

<quote source="Postgresql 7.2 User Manual, chapter 6 near the end">
Arrays are not sets; using arrays in the manner described in the previous
paragraph is often a sign of database misdesign.
</quote>

I have trouble implementing a way to easily check whether a user is part
of a group. (I use Apache::AuthDBI to implement authentication and wanted
to make a view with columns username, userid , groupname. And installing
the contrib/array give's me a postgresql that is different from all the
others :-(

-- 
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen    reinoud.v@n.leeuwen.net
http://www.xs4all.nl/~reinoud
__________________________________________________

Thu, 21 Nov 2002 14:28:35 +0100
Reinoud van Leeuwen <reinoud@xs4all.nl>
Why an array in pg_group?
Reinoud van Leeuwen <reinoud@xs4all.nl> writes:
> Is there any reason why the grolist field in the table pg_group is
> implemented as an array and not as a separate table?

It's easier to cache a single entry per group in the GRONAME and GROSYSID
syscaches than a bunch of them.  The design is optimized for the
needs of the system's internal permissions-checking code, not for
the convenience of people trying to interrogate pg_group in SQL.

> I have trouble implementing a way to easily check whether a user is part
> of a group.

Perhaps you could create a table that has no purpose except to be a
permissions-check target, and set it up to have permissions granted only
to the group you care about.  Then use has_table_privilege().

In the long run I'd have no objection to adding an is_group_member()
function (need a better choice of name, perhaps) to cater to this sort
of request.  Too late for 7.3 though.

			regards, tom lane

Thu, 21 Nov 2002 10:41:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Why an array in pg_group? 
Tom Lane wrote:
> > I have trouble implementing a way to easily check whether a user is part
> > of a group.
> 
> Perhaps you could create a table that has no purpose except to be a
> permissions-check target, and set it up to have permissions granted only
> to the group you care about.  Then use has_table_privilege().
> 
> In the long run I'd have no objection to adding an is_group_member()
> function (need a better choice of name, perhaps) to cater to this sort
> of request.  Too late for 7.3 though.

I believe Joe Conway already coded that, but it didn't make it into 7.3.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 21 Nov 2002 14:17:02 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Why an array in pg_group?