Toby Corkindale <toby.corkindale@xxxxxxxxxxxxxxxxxxxx> wrote: > From: "Patrick Simcoe" <patricksimcoe49@xxxxxxxxx> >> 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
