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
1. Update dataset configuration
The configuration is currently set to plaintext-duplicate
mode on each field.
In this mode, the plaintext field’s will be read from and written to.
The encrypted field’s will be written to.
Update the mode on each field in the dataset.yml
to encrypted-duplicate
mode.
In this mode, the plaintext field will be written to.
The encrypted field’s will be read from and written to.
Push this configration to CipherStash:
stash upload-config --file dataset.yml --client-id <the client-id from previous step> --client-key <the client-key from previous step>
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
, verify that the data is encrypted;
SELECT __full_name_encrypted, __full_name_match, __full_name_ore FROM users LIMIT 5;
3. Query those encrypted records via PSQL
Now back in the Rails console, to 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)
3. Query those encrypted records 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. Viewing logs of encryptions and decryptions
To view the logs showing encryptions and decryptions of data, go to your workspace folder ~/.cipherstash/<your workspace id>
.
Run:
tail -F 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.