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, regexes, 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?
Thanks so much!
Matt
My sense is that this is a difficult problem. However, I made the
mistake of promising this functionality,
Well it isn't that difficult except that you need some level of two way
encryption and it is going to be a performance nightmare.
I would suggest instead just mounting postgresql on an encrypted filesystem.
Sincerely,
Joshua D. Drake
so I'm scrambling to figure out some kind of solution. Any
suggestions?Thanks so much!
Matt
--
Your PostgreSQL solutions provider, Command Prompt, Inc.
24x7 support - 1.800.492.2240, programming, and consulting
Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit
http://www.commandprompt.com / http://www.postgresql.org
Doesn't that really only save you from having someone come in at the OS
level and copying your data files and than moutning them on a differet
server/database. A person could still come in to psql as a dba or anyone for
that matter with the proper select grants and query off that data and see it
in encrypted.
Not that this helps here but Oracle just implemented row level encryption in
10g release 2. In simple form everything has a key and for you to view the
data from anywhere including sql plus(it's like psql) you need the correct
key to decrypt it(I'm pulling this from my head after haveing read this some
time ago). This all happens on the fly. Of course there will be performance
hits for this but for today's world where the weakest link is usually an
internal employee with access to all the data the only way to keep people
from seeing it is a setup that encrypts it at the cost of performance. Maybe
the Oracle method is something that can make it's way to Postgresql over
time. If there isn't a third party patch that already does this.
Show quoted text
On 7/13/05, Joshua D. Drake <jd@commandprompt.com> wrote:
My sense is that this is a difficult problem. However, I made the
mistake of promising this functionality,Well it isn't that difficult except that you need some level of two way
encryption and it is going to be a performance nightmare.I would suggest instead just mounting postgresql on an encrypted
filesystem.Sincerely,
Joshua D. Drake
so I'm scrambling to figure out some kind of solution. Any
suggestions?Thanks so much!
Matt
--
Your PostgreSQL solutions provider, Command Prompt, Inc.
24x7 support - 1.800.492.2240, programming, and consulting
Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit
http://www.commandprompt.com / http://www.postgresql.org---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Bob <luckyratfoot@gmail.com> writes:
Doesn't that really only save you from having someone come in at the OS
level and copying your data files and than moutning them on a differet
server/database. A person could still come in to psql as a dba or anyone for
that matter with the proper select grants and query off that data and see it
in encrypted.
Yeah, the real question here is what threats are you trying to defend
against. "I need an encrypted database" is pretty nearly a meaningless
statement --- first tell us who the attacker is that you're worried about.
regards, tom lane
Tom Lane wrote:
Bob <luckyratfoot@gmail.com> writes:
Doesn't that really only save you from having someone come in at the OS
level and copying your data files and than moutning them on a differet
server/database. A person could still come in to psql as a dba or anyone for
that matter with the proper select grants and query off that data and see it
in encrypted.Yeah, the real question here is what threats are you trying to defend
against. "I need an encrypted database" is pretty nearly a meaningless
statement --- first tell us who the attacker is that you're worried about.
We have an encryption section in the documentation now --- please see
that.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Here is the link in case your fingers are broken and it hurts to type;)
http://www.postgresql.org/docs/8.0/interactive/encryption-options.html
Show quoted text
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, regexes, 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?
Thanks so much!
Matt
On Wed, Jul 13, 2005 at 11:18:04PM -0500, Bob wrote:
Here is the link in case your fingers are broken and it hurts to type;)
http://www.postgresql.org/docs/8.0/interactive/encryption-options.html
I think the "Password Storage Encryption" paragraph needs a note similar to
what Stephen Frost wrote in
http://archives.postgresql.org/pgsql-hackers/2005-04/msg00634.php
at the end. The encryption-options.html page says:
"If MD5 encryption is used for client authentication, the unencrypted
password is never even temporarily present on the server [...]"
which is right and wrong at the same time because the md5 hash becomes sort
of a new cleartext password.
Joachim