Encrypting ten years of meter data while keeping postcode reporting
Helio Energy stores meter readings for 2.3 million customers in a PostgreSQL cluster and ships nightly backups to object storage. Regulation requires ten-year retention and breach notification within 72 hours. A new privacy rule says meter IDs and addresses must be unreadable to storage administrators. Today the database volume and backups are encrypted with one AES-256 key held in a configuration file on the database host; six engineers can read that file. The key has not been rotated in four years. Helio is migrating to a cloud provider and wants to use the provider's managed key service, but the regulator requires Helio to demonstrate control of keys and to revoke provider access without provider involvement. A pilot of field-level encryption broke three reporting queries that aggregate consumption by postcode; analysts need daily results. The migration window is 11 weeks. The security team has two cryptography engineers and no dedicated database performance specialist.
1. Design the target encryption and key-management architecture. Explain how it satisfies the regulator and restores postcode reporting.
Read the worked answer
Target design. Use envelope encryption with a key hierarchy Helio controls. A root key sits in an HSM or a cloud HSM with external key storage; Helio generates or imports the key material, splits operational control between two custodians, and keeps an audited break-glass procedure. The root key wraps key-encryption keys; each data domain, table and backup gets its own data-encryption key. The provider stores ciphertext and wrapped keys but cannot unwrap them. Revocation is a Helio-controlled disable operation, and key-use logs go to Helio's SIEM. That is the evidence the regulator needs: customer-controlled key material, dual control, and a revocation path that does not depend on the provider.
Data fields. Encrypt the address with randomised authenticated encryption because it only needs to be displayed. Encrypt the meter ID deterministically, or better tokenise it, so lookups still work; document that deterministic encryption leaks equality. For postcode reporting, do not give analysts raw postcodes. Tokenise the postcode at ingest and build an aggregate table keyed by the token inside a trusted reporting service; analysts query aggregates, not row-level personal data. A token vault with its own access control keeps the mapping away from storage administrators.
Operations. Rotate the key-encryption key annually and re-wrap data keys rather than re-encrypting ten years of data. Retain retired keys in the HSM only while old backups exist. Back up key metadata and test restore. With two engineers and 11 weeks, pilot one table and one backup path, benchmark query performance, then roll out; do not build custom cryptography.
2. A backup from three years ago is found on a decommissioned server. Outline your response and the limits of crypto-shredding for ten-year retention.
Read the worked answer
Treat the discovery as a suspected personal-data breach, not a tidy-up. Preserve the server and media, identify the data and time range, and check whether the old key is still readable or was stored with the backup. If the data is personal and exposure is credible, the 72-hour notification clock starts when the breach is confirmed, not when the server is wiped. The data protection officer and legal team decide notification; security provides facts.
Crypto-shredding limits. Destroying keys is an effective deletion control only when retention no longer requires the data. Here, ten-year retention means Helio may have a lawful and operational need to read old records, and a regulator may demand them. Shredding the only key would destroy evidence and could itself breach records-management duties. The right pattern is per-object or per-period keys, so a compromised or expired period can be revoked without destroying all history; keep escrowed keys for legal hold under dual control.
Also, rotation does not retroactively protect old ciphertext: if the single old key was readable by six engineers, every old backup is suspect. Re-encrypt the backups that must be kept, destroy those that need not be, and rotate the key. Add a backup inventory with owner, key ID, retention date and last access test, so a decommissioned server cannot hold an unmanaged copy again.