pgcrypto

The pgcrypto module provides cryptographic functions for PostgreSQL™.

The functions crypt() and gen_salt() are specifically designed for hashing passwords. crypt() does the hashing and gen_salt() prepares algorithm parameters for it.

The algorithms in crypt() differ from the usual MD5 or SHA1 hashing algorithms in the following respects:

Table F.15, “Supported Algorithms for crypt() lists the algorithms supported by the crypt() function.

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.16, “Iteration Counts for crypt().

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.17, “Hash Algorithm Speeds” 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 lower case 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:

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.

The functions here implement the encryption part of the OpenPGP (RFC 4880) standard. Supported are both symmetric-key and public-key encryption.

An encrypted PGP message consists of 2 parts, or packets:

When encrypting with a symmetric key (i.e., a password):

When encrypting with a public key:

In either case the data to be encrypted is processed as follows:

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.

These functions only run a cipher over data; they don't have any advanced features of PGP encryption. Therefore they have some major problems:

So, with the introduction of PGP encryption, usage of raw encryption functions is discouraged.

encrypt(data bytea, key bytea, type text) returns bytea
decrypt(data bytea, key bytea, type text) returns bytea

encrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea
decrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea

Encrypt/decrypt data using the cipher method specified by type. The syntax of the type string is:

algorithm [ - mode ] [ /pad: padding ]

where algorithm is one of:

  • bf — Blowfish

  • aes — AES (Rijndael-128)

and mode is one of:

  • cbc — next block depends on previous (default)

  • ecb — each block is encrypted separately (for testing only)

and padding is one of:

  • pkcs — data may be any length (default)

  • none — data must be multiple of cipher block size

So, for example, these are equivalent:

encrypt(data, 'fooz', 'bf')
encrypt(data, 'fooz', 'bf-cbc/pad:pkcs')

In encrypt_iv and decrypt_iv, the iv parameter is the initial value for the CBC mode; it is ignored for ECB. It is clipped or padded with zeroes if not exactly block size. It defaults to all zeroes in the functions without this parameter.

gen_random_bytes(count integer) returns bytea

Returns count cryptographically strong random bytes. At most 1024 bytes can be extracted at a time. This is to avoid draining the randomness generator pool.

gen_random_uuid() returns uuid

Returns a version 4 (random) UUID.

Marko Kreen

pgcrypto uses code from the following sources:

AlgorithmAuthorSource origin
DES cryptDavid Burren and othersFreeBSD libcrypt
MD5 cryptPoul-Henning KampFreeBSD libcrypt
Blowfish cryptSolar Designerwww.openwall.com
Blowfish cipherSimon TathamPuTTY
Rijndael cipherBrian GladmanOpenBSD sys/crypto
MD5 hash and SHA1WIDE ProjectKAME kame/sys/crypto
SHA256/384/512 Aaron D. GiffordOpenBSD sys/crypto
BIGNUM mathMichael J. Frombergerdartmouth.edu/~sting/sw/imath