Re: Separation of clients' data within a database

On Thu, 2006-11-30 at 13:45, John McCawley wrote:
> 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...

That's just the point of search_path.

For me, it can be:

alter user smarlowe set search_path='common','smarlowe';

for joe user it might be

alter user joe_user set search_path='common','joe_user';

and all you have to change is the connection statement for your app
depending on who logged in. voila!