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

In response to Steve Atkins <steve@blighty.com>:
>
> On Apr 16, 2009, at 12:40 PM, Bill Moran wrote:
>
> (This is the traditional "you're asking the wrong question" response).
>
> >
> > What are folks doing to protect sensitive data in their databases?
>
> I don't think that's a useful way to look at it. Protecting sensitive
> data in the entire system, where the database is just one
> part of that system is likely to lead to a much better answer.

<snip>

I disagree.  We're already addressing the issues of security on the
application level through extensive testing, data validation out the
wazoo (to prevent SQL Injection and other application breaches).  All
our servers are in highly secure data centers.  We have VPNs and
access restrictions at the IP and the user level to the 9s.

It's still not enough.

My task here is to develop a system to protect the data in the event
that all of those fail.  As a result, I'm looking for general advice.

I already have a system in place.  This is apparently another part
that I should have described in more detail.  So, here goes:

To draw a parallel example on the application:
Imagine that you're an employee in a business.  When you're hired, you
enter your SSN into the company database.  Now, your department manager
needs to have access to your SSN for various reasons, so the system
grants access to your encryption key to the department manager.  Based
on system policy, the division manager has access to all the data in
the department, and the company head has access to all divisions.  As
a result, the company head can get your SSN out of the database using
the passphrase for his key.

However, Joe, over in IT can not access your SSN.  Even though he's the
DBA and can pull a full text dump of the database at will, he can not
decrypt your SSN unless he has a passphrase to one of the keys that
can decrypt it.

All that is pretty standard PKI stuff, and I've created the tables and
the functions that implement it.

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.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/