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
4. Drop plaintext columns
5. View logs of encryptions and decryptions

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 fields 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. In this mode all data is read from ciphertext fields and writes will save both plaintext and ciphertext.

After updating the configuration push it to CipherStash:

stash datasets config upload --file dataset.yml --client-id $CS_CLIENT_ID --client-key $CS_CLIENT_KEY

2. Create some user records

Create a new patient as you normally would with Sequelize by creating a script like the following:

// create-user.js
const { sequelize } = require("./models");

/** @type {import('sequelize').Sequelize['models']} */
const models = sequelize.models;

models.patient.create({
  full_name: "Grace Hopper",
  email: "grace@hopper.example",
  dob: new Date("9 December 1906"),
});

and running it with node:

node create-user.js

Once the script completes use the psql console to 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 the UI

Start the demo app by running npm start

npm start

Once the server starts navigate to the patients dashboard and verify that the demo is working correctly.

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.

Create the migration using sequelize-cli:

npx sequelize-cli migration:generate --name drop-plaintext-columns

This will generate another migration file under migrations/.

Open up that new migration file, and add this code to remove the plaintext columns:

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface) {
    await queryInterface.removeColumn("patients", "full_name");
    await queryInterface.removeColumn("patients", "email");
    await queryInterface.removeColumn("patients", "dob");
    await queryInterface.removeColumn("patients", "weight");
    await queryInterface.removeColumn("patients", "allergies");
    await queryInterface.removeColumn("patients", "medications");
  },
};

Once you’re sure that you’re ready to drop the plaintext columns, run the migration:

npx sequelize-cli db:migrate

To verify everything is still working correctly, check out the demo app on localhost:3000

5. 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 ~/.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.