postgresql des encrypt

Question

I have oracle database to move on to new postgresql server.

Some tables are having field sesitive and those are all encryted through DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT/DESDECRYPT.

The problem is here. The size of postgresql's encrypted data size(bytea type) should be the same with oracle's.

I tried to get it done with aes(encrypt/decrypt) which takes almost three times larger than raw data.(oracle takes 16byte with des algorithm, postgresql takes 33byte with aes and the raw data is of 13byte.)

I tried the postgresql crypt also, but the manual doesn't metion the way of decrypting it back limiting 8byte of raw data size.

Now i really need encrypt method which takes as small encryted data size as possible and provides decrypt method also.

Is there a good way or the other options for me??? Thanks in advance.

Annulus

Answer 1

Crypt and DES are old cyphers and should not be used

Plain old DES is an obsolete algorithm. You can't really usefully compare it to AES128; it's like complaining that a SHA256 hash is bigger than an MD5 hash - yep, it is, but only one of them might slow the attacker down for a while. DES was widely considered weak even in 1999 and should never be used in new applications. Do not use it.

I don't think it's a good idea to seek an encryption method that "provides the smallest data size possible" - because it's basically a waste of time to encrypt data using DES. Why not use ROT13 (caesar cypher)? The "encrypted" result is the same size as the input, pity the encryption can be broken by a 3-year-old.

crypt is of a similar vintage. The old UNIX crypt hashing algorithm is ... elderly ... and totally unsuitable for any new application. Hashes should be SHA256 at minimum, really.

Crypt is a one-way hash

As for not being able to figure out how to decrypt crypted data: crypt isn't an encryption algorithm, it's a cryptographic hash function or "one way hash". One way hashes are suitable for verifying that data is unmodified, comparing to a stored salted hash for password authentication, for use in challenge-response authentication, etc. You cannot decrypt crypted data.

Deal with the size

Use a decent cryptographic function and live with the size increase. bf or aes128 are about the weakest you can reasonably use.

Personally I prefer to do my encryption/decryption in the app, not in the DB. If it's done in the DB the keys can be revealed by pg_stat_statements, in the logs by log_statement or errors, etc. Better that the key never be in the same place as the stored data at all.

Most programming languages have good cryptographic routines you can use.

It's hard to offer any more advice as you haven't really explained what you're encrypting, why, what your requirements are, what the threat(s) are, etc.

Passwords?

If you're storing passwords, you're probably doing it wrong.

• If possible, let someone else do the authentication:

  • OAuth or OpenID for Internet

  • SSPI, Kerberos/GSSAPI, Active Directory, LDAP bind, SASL, HTTP DIGEST, etc for intranet

• If you really must do the auth yourself, add a salt to the passwords and hash the result. Store the hash and the salt. When you must compare passwords, salt the new plaintext from the user with the same salt you used for the stored hash, hash the new password+salt, and see if the hash is the same as what you stored. If it is, they gave the right password.

• You almost certainly don't need to recover cleartext passwords. Implement a secure password reset instead. If you really, really must, use a decently secure algorithm like aes to encrypt them and think carefully about key storage and management. See other posts on SO about key storage/management with pgcrypto.

See also:

• Secure method for storing/retrieving a PGP private key and passphrase?

Convocation

thanks for your advice. I know what you're pointing though, I do need to use des algorithm because stupid developer wrote query which makes encrypted column joined. I really need fast and small encrypted data for it. – Annulus

@Annulus Then make them fix the query. Comparing des vs comparing aes won't make that much difference compared to designing it sanely. You're porting between DBs so you'll be changing/fixing some queries anyway; this is a chance to improve it in both. – Convocation

@Annulus A common technique when you want to JOIN on columns that're stored encrypted is to store a hash of the plaintext and JOIN on the hashes, then filter out collisions. You can use quite a small, non-cryptographic hash, because once you have the results of the JOIN you weed out hash collisions by comparing the decrypted plaintext. Think SELECT * FROM (SELECT * FROM t1 INNER JOIN t2 ON t1.hash_val = t2.hash_val) WHERE decrypt(t1.real_val) = decrypt(t2.real_val); – Convocation

Answer 2

Depending on how your postgresql was built, it may have DES support in the pgcrypto module. It depends on if Postgres was configured with OpenSSL support as it relies on OpenSSL to do DES (while with other more modern algorithms it implements them itself).

PGCrypto Algorithms

If openssl support was included and you specify DES as the algorithm to encrypt and decrypt, the data should be the same as you get from Oracle (although you may need to specify padding options).

As Craig says though, the DES algorithm is weak and one of the reasons it is weak is because the output ciphertext is so small.

Deplane

are you telling me to configure with openssl like.. --with-openssl? – Annulus