skip to content

In an Active Record model, when do you need encrypts :national_id, deterministic: true, and what does it cost compared with the default?

level: middleimportance: must knowfreq 50%

answer

  1. random IV versus derived IV
  2. only deterministic supports where and find_by
  3. uniqueness validation and unique index
  4. equal values give equal ciphertext
  5. downcase: loses case; ignore_case: keeps original_

basics

~20 s

Use deterministic: true only when you must query or enforce uniqueness on the column, such as finding patients by national ID. It derives the IV from the plaintext, so equal values give equal ciphertexts, revealing which rows share a value.

solid answer

~40 s

By default `encrypts` uses AES-256-GCM with a random IV, so the same value encrypts differently each time and `where(national_id: ...)` cannot match. With `deterministic: true`, Rails derives the IV from an HMAC-SHA256 of the deterministic key and the plaintext, so a value always yields the same ciphertext; `Patient.find_by(national_id: "AB123456C")` encrypts the argument and compares, and `validates :national_id, uniqueness: true` and a unique index work. The cost is leakage: anyone reading the table sees which rows share a value and how often, and the `deterministic_key` cannot be rotated with a key list. For case-insensitive lookup, `downcase: true` stores the downcased value and loses the original case, while `ignore_case: true` also keeps the original in an encrypted `original_national_id` column; both require deterministic encryption to be useful for queries. Keep everything else non-deterministic.

code

ruby · 11 lines
ruby
class Patient < ApplicationRecord
  # Looked up at the front desk; must be unique
  encrypts :national_id, deterministic: true, ignore_case: true
  validates :national_id, uniqueness: true

  # Displayed only; never queried
  encrypts :diagnosis_notes
end

# Migration for ignore_case:
# add_column :patients, :original_national_id, :string, limit: 510

go deeper

for a junior

Recall that only deterministic: true lets you query or validate uniqueness on an encrypted column.

for a middle

Explain random versus derived IVs, how a query argument is encrypted before comparison, and the downcase: and ignore_case: options.

for a senior

Judge which attributes justify deterministic encryption, what equality leakage means for each, and how uniqueness and indexes depend on stable ciphertexts.

for a principal

Weigh queryability against leakage across a data model, and when a separate lookup design beats deterministic encryption.

## Two modes `encrypts` has two modes, and the difference is entirely in how the **initialization vector (IV)** is chosen: | | Default (non-deterministic) | `deterministic: true` | |---|---|---| | IV | random per write | HMAC-SHA256 of the key and the plaintext | | Same value, two rows | different ciphertexts | identical ciphertexts | | `where` / `find_by` on the column | no match | works | | Uniqueness validation, unique index | not supported | supported | | Key used | derived from `primary_key` | derived from `deterministic_key` | | Key rotation by key list | yes | no | | What a database reader learns | nothing about equality | which rows share a value, and how often | Both use AES-256-GCM; only the IV source differs. ## Why lookups need determinism When you query an encrypted attribute, Active Record serializes the argument through the attribute's type, which **encrypts it** before building the SQL. With a random IV the freshly encrypted argument never equals the stored ciphertext, so the query matches nothing. With a deterministic IV it is byte-for-byte equal, so the database can compare and even use an index. For a hospital that must find a patient record from the national ID typed at a front desk: ```ruby class Patient < ApplicationRecord encrypts :national_id, deterministic: true, downcase: true validates :national_id, uniqueness: true end Patient.find_by(national_id: "ab123456c") # encrypts the argument, then compares ``` ## Case handling National IDs may contain letters typed in either case. Two options exist: - **`downcase: true`** downcases before encrypting. Lookups ignore case, but the original case is gone; reading returns lower case. - **`ignore_case: true`** does the same for the main column and also stores the value with its original case, encrypted, in a column named `original_national_id`, which you must add. Reads return the original case, queries ignore it. Rails raises `ActiveRecord::Encryption::Errors::Configuration` if `ignore_case:` is used without `deterministic:`, and, while `support_unencrypted_data` is off, if the `original_` column is missing. A uniqueness validation's `case_sensitive: false` does not help on an encrypted column, because the database compares ciphertexts; use `downcase:` or `ignore_case:` instead. ## What determinism costs 1. **Equality leaks.** Anyone with read access to the table, or a backup of it, can group rows by ciphertext. For a national ID that is usually unique this reveals little; for a low-cardinality field such as blood type it reveals the distribution, and with outside knowledge, the values. 2. **No key-list rotation.** `deterministic_key` must be a single value; passing a list raises `Deterministic encryption keys can't be rotated`. Changing it means a migration with previous schemes. 3. **Ciphertexts must stay stable.** A unique index depends on every write producing the same ciphertext, so the attribute's encryption properties must not change casually. ## Choosing per attribute - Deterministic: identifiers you look up or must keep unique, such as a national ID or an email used for sign-in. - Non-deterministic: everything you only display, such as diagnoses, notes, addresses and phone numbers. - Not encrypted at all: values you need to sort, range-query or search partially, because neither mode supports `LIKE`, `ORDER BY` meaningfully or `BETWEEN` on the plaintext. ## What interviewers check - That you can explain why the default mode cannot be queried. - That you choose deterministic only where a lookup or uniqueness rule demands it, and can state the leak. - That you know the case options and their column requirement.

  • Why can you not search encrypted national IDs with LIKE 'AB12%'?
    Both modes store ciphertext, and a ciphertext's prefix has no relation to the plaintext's prefix. Deterministic encryption only guarantees that whole equal values encrypt identically, so equality lookups work while prefix, range and ordering queries do not. Searching by prefix needs a separate, deliberately designed index or leaving that data unencrypted.
  • What does validates :national_id, uniqueness: true, case_sensitive: false do on a deterministically encrypted column?
    The case_sensitive option cannot help: the database compares ciphertexts, and two cases of the same value encrypt differently unless the attribute normalizes them first. The Rails guide says to use downcase: or ignore_case: on encrypts for case-insensitive uniqueness instead.

Default encryption is like sealing each document in a fresh envelope with a random serial number, so two copies of the same letter look unrelated. Deterministic encryption stamps the envelope's number from the letter's contents, so you can find a letter by computing its number, but anyone can also see which envelopes hold identical letters.

  • Default encrypts columns can be queried with where because Rails decrypts in SQL.
  • Deterministic encryption is unencrypted hashing under another name.
  • downcase: true keeps the original case for display.
  • ignore_case: works without deterministic: true.
  • Deterministic attributes can rotate keys by listing several deterministic keys.