Re: Internal key management system - Mailing list pgsql-hackers

On Thu, Mar 19, 2020 at 09:33:09PM +0900, Masahiko Sawada wrote:
> Attached updated version patch. This patch incorporated the comments
> and changed pg_upgrade so that we take over the master encryption key
> from the old cluster to the new one if both enable key management.

We had a crypto team meeting today, and came away with a few ideas:

We should create an SQL-level master key that is different from the
block-level master key.  By using separate keys, and not deriving them
from a single key, they keys can be rotated and migrated to a different
cluster independently.  For example, users might want to create a new
cluster with a new block-level key, but might want to copy the SQL-level
key from the old cluster to the new cluster.  Both keys would be
unlocked with the same passphrase.

I was confused by how the wrap/unwrap work.  Here is an example from
the proposed doc patch:

    +<programlisting>
    +=# SELECT pg_wrap('user sercret key');
    +                                                                              pg_wrap

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
    +
\xb2c89f76f04f95d029f179e0fc3df4ed7254127b5562a9e27d42d1cd037c942dea65ce7c0750c520fa4f4e90481c9eb7e1e42a068248c262c1a6f25c6eab64303b1154ccc9a14361223641aab4a7aabe
    +(1 row)
    +</programlisting>
    +
    +  <para>
    +   Once wrapping the user key, user can encrypt and decrypt user data using the
    +   wrapped user key togehter with the key unwrap functions:
    +  </para>
    +
    +<programlisting>
    + =# INSERT INTO tbl
    +        VALUES (pgp_sym_encrypt('secret data',
    +
pg_unwrap('\xb2c89f76f04f95d029f179e0fc3df4ed7254127b5562a9e27d42d1cd037c942dea65ce7c0750c520fa4f4e90481c9eb7e1e42a068248c262c1a6f25c6eab64303b1154ccc9a14361223641aab4a7aabe')));
    + INSERT 1
    +
    + =# SELECT * FROM tbl;
    +                                                                             col

+--------------------------------------------------------------------------------------------------------------------------------------------------------------
    +
\xc30d04070302a199ee38bea0320b75d23c01577bb3ffb315d67eecbeca3e40e869cea65efbf0b470f805549af905f94d94c447fbfb8113f585fc86b30c0bd784b10c9857322dc00d556aa8de14
    +(1 row)
    +
    + =# SELECT pgp_sym_decrypt(col,
    +
pg_unwrap('\xb2c89f76f04f95d029f179e0fc3df4ed7254127b5562a9e27d42d1cd037c942dea65ce7c0750c520fa4f4e90481c9eb7e1e42a068248c262c1a6f25c6eab64303b1154ccc9a14361223641aab4a7aabe'))
ascol
 
    +    FROM tbl;
    +        col
    +------------------
    + user secret data

All pg_wrap() does is to take the user string, in this case 'user
sercret key' and encrypt it with the SQL-level master key.  It doesn't
mix the SQL-level master key into the output, which is what I originally
thought.  This means that the pg_unwrap() call above just returns 'user
sercret key'.

How would this be used? Users would call pg_wrap() once, and store the
result on the client.  The client could then use the output of pg_wrap()
in all future sessions, without exposing 'user sercret key', which is
the key used to encrypt user data.

The passing of the parameter to pg_wrap() has to be done in a way that
doesn't permanently record the parameter anywhere, like in the logs. 
pgcryptokey (https://momjian.us/download/pgcryptokey/) has a method of
doing this.  This is how it passes the data encryption key without
making it visible in the logs, using psql:

    SELECT get_shared_key()
        \gset
        \set enc_access_password `echo 'my secret' | tr -d '\n' | openssl dgst -sha256 -binary | gpg2 --symmetric
--batch--cipher-algo AES128 --passphrase :'get_shared_key' | xxd -plain | tr -d '\n'`
 
        SELECT set_session_access_password(:'enc_access_password');

Removing the sanity checks and user-interface simplicity, it is
internally doing this:

        SELECT set_config('pgcryptokey.shared_key',
                          encode(gen_random_bytes(32), 'hex'),
                          FALSE) AS get_shared_key
    \gset
        \set enc_access_password `echo 'my secret' | tr -d '\n' | openssl dgst -sha256 -binary | gpg2 --symmetric
--batch--cipher-algo AES128 --passphrase :'get_shared_key' | xxd -plain | tr -d '\n'`
 
        SELECT set_config('pgcryptokey.access_password',
                         encode(pgp_sym_decrypt_bytea(decode(:'enc_access_password', 'hex'),
                                                       :'get_shared_key'),
                                'hex'),
                          FALSE) || NULL;

In English, what it does is the server generates a random key, stores it
in a server-side veraible, and sends it to the client.  The client
hashes a user-supplied key and encrypts it with the random key it got
from the server, and sends it to the sever.  The server decrypts it
using the key it sent (stored in a server-side variable) and stores the
the result in another server-side veriable.  Perhaps this can be added
to our docs as a way of calling pg_wrap().

What good is this feature?  Well, the user-supplied data encryption key
like 'user sercret key', which is used to encrypt user data, is not
visible in the query or the server logs. The wrapped password is
visible, but to use it you must be able to connect to a running server
(to unwrap it), or have a shut down server and know the paasphrase. 
Read access to the file system is not sufficient since there is no
access to the pass phrase.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +