Re: [FEATURE REQUEST] Encrypted indexes over encrypted data - Mailing list pgsql-hackers

On Thu, Aug 09, 2018 at 03:00:26PM +0300, Danylo Hlynskyi wrote:
> The problem
> ==========
> 
> [...]
> 
> We don't trust full-disk-encryption or any other transparent encryption,
> because of possible SQL injections.

Can you elaborate on this?

> Solution 1 (possibly can be used even now)
> ========
> - perform full-disk encryption
> - perform encryption of column
> - add decrypting expression-based index with decryption key
> - limit ways on disclosing index internals. Ideally if no one except admin
> can do that
> - limit ways to read index definitions - so it's not possible for
> application to uncover decryption key from database itself, it should know
> it on it's own.

But... you have to have the decryption key(s) in memory at all times to
enable any write operations.  And plaintext as well at various times.

What does this gain you that FDE doesn't?

> Solution 2 (feature request)
> ========
> - full-disk encryption is optional
> - data column is encrypted
> - index is decrypted by construction, but each it's block is encrypted,
> even in memory.
> - lookups over index do lazy index buffer decrypt and close buffers ASAP
> - make every query that has to touch encrypted column or encrypted index
> require decryption key. This means, SELECT, DELETE, UPDATE, INSERT, VACUUM,
> CLUSTER, CREATE INDEX, pg_dump, pg_restore all should have decryption key
> supplied in order to be executed. This also means, that autovacuum daemon
> can't work.

Same response.

> What do you think about both solutions? Is it hard to implement soluition 2?

They gain little or nothing over doing filesystem encryption in the OS
or even just plain FDE (see below).  They are not worthwhile.

You need to define your threat model.  What bad actors are you
protecting against?  What threats are you protecting against?

https://www.postgresql.org/message-id/20180622042337.GL4200%40localhost

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
--