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!
