On Mon, Jun 11, 2018 at 06:22:22PM +0900, Masahiko Sawada wrote:
> As per discussion at PGCon unconference, I think that firstly we need
> to discuss what threats we want to defend database data against. If
We call that a threat model. There can be many threat models, of
course.
> user wants to defend against a threat that is malicious user who
> logged in OS or database steals an important data on datbase this
> design TDE would not help. Because such user can steal the data by
> getting a memory dump or by SQL. That is of course differs depending
> on system requirements or security compliance but what threats do you
> want to defend database data against? and why?
This design guards (somewhat) againts the threat of the storage theft
(e.g., because the storage is remote). It's a fine threat model to
address, but it's also a lot easier to address in the filesystem or
device drivers -- there's no need to do this in PostgreSQL itself except
so as to support it on all platforms regardless of OS capabilities.
Note that unless the pg_catalog is protected against manipulation by
remote storage, then TDE for user tables might be possible to
compromise. Like so: the attacker manipulates the pg_catalog to
escalate privelege in order to obtain the TDE keys. This argues for
full database encryption, not just specific tables or columns. But
again, this is for the threat model where the storage is the threat.
Another similar thread model is dump management, where dumps are sent
off-site where untrusted users might read them, or even edit them in the
hopes that they will be used for restores and thus compromise the
database. This is most easily addressed by just encrypting the backups
externally to PG.
Threat models where client users are the threat are easily handled by
PG's permissions system.
I think any threat model where DBAs are not the threat is just not that
interesting to address with crypto within postgres itself...
Encryption to public keys for which postgres does not have private keys
would be one way to address DBAs-as-the-thread, but this is easily done
with an extension... A small amount of syntactic sugar might help:
CREATE ROLE "bar" WITH (PUBLIC KEY "...");
CREATE TABLE foo (
name TEXT PRIMARY KEY,
payload TEXT ENCRYPTED TO ROLE "bar" BOUND TO name
);
but this is just syntactic sugar, so not that valuable. On the other
hand, just a bit of syntactic sugar can help tick a feature checkbox,
which might be very valuable for marketing reasons even if it's not
valuable for any other reason.
Note that encrypting the payload without a binding to the PK (or similar
name) is very dangerous! So the encryption option would have to support
some way to indicate what other plaintext to bind in (here the "name"
column).
Note also that for key management reasons it would be necessary to be
able to write the payload as ciphertext rather than as to-be-encrypted
TEXT.
Lastly, for a symmetric encryption option one would need a remote oracle
to do the encryption, which seems rather complicated, but in some cases
may well perform faster.
Nico
--
