Re: Internal key management system

On Sun, 2 Feb 2020 at 17:05, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
>
>
> Hello Masahiko-san,
>
> > I've started a new separate thread from the previous long thread[1]
> > for internal key management system to PostgreSQL. As I mentioned in
> > the previous mail[2], we've decided to step back and focus on only
> > internal key management system for PG13. The internal key management
> > system introduces the functionality to PostgreSQL that allows user to
> > encrypt and decrypt data without knowing the actual key. Besides, it
> > will be able to be integrated with transparent data encryption in the
> > future.
> >
> > The basic idea is that PostgreSQL generates the master encryption key
> > which is further protected by the user-provided passphrase. The key
> > management system provides two functions to wrap and unwrap the secret
> > by the master encryption key. A user generates a secret key locally
>
> In understand that the secret key is sent in the clear for being encrypted
> by a master key.

Yeah we need to be careful about the secret key not being logged in
any logs such as server logs for example when log_statement = 'all'. I
guess that wrapping key doesn't often happen during service running
but does once at development phase. So it would not be a big problem
but probably we need to have something to deal with it.

>
> > and send it to PostgreSQL to wrap it using by pg_kmgr_wrap() and save
> > it somewhere. Then the user can use the encrypted secret key to
> > encrypt data and decrypt data by something like:
> >
> > INSERT INTO tbl VALUES (pg_encrypt('user data', pg_kmgr_unwrap('xxxxx'));
> > SELECT pg_decrypt(secret_column, pg_kmgr_unwrap('xxxxx')) FROM tbl;
> >
> > Where 'xxxxx' is the result of pg_kmgr_wrap function.
>
> 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.

Regards,

--
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services