Data encryption in PostgreSQL TimescaleDB

To encrypt the data in PostgreSQL TimescaleDB, you can add the pgcrypto extension. This extension provides a set of cryptographic functions to protect data with:

standard data hashing — digest(), hmac();
password hashing — crypt(), gen_salt();
symmetric and public keys (encrypt data according to OpenPGP standard) — pgp_sym_encrypt(), pgp_sym_decrypt(), pgp_pub_encrypt(), pgp_pub_decrypt(), armor(), dearmor();
low-level encryption — encrypt(), decrypt();
receiving random data — gen_random_bytes(), gen_random_uuid().

When using the pgcrypto extension, be aware of security restrictions.

See examples of data encryption using the pgcrypto extension.

Read more about the pgcrypto extension and its functions in the article pgcrypto — cryptographic functions documentation PostgreSQL.

Security limitations of the pgcrypto extension

data and passwords are transmitted between pgcrypto functions and client applications in clear text, so we recommend connecting to the database cluster with SSL;
data protection with the pgcrypto extension is vulnerable to attacks via third-party channels. For example, the duration of decryption of texts of the same size may differ.

Examples of data encryption using the pgcrypto extension

Password hashing

Create a table:

CREATE TABLE tbl_hash_pwd(username varchar(100) PRIMARY KEY, cryptpwd text);

Paste the data into a table and encrypt it:

INSERT INTO tbl_hash_pwd(username, cryptpwd)
    VALUES ('username_1', crypt('Password_1', gen_salt('md5')));

Check that the data is encrypted:
```
SELECT * FROM tbl_hash_pwd;
```

Make sure that the encrypted data stores the text you encrypted in step 2.

SELECT username FROM tbl_hash_pwd
WHERE cryptpwd = crypt('Password_1', cryptpwd);

PGP encryption with symmetric key

Create a table:

CREATE TABLE tbl_crypt_sym_key (id SERIAL PRIMARY KEY, data text);

Insert the data you want to encrypt into the table:
```
INSERT INTO tbl_crypt_sym_key (data) VALUES (pgp_sym_encrypt('My confidential information', 'secret_key'));
```
Here secret_key is the secret key for encrypting the data. Save the secret key in a safe place — it is needed to decrypt the data.
Check that the data is encrypted:
```
SELECT * FROM tbl_crypt_sym_key;
```

To decrypt the data, use the pgp_sym_decrypt function:

SELECT pgp_sym_decrypt(data::bytea, 'secret_key') FROM tbl_crypt_sym_key;

PGP public key encryption

Install the GnuPG utility.
Open the CLI.
Create a PGP key:
```
gpg --full-generate-key
```
Specify:
- key type. The recommended key type is DSA and Elgamal;
- key size in bits;
- key expiration date;
- user name (Real name);
- e-mail address;
- the passphrase of the key.
Export the keys to files:
```
gpg -a --export real_name > public.key && \
gpg -a --export-secret-keys real_name > private.key
```
Here real_name is the username you specified when you created the key in step 3.

Create a table:

CREATE TABLE tbl_crypt_pub_key(ssn_id SERIAL PRIMARY KEY,
    username varchar(100), ssn bytea);

Add the data to be encrypted and encrypt it:

INSERT INTO tbl_crypt_pub_key (username, ssn)
SELECT tmp.username, pgp_pub_encrypt(tmp.ssn, keys.pubkey) AS tbl
FROM (
    VALUES ('username_1', '123-45-6789'), ('username_2', '123-45-67890'))
    AS tmp(username, ssn)
CROSS JOIN (SELECT  dearmor('public_key') AS pubkey) AS keys;

Here public_key is the contents of the public.key file you created in step 4.

Make sure the data is encrypted:
```
SELECT * FROM tbl_crypt_pub_key;
```

To decrypt the data, use the pgp_pub_decrypt function:

SELECT username, pgp_pub_decrypt(ssn, keys.privkey, 'passphrase')
    AS decrypted_ssn FROM tbl_crypt_pub_key
CROSS JOIN (SELECT dearmor('private_key') AS privkey) AS keys;

Here:

passphrase — key passphrase;
private_key — file content private.key file you created in step 4.