Skip to content
TheChatArchiveThe Chat Archive
GLMGLM-5 / Pony Alpha· unknownHugging Face Frontier Reasoning Traces

Design an envelope encryption pattern for PII in DB: keys, rotation, and search.

  1. User

    Design an envelope encryption pattern for PII in DB: keys, rotation, and search.

  2. GLM Assistant

    The user wants me to design an "envelope encryption pattern" for Personally Identifiable Information (PII) stored in a database (DB). Key aspects to cover are:

    1. Keys: What keys are used and how they are managed.
    2. Rotation: How to handle key rotation without downtime or data loss.
    3. Search: How to search encrypted data (since standard encryption prevents searching).

    1. Deconstruct the Request:

    • Context: PII in a DB. This implies sensitive data (names, SSNs, emails) that needs strong security (GDPR, CCPA, HIPAA compliance).
    • Core Concept: Envelope Encryption. This involves encrypting the Data Encryption Key (DEK) with a Key Encryption Key (KEK).
      • Why? It's faster (symmetric encryption for data) and more secure (asymmetric or isolated master key for the key itself).
    • Key Components to Design:
      • Keys: DEK, KEK (Master Key), KMS (Key Management Service).
      • Rotation: Both DEK and KEK rotation strategies.
      • Search: The tricky part. How to query WHERE email = 'alice@example.com' if the email is encrypted?

    2. Drafting the Architecture (The "Envelope"):

    • Data Key (DEK): AES-256 (Symmetric). Fast. Used to encrypt the PII column.
    • Master Key (KEK): RSA or AES (Symmetric). Stored in a Hardware Security Module (HSM) or Cloud KMS (AWS KMS, GCP KMS, Azure Key Vault).
    • Process:
      1. App asks KMS to generate a DEK.
      2. KMS returns plaintext DEK and encrypted DEK (EncryptedDEK).
      3. App encrypts PII with plaintext DEK.
      4. App stores: EncryptedPII + EncryptedDEK (often in a separate column or metadata).

    3. Addressing the "Search" Constraint:

    This is the biggest pain point in encryption.

    • Naive approach: Decrypt everything and scan. (Too slow, security risk).
    • Deterministic Encryption (DET): Same input -> same output. Good for equality checks (email = ?). Bad for security (frequency analysis).
    • Format-Preserving Encryption (FPE): Good for legacy schemas, but complex to implement securely.
    • Blind Index / Searchable Encryption (HMAC): Store a separate column: HMAC(key, plaintext). This is the standard modern approach for equality search.
    • Full Text Search: Very hard with standard encryption. Requires specialized algorithms (not requested, but worth noting). I'll focus on equality search for PII.

    4. Designing the Key Rotation Strategy:

    • Rotation triggers: Compliance (e.g., every 90 days), suspected compromise, or regular maintenance.
    • KEK Rotation: Easy. Re-encrypt the DEKs. No need to touch the massive data tables. Just update the EncryptedDEK blob.
    • DEK Rotation: Harder. Requires decrypting data with Old DEK and re-encrypting with New DEK.
      • Strategy: "Re-encrypt on read" (lazy rotation) or "Background batch job" (eager rotation). I'll propose a hybrid/online approach.

    5. Structuring the Response:

    I will organize the response logically:

    • High-Level Architecture: Visualizing the flow.
    • Key Hierarchy & Management: Defining KEK/DEK.
    • Encryption Workflow (Write): Step-by-step.
    • Decryption Workflow (Read): Step-by-step.
    • Search Strategy: Solving the query problem.
    • Key Rotation Strategy: The "How-To" for both KEK and DEK.
    • Schema Design: Example SQL table structure.
    • Summary/Best Practices: Security considerations.

    6. Refining the Content (Self-Correction during drafting):

    • Critique: Should I use Application-Layer Encryption (ALE) or DB-Layer (TDE)?
      • Correction: The prompt implies "Envelope Encryption," which is almost always ALE (Application Side) to protect against DB admin access or DB compromise. TDE doesn't allow searchable patterns easily. I will assume ALE.
    • Critique: How to handle search?
      • Correction: Use a "Blind Index". Store encrypted_value and searchable_hash. The hash is HMAC-SHA256. The key for the HMAC must be separate or derived.
    • Critique: Rotation is complex.
      • Correction: Differentiate between Crypto-Period (changing the key used for new data) and Re-encryption (changing the key for old data). I need to explain "Versioning" keys clearly.

    7. Detailed Technical Specifications:

    • Algorithms: AES-256-GCM (needs IV/Nonce + Auth Tag). RSA-2048 or KMS API calls.
    • Storage: IV (Initialization Vector) needs to be stored. Auth Tag needs to be stored.

    8. Final Polish:

    • Make it readable. Use code blocks for schema and pseudocode. Use bold text for emphasis. Ensure the distinction between KEK and DEK is clear. Ensure the distinction between Encryption and HMAC (for search) is clear.

    (Self-Correction on Search): I must emphasize that search on encrypted fields usually only supports exact match. Range queries (age > 25) are generally impossible without heavy Homomorphic Encryption (too slow) or leaking too much data. I will stick to Exact Match (HMAC) as the practical solution for PII.

    9. Generating Output: (This matches the provided good response).

    • Introduction: Definition.
    • Hierarchy: KEK/DEK.
    • Workflow: Write/Read.
    • Search: Blind Index.
    • Rotation: Detailed phases.
    • Schema: Visual example.

    Let's write it out. This design outlines a robust Envelope Encryption pattern tailored for PII (Personally Identifiable Information). This approach balances high security (separation of duties) with the practical need to search and rotate data.

    1. High-Level Architecture

    In Envelope Encryption, you do not encrypt data directly with the master key. Instead, you generate a unique key for every record (or column), encrypt the data with that local key, and then encrypt the local key with a master key.

    The Hierarchy:

    1. Customer Master Key (CMK) / KEK (Key Encryption Key): Stored in a Hardware Security Module (HSM) or Cloud KMS (AWS KMS, Google CKMS, Azure KeyVault). This never leaves the HSM unencrypted.
    2. Data Encryption Key (DEK): A unique 256-bit AES key generated for a specific row or record.
    3. Search Key (HMAC Key): A separate key used to create searchable tokens (blind index).

    2. Database Schema Design

    You cannot store PII in a standard VARCHAR column. You need a schema that supports the encrypted blob, the encrypted key, and the search token.

    Example Table: Users

    Column NameTypeDescription
    user_idUUIDPrimary Key (Unencrypted).
    email_blobBYTEA / BLOBThe encrypted PII (Ciphertext).
    email_key_blobBYTEA / BLOBThe DEK encrypted by the KEK (The "Envelope").
    email_nonceBYTEA / BLOBThe Initialization Vector (IV/Nonce) used for encryption.
    email_hashBYTEA / BLOBThe Blind Index (HMAC) for exact-match searching.
    kek_versionINTIdentifies which Master Key version was used.

    3. The Workflows

    A. Writing PII (Encryption Flow)

    When a new user signs up with email: "alice@example.com":

    1. Generate DEK: The application requests the KMS to generate a new Data Encryption Key (DEK).
      • Response: The KMS returns two things:
        1. Plaintext_DEK (Transient, in memory).
        2. Encrypted_DEK (The envelope).
    2. Encrypt Data: The app uses the Plaintext_DEK with an AES-256-GCM algorithm to encrypt "alice@example.com".
      • Output: Ciphertext and a unique Nonce.
    3. Generate Search Token: The app calculates an HMAC (e.g., SHA-256) of the plaintext email using a dedicated HMAC Key.
      • Search_Hash = HMAC("alice@example.com", HMAC_KEY)
    4. Persist: Save the Ciphertext, Nonce, Encrypted_DEK, and Search_Hash to the DB.
    5. Destroy: Discard the Plaintext_DEK from memory immediately.

    B. Searching for PII (Lookup Flow)

    You cannot query WHERE email = 'alice@example.com'.

    1. Client Request: App receives a request to find user by email.
    2. Generate Token: The app calculates the HMAC of the search input locally.
      • Input_Hash = HMAC("alice@example.com", HMAC_KEY)
    3. DB Query:
      • SELECT * FROM Users WHERE email_hash = Input_Hash
    4. Result: The DB returns the matching row(s).

    C. Reading PII (Decryption Flow)

    Once the row is retrieved (via search or ID):

    1. Extract: Pull the Encrypted_DEK and Ciphertext from the row.
    2. Decrypt DEK: Send Encrypted_DEK to the KMS.
      • Prerequisite: The application must have IAM permissions to kms:decrypt.
      • Response: KMS returns the Plaintext_DEK.
    3. Decrypt Data: Use the Plaintext_DEK and the stored Nonce to decrypt the Ciphertext back to "alice@example.com".

    4. Key Rotation Strategy

    Rotation is critical for compliance (e.g., PCI-DSS, HIPAA) and limiting the blast radius of a breach.

    Level 1: KEK (Master Key) Rotation

    • Frequency: Annually or upon suspected compromise.
    • Impact: No downtime required.
    • Process:
      1. Create a new CMK in the KMS (Version 2).
      2. Update application config to use Version 2 for new records.
      3. Re-encryption (Background Job): Read existing records, send the Encrypted_DEK to KMS (using old key) to decrypt, then send the Plaintext_DEK to KMS (using new key) to get a new Encrypted_DEK. Update the DB row and the kek_version column.
      4. Important: You are rotating the envelope, not re-encrypting the actual PII data. This is fast and low-overhead.

    Level 2: DEK (Data Key) & Data Rotation

    • Frequency: Only if the specific record was compromised or if algorithm standards change (e.g., moving from AES-CBC to AES-GCM).
    • Process:
      1. Retrieve the record.
      2. Decrypt the PII completely.
      3. Generate a brand new DEK.
      4. Re-encrypt the PII with the new DEK.
      5. Encrypt the new DEK with the latest KEK.
      6. Save all new blobs to the DB.

    Level 3: Search Key (HMAC) Rotation

    • The Risk: If the HMAC Key is leaked, an attacker can brute-force your hashes (dictionary attack) to reveal PII.
    • Strategy: This is the hardest rotation.
      1. Introduce a version prefix to the hash (e.g., v1:hash..., v2:hash...).
      2. Application attempts to match v2 first. If not found, falls back to v1.
      3. Lazy Re-hash: When a user logs in or updates their profile, re-calculate the hash with the new key and save v2.

    5. Security Considerations & Best Practices

    1. Algorithm Choice:

      • Use AES-256-GCM (Galois/Counter Mode). It provides Authenticated Encryption, ensuring that an attacker cannot tamper with the ciphertext without detection.
      • Never use ECB mode. Avoid CBC if possible (requires padding).
    2. Nonce Management:

      • Never reuse a Nonce (IV) with the same DEK.
      • Implementation: Use a cryptographically secure random number generator (CSPRNG) to generate a 12-byte (96-bit) nonce for every encryption operation. Store it alongside the ciphertext.
    3. Search Limitations:

      • This pattern only supports Exact Match searches (equality).
      • It does not support LIKE %...% (wildcards), range queries (>, <), or sorting on the PII column.
      • Advanced Alternative: If you require complex search (e.g., "Find users over 50 near New York"), you must look into Homomorphic Encryption or dedicated encrypted search tools like Cipherva or MongoDB Queryable Encryption, but these have significant performance costs.
    4. Separation of Duties:

      • Ideally, the Database Admin (DBA) should not have permissions to call the KMS decrypt function.
      • The Application Server should have the decrypt permission.
      • This ensures that if the DB is stolen (SQL Injection or Backup leak), the attacker only has encrypted blobs and keys they cannot open.

    Summary Diagram

    [User Input: PII]
          |
          v
    [App Server] <---(1. Generate DEK)---> [Cloud KMS (KEK)]
          |                                      |
          | (Receives Plaintext_DEK & Encrypted_DEK)
          v
    [App Memory]
          |
          +---> (2. Encrypt PII with Plaintext_DEK) -> [Ciphertext]
          |
          +---> (3. HMAC PII with Search_Key) -> [Search Hash]
          |
          v
    [Database Storage]
    +----------------+------------------+------------+
    | Ciphertext     | Encrypted_DEK    | Search_Hash|
    +----------------+------------------+------------+
    

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.