Re: Transparent encryption in PostgreSQL?

mcneil(at)callahanpro(dot)com ("Matt McNeil") writes:
> Greetings,I need to securely store lots of sensitive contact
> information andnotes in a freely available database (eg PostgreSQL
> or MySQL) that will bestored on a database server which I do not
> have direct access to. This database will be accessed by a PHP
> application that I amdeveloping.  However, I also need to be able to
> search/sort these datawith the database functions (SELECT, ORDER BY,
> etc) so encrypting onthe client side (web application) or using
> encryption of specific fields would not work.  (For example, I need
> to encryptcontacts' names, but need to be able to search for results
> by name). (Irealize I could load the entire table into memory with
> PHP andprocess/search/sort it there, butthat's obviously not a very
> good solution).  Ideally I would like toencrypt entire tables.  I
> read something about the pgcrypto contribmodule, but have't been
> able to discern if it can do ecryption in atransparent 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 themistake of promising this
> functionality, so I'm scrambling to figure out some kind of
> solution.  Anysuggestions? Thanks so much! Matt

It seems to me that you have to step back and actually analyze the
"threat model" that you are trying to deal with.

There is a discussion in recent versions of the documentation as to
different means of encryption that are available, and the sorts of
threats that they protect against, as well as those that they do NOT
protect against.

<http://www.postgresql.org/docs/current/static/encryption-options.html>

I would suppose that if the goal is simply to say "Hey! I'm using
encryption!!!", you might accomplish this by using an encrypted
partition.

And that does not require *any* particular support from the database
system.

That seems to me like the easiest way to 'scramble' to provide
something that allows you to say, "Look ma, it's all encrypted!!!"

Of course, the main threat that this protects against is that of
someone walking away with the disk drives. That is probably not the
threat model you honestly need to worry about.
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>