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

In response to Chris.Ellis@shropshire.gov.uk:

> > What are folks doing to protect sensitive data in their databases?
> >
> > We're running on the assumption that the _really_ sensitive data
> > is too sensitive for us to just trust the front-end programs that
> > connect to it.
> >
> > The decision coming down from on-high is that we need to encrypt
> > certain fields.  That's fine, looked at pgcrypto, but found
> > the requirement to use pgp on the command line for key management
> > to be a problem.
> >
> > So we're trying to implement the encryption in the front-end, but
> > the problem we're having is searching on the encrypted fields.  Since
> > we have to decrypt each field to search on it, queries that previously
> > took seconds now take minutes (or worse).
> >
> > We've tested a number of cryptographic accelerator products.  In
> > case nobody else has tried this, let me give away the ending: none
> > that we've found are any faster than a typical server CPU.
> >
> > So, it's a pretty open-ended question, since we're still pretty open
> > to different approaches, but how are others approaching this problem?
> >
> > The goal here is that if we're going to encrypt the data, it should
> > be encrypted in such a way that if an attacker gets ahold of a dump
> > of the database, they still can't access the data without the
> > passphrases of the individuals who entered the data.
>
> Take the performance hit, If people on high want the data encrypted, then
> they have to suffer the performance penalty, however bad.

As reasonable as that sounds, I don't think it's true.  We've already
brainstormed a dozen ways to work around the performance issue (creative
hashing, backgrounding the decryption and using ajax to display the
results as they're decrypted ...)

Problem is that all of these methods complicate things in the
application.  I was hoping there were better approaches to the
solution, but I'm starting to think that we're already on the
right path.

> Could you not write some server extensions to encrypt / decrypt the data
> server side, coupled with a custom index implementation?

Not sure how the index implementation would work.  The server-side
encryption doesn't really help much ... it's difficult to add more
DB servers in order to improve throughput, but adding more web
servers fits easily into our load balanced setup.  In any event,
the addition of processing cores (not matter where) doesn't speed
up the decryption of individual items, it only allows us to do more
in parallel.

> Can you use a global server side key or do you need fine grained
> encryption?
>
> Is a database the correct tool for the job if you want this level of
> encryption and granularity?

The global side key puts us in pretty much the same situation that
filesystem encryption does, which is not quite as strong as we're
looking for.

I've considered the possibility of using something other than the
DB, but I can't think of any storage method that gains us anything over
the DB.  Also, if we use something different than the DB, we then have
to come up with a way to replicated it to the backup datacenter.  If
we put the data in the DB, slony is already set up to take care of that.

> Also, how secure are you communication channels, what stops me snooping
> the data in transit, ARP posioning and other techniques etc.

We do what we can.  Everything is transferred over HTTPS, and we log and
monitor activity.  We're constantly looking for ways to improve that
side of things as well, but that's a discussion for a different forum.

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