Re: Transparent encryption in PostgreSQL?

On 7/13/05, Matt McNeil <mcneil(at)callahanpro(dot)com> wrote:
> Greetings,
> I need to securely store lots of sensitive contact information and
> notes in a freely available database (eg PostgreSQL or MySQL) that will be
> stored on a database server which I do not have direct access to.
> This database will be accessed by a PHP application that I am
> developing. However, I also need to be able to search/sort these data
> with the database functions (SELECT, ORDER BY, etc) so encrypting on
> the client side (web application) or using encryption of specific fields
> would not work. (For example, I need to encrypt
> contacts' names, but need to be able to search for results by name). (I
> realize I could load the entire table into memory with PHP and
> process/search/sort it there, but
> that's obviously not a very good solution). Ideally I would like to
> encrypt entire tables. I read something about the pgcrypto contrib
> module, but have't been able to discern if it can do ecryption in a
> transparent way (e.g. so that I can do regex searches on the data).
>
> My sense is that this is a difficult problem. However, I made the
> mistake of promising this functionality,
> so I'm scrambling to figure out some kind of solution. Any
> suggestions?

Go back to your client and renegotiate? That would be my first
option. Encryption isn't all it's cracked up to be, especially when
most people think they can ignore application level security just by
encrypting their data.

One possible compromise might be to lower case the name, strip out any
spaces or punctuation, split it into first and last name (if it's web
based use separate input fields), and then hash those values and stick
them in their own rows. You would also have the full name encrypted
in it's own row. To search you lower case the search string, strip
spaces, hash the value, and perform the query. Kind of a hack but it
works.

Chris