Encryption

You can use the additional pgcrypto module to encrypt specific database fields. It can be useful if a part of data is sensitive. Pgcrypto provides a number of hashing and cryptographic functions that allows you to encrypt data with symmetric and public-key cryptographic algorithms, hash passwords, and get random data.

Security limitations

All pgcrypto functions run on the database server. The data and passwords move between pgcrypto and client applications in clear text. You must connect locally or use SSL connections. Alternatively, you can encrypt data inside the client application.

Note, data protection with pgcrypto is vulnerable for side-channel attacks.

According to the SQL standard, all pgcrypto functions return NULL if any of the arguments are NULL. This may cause security risks on careless usage.

Install pgcrypto

Pgcrypto is included in the contrib extension that is preinstalled in ADPG. For more information, see Work with extensions.

The pgcrypto module is trusted, users with CREATE privilege on the current database can install the module.

The following command installs pgcrypto:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Supported algorithms

The following algorithms are supported:

  • MD5;

  • SHA1;

  • SHA224/256/384/512;

  • other digest algorithms supported by OpenSSL, except ciphers, which should be explicitly supported;

  • Blowfish;

  • AES;

  • DES/3DES/CAST5;

  • raw encryption;

  • PGP Symmetric encryption;

  • PGP Public-Key encryption.

Pgcrypto functions

The pgcrypto module includes cryptographic functions. You can utilize them to hash data, hash passwords, encrypt data with symmetric and public keys (PGP) and raw encryption algorithms, get random data.

General hashing functions

digest()

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

This function calculates a binary hash of the given data. The type parameter defines the algorithm to use. Standard algorithms are md5, sha1, sha224, sha256, sha384, and sha512. You can also use any supported algorithm. The example below uses digest to generate a binary hash of the key_code field.

CREATE TABLE table1( key_code text, hash bytea );

CREATE OR REPLACE FUNCTION update_table1() RETURNS trigger AS $$
BEGIN
        NEW.hash = digest(NEW.key_code, 'sha256');
        RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER table1_hash_insert
BEFORE INSERT ON table1
FOR EACH ROW
EXECUTE PROCEDURE update_table1();

CREATE TRIGGER table1_hash_update
BEFORE UPDATE ON table1
FOR EACH ROW
WHEN ( NEW.key_code IS DISTINCT FROM OLD.key_code )
EXECUTE PROCEDURE update_table1();

You can use encode to convert a binary string into hexadecimal:

SELECT encode(digest('MyPassword', 'sha256'),'hex');

hmac()

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

This function calculates hashed MAC (message authentication code) for data with the specified key. The type parameter defines the algorithm to use.

Hmac is similar to digest, but you need to know the key to recalculate the hash. It prevents the scenario where someone modifies the data and also changes the hash.

SELECT hmac('MyPassword', 'My_key', 'sha256');

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

Password hashing functions

The crypt and gen_salt functions are designed for hashing passwords. The crypt function performs the hashing, and gen_salt prepares the algorithm parameters.

The algorithms in crypt differ from the standard hashing algorithms:

  • They are slow. Since the amount of data is small, this is the way to make brute-forcing passwords difficult.

  • They use a random value called the salt. Users with the same passwords have different encrypted passwords.

  • They include the algorithm type in the result, so passwords hashed with different algorithms can co-exist.

  • Some of them are adaptive. When computers get faster, you can tune the algorithm to be slower and keep it compatible with existing passwords.

The table below lists the supported algorithms.

Name Max password length Adaptivity Salt, bits Output length Description

bf

72

yes

128

60

Blowfish-based, variant 2a

md5

unlimited

no

48

34

MD5-based crypt

xdes

8

yes

24

20

Extended DES

des

8

no

12

13

Original UNIX crypt

crypt()

crypt(password text, salt text) returns text

This function calculates a crypt(3)-style hash of the password parameter. Use gen_salt to generate a new salt value when you store a new password.

The following example shows how to set a new password using the crypt function:

UPDATE table1 SET password_hash = crypt('new_password', gen_salt('md5'));

The following statement retrieves data for authentication and returns true if the entered password is correct:

SELECT (password_hash = crypt('entered_password', password_hash)) AS password_match FROM table1;

gen_salt()

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

This function generates a new random salt string for crypt. The salt string also defines the algorithm used in crypt. The type parameter specifies the hashing algorithm. The possible values are: des, xdes, md5, and bf.

The iter_count parameter specifies the number of iteration for the xdes and bf algorithms. The larger the number, the longer it takes to hash the password and the longer to break it. Although, if the value is too large, the calculation time can be several years. If the iter_count parameter is omitted, the default value is used. The table below contains allowed values for iter_count. The xdes algorithm has an additional limitation that iter_count must be an odd number.

Algorithm Default Min Max

xdes

725

1

16777215

bf

6

4

31

PGP encryption functions

PGP encryption functions implement the encryption part of the OpenPGP (RFC 4880) standard. They support the symmetric-key and public-key encryption.

