Re: Separation of clients' data within a database - Mailing list pgsql-general

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.