Postgres Pro
Downloads
Home   >   mailing lists

Thread: Transparent encryption in PostgreSQL?

Transparent encryption in PostgreSQL?

From
"Matt McNeil"
Date:
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

Re: Transparent encryption in PostgreSQL?

From
"Joshua D. Drake"
Date:
> 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

Re: Transparent encryption in PostgreSQL?

From
Bob
Date:
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.

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

Re: Transparent encryption in PostgreSQL?

From
Tom Lane
Date:
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

Re: Transparent encryption in PostgreSQL?

From
Bruce Momjian
Date:
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 19073

Re: Transparent encryption in PostgreSQL?

From
Bob
Date:
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


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

Re: Transparent encryption in PostgreSQL?

From
Joachim Wieland
Date:
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