Обсуждение: CUG
Hi, what would be the best way to implement a closed user group in an app with postgresql ? The app will be a cgi, so there will be an enormous number of 'guest'-users. Groups should be containable in other groups (AFAIK this is not possible in postgresql's group management). Up to now I had my own user/group management (one col per table indicating the group which has read-access). It was easy to check permissions in my app and in WHERE clauses. But now I need groups of groups which needs some kind of recursion. Therefore I would write a (recursive) plpgsql function which I can use in my app and in arbitrary sql-queries' WHERE clauses. Q: Before I do this, I would like to know whether this is the correct approach. Q: Should I use the built in user/group features ? (If not: What are they there for?) Q: Are there other mechanisms to handle tree-structures ? With properly defined REFERENCES this should be possible (?) The only hints on recursions and postgresql where features to AVOID recursion :(((((( TIA -- Nabil Sayegh GPG-Key available at http://www.sayegh.de (see http://www.gnupg.org for details)
On Sat, 3 Feb 2001, Nabil Sayegh wrote: > what would be the best way to implement a closed user group in an app > with postgresql ? You really need to give us more details. If it is something like a forum/bulleting board/ or something like egroups, then your best bet by far is to start off existing code. Don't see the point on re-inventing the wheel from scratch. > Groups should be containable in other groups (AFAIK this is not possible > in postgresql's group management). The groups managmenent of the database is to manage rights. I really don't think you want to use db groups for your projects. I even think you won't need more than one DB user for the project. > Up to now I had my own user/group management (one col per table > indicating the group which has read-access). > It was easy to check permissions in my app and in WHERE clauses. > But now I need groups of groups which needs some kind of recursion. I see no problems with that. There are several not too difficult ways to implement this, but you need to give us more details. I personally don't see the benefit AT ALL on using recursion. If someone needs access to different things which different groups have access to, then you add that person to the different groups which have ther proper rights. Perhaps I don't know enough about what you are trying to do, but recursion does not sound like the right thing to use. > Q: Before I do this, I would like to know whether this is the correct > approach. I don't think so. > Q: Should I use the built in user/group features ? (If not: What are > they there for?) To determine who has rights and what type of rights to particular tables. > Q: Are there other mechanisms to handle tree-structures ? With properly > defined REFERENCES this should be possible (?) Don't really see why you would need tree structures for an access policy type of databases. The only hints on recursions > and postgresql where features to AVOID recursion :(((((( Right now I can not think of many instances where recursion can make your life easier in databases. Moreover from what you are describing it sounds like your design is not clear in terms of what each part should do. A database is not a programming environment. It is a place to efficiently store and access data. Recursion is a programming concept which can be implemented in many different ways and without direct connection to how data is stored. Again if you give us more info about your goals we may be able to provide with better feedback.
Francisco Reyes wrote: > I see no problems with that. > There are several not too difficult ways to implement this, but you need > to give us more details. I personally don't see the benefit AT ALL on > using recursion. If someone needs access to different things which > different groups have access to, then you add that person to the different > groups which have ther proper rights. If e.g. I want to restrict access to pictures to groups: pics: id | group ------------- 1 | guest 2 | guest 3 | guest 4 | family 5 | friend 6 | friend 7 | family 8 | colleague 9 | guest groups: id | parent_id -------------------- guest | NULL friend | guest family | friend colleague| guest assigned_groups: user_id | group_id --------------------- Stefan | colleague Stefan | friend Patrick | friend 1 | guest 2 | guest 3 | guest Peter | family > Perhaps I don't know enough about what you are trying to do, but recursion > does not sound like the right thing to use. It's clear that everybody has the permissions that guests have. Members of family shall also(automatically) have the permissions that friends have ... I want the same group management like under unix. > > Q: Should I use the built in user/group features ? (If not: What are > > they there for?) > To determine who has rights and what type of rights to particular tables. Okay, then I have to implement it in the app. > Don't really see why you would need tree structures for an access policy > type of databases. > Right now I can not think of many instances where recursion can make your > life easier in databases. How shall I know that family may see 'guest-pictures' ? Without groups in groups its easy to SELECT the pictures somebody has access to, but with ? It isn't convenient having to put every user to all groups he has access to. What if later I want to give all colleagues the rights that my friends have (stupid example, I know ...) > Again if you give us more info about your goals we may be able to provide > with better feedback. thx -- Nabil Sayegh GPG-Key available at http://www.sayegh.de (see http://www.gnupg.org for details)
On Sat, 3 Feb 2001, Nabil Sayegh wrote: > It's clear that everybody has the permissions that guests have. > Members of family shall also(automatically) have the permissions > that friends have ... I thought you wanted something more complex. From what I understand you could implement "levels". When you put a picture you indicate what level the person needs. A picture with level 0 can be seen by anybody. A picture with level 1 can be seen by anyone with leve 1 and above.. Get it? > I want the same group management like under unix. Not really. :-) > How shall I know that family may see 'guest-pictures' ? The "level" scheme accomplishes this. > Without groups in groups its easy to SELECT the pictures somebody has > access to, but with ? > What if later I want to give all colleagues the rights that my > friends have (stupid example, I know ...) Another way which is more flexible, although a bit more complex, is to allow someone to belong to different groups and to have each picture allowable to more than one group. Something like --picture table pictureID Description 1 graduation 2 dance party 3 at the library 4 getting out of shower 5 nude --group table groupID Description 1 Guest 2 friends 3 girlfriend --user table personID groupID 1 guest 2 friend 3 girlfriend -user_group userID groupID 1 1 2 1 2 2 3 3 --Picture table pictureID groupID 1 1 2 1 3 1 4 2 5 3 That should work for what you described with no problems (except if you have more than one user on the girlfriend category, then you could eventually have serious problems with this setup) <G> The query for a setup like this would be a 3 way join between user, group and the picture table. Something like select picture.PictureID, from picture, user_group where userVAR = user_group.userID and user_group.groupID = picture.groupID I didn't test the query and didn't looked at it for too long, but it would be something simmilar if not that. Based on you request I think the "levels" is by far the easiest way to accomplish what I understood you asked for. Hope this helped.
Thanks for the quick responses ...
Francisco Reyes wrote:
> I thought you wanted something more complex. From what I understand you
> could implement "levels". When you put a picture you indicate what level
> the person needs.
Actually it will be a content management system, so it should be
abstract enough to
adapt to the customer's needs.
> A picture with level 0 can be seen by anybody. A picture with level 1 can
> be seen by anyone with leve 1 and above.. Get it?
Okay, this could be an option, perhaps it's best to implement several
different
mechanisms from which the customer may choose.
> Another way which is more flexible, although a bit more complex, is to
> allow someone to belong to different groups and to have each picture
> allowable to more than one group.
That's the state of affairs. Now I want to attach group girlfriend(s) to
group friends
so that if I add new pictures and give them the group friends my girl
may see it,
!!!without_explicitly_mentioning!!!
So far so good, here the level scheme could work, but ...
guest
|
friend
/ \
family colleague
| / \
girlfriend team1 team2
Business stuff is confidential and must not be seen even by my family.
It's like attaching the (unix-)group users to the group audio.
> --picture table
> --group table
> --user table
> --user_group
> --Picture table
> That should work for what you described with no problems (except if you
> have more than one user on the girlfriend category, then you could
> eventually have serious problems with this setup) <G>
(Thats just a matter of time-management ;^)
I would like to add another table group_group and this puzzles me
because I would need to recurse.
Perhaps It could be done transparent to the customer by adding triggers
which automatically add a new user to all necessary groups via
prototypes but
it would be hard to manage if they decide to give/take away permissions
from one group.
I think I will try a plpgsql function which descends the group tree till
NULL or success.
But this would restrict me to postgresql ... Is plpgsql available for
other DBs ?
cu
--
Nabil Sayegh
GPG-Key available at http://www.sayegh.de
(see http://www.gnupg.org for details)
Hi, list... Well, many providers do support mysql. Some of them do this even for free. Some do support java servlets / jsp as well. Some of them are free also. But it seems to be difficult to find a hosting provider who supports postgresql. So, if anyone can give me a hint to find a free/cheap and/or reliable hosting provider, i would appreciate it.. What I'm looking for (in desc. order...:) 1. Provider w. postgresql hosting + Java/Servlet/JSP-Support + for free 2. Provider w. postgresql hosting + Java/Servlet/JSP-Support + for a reasonable price 3. Provider w. postgresql hosting for free 4. Provider w. postgresql hosting for a reasonable price Is there any complete list of providers supporting pgsql on the web...? -- Best regards, Bruno mailto:bruno@dickhoff.de
On Sun, 4 Feb 2001, Bruno Dickhoff wrote: > 4. Provider w. postgresql hosting for a reasonable price Have not used them yet, but considering.. hub.org On the Docs section it mentions "java servlets " soon... So if they will have that in the docs soon, they may already suppor them. Good luck.
On Sun, 4 Feb 2001, Nabil Sayegh wrote: > Okay, this could be an option, perhaps it's best to implement several > different > mechanisms from which the customer may choose. I highly recommend to pick a method and stick to it. I continue to think that you are trying to use canons to kill mosquitos. In other words that you are over designing this. Again maybe I don't really understand what you are trying to do, but what you describe as your needs and what you describe as your solutions seem to have a very big gap between what is needed and what you are trying to implement. > That's the state of affairs. Now I want to attach group girlfriend(s) to > group friends so that if I add new pictures and give them the group friends my girl > may see it, > !!!without_explicitly_mentioning!!! This makes 0 sense to me. If you want your girlfriend to see the pictures that friends can see, add her to the friend group. > I would like to add another table group_group and this puzzles me > because I would need to recurse. I still don't see what recursion would get you except tons of more chances to screw up the design, lengthen development and increase chances of having to scrap the whole thing and do it from scratch at a later date. > Perhaps It could be done transparent to the customer by adding triggers > which automatically add a new user to all necessary groups via Again... my suggestion is: -A picture can be specified to be seen by different groups. -Put whoever you want to see the picture in the appropriate group(s) If you want all member of Group A to see the same pictures that Group B can see simply select the ID of all pictures visible by Group G and create records for Group A to be able to see the same pictures.
Hi,
I'm from hub, use us :) {shameless plug}
Seriously tho, we do support PostgreSQL fully, and we have
just installed Jakarta Tomcat.
(oh, and upgraded all servers to 1 gig ram last week)
Jeff
On Sun, 4 Feb 2001, Francisco Reyes wrote:
> On Sun, 4 Feb 2001, Bruno Dickhoff wrote:
>
> > 4. Provider w. postgresql hosting for a reasonable price
>
> Have not used them yet, but considering..
> hub.org
>
> On the Docs section it mentions "java servlets " soon... So if they will
> have that in the docs soon, they may already suppor them.
>
> Good luck.
>
Jeff MacDonald,
-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Facsimile : 1 902 542 5386
IRC Nick : bignose
PGP Public Key : http://bignose.hub.org/public.txt
Francisco Reyes wrote: > I highly recommend to pick a method and stick to it. I continue to think > that you are trying to use canons to kill mosquitos. In other words that > you are over designing this. Again maybe I don't really understand what > you are trying to do, but what you describe as your needs and what you > describe as your solutions seem to have a very big gap between what is > needed and what you are trying to implement. I think you're right. I will write some insert/delete-by-prototype functions to simplify the administration tasks. cu -- Nabil Sayegh GPG-Key available at http://www.sayegh.de (see http://www.gnupg.org for details)
Jeff MacDonald wrote:
>
> Hi,
>
> I'm from hub, use us :) {shameless plug}
>
> Seriously tho, we do support PostgreSQL fully, and we have
> just installed Jakarta Tomcat.
>
> (oh, and upgraded all servers to 1 gig ram last week)
Another one is mmaweb.net
--
____________________________________________________________
Glenn Holmer gholmer@weycogroup.com
Programmer/Analyst phone: 414.908.1809
Weyco Group, Inc. fax: 414.908.1601