PostgreSQL

F.25.1. General Hashing Functions

F.25.2. Password Hashing Functions

F.25.3. PGP Encryption Functions

F.25.4. Raw Encryption Functions

F.25.5. Random-Data Functions

F.25.6. Notes

F.25.7. Author

The pgcrypto module provides cryptographic functions for PostgreSQL.

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

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

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.

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.16 lists the algorithms supported by the crypt() function.

Table F.16. Supported Algorithms for crypt()

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:

Example of authentication:

This returns true if the entered password is correct.

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

Table F.17. 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.18 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.

Table F.18. Hash Algorithm Speeds

Notes:

Note that “[.quote]#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:

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

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.

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

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

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.

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:

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.

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

If the `keys and values` arrays are specified, an armor header is added to the armored format for each key/value pair. Both arrays must be single-dimensional, and they must be of the same length. The keys and values cannot contain any non-ASCII characters.

pgp_armor_headers() extracts the armor headers from `data`. The return value is a set of rows with two columns, key and value. If the keys or values contain any non-ASCII characters, they are treated as UTF-8.

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:

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.

Which cipher algorithm to use.

Which compression algorithm to use. Only available if PostgreSQL was built with zlib.

How much to compress. Higher levels compress smaller but are slower. 0 disables compression.

Whether to convert \n into \r\n when encrypting and \r\n to \n when decrypting. RFC 4880 specifies that text data should be stored using \r\n line-feeds. Use this to get fully RFC-compliant behavior.

Do not protect data with SHA-1. The only good reason to use this option is to achieve compatibility with ancient PGP products, predating the addition of SHA-1 protected packets to RFC 4880. Recent gnupg.org and pgp.com software supports it fine.

Use separate session key. Public-key encryption always uses a separate session key; this option is for symmetric-key encryption, which by default uses the S2K key directly.

Which S2K algorithm to use.

The number of iterations of the S2K algorithm to use. It must be a value between 1024 and 65011712, inclusive.

Which digest algorithm to use in S2K calculation.

Which cipher to use for encrypting separate session key.

Whether to convert textual data from database internal encoding to UTF-8 and back. If your database already is UTF-8, no conversion will be done, but the message will be tagged as UTF-8. Without this option it will not be.

To generate a new key:

The preferred key type is “[.quote]#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:

To export a public key in ASCII-armor format:

To export a secret key in ASCII-armor format:

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 https://www.gnupg.org/.

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/decrypt data using the cipher method specified by `type. The syntax of the type` string is:

where `algorithm` is one of:

and `mode` is one of:

and `padding` is one of:

So, for example, these are equivalent:

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.

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.

Returns a version 4 (random) UUID.

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.

Table F.19. Summary of Functionality with and without OpenSSL

When compiled against OpenSSL 3.0.0 and later versions, the legacy provider must be activated in the openssl.cnf configuration file in order to use older ciphers like DES or Blowfish.

Notes:

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

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:

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

The implementation does not resist side-channel attacks. For example, the time required for a pgcrypto decryption function to complete varies among ciphertexts of a given size.

Marko Kreen <`[email protected]>`

pgcrypto uses code from the following sources:

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Privacy Policy | Code of Conduct | About PostgreSQL | Contact

Copyright © 1996-2023 The PostgreSQL Global Development Group