Thread: PGP encrypt/decrypt - Prereqistes

On 06/10/2011 05:22 PM, Vikram A wrote:

> I am new to postgreSQL. I would like to use encrypt/decrypt using PGP
> keys on windows.

It'd be helpful to know in more detail what you want to encrypt and
decrypt, and how. Getting crypto right is about a lot more than using
good algorithms and good implementations.

> 2. Any manual to follow the steps ?

You'll want the pgycrypto contrib module. Its documentation is here:

http://www.postgresql.org/docs/current/static/pgcrypto.html#AEN124147

... and I think (but am not certain) that it's available on Windows.
Note that according to the documentation pgcrypto does not support
signing - or presumably for verifying signatures.

You will also want GnuPG from http://www.gnupg.org/  to use it for key
management and key generation.

--
Craig Ringer

On 14/06/11 11:51, Vikram A wrote:
> Dear Mr. Craig Ringer,
> Thank you for your response.
> I would like to crypt certain sensitive information in my applications
> such as Student register number, their marks, results etc. For this
> reason i done a study for doing encryption. Where I found that this PGP
> will help the encryption/decryption.

Sure. You can also just use a simple symmetric cypher for that sort of
thing, unless you know you need the ability to encrypt data without
knowing the secret key.

Since I presume your application needs to be able to *read* the data it
has written, and needs to be able to access that data in decrypted form,
I doubt you really need a public key system like PGP. Your application
will need to know the secret key to decrypt the data anyway.

> Any other methods/techniques are there for encryption?

Symmetric cyphers like AES. They're faster and easier to work with, just
a bit less flexible.

> I
> prefer encryption with "key"; key also should not be known to Developers.

All encryption systems use a key. Do you mean a separate public and
private key? If so: why? What specific advantages does that gain you in
your situation?

Public key crypto (for encryption) is mostly useful when you have
less-trusted users/clients/whatever sending you information that you
want to keep secret from other people and from other users, so only a
few people can *read* the information even though many can *write* it.
If this is your situation you might have a use for pgcrypto's PGP
support; otherwise I'd suggest using a symmetric key for simplicity.

As for secrecy: You can't really keep the key secret from the developers
if they will have any access to the production system (for testing,
troubleshooting, performance tuning, etc). That's because the production
system needs to know the decryption key, and if the system knows the key
the developers of the system can get it to reveal that key.

Even if you don't give the developers the key, nothing stops them
modifying the program so that it emails a copy of the key to them
whenever it loads a key, or saves an unencrypted copy to a file on a
shared drive, or whatever. If the production system has an Internet
connection or any access to any other resource the developers can access
as well, they can get the key.

--
Craig Ringer

On 14 Jun 2011, at 5:51, Vikram A wrote:

> Dear Mr. Craig Ringer,
> Thank you for your response.
> I would like to crypt certain sensitive information in my applications such as Student register number, their marks,
resultsetc. For this reason i done a study for doing encryption. Where I found that this PGP will help the
encryption/decryption.  
> Any other methods/techniques are there for encryption? I prefer encryption with "key"; key also should not be known
toDevelopers.  


I fail to see why you would need encryption for something like this.

If you can't trust your developers, then you're in trouble. Your developers surely signed a contract that prohibits
themfrom sharing confident information (and apparently it is, or you wouldn't bother trying to get it encrypted) with
otherpeople - they did, right? 

Your students shouldn't have access to your database server, so they can't read or manipulate that data.

If your developers do share such information with the "outside" world, they're looking at a court case with a hefty
fee.If your students break into your server, they committed a crime. 

So what exactly is the encryption supposed to solve? Do you really need it?

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4df7066c12091187585653!

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

On Mon, Jun 13, 2011 at 11:51 PM, Vikram A <vikkiatbipl@yahoo.in> wrote:
> I would like to crypt certain sensitive information in my applications such
> as Student register number, their marks, results etc. For this reason i done
> a study for doing encryption. Where I found that this PGP will help
> the encryption/decryption.
> Any other methods/techniques are there for encryption? I
> prefer encryption with "key"; key also should not be known to Developers.
> Please suggest any such methods.
> Thank you in advance.

What exactly is the threat against which you are defending? Without a
clearly defined goal, adding encryption for encryption's sake is just
pointless.  The solution you choose cannot be determined without
defining the threat first.