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:

1. Update our dataset configuration to read from encrypted fields
2. Create some user records to query
3. Query those encrypted records via Rails
4. 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’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.