Part 2: Encrypt the sensitive data
In the previous part, we:
- Logged in to the
stash
CLI tool - Created a dataset
- Pushed the dataset configuration
Now we’re going change the database schema to support new columns.
In this part we will:
- Add and apply migrations to install Protect custom types and change the database structure to support the encrypted indexes
- Encrypt the sensitive data inside your existing database
1. Add and apply migrations
The first migration to run, is the install of the Protect custom types into your database.
This migration adds in the custom types ore_64_8_v1
and ore_64_8_v1_term
.
ore_64_8_v1
is used forstring
andtext
types.ore_64_8_v1_term
is used for non string types.
We do this by creating a Rails migration:
rails generate migration AddProtectDatabaseExtensions
And adding the following code:
class AddProtectDatabaseExtensions < ActiveRecord::Migration[7.0]
def up
ActiveRecord::ConnectionAdapters::CipherStashPG.install
end
def down
ActiveRecord::ConnectionAdapters::CipherStashPG.uninstall
end
end
Apply the migration:
rails db:migrate
The CipherStash driver works by rewriting your app’s SQL queries to use the underlying encrypted columns.
To set up those encrypted columns, generate another Rails migration:
rails generate migration AddProtectEncryptedColumnstoPatientsTable
And add the following code:
class AddProtectEncryptedColumnstoPatientsTable < ActiveRecord::Migration[7.0]
def change
add_column :patients, :__full_name_encrypted, :text
add_column :patients, :__full_name_match, :integer, limit: 2, array: true
add_column :patients, :__full_name_ore, :ore_64_8_v1
add_column :patients, :__full_name_unique, :text
add_column :patients, :__email_encrypted, :text
add_column :patients, :__email_match, :integer, limit: 2, array: true
add_column :patients, :__email_ore, :ore_64_8_v1
add_column :patients, :__email_unique, :text
add_column :patients, :__dob_encrypted, :text
add_column :patients, :__dob_ore, :ore_64_8_v1_term
add_column :patients, :__weight_encrypted, :text
add_column :patients, :__weight_ore, :ore_64_8_v1_term
add_column :patients, :__allergies_encrypted, :text
add_column :patients, :__allergies_match, :integer, limit: 2, array: true
add_column :patients, :__allergies_ore, :ore_64_8_v1
add_column :patients, :__allergies_unique, :text
add_column :patients, :__medications_encrypted, :text
add_column :patients, :__medications_match, :integer, limit: 2, array: true
add_column :patients, :__medications_ore, :ore_64_8_v1
add_column :patients, :__medications_unique, :text
# Add indexes to the encrypted columns.
add_index :patients, :__full_name_ore
add_index :patients, :__email_ore
add_index :patients, :__dob_ore
add_index :patients, :__weight_ore
add_index :patients, :__allergies_ore
add_index :patients, :__medications_ore
add_index :patients, :__full_name_match, using: :gin
add_index :patients, :__email_match, using: :gin
add_index :patients, :__allergies_match, using: :gin
add_index :patients, :__medications_match, using: :gin
end
end
The _encrypted
columns are the encrypted values, and the _match
and _ore
columns are the encrypted indexes.
Apply the migration:
rails db:migrate
2. Encrypt the sensitive data
Now we have the necessary database structure in place, it’s time to encrypt your data.
Using bash:
rails 'cipherstash:migrate[Patient]'
This will pull the unencrypted data, encrypt it, and write it back to the new columns.
3. Update your model
This is a temporary fix to enable CipherStash to work.
We intend to ship a fix for this in Q2 2023.
Add the below to the Patient model.
class Patient < ApplicationRecord
# This will be removed when Protect GA is released.
self.ignored_columns = %w[wildcardoperatorfix]
end
Now it’s time for the final part: query the newly encrypted data.