Data encryption in PostgreSQL

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

standard data hashing — digest(), hmac();
password hashing — crypt(), gen_salt();
symmetric and public keys (encrypting 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, take into account the security limitations.

See the examples of data encryption using the pgcrypto extension.

Learn more about the pgcrypto extension and its functions in the pgcrypto — cryptographic functions section of 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 connecting to the database cluster using 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 data encryption. Keep the secret key in a safe place — it is required for data decryption.
Verify 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 encryption with a public key

Install the GnuPG utility.
Open the CLI.
Create a PGP key:
```
gpg --full-generate-key
```
Specify:
- the 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 user name 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 content of the public.key file you created in step 4.

Verify that 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;

Where:

passphrase is the key passphrase; ;
private_key is the content of the private.key file you created in step 4.