Thread: CUG

CUG

From
Nabil Sayegh
Date:
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)

Re: CUG

From
Francisco Reyes
Date:
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.


Re: CUG

From
Nabil Sayegh
Date:
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)

Re: CUG

From
Francisco Reyes
Date:
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.


Re: CUG

From
Nabil Sayegh
Date:
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)

PGSQL Hosting providers?

From
Bruno Dickhoff
Date:
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



Re: PGSQL Hosting providers?

From
Francisco Reyes
Date:
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.


Re: CUG

From
Francisco Reyes
Date:
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.


Re: PGSQL Hosting providers?

From
Jeff MacDonald
Date:
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


Re: CUG

From
Nabil Sayegh
Date:
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)

Re: PGSQL Hosting providers?

From
Glenn Holmer
Date:
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