F.23. pgcrypto

F.23.1.1. digest()

    digest(data text, type text) returns bytea
    digest(data bytea, type text) returns bytea
   

Computes a binary hash of the given data. type is the algorithm to use. Standard algorithms are md5, sha1, sha224, sha256, sha384 and sha512. If pgcrypto was built with OpenSSL, more algorithms are available, as detailed in Table F-21.

If you want the digest as a hexadecimal string, use encode() on the result. For example:

    CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$
      SELECT encode(digest($1, 'sha1'), 'hex')
    $$ LANGUAGE SQL STRICT IMMUTABLE;
   

F.23.1.2. hmac()

    hmac(data text, key text, type text) returns bytea
    hmac(data bytea, key text, type text) returns bytea
   

Calculates hashed MAC for data with key key. type is the same as in digest().

This is similar to digest() but the hash can only be recalculated knowing the key. This prevents the scenario of someone altering data and also changing the hash to match.

If the key is larger than the hash block size it will first be hashed and the result will be used as key.

F.23.2.1. crypt()

    crypt(password text, salt text) returns text
   

Calculates a crypt(3)-style hash of password. When storing a new password, you need to use gen_salt() to generate a new salt value. To check a password, pass the stored hash value as salt, and test whether the result matches the stored value.

Example of setting a new password:

    UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));
   

Example of authentication:

    SELECT pswhash = crypt('entered password', pswhash) FROM ... ;
   

This returns true if the entered password is correct.

F.23.2.2. gen_salt()

    gen_salt(type text [, iter_count integer ]) returns text
   

Generates a new random salt string for use in crypt(). The salt string also tells crypt() which algorithm to use.

The type parameter specifies the hashing algorithm. The accepted types are: des, xdes, md5 and bf.

The iter_count parameter lets the user specify the iteration count, for algorithms that have one. The higher the count, the more time it takes to hash the password and therefore the more time to break it. Although with too high a count the time to calculate a hash may be several years — which is somewhat impractical. If the iter_count parameter is omitted, the default iteration count is used. Allowed values for iter_count depend on the algorithm and are shown in Table F-19.

For xdes there is an additional limitation that the iteration count must be an odd number.

To pick an appropriate iteration count, consider that the original DES crypt was designed to have the speed of 4 hashes per second on the hardware of that time. Slower than 4 hashes per second would probably dampen usability. Faster than 100 hashes per second is probably too fast.

Table F-20 gives an overview of the relative slowness of different hashing algorithms. The table shows how much time it would take to try all combinations of characters in an 8-character password, assuming that the password contains either only lowercase letters, or upper- and lower-case letters and numbers. In the crypt-bf entries, the number after a slash is the iter_count parameter of gen_salt.

Notes:

• The machine used is a 1.5GHz Pentium 4.

• crypt-des and crypt-md5 algorithm numbers are taken from John the Ripper v1.6.38 -test output.

• md5 numbers are from mdcrack 1.2.

• sha1 numbers are from lcrack-20031130-beta.

• crypt-bf numbers are taken using a simple program that loops over 1000 8-character passwords. That way I can show the speed with different numbers of iterations. For reference: john -test shows 213 loops/sec for crypt-bf/5. (The very small difference in results is in accordance with the fact that the crypt-bf implementation in pgcrypto is the same one used in John the Ripper.)

Note that "try all combinations" is not a realistic exercise. Usually password cracking is done with the help of dictionaries, which contain both regular words and various mutations of them. So, even somewhat word-like passwords could be cracked much faster than the above numbers suggest, while a 6-character non-word-like password may escape cracking. Or not.

F.23.3.1. pgp_sym_encrypt()

    pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
    pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea
   

Encrypt data with a symmetric PGP key psw. The options parameter can contain option settings, as described below.

F.23.3.2. pgp_sym_decrypt()

    pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text
    pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea
   

Decrypt a symmetric-key-encrypted PGP message.

