Обсуждение: 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, Pennsylvania19073