Part 3: Query the newly encrypted data
In the previous part, we:
- Added and applied migrations
- Encrypted the sensitive data
Now we’re going query the encrypted data.
In this part we will:
- Update our dataset configuration to read from encrypted fields
- Create some user records to query
- Query those encrypted records via Rails
- View decryption logs
1. Update dataset configuration
The provided CipherStash configuration in the dataset.yml
file sets all columns to the plaintext-duplicate
mode.
In this mode, all data is read from the plaintext columns but writes will save both plaintext and ciphertext.
To test that queries are working properly change all columns in the dataset.yml
to use encrypted-duplicate
mode.
mode: encrypted-duplicate
In this mode, all data is read from ciphertext columns, and writes will save both plaintext and ciphertext.
Push this configration to CipherStash:
stash datasets config upload --file dataset.yml --client-id $CS_CLIENT_ID --client-key $CS_CLIENT_KEY
2. Create some user records
Open your Rails console:
rails console
Create a patient:
Patient.create(full_name: "Grace Hopper", email: "grace@hopper.example", dob: Date.parse("9 December 1906"))
In psql rails_demo
, verify that the data is encrypted;
SELECT __full_name_encrypted, __full_name_match, __full_name_ore FROM patients LIMIT 5;
3. Query those encrypted records:
Via the Rails console:
rails c
Find that new record by email address:
Patient.where(email: "grace@hopper.example")
This will return a result that looks like this:
Patient Load (0.5ms) SELECT "users".* FROM "users" WHERE "users"."email" = ? [["email", "grace@hopper.example"]]
=>
[#<User:0x0000000119cd47d0
id: 1,
full_name: "Grace Hopper",
email: "grace@hopper.example",
created_at: Wed, 15 Feb 2023 22:37:08.134554000 UTC +00:00,
updated_at: Wed, 15 Feb 2022 22:37:08.134554000 UTC +00:00]
To order users alphabetically by name, do:
Patient.order(:full_name)
Via the UI:
Start your Rails server:
rails s
Go to the patients dashboard.
Create a patient:
- Click on new patient
- Complete patient details
- Click on
Create Patient
Use the filters on the side to perform queries.
4. Dropping plaintext columns
Once you are sure that the app is working correctly, update the column mode to encrypted
mode in the dataset.yml
file.
mode: encrypted
This tells the CipherStash driver to only read and write from the encrypted columns.
Push this configration to CipherStash:
stash datasets config upload --file dataset.yml --client-id $CS_CLIENT_ID --client-key $CS_CLIENT_KEY
In this mode all data is encrypted and plaintext columns are completely ignored.
Once you’ve verified that everything is working, you can create a migration that drops the original columns:
rails generate migration DropPlaintextColumnsFromPatientsTable
And add the following code:
class DropPlaintextColumnsFromPatientsTable < ActiveRecord::Migration[7.0]
def change
remove_column :patients, :full_name
remove_column :patients, :email
remove_column :patients, :dob
remove_column :patients, :weight
remove_column :patients, :allergies
remove_column :patients, :medications
end
end
Once you remove the plaintext columns, anything that hasn’t been encrypted will be lost.
Before you run the remove column step, it is very important that you:
- Create a backup of all your data, in case you need to restore.
- Ensure all your data is encrypted, by running the data migration rake task
Once you’re sure that you’re ready to drop the plaintext columns, run the migration:
Run:
rails db:migrate
In order for the encrypted
mode to work after the plaintext columns have been dropped, the types of the CipherStash encrypted columns must be specified in the model.
Uncomment the below in your Patient model in app/models/patient.rb
:
# Note that the types of CipherStash-protected columns must be specified here in
# order to drop the original plaintext columns and for "encrypted" mode to work.
attribute :full_name, :string
attribute :email, :string
attribute :dob, :date
attribute :weight, :float
attribute :allergies, :string
attribute :medications, :string
# The rails demo uses ActiveAdmin, which uses Ransack for the filters.
# For the dob and weight filters to continue to work, the below types need to be added to the model.
ransacker :dob, type: :date
ransacker :weight, type: :numeric
Start up the Rails server:
rails s
Go to the patients dashboard.
5. Viewing logs of encryptions and decryptions
The CipherStash driver creates a local log of encryptions and decryptions for a given workspace in ~/.cipherstash/<your workspace id>
.
To see a real time log of cryptography operations, run:
tail -F ~/.cipherstash/*/decryptions.log
That’s it. All your queries will work with no modification. Your data is encrypted, and the CipherStash driver transparently rewrites all your queries.
Most importantly, all of the queries and decryptions are logged, so you can cryptographically prove when your sensitive data is accessed.