Senior LevelPostgreSQL
How do you implement Data-at-Rest Encryption for PostgreSQL using pgcrypto or disk-level LUKS?
❓ Interview Prompt: Explain Data-at-Rest Encryption, column-level encryption via `pgcrypto`, tablespace encryption, and Linux block-level `LUKS` disk encryption.
Securing sensitive data at rest protects database files from physical disk theft:
1
Column-Level Encryption ('pgcrypto'): Encrypts specific column values using 'pgp_sym_encrypt()'. Plaintext data is never written to disk or WAL logs!
2
Block-Level Encryption ('LUKS'): Encrypts the entire underlying Linux storage partition hosting '/var/lib/postgresql/data'.
Code Implementation Example
sql
-- Column-level encryption via pgcrypto
INSERT INTO user_secrets (user_id, secret_data)
VALUES (101, pgp_sym_encrypt('Sensitive Payload', 'MasterEncryptionKey'));
-- Decrypt column value
SELECT pgp_sym_decrypt(secret_data::bytea, 'MasterEncryptionKey') AS secret
FROM user_secrets WHERE user_id = 101;💡Encrypting and decrypting sensitive column values using pgcrypto.
🎯 Interview Key Takeaway
Combine LUKS disk encryption with pgcrypto column-level encryption for defense-in-depth data-at-rest security.
#Data-at-Rest#Encryption#pgcrypto#LUKS#Senior
Back to all PostgreSQL Questions→