Data encryption in PostgreSQL

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

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

When using the pgcrypto extension, consider the security limitations.

See examples of encrypting data using the pgcrypto extension.

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

Security limitations of the pgcrypto extension

data and passwords are transmitted between pgcrypto functions and client applications in plaintext, so we recommend that you connect to the database cluster with SSL;
data protection using the pgcrypto extension is vulnerable to side-channel attacks. For example, the decryption time for 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);

Insert data into the table and encrypt it:

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

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

Ensure that the encrypted data contains the text you encrypted in step 2.

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

PGP encryption with a symmetric key

Create a table:

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

Insert the data you wish 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 data. Keep the secret key in a secure place — it is required to decrypt data.
Verify that the data is encrypted:
```
SELECT * FROM tbl_crypt_sym_key;
```

To decrypt data, use the pgp_sym_decrypt function:

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

PGP encryption with a public key

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;
- the key size in bits;
- the key expiration date;
- the user name (Real name);
- the email address;
- the key passphrase.
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 that you specified when creating 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 you want to encrypt 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 that you created in step 4.

Verify that the data is encrypted:
```
SELECT * FROM tbl_crypt_pub_key;
```

To decrypt 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;

Where:

passphrase — key passphrase;
private_key — the contents of the private.key file that you created in step 4.