Re: [ADMIN] [PHP] Secure DB Systems - How to - Mailing list pgadmin-support

Daniel Struck wrote:

>>If you decrypt the data on the database, the sysadmin can see it.
>>
>>
>
>Hm, you are right. If one does decrypt the data on the database you have to sent the password to postgresql and so a
administratorof the database could easily grasb the password. 
>
>So the only way to go, would be to perform en/decryption on the client side?
>
>

Exactly.  That is the only way to ensure that the data is never
decrypted within the database or database server.

Now, the 'client' IMHO is the PHP application.  If the key for your
encryption is stored in the user's session (on the webserver
temporarily) then there is no log of that key or data (unless you store
the session data in the database, then you got problems, see below).

I'm starting an article on doing just this for International PHP
Magazine, and of course will use PostgreSQL as the back-end ;-)

>I wonder now; if somebody could achieve to get a snapshot of the database, they could also be able to get the log-file
ofpostgresql. 
>So one would also have to make attention that the information like sql statements don't leak that way.
>Are there other places where this kind of information could leak?
>
>

If you wanted the key based on each user, then you could use their
password (which is typically stored in an MD5 hash) as the key for
encryption/decryption, and put it in the session for use by the
application while they are logged in.  This is the easiest (and most
effective, IMHO) way to keep the encryption at the user level, and keep
the data in the database encrypted at all times.  Basically every record
would be encrypted with the key for the user associated with that
record, and there's a lot of work for anyone with a snapshot who is
working on brute forcing all that data row by row...  :)

The only time that data is not encrypted is on the webserver, and only
during transmission of that data back to the client.  SSL would be the
most common approach to solving this problem.

My absolute favourite DB layer for PHP is ADOdb, which also has a class
that transparently stores your session data in the database (if
desired).  This is crucial for sites that have multiple webservers and
load balancers, as your session data needs to be accessible from the
webserver that you are currently at.

The problem here is that the key for each user would also be stored in
the database if this method were used, rendering your efforts pointless!

OTOH, using the default storage of session data would put all the user
keys in temp files on the hard drives of the webservers.  Not only does
this not scale well (as you have to tell pound or LocalDirector or
whatever load balancer you use to stick each user to the primary
server), but some would consider this absolutely not-acceptable, as any
administrator on the servers could see that data.  So I suppose you have
to pick your poison on this one.

I gotta figure this out so I can start writing ;^P

-- Mitch