pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
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
Date: Tue, 27 Jul 2004 13:41:33 +0200
Message-ID: <20040727134133.676ab9cf@localhost> (raw)
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>

> 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@retrovirology.lu



view thread (17+ messages)  latest in thread

Message-ID: <20040727134133.676ab9cf@localhost>
Permalink:  ../20040727134133.676ab9cf@localhost/
Also on:    postgresql.org/message-id/20040727134133.676ab9cf@localhost

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: struck.d@retrovirology.lu, mitchy@spacemonkeylabs.com, pgsql-php@postgresql.org
  Subject: Re: [ADMIN] Secure DB Systems - How to
  In-Reply-To: <20040727134133.676ab9cf@localhost>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox