Existing Application

📌 Overview

This document provides a generalized implementation plan for integrating a Database Encryption Proxy into an existing application stack, with the goal of securing PII (Personally Identifiable Information) at rest without modifying the application code.

---

🔍 Step 1: Identify PII Data to Protect

Start by identifying sensitive fields across tables in your existing database.

Table Name	Column Name	Type	Notes
users	email	Text	Login and contact info
users	username	Text	Unique identifier
users	phone_number	Text	Optional contact field
profiles	first_name	Text	User’s real name
profiles	last_name	Text	User’s real name
attributes	value	Text	Dynamic user attributes

✅ Tip: Focus on data that is subject to privacy regulations such as GDPR, HIPAA, or local data protection laws.

---

⚙️ Step 2: Define Encryption Rules in config.yaml

Specify which columns should be encrypted and/or hashed using the proxy configuration file:

rules:
  sql:
    your_database:
      compatibility_mode: true
      scheme: public
      write:
        users:
          email:
            hash: true
          username:
            hash: true
          phone_number:
            hash: true
        profiles:
          first_name:
            hash: true
          last_name:
            hash: true
        attributes:
          value:
            hash: true

---

🏗️ Step 3: Install the Encryption Proxy

You can run the proxy using Docker:

docker run -d --name encryption-proxy \
  -p 5433:5432 \
  -v $(pwd)/config.yaml:/etc/config.yaml \
  -v $(pwd)/licenses.pem:/etc/licenses.pem \
  afisme/encryption-proxy:latest

Ensure it can connect to your actual database update your upstream configuration in config.yaml:

proxy:
  postgres:
    upstream:
      host: your-real-db-host:5432

---

📥 Step 4: Add New Columns to Support Encryption

The proxy requires additional columns to store encrypted and hashed versions:

• <field>_ciphertext

• <field>_digest

Use your migration tool (e.g., Flyway, Liquibase, or direct SQL):

ALTER TABLE users ADD COLUMN email_ciphertext TEXT, ADD COLUMN email_digest VARCHAR(32);

If column is json

ALTER TABLE "sample_data" ADD "owner_ciphertext" JSONB NULL;

Repeat for all encrypted fields.

---

🔁 Step 5: Run Migration to Encrypt Existing Data

Once configuration and schema are ready, run the migration tool:

docker run --rm \
  -v $(pwd)/migration.yaml:/etc/migration.yaml:ro \
  --net your-app-network \
  afisme/encryption-proxy:latest migrate --config /etc/migration.yaml

---

🔄 Step 6: Switch Application to Use Encryption Proxy

Update your application's database connection string to point to the proxy instead of the database:

DB_HOST=encryption-proxy
DB_PORT=5433

Restart the application if needed.

---

✅ Step 7: Validate Application Behavior

• Test authentication/login

• Check search functionality (note: partial search is not supported on encrypted fields)

• Insert new records and verify encryption

---

🔐 Security Notes

• Use AES-GCM encryption (default)

• Hashing enables exact-match search

• Avoid enabling log_original_query in production

---

📊 Success Criteria

Criteria	Status
Application works without code changes	✅
PII fields are encrypted in the database	✅
Exact match search works as expected	✅
Data insert/update encrypted automatically	✅
Original data no longer visible in plaintext	✅

---

📌 Next Steps

• Perform security audit

• Monitor performance impact

• Rotate encryption keys periodically

• Enable logging for auditing (if required by compliance)

This general documentation can be adapted to any application and schema. For assistance in adapting to your specific setup, feel free to reach out or fork the demo repo.