An encrypted PGP message consists of 2 parts or packets:

  • A packet that contains a session key (symmetric-key or public-key).

  • A packet that contains data encrypted with the session key.

When encryption is performed with a symmetric key (password), the String2Key (S2K) algorithm is used to hash a password. S2K is similar to crypt algorithms, it is purposefully slow and uses a random salt. S2K produces a full-length binary key. If a separate session key is requested, a new random key is generated. Otherwise, the S2K key is used as the session key. If the S2K key is a session key, the session key packet contains only S2K settings. Otherwise, the session key is encrypted with the S2K key and put into the session key packet.

When encryption is performed with a public key, a new random session key is generated. The session key is encrypted with the public key and put into the session key packet. For information on key generation, see Generate PGP keys with GnuPG.

The data encryption includes the following steps:

  1. Optional data-manipulation: compression, conversion to UTF-8, line-endings conversion.

  2. A block of random bytes is added before data.

  3. A SHA1 hash of the random prefix and data is appended.

  4. The data with the random prefix and their hash are encrypted with the session key and placed in the data packet.

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

This function encrypts data with a symmetric PGP key. The data parameter is data to encrypt, psw is a symmetric PGP key. The options parameter contains option settings separated from each other with commas. The table below lists available options.

Name Description Default value Possible values

cipher-algo

A cipher algorithm to use

aes128

bf, aes128, aes192, aes256; openSSL-only: 3des, cast5

compress-algo

A compression algorithm to use

0

0 — no compression, 1 - ZIP compression, 2 — ZLIB compression

compress-level

A level of compression. The higher the level, the smaller the result and the longer the compression process. The value 0 disables compression

6

0-9

convert-crlf

Specifies whether to convert \n into \r\n when encrypting and \r\n to \n when decrypting. RFC 4880 specifies that text data should contain \r\n line-feeds. Use this option to get fully RFC-compliant behavior

0

0 — not convert, 1 — convert

disable-mdc

Disables data protection with SHA-1. Use this option only to achieve compatibility with ancient PGP products

0

0 — enable data protection, 1 — disable data protection

sess-key

Specifies whether to use a separate session key

0

0 — uses the S2K key as a session key, 1 — uses a separate session key

s2k-mode

An S2K algorithm to use

3

0 — without salt (not recommended), 1 — with salt and fixed iteration count, 3 — with salt and variable iteration count

s2k-count

A number of iterations for the S2K algorithm. To apply this setting, the s2k-mode should be equal 3

A random value between 65536 and 253952

A value between 1024 and 65011712

s2k-digest-algo

A digest algorithm to use in the S2K calculation

sha1

md5, sha1

s2k-cipher-algo

Cipher to use for encrypting a separate session key

The cipher-algo value is used

bf, aes, aes128, aes192, aes256

unicode-mode

Specifies whether text data should be converted from database internal encoding to UTF-8 and back. If the database is UTF-8, no conversion is done, but the message is tagged as UTF-8

0

0 — not convert, 1 — convert

Example:

CREATE TABLE users (user_id serial PRIMARY KEY, username VARCHAR(50), password TEXT);

INSERT INTO users (username, password)
VALUES ('Robert',pgp_sym_encrypt('password', 'aes_key', 'compress-algo=1, cipher-algo=aes256'));

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

This function decrypts a symmetric-key-encrypted PGP message. The msg parameter is a message to decrypt, psw is a symmetric PGP key. The options parameter contains option settings. The table below lists available options.

Name Description Default value Possible values

convert-crlf

Specifies whether to convert \n into \r\n when encrypting and \r\n to \n when decrypting. RFC 4880 specifies that text data should contain \r\n line-feeds. Use this option to get fully RFC-compliant behavior

0

0 — not convert, 1 — convert

Example:

SELECT username, pgp_sym_decrypt(password::bytea, 'aes_key') as password
FROM users WHERE (username LIKE '%Robert%');

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

This function encrypts data with a public PGP key. If you pass this function a secret key, an error occurs. Use the pgp_sym_decrypt_bytea function to encrypt bytea data. You can also utilize pgp_pub_encrypt_bytea to encrypt text data.

The data parameter is data to encrypt, key is a public PGP key. The options parameter contains option settings separated from each other with commas. The table below lists available options.

Name Description Default value Possible values

cipher-algo

A cipher algorithm to use

aes128

bf, aes128, aes192, aes256; openSSL-only: 3des, cast5

compress-algo

A compression algorithm to use

0

0 — no compression, 1 - ZIP compression, 2 — ZLIB compression

compress-level

A level of compression. The higher the level, the smaller the result and the longer the compression process. The value 0 disables compression

6

0-9

convert-crlf

Specifies whether to convert \n into \r\n when encrypting and \r\n to \n when decrypting. RFC 4880 specifies that text data should contain \r\n line-feeds. Use this option to get fully RFC-compliant behavior

