Re: Separation of clients' data within a database

Is it possible to have each user connect via different postgresql
account? if so, then you can use

alter user set search_path='common_schema','user_schema';

where common schema has the things that would be the same for each
instance of the app, and user_schema is the name of that user's schema.
Then you don't have to change your app very much, if any, and each user
can only get to their schema, assuming you've only granted them
permission on their own schemas (change rights) and the common_schema
(usage rights)

On Thu, 2006-11-30 at 13:35, Rodrigo Gonzalez wrote:
> search_path="$user" in postgresql.conf
>
> and you create one schema for each user with the user name as name....
>
> Rodrigo
>
>
> John McCawley wrote:
> > It seems that this approach would suffer the same problem as the one I
> > outlined in "1) Actually separate client data by table". I would have
> > to modify the logic of my web app...My web app currently handles all
> > of the data, regardless of company, so it would have to aggregate the
> > data from the different schemas when pulling data, and be smart enough
> > to write back to the proper schemas when writing data.
> >
> > Leonel Nunez wrote:
> >
> >> why don't you create a schema for every company and grant
> >> permissions to
> >> use the shchema to only the user that needs to use that??
> >>
> >> more info :
> >> http://www.postgresql.org/docs/8.1/static/ddl-schemas.html
> >>
> >>
> >> Leonel
> >>
> >>
> >>
> >>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly