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:

1. Add and apply migrations to install Protect custom types and change the database structure to support the encrypted indexes
2. 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 for string and text 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.

Fire up a Rails console:

rails c

And encrypt your data:

Patient.find_each(:batch_size => 1000) { |p| p.update
_columns(p.attributes.filter { |attr| !attr.starts_with?("__") }) }

This will pull the unencrypted data, encrypt it, and write it back to the new columns.

3. Update your model

Add the below to the Patient model.

 class Patient < ApplicationRecord
  # This will be removed when Protect GA is released.
  self.ignored_columns = %w[wildcardoperatorfix]
  # This will be removed when Protect GA is released.
  def self.columns
    super.reject {|column| column.name.starts_with?('__')}
  end
end

Now it’s time for the final part: query the newly encrypted data.