Re: Two-way encryption

Toby Corkindale <toby(dot)corkindale(at)strategicdata(dot)com(dot)au> wrote:
> From: "Patrick Simcoe" <patricksimcoe49(at)gmail(dot)com>

>> I have a question regarding two-way encryption data for specific columns.
>>
>> Does anyone have a technique or recommendation for two-way encryption which
>> somehow obfuscates the decrypt key so that it isn't easily retrievable from
>> the database or the application source code? We've already considered (a)
>> letting users hold the decrypt key and (b) obfuscating the decrypt key with
>> the user's own (one-way encrypted) password, but neither of these
>> approaches are viable for us.
>
> If you want the application to be able to decrypt the data automatically, then
> it has to hold the decryption key somewhere. There's really no way around
> that.

That is the tricky bit; if you store it on the machine with the
database, you haven't really protected yourself against anything.

The first thing to do when designing a security mechanism like this
is to be clear what the threat model is that you are trying to
protect against.  In a previous job I had to protect a few
sensitive columns on physically distributed machines against theft
of the server or of its drive array.  The design we developed was
to use the pgcrypto extension with symmetric encryption using
random leading salt.  A view was placed in front of protected
tables, which would decrypt the column upon selection and use a
trigger to encrypt the column when written.  None of that was too
hard -- the hard part was how to supply, store, and retrieve the
encryption keys.

These distributed machines were all on a WAN, and the central site
had a secure server room with a special locked cage for
high-security machines.  A machine in this cage had the keys, and
would only supply them to properly authenticated servers (with the
right fingerprints from the right IP addresses).  We wrote a
special C loadable module which would request the keys on cluster
startup, and store them in private RAM, providing them only to the
database superuser.  Security definer functions (owned by the
database superuser) did the encryption and decryption.  In the
event of a WAN outage during cluster startup, the encrypted columns
were not available until the WAN came back and the superuser
manually ran the function to load the passwords.

This design allowed any database user with rights to select from
such a column of the table to see the encrypted data rather
transparently, but it should be pretty effective in protecting the
data should someone walk off with the hardware.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company