Date: Tue, 27 Jul 2004 13:41:33 +0200
From: Daniel Struck <struck.d@retrovirology.lu>
To: Mitch Pirtle <mitchy@spacemonkeylabs.com>
Cc: pgsql-php@postgresql.org
Subject: Re: [ADMIN] Secure DB Systems - How to
Message-Id: <20040727134133.676ab9cf@localhost>
In-Reply-To: <40F3EC4A.1060101@spacemonkeylabs.com>
References: <ccjra4$pgl$1@sea.gmane.org> <20040709152722.GA20935@wolff.to>
	<20040713113557.31e9d6dc@localhost>
	<20040713123817.GB3710@wolff.to>
	<20040713151838.0004cb8e@localhost>
	<40F3EC4A.1060101@spacemonkeylabs.com>
Organization: gwia.inf.chl.lu
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable

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

But then one will also lose the ability to make sql-queries with the encryp=
ted columns . Something I wouldn't want to miss, or else it doesn't make se=
nse 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=20
> encryption is stored in the user's session (on the webserver=20
> temporarily) then there is no log of that key or data (unless you store=
=20
> 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 veri=
fy 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=20
> 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=
=20
> would be encrypted with the key for the user associated with that=20
> record, and there's a lot of work for anyone with a snapshot who is=20
> working on brute forcing all that data row by row...  :)

I have a different scenario; a couple of common sensitive information sever=
al 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/fil=
es/doc/apache-client-authentication(v0.5.1).pdf, but I haven't yet tested i=
t 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

--=20
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barbl=E9
L-1210 Luxembourg

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