Skip site navigation (1) Skip section navigation (2)

Re: Separation of clients' data within a database

From: John McCawley <nospam(at)hardgeus(dot)com>
To: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
Cc: Rodrigo Gonzalez <rjgonzale(at)gmail(dot)com>, Leonel Nunez <lnunez(at)enelserver(dot)com>, pgsql general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Separation of clients' data within a database
Date: 2006-11-30 20:07:26
Message-ID: 456F39FE.1010506@hardgeus.com (view raw or flat)
Thread:
Lists: pgsql-general
Maybe I'm not understanding what you're getting at, so I'll throw out an 
example:

--------------------------
With my current architecture, smartlowe logs in, but his login is 
handled at the application layer, so his database connection is simply 
"foo".  He inserts a hundred records in the invoice table, which is the 
public table invoice...these invoice records are automatically tagged 
with the client_id associated with his login.  When he runs a report, 
the program forces a filter, aso by the client_id associated with his 
login. 

Now joe_user logs in, once again the "foo" db user, and inserts 100 
records, tagged with his client_id.  When he runs a report, it is 
forceably filtered by his client_id.  smartlowe and joe_user are writing 
to the same database table, but the application forces the separation of 
their data and reporting.

 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.
--------------------------

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. 

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)


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 ;)


Scott Marlowe wrote:

>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!
>  
>

In response to

Responses

pgsql-general by date

Next:From: Martijn van OosterhoutDate: 2006-11-30 20:11:10
Subject: Re: Ident authentication failed for user "dsivam"
Previous:From: Leonel NunezDate: 2006-11-30 19:55:02
Subject: Re: Separation of clients' data within a database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group