Re: PGP encrypt/decrypt - Prereqistes - Mailing list pgsql-general

---

From: Craig Ringer <craig@postnewspapers.com.au>
To: Vikram A <vikkiatbipl@yahoo.in>
Cc: PGSQL - Genearal <pgsql-general@postgresql.org>
Sent: Tuesday, 14 June 2011 12:52 PM
Subject: Re: [GENERAL] PGP encrypt/decrypt - Prereqistes

On 14/06/11 14:29, Vikram A wrote:
> My application work in a LAN. It will not with across internet. Number
> users also less than 25. Only certain information to be  cipher. Also I
> do not want such a complicated public and private key as PGP defines. As
> you said, I would like to go for simple[ Symmetric] method with our own
> key.

OK, so you can still use pgcrypto, but just using the pgp_sym_encrypt
and pgp_sym_decrypt functions.

Are you aware, though, that by encrypting your data field-by-field you
prevent it from being usefully indexed or otherwise processed by the
database? You will make a lot of things harder - and slower - than they
would otherwise be. Choose what you encrypt carefully.

You also need to do your key storage and access right. Encrypting data
is no use if you store the encryption/decryption key alongside the data,
after all.

> I agree that, we can not keep the key secret from the developers. My
> question is "If the developer/or one is knowing the key is left the
> organization" It is ultimately waste of doing encryption know? Is there
> any way to avoid such things?

You can have a batch process that decrypts the data in the database and
re-encrypts it with a newly generated key unknown to that person. This
isn't a bad idea to have ready, because keys can be compromised for all
sorts of reasons including network intrusions.

That won't stop a currently-active developer from dumping and decypting
all your data, of course. As Alban Hertroys just pointed out, what it
comes down to is that if you can not trust your developers then  you're
screwed. You can prevent casual access and abuse, but not planned
attacks using a well-hidden trojan in the source code that's hidden in
an otherwise unobtrusive patch.

Nonetheless, you need to have a way to re-key if an old key is compromised.

On 14/06/11 14:57, Alban Hertroys wrote:
> So what exactly is the encryption supposed to solve? Do you really
> need it?

Usually people are trying to satisfy privacy rules or other
legislative/policy compliance requirements.

There *are* uses to encrypting data in a DB- at the very least, you can
reduce the amount of your infrastructure that knows how to decrypt the
data of interest to a small subset of your system. That makes it harder
for Joe Script Kiddie to exploit some dumb SQL injection vulnerability
to dump all the sensitive bits of your database in one go.

You never store your keys with your database dumps and they never travel
over the wire with replication traffic, so you're helped out in those
areas too. Of course, your replication traffic should be over SSL or on
a very secure LAN and your backups should be encrypted anyway, so
neither of those are as big a help... but they can't hurt.

--
Craig Ringer