Re: [ADMIN] Secure DB Systems - How to - Mailing list pgsql-php

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

But then one will also lose the ability to make sql-queries with the encrypted columns . Something I wouldn't want to
miss,or else it doesn't make sense to store this data in a database if one can't work with it. 
I do send queries with the password within it, so I disabled the logging of sql-statements in postgresql to prevent the
leakageof the password. 
But the problem exists that the password is the available at several places.
As I am the only admin of the machine this shouldn't be such a big problem.


> 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 simply store a sha1 hash of the password in a configuration file, to verify that the user did enter the correct
password,or else he would only see garbage ;-) 


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

Interesting, good that one can also order this editions online ;-)


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

I have a different scenario; a couple of common sensitive information several users must have access to. So I have to
gowith only one password. 

(At last their is a second layer of protection if someone steals the server or hard drives: I will encrypt the postgres
fileswith dm_crypt. Right now I am testing it, and it seems to work fine so far.) 


> during transmission of that data back to the client.  SSL would be the
> most common approach to solving this problem.

I have tested two-way ssl authentication. The certificates for the clients are stored on smartcards accessible to the
mozillabrowser. 
(I have written a small howto, you can find it on
http://www.opensc.org/files/doc/apache-client-authentication(v0.5.1).pdf,but I haven't yet tested it with the latest
releaseof opensc) 


> the database if this method were used, rendering your efforts pointless!

True, if you find the solution I hope to find it in your article ;-)


Daniel

--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg

phone: +352-44116105
fax:   +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu