Encryption using pgcrypto
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:
-
Optional data-manipulation: compression, conversion to UTF-8, line-endings conversion.
-
A block of random bytes is added before data.
-
A SHA1 hash of the random prefix and data is appended.
-
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 |
|
compress-level |
A level of compression. The higher the level, the smaller the result and the longer the compression process. The value |
6 |
0-9 |
convert-crlf |
Specifies whether to convert |
0 |
|
disable-mdc |
Disables data protection with SHA-1. Use this option only to achieve compatibility with ancient PGP products |
0 |
|
sess-key |
Specifies whether to use a separate session key |
0 |
|
s2k-mode |
An S2K algorithm to use |
3 |
|
s2k-count |
A number of iterations for the S2K algorithm. To apply this setting, the |
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 |
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 |
|
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 |
0 |
|
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 |
|
compress-level |
A level of compression. The higher the level, the smaller the result and the longer the compression process. The value |
6 |
0-9 |
convert-crlf |
Specifies whether to convert |
0 |
|
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 |
|
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 |
0 |
|
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.
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 |
|
mode |
|
padding |
|
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.