Re: Separation of clients' data within a database

[John McCawley <nospam(at)hardgeus(dot)com>]

Note that my in my current code, application-level permissions are 
completely detached from database permissions.  The entire web app uses 
one user/pass to login to the database.   The web app is used both by 
individual companies who can only view their data, and also the 
overseeing company who is capable of viewing everything.   While they 
are logging in with different application-level users, they are querying 
with the same database-level user.   My question regarding database 
user-level permission was for the purpose of the IT departments going 
"under the hood" rather than for security in my web app.

As the app is currently written, I have dropdown filters for what data 
the report will produce.  The "lesser' companies' filter forces them to 
view only their data (where tbl_foo.company_id = bar), whereas the 
overseeing company runs the same report without a filter, and the data 
is organized with a group by.  Right now, the addition of a company is 
simply an addition of a row in the client table, and the app adjusts 
without modification.  If I add a schema per company, every time I add a 
company I would have to modify every query in the system to also pull 
from this additional schema, or modify my entire application to pull 
from views which must be modified every time a company is added...



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