Re: Separation of clients' data within a database

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