Thread: Transparent encryption in PostgreSQL?
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
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
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?
mistake of promising this functionality,
so I'm scrambling to figure out some kind of solution. Any
suggestions?
Thanks so much!
Matt
On 7/13/05, Matt McNeil <mcneil@callahanpro.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
The point of a data base is storing ASCII or unicode not encypting the data... encrypting the data IN the database is a bad idea.... what happens if you ever lose the key ??? you lose ALL your data... Additionally -- encryption keys are usually machine-dependent so you lose the ability to migrate to new hardware and possibly the ability to upgrade the RDBMS engine itself...
It sounds to me like your issues are really about security and access control.... You'd be better off using an ACL and locking down your server...
""Matt McNeil"" <mcneil@callahanpro.com> wrote in message news:20050713191150.7D88752BDF@svr1.postgresql.org...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 onthe client side (web application) or using encryption of specific fieldswould 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 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 the
mistake of promising this functionality,
so I'm scrambling to figure out some kind of solution. Any
suggestions?Thanks so much!Matt
mcneil@callahanpro.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@~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/>