Decrypting bytea data with pgp_sym_decrypt is disallowed. This is to avoid outputting invalid character data. Decrypting originally textual data with pgp_sym_decrypt_bytea is fine.

The options parameter can contain option settings, as described below.

F.23.3.3. pgp_pub_encrypt()

    pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea
    pgp_pub_encrypt_bytea(data bytea, key bytea [, options text ]) returns bytea
   

Encrypt data with a public PGP key key. Giving this function a secret key will produce a error.

The options parameter can contain option settings, as described below.

F.23.3.4. pgp_pub_decrypt()

    pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text ]]) returns text
    pgp_pub_decrypt_bytea(msg bytea, key bytea [, psw text [, options text ]]) returns bytea
   

Decrypt a public-key-encrypted message. key must be the secret key corresponding to the public key that was used to encrypt. If the secret key is password-protected, you must give the password in psw. If there is no password, but you want to specify options, you need to give an empty password.

Decrypting bytea data with pgp_pub_decrypt is disallowed. This is to avoid outputting invalid character data. Decrypting originally textual data with pgp_pub_decrypt_bytea is fine.

The options parameter can contain option settings, as described below.

F.23.3.5. pgp_key_id()

    pgp_key_id(bytea) returns text
   

pgp_key_id extracts the key ID of a PGP public or secret key. Or it gives the key ID that was used for encrypting the data, if given an encrypted message.

It can return 2 special key IDs:

• SYMKEY

  The message is encrypted with a symmetric key.

• ANYKEY

  The message is public-key encrypted, but the key ID has been removed. That means you will need to try all your secret keys on it to see which one decrypts it. pgcrypto itself does not produce such messages.

Note that different keys may have the same ID. This is rare but a normal event. The client application should then try to decrypt with each one, to see which fits — like handling ANYKEY.

F.23.3.6. armor(), dearmor()

    armor(data bytea) returns text
    dearmor(data text) returns bytea
   

These functions wrap/unwrap binary data into PGP Ascii Armor format, which is basically Base64 with CRC and additional formatting.

F.23.3.7. Options for PGP functions

Options are named to be similar to GnuPG. An option's value should be given after an equal sign; separate options from each other with commas. For example:

    pgp_sym_encrypt(data, psw, 'compress-algo=1, cipher-algo=aes256')
   

All of the options except convert-crlf apply only to encrypt functions. Decrypt functions get the parameters from the PGP data.

The most interesting options are probably compress-algo and unicode-mode. The rest should have reasonable defaults.

    Values: bf, aes128, aes192, aes256 (OpenSSL-only: 3des, cast5)
    Default: aes128
    Applies to: pgp_sym_encrypt, pgp_pub_encrypt
   

    Values:
      0 - no compression
      1 - ZIP compression
      2 - ZLIB compression (= ZIP plus meta-data and block CRCs)
    Default: 0
    Applies to: pgp_sym_encrypt, pgp_pub_encrypt
   

    Values: 0, 1-9
    Default: 6
    Applies to: pgp_sym_encrypt, pgp_pub_encrypt
   

    Values: 0, 1
    Default: 0
    Applies to: pgp_sym_encrypt, pgp_pub_encrypt, pgp_sym_decrypt, pgp_pub_decrypt
   

    Values: 0, 1
    Default: 0
    Applies to: pgp_sym_encrypt, pgp_pub_encrypt
   

    Values: 0, 1
    Default: 0
    Applies to: pgp_sym_encrypt
   

    Values:
      0 - Without salt.  Dangerous!
      1 - With salt but with fixed iteration count.
      3 - Variable iteration count.
    Default: 3
    Applies to: pgp_sym_encrypt
   

    Values: md5, sha1
    Default: sha1
    Applies to: pgp_sym_encrypt
   

    Values: bf, aes, aes128, aes192, aes256
    Default: use cipher-algo
    Applies to: pgp_sym_encrypt
   

    Values: 0, 1
    Default: 0
    Applies to: pgp_sym_encrypt, pgp_pub_encrypt
   

