F.20.1.1.digest()

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

Computes a binary hash of the givendata.typeis the algorithm to use. Standard algorithms aremd5,sha1,sha224,sha256,sha384andsha512. Ifpgcryptowas built with OpenSSL,more algorithms are available,as detailed inTable F-21.

If you want the digest as a hexadecimal string,useencode()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.20.1.2.hmac()

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

Calculates hashed MAC fordatawith keykey.typeis the same as indigest().

This is similar todigest()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.20.2.1.crypt()

    crypt(password text,salt text) returns text
   

Calculates a crypt(3)-style hash ofpassword. When storing a new password,you need to usegen_salt()to generate a newsaltvalue. To check a password,pass the stored hash value assalt,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 returnstrueif the entered password is correct.

F.20.2.2.gen_salt()

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

Generates a new random salt string for use incrypt(). The salt string also tellscrypt()which algorithm to use.

Thetypeparameter specifies the hashing algorithm. The accepted types are:des,xdes,md5andbf.

Theiter_countparameter 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 theiter_countparameter is omitted,the default iteration count is used. Allowed values foriter_countdepend on the algorithm:

Forxdesthere 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.

Here is a table that 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 thecrypt-bfentries,the number after a slash is theiter_countparameter ofgen_salt.

Notes:

• The machine used is a 1.5GHz Pentium 4.

• crypt-desandcrypt-md5algorithm numbers are taken from John the Ripper v1.6.38-testoutput.

• md5numbers are from mdcrack 1.2.

• sha1numbers are from lcrack-20031130-beta.

• crypt-bfnumbers 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 -testshows 213 loops/sec forcrypt-bf/5. (The very small difference in results is in accordance with the fact that thecrypt-bfimplementation inpgcryptois 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.20.3.1.pgp_sym_encrypt()

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

Encryptdatawith a symmetric PGP keypsw. Theoptionsparameter can contain option settings,as described below.

F.20.3.2.pgp_sym_decrypt()

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

Decrypt a symmetric-key-encrypted PGP message.

Decrypting bytea data withpgp_sym_decryptis disallowed. This is to avoid outputting invalid character data. Decrypting originally textual data withpgp_sym_decrypt_byteais fine.

Theoptionsparameter can contain option settings,102)"> F.20.3.3.pgp_pub_encrypt()

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

Encryptdatawith a public PGP keykey. Giving this function a secret key will produce a error.

Theoptionsparameter can contain option settings,102)"> F.20.3.4.pgp_pub_decrypt()

    pgp_pub_decrypt(msg bytea,options text ]]) returns text
    pgp_pub_decrypt_bytea(msg bytea,options text ]]) returns bytea
   

Decrypt a public-key-encrypted message.keymust 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 inpsw. If there is no password,but you want to specify options,you need to give an empty password.

Decrypting bytea data withpgp_pub_decryptis disallowed. This is to avoid outputting invalid character data. Decrypting originally textual data withpgp_pub_decrypt_byteais fine.

Theoptionsparameter can contain option settings,102)"> F.20.3.5.pgp_key_id()

    pgp_key_id(bytea) returns text
   

pgp_key_idextracts 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.pgcryptoitself 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 handlingANYKEY.

F.20.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.20.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 exceptconvert-crlfapply only to encrypt functions. Decrypt functions get the parameters from the PGP data.

The most interesting options are probablycompress-algoandunicode-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,102)"> F.20.3.7.3. compress-level 
   
How much to compress. Higher levels compress smaller but are slower. 0 disables compression.
 
   
    Values: 0,1-9
    Default: 6
    Applies to: pgp_sym_encrypt,102)"> F.20.3.7.4. convert-crlf 
   
Whether to convert\ninto\r\nwhen encrypting and\r\nto\nwhen decrypting. RFC 4880 specifies that text data should be stored using\r\nline-Feeds. Use this to get fully RFC-compliant behavior.
 
   
    Values: 0,1
    Default: 0
    Applies to: pgp_sym_encrypt,pgp_pub_encrypt,pgp_sym_decrypt,pgp_pub_decrypt
   
 
  

    Values: 0,102)"> F.20.3.7.6. enable-session-key 
   
Use separate session key. Public-key encryption always uses a separate session key; this is for symmetric-key encryption,which by default uses the S2K key directly.
 
   
    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,aes256
    Default: use cipher-algo
    Applies to: pgp_sym_encrypt
   

    Values: 0,pgp_pub_encrypt
   

F.20.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 withgpg --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 usedearmor()on these keys before giving them to the PGP functions. Or if you can handle binary data,you can drop-afrom the command.

For more details seeman gpg,The GNU Privacy Handbookand other documentation onhttp://www.gnupg.org.

F.20.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 withpgcrypto,but create new ones,as the usage scenario is rather different.

F.20.6.1. Configuration

pgcryptoconfigures itself according to the findings of the main Postgresqlconfigurescript. The options that affect it are--with-zliband--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,pgcryptowill 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,pgcryptowill use built-in code.

F.20.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.20.6.3. Security limitations

Allpgcryptofunctions run inside the database server. That means that all the data and passwords move betweenpgcryptoand 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.20.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.20.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://www.cs.ut.ee/~helger/crypto/

  Collection of cryptology pointers.