How to Use pgcrypto Extension in PostgreSQL for Encryption and Hashing

To use the pgcrypto extension in PostgreSQL, first enable it with CREATE EXTENSION pgcrypto;. Then you can use its functions like crypt() for hashing passwords and pgp_sym_encrypt() for encrypting data.

Syntax

The pgcrypto extension provides functions for encryption, decryption, and hashing. You enable it with CREATE EXTENSION pgcrypto;. Common functions include:

• crypt(password, salt): hashes a password with a salt.
• gen_salt(type): generates a salt for hashing.
• pgp_sym_encrypt(data, key): encrypts data symmetrically with a key.
• pgp_sym_decrypt(data, key): decrypts symmetrically encrypted data.

sql

CREATE EXTENSION pgcrypto;

-- Hash a password
SELECT crypt('my_password', gen_salt('bf'));

-- Encrypt data
SELECT pgp_sym_encrypt('secret data', 'encryption_key');

-- Decrypt data
SELECT pgp_sym_decrypt(pgp_sym_encrypt('secret data', 'encryption_key'), 'encryption_key');

Example

This example shows how to enable pgcrypto, hash a password, and encrypt/decrypt a message.

sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

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

-- Encrypt a message
SELECT pgp_sym_encrypt('Hello, world!', 'mysecretkey') AS encrypted_message;

-- Decrypt the message
WITH encrypted AS (
  SELECT pgp_sym_encrypt('Hello, world!', 'mysecretkey') AS encrypted_message
)
SELECT pgp_sym_decrypt(encrypted_message, 'mysecretkey') AS decrypted_message FROM encrypted;

Output

 hashed_password
------------------------------------
 $2a$06$.........................
(1 row)

 encrypted_message
-------------------------------------
 \x... (binary data) ...
(1 row)

 decrypted_message
-----------------
 Hello, world!
(1 row)

Common Pitfalls

Common mistakes when using pgcrypto include:

• Not enabling the extension before using its functions.
• Using a fixed salt instead of generating a new one with gen_salt() for password hashing.
• Forgetting to use the same key for encryption and decryption.
• Storing encrypted data as text instead of bytea, which can cause errors.

sql

/* Wrong: Using fixed salt (insecure) */
SELECT crypt('mypassword', 'fixedsalt');

/* Right: Use gen_salt() to generate a secure salt */
SELECT crypt('mypassword', gen_salt('bf'));

Quick Reference

Function	Purpose	Example Usage
CREATE EXTENSION pgcrypto;	Enable pgcrypto extension	CREATE EXTENSION pgcrypto;
crypt(password, salt)	Hash a password securely	SELECT crypt('pass', gen_salt('bf'));
gen_salt(type)	Generate a salt for hashing	SELECT gen_salt('bf');
pgp_sym_encrypt(data, key)	Encrypt data symmetrically	SELECT pgp_sym_encrypt('text', 'key');
pgp_sym_decrypt(data, key)	Decrypt symmetrically encrypted data	SELECT pgp_sym_decrypt(encrypted_data, 'key');

Key Takeaways

Enable the pgcrypto extension with CREATE EXTENSION before using its functions.

Use gen_salt() to generate a secure salt for password hashing with crypt().

Use pgp_sym_encrypt() and pgp_sym_decrypt() for symmetric encryption and decryption.

Always use the same key for encryption and decryption to avoid errors.

Store encrypted data in bytea columns to handle binary data correctly.