On Thu, 2006-11-30 at 14:07, John McCawley wrote: > Maybe I'm not understanding what you're getting at, so I'll throw out an > example: I completely understand what you're saying, but I'm not quite getting the reasons for it. For instance: > HOWEVER, when user big_daddy logs into the application, he just just > run a global query on the invoice table and pull all invoices. This > requires no special knowledge by the app of what clients exist in the > system...The SQL query handles the organization of the report by company. Why does user big_daddy need to access everybody's data? Who is he? What's his role? It seems like a big security problem waiting to happen, but that's just me. > I don't understand how I could implement what you're describing without > massive changes to my existing (5+ years in development) application. > Even factoring out that there are literally hundreds of people actually > logging into this system, I will just address a hypothetical if each > company only logs in with one user. > > company_a logs in and inserts 100 records into tbl_invoice which > automagically becomes company_a.tbl_invoice. In his report, this also > automagically becomes company_a.tbl_invoice. > > company_b logs in and inserts 100 records into tbl_invoice which > automagically becomes company_b.tbl_invoice. In his report, this also > automagically becomes company_b.tbl_invoice. So far, everything seems fine. No changes in your app needed but for logins > big_daddy logs in and wants to view *all* invoice data. In the invoice > report, this becomes big_daddy.tbl_invoice, which has nothing in it > because big daddy doesn't invoice. He wants the data from all of the > different companies. How would the system know to aggregate the > company_a.tbl_invoice UNION company_b.tbl_invoice UNION (etcetera) And again the weird requirement that your data be segregated for most users, but then not be segregated for others. For a database to do that, you'd need per row permissions and postgresql doesn't do that, at least not natively. You could maybe make some kind of view / rule / trigger system that checked each row against some master permissions table. But performance wise you're just asking for trouble once you're under heavy load doing that. > As I said, we're talking about a pretty big system here, I don't have > the luxury of gutting the entire thing. Of course, I may just not > understand what I'm talking about with schemas, but that's why I'm asking ;) Well, I think if you're willing to write some extra code for the "supervisor role" to be able to view everything, schemas make a lot of sense. I imagine you could make some schema that has those unions you mention above for reading all the data, and a user with usage permission on all the schemas to run reports. you could even aggregate multiple companies in different ways if need be (i.e. company_a has two subdivisions, you give them a single view of the two subdivisions with a single user log in to review all the data at once) but don't let anyone else see their schemas. This is one of those fundamental problems you run into when you make a design decision up front (user perms in the app) and some change in architecture (users in charge of web servers) changes your whole security model. You really don't have a lot of choice at this point. You've pretty much GOT to put the security in some layer below the web/app server, because you can't trust those to do the right thing anymore if you don't control them. I've found myself in your position before. Maybe you would be better off writing some middleware layer that the front end hits. I.e. split your web app in half. Front half asks back half to do something, you maintain the back half locally. That idea might or might not be too hare-brained depending on your situation.
