We already have every piece: versioned AEAD, KDFs, signatures, TLS, mTLS, and, since the last lesson, a secrets manager with envelope encryption. This lesson changes altitude: we stop looking at primitives and adopt the architect's view. The question is no longer "how does AES-GCM work?" but "which of MediNube's data is encrypted, at which layer, and against which attacker does each layer protect?" You're going to draw the complete map of the data — from Ana Pérez's browser to the row in PostgreSQL and the backup tape — and discover that "it's encrypted" is a sentence that means nothing until you answer: encrypted where and against whom?

1. MediNube's data map: states and boundaries
2. In transit: external and internal TLS (the database speaks TLS too)
3. At rest: the four layers and which threat each one covers
4. Searching over encrypted data: the dilemma and the blind index
5. Hash it, encrypt it, or leave it in the clear: the decision table
6. The stolen dump: a guided thought experiment
7. End-to-end encryption: the maximalist option, and why MediNube isn't it

Every piece of data is in one of two states: in transit (traveling across a network) or at rest (written to disk). Each state has its own threats and its own tools. Here's MediNube's map with everything we've built so far:

Architect's lesson number one: the arrows are transit (TLS's domain, module 5) and the cylinders are at rest (modules 2 and 4's domain). Lesson number two: the inside of the "trusted" network also needs locks — the era of "TLS only at the perimeter" ended once attackers learned to move laterally through internal networks.

The external leg was already solved in module 5, and here we just reuse it: TLS 1.3 on portal.medinube.example and api.medinube.example, mTLS for clinics with their clinica-sol.clientes.medinube.example SAN, HSTS, short-lived ACME certificates. Nothing new there.

What is new is looking inward: the app ↔ PostgreSQL connection also crosses a network, and on that network there could be a compromised container listening in. PostgreSQL speaks TLS natively, and the client decides how much it demands via sslmode:

Here's the trap that catches almost everyone: require encrypts but doesn't verify. It accepts any certificate, including a self-signed one that MalloryClinic presents in an internal-network MITM. It's verify=False from 05-02 in disguise as a reasonable option: an encrypted channel with a stranger. The correct configuration uses the internal CA we created in 05-01:

Explanation: sslrootcert points to the MediNube Lab Root CA root deployed in 05-01; verify-full forces the server's certificate to be signed by that CA and its SAN to match pg.interna.medinube.example. It's exactly the chain-plus-name validation from module 5, applied to the database. The password, of course, comes from the previous lesson's manager.

"The database is encrypted" can mean four very different things. The table below is probably the lesson's most important one — each layer protects against a specific attacker and is transparent to every other one:

Three points worth dwelling on:

• Disk encryption is necessary and almost never sufficient. While the server is powered on (always, for a SaaS), LUKS is transparent: any process with permissions reads the data in the clear. Its real-world scenario is the disk that leaves the data center. Always turn it on (cloud providers usually enable it by default), but don't put it in the security report as "medical records are encrypted."

• TDE and pgcrypto live inside the DB. TDE (Transparent Data Encryption) encrypts the engine's files: same scope as disk encryption, more granular. pgcrypto lets you encrypt columns straight from SQL:

  -- pgcrypto: symmetric per-column encryption, straight from SQL
  UPDATE patients
     SET phone = pgp_sym_encrypt('612345678', 'secret-key')
   WHERE id = 4471;
  
  SELECT pgp_sym_decrypt(phone::bytea, 'secret-key') FROM patients;
  

  Its limit is obvious: the key travels in the SQL query itself, so it ends up in the DB's logs, in pg_stat_activity, and in the hands of any attacker already inside the engine — exactly the attacker we wanted protection from. Useful in narrow scenarios; not for MediNube's medical records.

• Application-level encryption is the most granular and the strongest. It's what we built in 02-03: Ana Pérez's medical record gets encrypted with AES-256-GCM (format v1, AAD paciente=...;formato=v1) before it leaves the app, and travels already encrypted all the way to the PostgreSQL row. The DB only sees opaque bytes: a full dump, a SQL injection, or a curious administrator get exactly nothing. The DEK arrives via 06-01's envelope encryption. The price is real too, and gets its own section next: the DB can no longer index or search on that field.

Layers don't compete: they stack. MediNube runs encrypted disks (against physical theft), internal TLS (against the network), application-level encryption on sensitive fields (against a compromised DB), and backups encrypted with 04-05's hybrid envelope when the recipient is external (a clinic requesting its own copy: the backup's DEK gets encrypted with the clinic's public key).

Here's where application encryption's central conflict shows up. A well-encrypted AEAD field is indistinguishable from random bytes, and, on top of that, encrypting the same value twice produces different blobs (random nonce). Direct consequence:

That's not a flaw: it's the security semantics we wanted. But front-desk staff need to look up Ana Pérez by national ID number. The practical pattern is the blind index: alongside the encrypted field, you store an HMAC of the normalized value, using a dedicated key from the manager:

Why each piece is the way it is: normalization guarantees that "12345678-Z" and "12345678z" produce the same index (otherwise the lookup breaks on formatting); the keyed HMAC (not a plain sha256) stops someone who steals the table from brute-forcing the index of every possible national ID number — national ID numbers have very little entropy, golden rule 5 — and the dedicated key limits the damage if it leaks.

Mandatory honesty about what the blind index reveals: it's deterministic, so it exposes which rows share a value (equality), and lets anyone holding the index key confirm a specific national ID number. It only works for exact equality: no LIKE 'PER%', no ranges, no ordering. More ambitious schemes exist (order-preserving encryption, searchable encryption), but their leakage is far worse than it looks and their academic history is full of breaks: for MediNube, exact equality with a blind index, and any richer search on non-sensitive fields instead.

With the layers and the blind index on the table, we can decide field by field. This is the real table for the patients table and friends at MediNube — the criterion: do I need to recover the original value? Do I need to search by it? What happens if it leaks?

The password row deserves underlining: encrypting a password (recoverable) instead of hashing it is a classic audit finding. If the system can recover your password, so can an attacker.

Let's stress-test the design. MalloryClinic gets hold of a complete pg_dump of MediNube's DB (a SQL injection, a poorly protected backup, doesn't matter how). Let's walk through what she gets, depending on which layers are present:

1. No layers at all (module 1's MediNube): everything in the clear. Medical records, national ID numbers, passwords. Catastrophic breach, notification to the authority, front-page news.
2. Only encrypted disk: exactly the same as point 1. The dump was taken with the system running; LUKS never even noticed. Lesson burned in for good: disk encryption doesn't protect against logical dumps.
3. + TDE / DB encryption: also almost the same: the logical dump comes out of the engine, which decrypts transparently. TDE would protect against theft of the engine's files, not the dump.
4. + application-level encryption (MediNube today): medical records and national ID numbers are AEAD blobs without the DEKs (which are in the KMS, not the DB); passwords are Argon2id and, on top of that, missing the pepper (which lives in the manager); blind indexes can't be inverted without their key. Mallory has ids, dates, and metadata. It's still an incident (metadata counts under the GDPR too, and it must be reported), but Ana Pérez's clinical content is safe.

This exercise — "what does the attacker see with this dump?" — is the cheapest design tool there is. Run it on every new table before you write the migration.

There's one layer left above all the others: end-to-end encryption (E2E), where not even the server can read the data — only the endpoints hold the keys. Signal, which we mentioned back in module 4, is the canonical example: Signal's server carries blobs it can't open.

Could MediNube be E2E, with medical records encrypted under keys held only by clinics and patients? Technically, yes (the pieces are module 4's: X25519, envelopes, signatures). But it would mean giving up functionality that's actually part of the product today: the server couldn't generate aggregate reports, couldn't index for search, and couldn't let an ER doctor access a record if the patient isn't there to "authorize" it cryptographically; and losing the patient's key would mean irrecoverably losing their record (or a recovery system... that reintroduces trust in the server). E2E is the right choice when the server is only a carrier (messaging); MediNube is a processor of data, and its honest tradeoff is a different one: application-level encryption with keys in an audited KMS, so that reading data requires compromising two systems and leaves a trail. Saying "we're E2E" without actually being it would be marketing, not cryptography — and GDPR audits tell the two apart perfectly well.

• Mistake: sslmode=require and a false sense of security. It encrypts, but accepts any certificate. Against an internal MITM it's useless: verify-full + your internal CA, always.
• Mistake: counting disk encryption as data encryption. It protects powered-off disks. In the security report, spell out layer by layer what's actually in place.
• Mistake: passing the pgcrypto key in every query, and later finding it in the PostgreSQL logs. If you need protection against the DB itself, encrypt in the application.
• Mistake: a blind index with an unkeyed hash (sha256(national_id)): with ~10⁸ possible national ID numbers, it's inverted by brute force in minutes. Keyed HMAC from the manager, and normalize first.
• Tip: stack layers based on the attacker, not the fashion. The design question is always "against whom?": physical theft → disk; internal network → TLS with verify-full; compromised DB → application; external media → backups with an envelope.
• Tip: document the field decision table (clear/hash/encrypted/index) in the repository. It's gold for the next developer and for the auditor.

1. Classify the new table: MediNube adds an appointments table with columns id, patient_id, doctor_id, date_time, visit_reason (free text: "chest pain..."), contact_phone. Assign a treatment to each column (clear / AEAD / AEAD+blind index / hash) and justify it. Hint: front-desk staff need to look up appointments by contact phone.
2. The dump, again: with your design from exercise 1, write down exactly what MalloryClinic gets from a pg_dump of appointments, and which metadata still leaks even with everything sensitive encrypted. Is "patient X's appointment with doctor Y at 9:00" harmless data?
3. Hunting the require: write the incorrect connection string (or psycopg.connect block) using sslmode=require and its fix using verify-full, and explain in two sentences the concrete attack the incorrect version allows inside MediNube's network.

1. id, patient_id, doctor_id: clear (JOIN keys; they're opaque references). date_time: clear (schedules need range queries: WHERE date_time BETWEEN..., impossible over AEAD, and the blind index doesn't cover ranges). visit_reason: AEAD with AAD (e.g. f"cita={id};formato=v2") — it's clinical content, as sensitive as the medical record. contact_phone: AEAD + blind index with normalization (strip spaces, +34 prefix) because it needs to be recovered (to call the patient) and searched by.
2. Mallory sees the graph of appointments: which patient_id visits which doctor_id and when, without reasons or phone numbers. That's not harmless: if doctor Y is identifiable as an oncologist, the mere existence of frequent appointments reveals health information — that's why metadata is also personal data under the GDPR, and the incident still has to be reported. Possible mitigations: pseudonymize doctor_id at the application layer, or encrypt that relationship too, accepting the cost in queries.
3. Incorrect: psycopg.connect(..., sslmode="require"). Correct: sslmode="verify-full", sslrootcert="/etc/medinube/pki/ca.crt". Attack: a compromised pod on the internal network does ARP/DNS spoofing toward pg.interna.medinube.example, presents a self-signed certificate, and the client with require accepts it without a peep: MalloryClinic terminates the TLS, reads credentials and queries, and forwards the traffic to the real DB (a complete, invisible MITM).

You no longer think in primitives: you think in layers. You know "encrypted" without qualifiers means nothing, that each layer (disk, DB, application, backup) has its own attacker, that the app talks to PostgreSQL with verify-full against the internal CA from 05-01, that searching over encrypted data is paid for with blind indexes and honesty about their leakage, and that Ana Pérez's medical record arrives encrypted all the way to the database row, with DEKs living in 06-01's KMS. MediNube's data map is complete.

One traveling piece of data still carries a module-1 embarrassment: the portal's session token, that eyJ... that turned out in 01-02 to be unsigned Base64 — encoding isn't encrypting... and it isn't authenticating either. In the next lesson we turn it into what it always should have been: a signed token. JWT, its classic attacks, and how to use it right. See you there.