Re: Looking for advice on database encryption - Mailing list pgsql-general

From Bill Moran
Subject Re: Looking for advice on database encryption
Date
Msg-id 20090416212700.2d737bc6.wmoran@potentialtech.com
Whole thread Raw
In response to Re: Looking for advice on database encryption  ("Jonathan Bond-Caron" <jbondc@openmv.com>)
List pgsql-general
"Jonathan Bond-Caron" <jbondc@openmv.com> wrote:
>
> On Thu Apr 16 05:06 PM, Bill Moran wrote:
> >
> > The problem comes when the company head wants to search through the
> > database to find out which employee has a specific SSN.  He should be
> > able to do so, since he has access to everything, but the logistics of
> > doing so in a reasonable amount of time are rather complex and very
> > time consuming.  On a million rows with the SSN unencrypted, such a
> > query would take less than a second with an appropriate index, but
> > pulling those million rows into the application in order to decrypt
> > each one and see if it matches can easily take a half hour or longer.
> >
> > That's where we're having difficulty.  Our requirements are that the
> > data must be strongly protected, but the appropriate people must be
> > able to do (often complex) searches on it that complete in record time.
>
> Would storing a one-way hash of the SSN work for you? i.e. combine sha1
> and/or md5, use a salt...
>
> SELECT ssn_encrypted FROM employees WHERE ssn_hash =
> yourhashmethod(SSN_PLAINTEXT)
>
> So you have both an encrypted version of the SSN and a one-way hash of it.
>
> That's how we store credit card numbers.

We're considering that for some fields.  It does limit a lot ... we can't
do partial matching for example.

Other fields don't work so well.  If I try to use that trick on a field
that has too few choices (true/false is the worst, but anything with
less than a few thousand possibilities, i.e. "county of residence" is
a problem) then I've left it open to an easy dictionary attack.

Thanks for the input.

--
Bill Moran
http://www.potentialtech.com

pgsql-general by date:

Previous
From: Bill Moran
Date:
Subject: Re: Looking for advice on database encryption
Next
From: Rick Schumeyer
Date:
Subject: Full text search strategy for names