Re: Create a cache DB between web portal and internal DB? - Mailing list pgsql-general

A much better way to do this, assuming that the database login uses the
actual username/password of the manager in question (if not, see if
this can be arranged securely):

Assume you have a table with the restricted information.  Call it
real_data:

CREATE TABLE real_data
(
     accountID INTEGER,
     ...
)


Then you have another table storing the user id's of the managers:

CREATE TABLE managers
(
     managerUserID  TEXT,
     managerID           SERIAL
)


Now keep one more table, this one mapping which managers may access
which accounts:

CREATE TABLE access_control
(
     managerID    INTEGER,
     accountID      INTEGER
)


Rather than granting access to the tables, create a view which the user
software has privileges on:

CREATE VIEW data AS
     SELECT * FROM real_data
     WHERE accountID IN
         (SELECT accountID FROM access_control WHERE managerID =
             (SELECT managerID FROM managers WHERE managerUserID =
$CURRENT_USER))


Note: this code is off the top of my head and untested, so it may need
to be tweaked.

Assuming I got all of that right, this should set up a view, which you
would then GRANT privileges on, so that managers would only be able to
see data from the accounts the access_control table says they may see.
You can set up RULEs to permit UPDATEs and DELETEs, if these are
desired.

Note that for the security to work, any such RULEs you create will also
need to check access rights, and you will need to grant access to the
view (data), but *not* to the table (real_data), otherwise this makes
no real difference.


On Dec 21, 2004, at 5:31 AM, Dick Davies wrote:

> * Dearman, Rick <rick.dearman@teamuk.telstra.com> [1245 05:45]:
>> I have a requirement from my security manager but I can't seem to
>> find a good solution.
>> So I wondered if someone had done something similar.
>>
>> We have a web portal and a DB in PostgreSQL (obviously) which
>> contains user data.
>> The portal is accessed by account managers who have access to only
>> specific user accounts.
>> This all works fine however the concern is that if you ever got
>> access more directly
>> into the DB through a hack, or poorly designed site code,
>> you could potentially access information that you shouldn't.
>>
>> So the idea is that he is floating is we create a cache DB between
>> the portal and the
>> main DB which will only keep the information currently being worked
>> on by the person logged in,
>
> If I was you, I'd have major concerns and have a chat with the manager
> in question.
>
> How is that going to help anything - surely the cache DB would have to
> do a
> query to populate itself anyway, which you have to check to gain any
> security benefit?
> You're in a similar boat for updates.
>
> If you're going to check the queries it makes somehow, just do those
> checks on the queries
> you get in the first place.
>
> This sounds like a pain in the arse to implement, maintain and debug
> with no benefits.
> Far better to  spend that time cleaning up your application code and
> implementing a decent backup
> policy.
>
> --
> 'A little rudeness and disrespect can elevate a meaningless interaction
> into a battle of wills and add drama to an otherwise dull day.'
>         -- Calvin discovers Usenet
> Rasputin :: Jack of All Trades - Master of Nuns
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
-----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com