Encrypting Sensitive Data with PostgreSQL pgcrypto

To comply with privacy requirements, organizations must avoid storing plaintext personal data (e.g., ID numbers, phone numbers) in databases. The article discusses a common approach of encrypting data in application code, but highlights the high migration cost for legacy systems.

PostgreSQL offers a built‑in encryption module called pgcrypto, which provides a rich set of cryptographic functions that can be used directly in SQL statements.

Key functions demonstrated include: encrypt(data, key, 'aes') – encrypts a value using AES. decrypt(ciphertext, key, 'aes') – decrypts the ciphertext. convert_from(..., 'SQL_ASCII') – converts binary output to a readable string.

Example workflow:

1. Create a table to store user data: CREATE TABLE user_data (id serial PRIMARY KEY, mobile text); 2. Insert a plaintext phone number (for demonstration): INSERT INTO user_data (mobile) VALUES ('13812345678'); 3. Alter the column type to bytea to hold encrypted binary data:

ALTER TABLE user_data ALTER COLUMN mobile TYPE bytea USING mobile::bytea;

4. Encrypt existing rows with a secret key (e.g., '314159265358'):

UPDATE user_data SET mobile = encrypt(mobile, '314159265358', 'aes');

5. Query the table; the mobile column now contains ciphertext: SELECT * FROM user_data; 6. To view the plaintext, decrypt and convert the binary data:

SELECT id, convert_from(decrypt(mobile, '314159265358', 'aes'), 'SQL_ASCII') AS mobile FROM user_data;

7. To search for a specific phone number, encrypt the search value and compare against the stored ciphertext:

SELECT id, convert_from(decrypt(mobile, '314159265358', 'aes'), 'SQL_ASCII') FROM user_data WHERE mobile = encrypt('18600003469', '314159265358', 'aes');

By securely managing the encryption key (e.g., via a key management system), organizations can achieve high data security while keeping the encryption logic within the database.