> 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.d@xxxxxxxxxxxxxxxx
