Why an array in pg_group?
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
__________________________________________________
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
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