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 21:56:43
Message-ID: 456F539B.4080805@hardgeus.com (view raw or flat)
Thread:
Lists: pgsql-general
>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.
>  
>
Uncle Sam :)

>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.
>  
>
Well, you're right, the security model has changed.  The situation is 
that the system was written for one company to manage its clients, and 
the permission model was basically company/client, and the client had 
pared-down access enforced by the app (the security model is quite a bit 
more refined than that, but I'm simplifying)...The problem domain has 
expanded for there to be many companies (clients no longer really 
exist), and one over-arching super-company able to view everything.

Note that I am retaining 100% control of the Web-App server and the 
database server (i.e. no one else will have superuser abilities), but I 
know that the different companies will want the ability to connect to 
the database under the hood.  I think the most effective solution will 
be to simply create a database user for each company, and for each 
company create a series of views, owned by that user, which are 
hard-wired to view only their data.

Of course I still have to modify my web app and schema to facilitate the 
new security structure, but I was never too worried about handling it in 
my app...My concern was allowing people direct access to the underlying 
DB while a) blocking them from viewing others' data, and b) without 
having to drastically modify the fundamental structure of my app.



In response to

Responses

pgsql-general by date

Next:From: Jim NasbyDate: 2006-11-30 22:05:45
Subject: Re: Blob fields and backups
Previous:From: Jim NasbyDate: 2006-11-30 21:49:14
Subject: Re: Solaris 10 problem

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