Re: [FEATURE REQUEST] Encrypted indexes over encrypted data

Some regulatory standards require all UII, even all PII, information be
encrypted within the database, not just on encrypted media. That's to
reduce exposure even if someone gets access to a live server, e.g., via SQL
Injection. (The perennial #1 risk for software vulnerabilities.)

UII is uniquely identifiable information, e.g., SSN.

PII is personally identifiable information, e.g, email address, phone
number, address. It doesn't have to be enough to uniquely identify the
person, just enough to cut the number of possible individuals down to a
handful. That's a surprising large number of fields, e.g., knowing where
someone was born and their birthdate will get you close to their SSN for
anyone born after the IRS started requiring SSNs for all claimed
dependents. Knowing someone's birth date and city of residence will get you
down to a handful of individuals, often a single individual depending upon
their age and the size of their city. It's remarkably easy to uniquely
identify something like 75% of the population if you have the data from a
couple different sites and some way to correlate the records. (That's why
it's not good enough to just use the sha1 of an email address, etc.)

I know the government required UII encryption in its databases when I last
worked on a government contract, and I think they've required PII
encryption as well for years. I would be verify surprised if HIPAA doesn't
require that as well for PII in addition to the medical info. I definitely
know PCI-DSS requires encryption of all information on the credit card
itself - you can keep the last few digits (I think 6 are allowed but for is
recommended) to facilitate searches. Of course companies could still have
the same information unencrypted in other columns or tables (except for the
CC number itself - and you *never* keep the CVN in any form on threat of
losing your ability to accept credit cards if you're caught) but they were
encouraged to encrypt it as well.

Anyway legal requirements is "#0" on that list. Everything else *might*
happen but depending upon the nature of the data you *will* be audited for
compliance with regulations, either preemptively (e.g., VISA requires
periodic audits of anyone making more than $X in transactions per year) or
after a breach. One of my other past employers did the type of auditing
VISA requires and their promotional material was full of interviews with
former small business owners who lost their business after a breach. It
wasn't due to the loss itself, it's because any breach automatically
requires the strictest auditing for the next (4?) years and that cost far
more than the average independent restaurant, auto repair shop, etc., can
afford. Obviously their business model is (in part) to scare people but
there are plenty of situations where you have to encrypt data within the
database and not just rely on encrypted media.

> Here are some threats you might choose to protect against:
>
> 1) passive attackers on the wire
> 2) active attackers on the wire
> 3a) theft / compromise of storage devices
> 3b) compromise of decommissioned storage devices
> 3c) theft of running server
> 4) compromised backup storage
> 5) bad / compromised clients
> 6) bad / compromised DBAs or sysadmins
> 7) side channel exploits
> 8) ??
>
> (1) and (2) are taken care of by TLS.
>
> (3a) is taken care of by FDE in controllers, say, or by physical
> security.
>
> (3b) is taken care of by proper decommissioning, but FDE helps.
>
> (3c) you can't protect against if you have keys in memory. You could
> use client-side crypto, but you'll have more clients to worry about than
> servers. Physical security is your best option. (And really, you don't
> get any way to protect against law enforcement taking the devices.)
>
> (4) is taken care of by encrypting backups, which requires no changes to
> PG to get.
>
> (5) is taken care of (to some degree) by server-side logic (triggers,
> ...).
>
> (6)... You can't protect against sysadmins, really, nor DBAs, but you
> can use crypto on the *client*-side to get some protection. Since the
> PG client is very thin and dumb, the PG client can't easily do this.
> The idea is to encrypt values and MAC/sign rows to prevent DBAs/
> sysadmins seeing sensitive data or tampering with your data.
>
> (7) one deals with by using crypto implementations built with side
> channel protection, though, really, this is a very difficult subject in
> general, especially since Spectre.
>
> Nico
> --
>
>