F.23.3.8. Generating PGP keys with GnuPG

To generate a new key:

   gpg --gen-key
  

The preferred key type is "DSA and Elgamal".

For RSA encryption you must create either DSA or RSA sign-only key as master and then add an RSA encryption subkey with gpg --edit-key.

To list keys:

   gpg --list-secret-keys
  

To export a public key in ascii-armor format:

   gpg -a --export KEYID > public.key
  

To export a secret key in ascii-armor format:

   gpg -a --export-secret-keys KEYID > secret.key
  

You need to use dearmor() on these keys before giving them to the PGP functions. Or if you can handle binary data, you can drop -a from the command.

For more details see man gpg, The GNU Privacy Handbook and other documentation on http://www.gnupg.org.

F.23.3.9. Limitations of PGP code

• No support for signing. That also means that it is not checked whether the encryption subkey belongs to the master key.

• No support for encryption key as master key. As such practice is generally discouraged, this should not be a problem.

• No support for several subkeys. This may seem like a problem, as this is common practice. On the other hand, you should not use your regular GPG/PGP keys with pgcrypto, but create new ones, as the usage scenario is rather different.

F.23.6.1. Configuration

pgcrypto configures itself according to the findings of the main PostgreSQL configure script. The options that affect it are --with-zlib and --with-openssl.

When compiled with zlib, PGP encryption functions are able to compress data before encrypting.

When compiled with OpenSSL, there will be more algorithms available. Also public-key encryption functions will be faster as OpenSSL has more optimized BIGNUM functions.

Notes:

1. SHA2 algorithms were added to OpenSSL in version 0.9.8. For older versions, pgcrypto will use built-in code.

2. Any digest algorithm OpenSSL supports is automatically picked up. This is not possible with ciphers, which need to be supported explicitly.

3. AES is included in OpenSSL since version 0.9.7. For older versions, pgcrypto will use built-in code.

F.23.6.2. NULL handling

As is standard in SQL, all functions return NULL, if any of the arguments are NULL. This may create security risks on careless usage.

F.23.6.3. Security limitations

All pgcrypto functions run inside the database server. That means that all the data and passwords move between pgcrypto and client applications in clear text. Thus you must:

1. Connect locally or use SSL connections.

2. Trust both system and database administrator.

If you cannot, then better do crypto inside client application.

F.23.6.4. Useful reading

• http://www.gnupg.org/gph/en/manual.html

  The GNU Privacy Handbook.

• http://www.openwall.com/crypt/

  Describes the crypt-blowfish algorithm.

• http://www.stack.nl/~galactus/remailers/passphrase-faq.html

  How to choose a good password.

• http://world.std.com/~reinhold/diceware.html

  Interesting idea for picking passwords.

• http://www.interhack.net/people/cmcurtin/snake-oil-faq.html

  Describes good and bad cryptography.

F.23.6.5. Technical references

• http://www.ietf.org/rfc/rfc4880.txt

  OpenPGP message format.

• http://www.ietf.org/rfc/rfc1321.txt

  The MD5 Message-Digest Algorithm.

• http://www.ietf.org/rfc/rfc2104.txt

  HMAC: Keyed-Hashing for Message Authentication.

• http://www.usenix.org/events/usenix99/provos.html

  Comparison of crypt-des, crypt-md5 and bcrypt algorithms.

• http://csrc.nist.gov/cryptval/des.htm

  Standards for DES, 3DES and AES.

• http://en.wikipedia.org/wiki/Fortuna_(PRNG)

  Description of Fortuna CSPRNG.

• http://jlcooke.ca/random/

  Jean-Luc Cooke Fortuna-based /dev/random driver for Linux.

• http://research.cyber.ee/~lipmaa/crypto/

  Collection of cryptology pointers.