PostgreSQL  

How to Encrypt Data in Production (Real-World Approach + Pros & Cons)

In modern applications, protecting user data isn’t optional—it’s a responsibility. Here’s a practical breakdown of how we implement data encryption in production systems, along with the trade-offs every engineering team should understand.

Enable EXTENSION in Postgrsql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Generate secret_key
SELECT encode(gen_random_bytes(32), 'base64') as your_secret_key;

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    mobile_encrypted BYTEA
);


INSERT INTO customers (name, mobile_encrypted)
VALUES (
    'John Doe',
    pgp_sym_encrypt('9876543210', 'your_secret_key', 'cipher-algo=aes256')
);

SELECT
    id,
    name,
    pgp_sym_decrypt(mobile_encrypted, 'your_secret_key') AS mobile
FROM customers;


SELECT * FROM customers;



UPDATE customers
SET mobile_encrypted =
    pgp_sym_encrypt(mobile, 'your_secret_key', 'cipher-algo=aes256');
	
	
SELECT
    'XXXXXX' || RIGHT(pgp_sym_decrypt(mobile_encrypted, 'your_secret_key'),4)
FROM customers;

How to Encrypt Data in Production

1. Encrypt Sensitive Data at Rest

  • Use strong encryption like AES-256

  • In PostgreSQL, leverage pgcrypto:

    pgp_sym_encrypt(data, key)

  • Store encrypted values as BYTEA (binary format)

2. Never Store Plaintext Secrets

  • Keep encryption keys in environment variables or a secret manager

  • NEVER hardcode keys in your codebase

3. Enable Secure Lookups with Hashing

  • Use SHA-256 hashing for searchable fields (like mobile/email)

  • Example:

    encode(digest(value, 'sha256'), 'hex')

4. Encrypt in Transit

  • Always enforce HTTPS (TLS)

  • Protect API communication and database connections

5. Restrict Decryption

  • Decrypt data only when absolutely needed

  • Keep decryption logic inside controlled application layers

6. Add Audit Logging

  • Track all changes (INSERT, UPDATE, DELETE)

  • Store old/new values (JSONB) for forensic analysis

Pros of Encryption

  • Protects sensitive user data (PII, financial, KYC)

  • Helps achieve compliance (GDPR, SOC2, ISO)

  • Reduces risk of data breaches

  • Builds user trust and brand credibility

  • Even database admins can’t read raw data

Cons / Trade-offs

  • Performance overhead (encryption/decryption cost)

  • Complex key management (rotation, storage)

  • Harder debugging (no readable data)

  • Limited querying on encrypted columns

  • Requires additional logic for search (hashing/indexing)

Pro Tips from Production Experience

  • Combine encryption + hashing for best balance

  • Use rate limiting + validation to prevent abuse

  • Keep audit logs encrypted as well

  • Rotate keys periodically without downtime

  • Design your schema with encryption in mind (don’t retrofit later)

Bottom Line

Encryption is not just a feature—it’s a system design decision. Done right, it protects your users, your business, and your reputation.

If you're building systems handling sensitive data, this should be your baseline—not an afterthought.

Membership not found