Encryption with pgcrypto in PostgreSQL

Encryption with pgcrypto in PostgreSQL

The pgcrypto module in PostgreSQL provides cryptographic functions for encrypting and decrypting data. It supports both symmetric encryption (using a single key) and public-key encryption (using a pair of keys). It's particularly useful for securely storing sensitive data in your database.

Documentation is available here : https://docs.postgresql.fr/15/pgcrypto.html

1. Enabling pgcrypto

Before using pgcrypto, ensure the extension is installed in your PostgreSQL database:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

2. Symmetric Encryption and Decryption

Symmetric encryption uses the same key for both encrypting and decrypting data.

Example: Encrypting and Decrypting a Column Value

-- Encrypting data
INSERT INTO secrets (data)
VALUES (pgp_sym_encrypt('This is a secret message', 'encryption_key'));

-- Decrypting data
SELECT pgp_sym_decrypt(data, 'encryption_key') AS decrypted_message
FROM secrets;

Explanation:

• pgp_sym_encrypt('data', 'key'): Encrypts the string 'data' using the key 'key'.
• pgp_sym_decrypt(data, 'key'): Decrypts the encrypted column data with the same key.

3. Hashing Data

Hashing is one-way encryption, meaning it cannot be decrypted. It’s commonly used for storing passwords.

Example: Generating and Comparing Hashes

-- Hash a password
SELECT crypt('my_secure_password', gen_salt('bf')) AS hashed_password;

-- Store the result in a table
INSERT INTO users (username, password)
VALUES ('briac', crypt('my_secure_password', gen_salt('bf')));

-- Verify a password during login
SELECT username
FROM users
WHERE password = crypt('user_entered_password', password);

Explanation:

• crypt('password', gen_salt('algorithm')): Hashes the password using a salt generated by the specified algorithm (e.g., bf for Blowfish).
• The crypt() function compares the stored hash with the provided input.

4. Public-Key Encryption and Decryption

Public-key encryption uses a public key for encryption and a private key for decryption.

Example: Generating and Using Keys

-- Encrypt using a public key
INSERT INTO secrets (data)
VALUES (pgp_pub_encrypt('Confidential data', dearmor('-----BEGIN PGP PUBLIC KEY----- ... -----END PGP PUBLIC KEY-----')));

-- Decrypt using a private key
SELECT pgp_pub_decrypt(data, dearmor('-----BEGIN PGP PRIVATE KEY----- ... -----END PGP PRIVATE KEY-----'), 'private_key_password')
FROM secrets;

Explanation:

• pgp_pub_encrypt('data', public_key): Encrypts the data with the provided public key.
• pgp_pub_decrypt(data, private_key, password): Decrypts the data with the private key and its password.

5. Storing Encrypted Data

You can design a table to store encrypted data securely:

CREATE TABLE secrets (
    id SERIAL PRIMARY KEY,
    data BYTEA NOT NULL
);

Encrypted data is stored as a BYTEA type, which can handle binary data. When querying encrypted columns, ensure you decrypt them on-the-fly using pgp_sym_decrypt().

6. Tips and Best Practices

1. Secure Key Storage:
   Do not store encryption keys in the same database as the encrypted data. Use an external key management service or environment variables.
2. Indexing Encrypted Columns:
   You cannot index encrypted columns directly. If searching is required, store a hashed version for lookups.
   Copy

   INSERT INTO secrets (data, data_hash)
   VALUES (pgp_sym_encrypt('Sensitive Info', 'key'), digest('Sensitive Info', 'sha256'));
   
   -- Search by hash
   SELECT * FROM secrets WHERE data_hash = digest('Sensitive Info', 'sha256')
   

3. Algorithm Choice:
   Use strong algorithms like AES for symmetric encryption. For hashing, prefer algorithms like SHA-256.
4. Regularly Rotate Keys:
   Periodically re-encrypt data with a new key to improve security.

7. Complete Example

-- Create a table for storing encrypted data
CREATE TABLE secrets (
    id SERIAL PRIMARY KEY,
    data BYTEA NOT NULL
);

-- Insert encrypted data
INSERT INTO secrets (data)
VALUES (pgp_sym_encrypt('This is highly sensitive information.', 'secure_key'));

-- Retrieve and decrypt data
SELECT pgp_sym_decrypt(data, 'secure_key') AS decrypted_message
FROM secrets;

With these functions, you can securely handle sensitive information directly within PostgreSQL while adhering to encryption best practices!