0

0 — not convert, 1 — convert

unicode-mode

Specifies whether text data should be converted from database internal encoding to UTF-8 and back. If the database is UTF-8, no conversion is done, but the message is tagged as UTF-8

0

0 — not convert, 1 — convert

Example:

INSERT INTO users (username, password) VALUES ('Robert',pgp_pub_encrypt('password', dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----XXX-----END PGP PUBLIC KEY BLOCK-----')));

In this example the dearmor function unwraps binary data from the PGP ASCII-armor format.

 

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

This function decrypts a public-key-encrypted message. Use the pgp_pub_decrypt_bytea function to decrypt bytea data. You can also utilize pgp_pub_decrypt_bytea to decrypt text data.

The msg parameter is a message to decrypt, key should contain the secret key corresponding to the public key that is used for encryption. If the secret key is password-protected, pass the password as the psw parameter. If there is no password, but you want to specify options, use an empty password. The options parameter contains option settings. The table below lists available options.

Name Description Default value Possible values

convert-crlf

Specifies whether to convert \n into \r\n when encrypting and \r\n to \n when decrypting. RFC 4880 specifies that text data should contain \r\n line-feeds. Use this option to get fully RFC-compliant behavior

0

0 — not convert, 1 — convert

Example:

SELECT pgp_pub_decrypt(password::bytea,dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----XXXX-----END PGP PRIVATE KEY BLOCK-----'),'PRIVATE-KEY-PASSWORD') AS password FROM users;

In this example the dearmor function unwraps binary data from the PGP ASCII-armor format.

 

pgp_key_id()

pgp_key_id(bytea) returns text

This function returns the key ID of a PGP public or secret key. It also extracts the key ID that is used for encrypting the data, if an encrypted message is passed as a parameter.

It can also return two special key IDs:

  • SYMKEY — the message is encrypted with a symmetric key.

  • ANYKEY — the message is public-key encrypted, but the key ID is removed.

Note, different keys may have the same ID. In this case, the client application should try to decrypt the message with each key to determine which one is appropriate.

 

armor(), dearmor()

armor(data bytea [ , keys text[], values text[] ]) returns text
dearmor(data text) returns bytea

These functions wrap/unwrap binary data into PGP ASCII-armor format, which is based on 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 have the same length. The keys and values arrays can contain only ASCII characters.

 

pgp_armor_headers()

pgp_armor_headers(data text, key out text, value out text) returns setof record

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

Generate PGP keys with GnuPG

You can use the GnuPG tool to generate PGP keys. Execute the following command to generate a new key:

gpg --gen-key

The terminal asks you to select a type of key:

Please select what kind of key you want:
(1) RSA and RSA (default)
(2) DSA and Elgamal
(3) DSA (sign only)
(4) RSA (sign only)
Your selection?

The preferred key type is DSA and Elgamal. For RSA encryption, you should create DSA (sign only) or RSA (sign-only) key as master and then add an RSA encryption subkey with the gpg --edit-key command.

Execute the following command to list keys:

gpg --list-secret-keys

To export a public key in the ASCII-armor format:

gpg -a --export KEYID > public.key

To export a secret key in the ASCII-armor format:

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

Use the dearmor function on these keys before pass them to the PGP functions. Alternatively, if you can handle binary data, omit the -a option from the gpg command.

Raw encryption functions

These functions run a cipher over data and do not have any advanced features of the PGP encryption. They have the following major problems:

  • Functions use a user key directly as cipher key.

  • Functions do not provide any integrity checking, to see if the encrypted data is modified.

  • Users manage all encryption parameters themselves, even IV.

  • Functions do not handle text.

CAUTION
Usage of raw encryption functions is not recommended. Use the PGP encryption functions instead.

Raw encryption functions use the cipher method specified by type to encrypt/decrypt data.

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

The syntax of the type string is:

algorithm [ - mode ] [ /pad: padding ]

The table below contains possible parameter values.

Name Values

algorithm

  • bf — Blowfish;

  • aes — AES (Rijndael-128, -192 or -256).

mode

  • cbc — next block depends on previous (default);

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

padding

  • pkcs — data can be of arbitrary length (default);

  • none — data should be multiple of cipher block size.

The following functions are equivalent:

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

In encrypt_iv and decrypt_iv, the iv parameter specifies the initial value for the CBC mode. This value is ignored for the ECB mode. The initial value is truncated or padded with zeros if its size is not exactly the size of the block. In functions without this parameter, it is filled with zeros by default.

Random-data functions

gen_random_bytes()

gen_random_bytes(count integer) returns bytea

This function returns a specified number (the count parameter) of cryptographically strong random bytes. A maximum of 1024 bytes can be received per call to avoid draining the randomness generator pool.

 

gen_random_uuid()

gen_random_uuid() returns uuid

This function returns a version 4 (random) UUID.

Found a mistake? Seleсt text and press Ctrl+Enter to report it