Re: [ADMIN] Secure DB Systems - How to

> 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 leakage of 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 go with 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 files with 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 mozilla browser.
(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 release of 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(dot)d(at)retrovirology(dot)lu