Encryption

Chapter 48: Data Encryption

Protecting Data at Rest and in Transit

---

48.1 Encryption Types

                    Encryption in PostgreSQL
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                     Encryption Layers                               │
    │                                                                      │
    │   Data at Rest:                                                     │
    │   ─────────────                                                     │
    │   • pgcrypto extension - column-level encryption                   │
    │   • Tablespace encryption - OS-level                              │
    │   • Full disk encryption - LUKS, dm-crypt                         │
    │                                                                      │
    │   Data in Transit:                                                  │
    │   ───────────────                                                  │
    │   • SSL/TLS connections                                            │
    │   • Certificate-based authentication                               │
    │                                                                      │
    │   Password Encryption:                                              │
    │   ───────────────────                                              │
    │   • scram-sha-256 (default)                                        │
    │   • md5                                                            │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

---

48.2 Column-Level Encryption

-- Enable pgcrypto extension
CREATE EXTENSION pgcrypto;

-- Encrypt data using PGP
CREATE TABLE sensitive_data (
    id SERIAL PRIMARY KEY,
    ssn_encrypted BYTEA
);

-- Encrypt on insert
INSERT INTO sensitive_data (ssn_encrypted)
VALUES (pgp_sym_encrypt('123-45-6789', 'my_secret_key'));

-- Decrypt on select
SELECT id, pgp_sym_decrypt(ssn_encrypted::bytea, 'my_secret_key') as ssn
FROM sensitive_data;

-- Using symmetric key from file
-- pgp_sym_encrypt(data, key)

---

48.3 SSL/TLS Configuration

# postgresql.conf
ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'
ssl_ca_file = '/path/to/ca.crt'

# Force SSL connections
# pg_hba.conf:
host all all 0.0.0.0/0 scram-sha-256 hostssl
host all all ::/0 scram-sha-256 hostssl

---

Summary

Type	Level	Use Case
pgcrypto	Column	Sensitive data
SSL/TLS	Connection	Network security
Disk encryption	OS	Full protection

---

Next: Chapter 49: JSON & JSONB Data Types