Data encryption in PostgreSQL TimescaleDB

Last update: October 08 2024

Data encryption in PostgreSQL TimescaleDB

To encrypt the data in PostgreSQL TimescaleDByou can add extension pgcrypto. 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 (encrypts data according to the standard OpenPGP) — 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, please note security restrictions.

Check it out examples of data encryption using the pgrypto extension.

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

Security limitations of the pgcrypto extension⁠

• data and passwords are transmitted between pgcrypto functions and client applications in clear text, so we recommend that connect 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 decrypting texts of the same size may differ.

Examples of data encryption using the pgcrypto extension⁠

Password hashing⁠

1. Create a table:

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

2. 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')));

3. Check that the data is encrypted:

   SELECT * FROM tbl_hash_pwd;

4. 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⁠

1. Create a table:

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

2. 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 — secret key to encrypt the data. Keep the secret key in a safe place — you need it to decrypt the data.

3. Check that the data is encrypted:

   SELECT * FROM tbl_crypt_sym_key;

4. To decrypt the data, use the function pgp_sym_decrypt:

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

PGP public key encryption⁠

1. Install the GnuPG utility.

2. Open the CLI.

3. Create a PGP key:

   gpg --full-generate-key

   Specify:

   • key type. The recommended key type is DSA и Elgamal;
   • key size in bits;
   • key expiration date;
   • user name (Real name);
   • e-mail address;
   • the passphrase of the key.

4. Export the keys to files:

   gpg -a --export real_name > public.key && \
   gpg -a --export-secret-keys real_name > private.key

   Here. real_name — the username you specified when creating the key in step 3.

5. Create a table:

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

6. 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 — file content public.keythat you created in step 4.

7. Make sure the data is encrypted:

   SELECT * FROM tbl_crypt_pub_key;

8. To decrypt the data, use the function pgp_pub_decrypt:

   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.keythat you created in step 4.