Re: Internal key management system

Since the user does not need to know the master secret key used to cipher the data, I don't think we should expose "pg_kmgr_unwrap("xxxx")" SQL function to the user at all.

The wrapped key "xxxx" is stored in control data and it is possible to obtain by malicious user and steal the key by running SELECT pg_kmgr_unwrap("xxxx"). 

Even the user is righteous, it may not be very straightforward for that user to obtain the wrapped key "xxxx" to use in the unwrap function.

pg_kmgr_(un)wrap function is in discussion because encrypt and decrypt function require the master secret key as input argument. 

I would suggest using cluster passphrase as input instead of master key, so the user does not have to obtain the master key using pg_kmgr_unwrap("xxxx") in order to use the encrypt and decrypt function. 

The passphrase is in fact not stored anywhere in the system and we have to be careful that this passphrase is not shown in any activity log

so instead of:

------------------

INSERT INTO tbl VALUES (pg_encrypt('user data', pg_kmgr_unwrap('xxxxx'));

SELECT pg_decrypt(secret_column, pg_kmgr_unwrap('xxxxx')) FROM tbl;

it would become:

------------------

INSERT INTO tbl VALUES (pg_encrypt('user data', 'cluster_pass_phrase');

SELECT pg_decrypt(secret_column, 'cluster_pass_phrase') FROM tbl;

pg_decrypt will then have to:

1. derive the cluster pass phrase into KEK and HMAC key 

2. verify pass phrase by comparing MAC

3. unwrap the key - Sehrope suggests a good approach to make wrap/unwrap function more secure by adding MAC verification and randomed IV instead of default. I think it is good

4. decrypt the data

5. return

Using passphrase instead of master key to encrypt and decrypt function will also make front end tool integration simpler, as the front end tool also do not need to know the master key so it does not need to derive KEK or unwrap the key...etc. 

Not sure if you guys agree?

Thanks!

Cary Huang

-------------

HighGo Software Inc. (Canada)

mailto:cary(dot)huang(at)highgo(dot)ca

http://www.highgo.ca

---- On Thu, 06 Feb 2020 12:30:02 -0800 Robert Haas <robertmhaas(at)gmail(dot)com> wrote ----

On Mon, Feb 3, 2020 at 10:18 PM Masahiko Sawada
<mailto:masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> > I'm lost. If pg_{en,de}crypt and pg_kmgr_unwrap are functions, what
> > prevent users to:
> >
> > SELECT pg_kmgr_unwrap('xxxx');
> >
> > so as to recover the key, somehow in contradiction to "allows user to
> > encrypt and decrypt data without knowing the actual key".
>
> I might be missing your point but the above 'xxxx' is the wrapped key
> wrapped by the master key stored in PostgreSQL server. So user doesn't
> need to know the raw secret key to encrypt/decrypt the data. Even if a
> malicious user gets 'xxxx' they cannot know the actual secret key
> without the master key. pg_kmgr_wrap and pg_kmgr_unwrap are functions
> and it's possible for user to know the raw secret key by using
> pg_kmgr_unwrap(). The master key stored in PostgreSQL server never be
> revealed.

I think I have the same confusion as Fabien. Isn't it bad if somebody
just runs pg_kmgr_unwrap() and records the return value? Now they've
stolen your encryption key, it seems.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company