Re: Internal key management system

From: Cary Huang <cary(dot)huang(at)highgo(dot)ca>
To: "Ahsan Hadi" <ahsan(dot)hadi(at)gmail(dot)com>
Cc: "Masahiko Sawada" <masahiko(dot)sawada(at)2ndquadrant(dot)com>, "Bruce Momjian" <bruce(at)momjian(dot)us>, "PostgreSQL Hackers" <pgsql-hackers(at)lists(dot)postgresql(dot)org>, "Moon, Insung" <tsukiwamoon(dot)pgsql(at)gmail(dot)com>, "Robert Haas" <robertmhaas(at)gmail(dot)com>, "Fabien COELHO" <coelho(at)cri(dot)ensmp(dot)fr>, "Sehrope Sarkuni" <sehrope(at)jackdb(dot)com>, "cary huang" <hcary328(at)gmail(dot)com>, "Ibrar Ahmed" <ibrar(dot)ahmad(at)gmail(dot)com>, "Joe Conway" <mail(at)joeconway(dot)com>
Subject: Re: Internal key management system
Date: 2020-05-01 22:16:46
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi all

I am sharing here a document patch based on top of kms_v10 that was shared awhile back. This document patch aims to cover more design details of the current KMS design and to help people understand KMS better. Please let me know if you have any more comments.

thank you

Best regards

Cary Huang


HighGo Software Inc. (Canada)


---- On Tue, 07 Apr 2020 20:56:12 -0700 Ahsan Hadi <mailto:ahsan(dot)hadi(at)gmail(dot)com> wrote ----

Hi Bruce/Joe,

In the last meeting we discussed the need for improving the documentation for KMS so it is easier to understand the interface. Cary from highgo had a go at doing that, please see the previous email on this thread from Cary and let us know if it looks good...?

-- Ahsan 

On Wed, Apr 8, 2020 at 3:46 AM Cary Huang <mailto:cary(dot)huang(at)highgo(dot)ca> wrote:


Highgo Software (Canada/China/Pakistan)
ADDR: 10318 WHALLEY BLVD, Surrey, BC
EMAIL: mailto: 


Thanks a lot for the patch, I think in terms of functionality, the patch provides very straightforward functionalities regarding key management. In terms of documentation, I think the patch is still lacking some pieces of information that kind of prevent people from fully understanding how KMS works and how it can be used and why, (at least that is the impression I got from the zoom meeting recordings :p). I spent some time today revisiting the key-management documentation in the patch and rephrase and restructure it based on my current understanding of latest KMS design. I mentioned all 3 application level keys that we have agreed and emphasize on explaining the SQL level encryption key because that is the key that can be used right now. Block and WAL levels keys we can add here more information once they are actually used in the TDE development. 

Please see below the KMS documentation that I have revised and I hope it will be more clear and easier for people to understand KMS. Feel free to make adjustments. Please note that we use the term "wrap" and "unwrap" a lot in our past discussions. Originally we used the terms within a context involving Key encryption keys (KEK). For example, "KMS wraps a master key with KEK". Later, we used the same term in a context involving encrypting user secret /password. For example, "KMS wraps a user secret with SQL key". In my opinion, both make sense but it may be confusing to people having the same term used differently. So in my revision below, the terms "wrap" and "unwrap" refer to encrypting or decrypting user secret / password as they are used in "pg_wrap() and pg_unwrap()". I use the terms "encapsulate" and "restore" when KEK is used to encrypt or decrypt a key.

Chapter 32: Encryption Key Management


PostgreSQL supports internal Encryption Key Management System, which is designed to manage the life cycles of cryptographic keys within the PostgreSQL system. This includes dealing with their generation, storage, usage and rotation.

Encryption Key Management is enabled when PostgreSQL is build with --with-openssl and cluster passphrase command is specified during initdb. The cluster passphrase provided by --cluster-passphrase-command option during initdb and the one generated by cluster_passphrase_command in the postgresql.conf must match, otherwise, the database cluster will not start up.

32.1 Key Generations and Derivations


When cluster_passphrase_command option is specified to the initdb, the process will derive the cluster passphrase into a Key Encryption Key (KEK) and a HMAC Key using key derivation protocol before the actual generation of application level cryptographic level keys.

-Key Encryption Key (KEK)

KEK is primarily used to encapsulate or restore a given application level cryptographic key


HMAC key is used to compute the HASH of a given application level cryptographic key for integrity check purposes

These 2 keys are not stored physically within the PostgreSQL cluster as they are designed to be derived from the correctly configured cluster passphrase.

Encryption Key Management System currently manages 3 application level cryptographic keys that have different purposes and usages within the PostgreSQL system and these are generated using pg_strong_random() after KEK and HMAC key derivation during initdb process.

The 3 keys are:

-SQL Level Key

SQL Level Key is used to wrap and unwrap a user secret / passphrase via pg_wrap() and pg_unwrap() SQL functions. These 2 functions are designed to be used in conjunction with the cryptographic functions provided by pgcrypto extension to perform column level encryption/decryption without having to supply a clear text user secret or passphrase that is required by many pgcrypto functions as input. Please refer to [Wrap and Unwrap User Secret section] for usage examples.

-Block Level Key

Block Level Key is primarily used to encrypt / decrypt buffers as part of the Transparent Data Encryption (TDE) feature

-WAL Level Key

WAL Level Key is primarily used to encrypt / decrypt WAL files as part of the Transparent Data Encryption (TDE) feature

The 3 application level keys above will be encapsulated and hashed using KEK and HMAC key mentioned above before they are physically stored to pg_cryptokeys directory within the cluster.

32.1. Key Initialization


When a PostgreSQL cluster with encryption key management enabled is started, the cluster_passphrase_command parameter in postgresql.conf will be evaluated and the cluster passphrase will be derived into KEK and HMAC Key in similar ways as initdb.

After that, the 3 encapsulated application level cryptographic keys will be retrieved from pg_cryptokeys directory to be restored and integrity-checked by the key management system using the derived KEK and HMAC key. If this process fails, it is likely that the cluster passphrase supplied to the cluster is not the same as that supplied to the initdb process. The cluster will refuse to start in this case and user has to manually correct the cluster passphrase.

32.2. Wrap and Unwrap User Secret


Encryption key management system provides pg_wrap() and pg_unwrap SQL functions (listed in Table 9.97) to perform wrap and unwrap operations on user secret with the SQL level encryption key. The SQL level encryption key is one of the 3 application level keys generated during initdb process when cluster_passphrase is supplied.

When pg_wrap() and pg_unwrap() functions are invoked, SQL level encryption key will internally be used to perform the encryption and decryption operation with HMAC-based integrity check. From user's point of view, he or she is not aware of the actual SQL level encryption key used internally by both wrap functions 

One possible use case is to combine pg_wrap() and pg_unwrap() with pgcrypto. User wraps the user encryption secret with pg_wrap function and passes the wrapped encryption secret to pg_unwrap function for the pgcrypto encryption functions. The wrapped secret can be stored in the application server or somewhere secured and should be obtained promptly for cryptographic operation with pgcrypto.

Here is an example that shows how to encrypt and decrypt data together with wrap and unwrap functions:

=# SELECT pg_wrap('my secret passward');




(1 row)

Once wrapping the user key, user can encrypt and decrypt user data using the wrapped user key together with the key unwrap functions:


        VALUES (pgp_sym_encrypt('secret data',



=# SELECT * FROM tbl;




(1 row)

=# SELECT pgp_sym_decrypt(col,

                           pg_unwrap('\xb2c89f76f04f95d029f179e0fc3df4ed7254127b5562a9e27d42d1cd037c942dea65ce7c0750c520fa4f4e90481c9eb7e1e42a068248c262c1a6f25c6eab64303b1154ccc9a14361223641aab4a7aabe')) as col

    FROM tbl;



secret data

(1 row)

The data 'secret data' is practically encrypted by the user secret 'my secret passward' but using wrap and unwrap functions user don't need to know the actual user secret during operation.

32.3. Key Rotation Process


Encryption keys in general are not interminable, the longer the same key is in use, the chance  of it being breached increases. Performing key rotation on a regular basis help meet standardized security practices such as PCI-DSS and it is a good practice in security to limit the number of encrypted bytes available for a specific key version. The key lifetimse are based on key length, key strength, algorithm and total number of bytes enciphered. The key management systems provides a efficient method to perform key rotation.

Please be aware that the phrase "key rotation" here only refers to the rotation of KEK and HMAC keys. The 3 application level encryption keys (SQL, Block and WAL levels) are not rotated; they will in fact be the same before and after a "key rotation." This can be justified because the actual keys are never stored anywhere physically, presented to user or captured in logging. What is being rotated here is the KEK and HMAC keys who are responsible for encapsulating and restoring the actual application level encryption keys.

Since both KEK and HMAC keys are derived from a cluster passphrase, the "key rotation" ultimately refers to the rotation of cluster passphrase and deriving a new KEK and HMAC keys from the new cluster passphrase. The new set of KEK and HMAC keys can then be used to encapsulate all 3 application level encryptions keys and store the new results in pg_cryptokeys directory.

To rotate the cluster passphrase, user firstly needs to update cluster_passphrase_command in the postgresql.conf and then execute pg_rotate_cluster_passphrase() SQL function to initiate the rotation.

Cary Huang


HighGo Software Inc. (Canada)


---- On Mon, 30 Mar 2020 21:30:19 -0700 Masahiko Sawada <mailto:masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote ----

On Tue, 31 Mar 2020 at 09:36, Cary Huang <mailto:cary(dot)huang(at)highgo(dot)ca> wrote:
> Hi
> I had a look on kms_v9 patch and have some comments
> --> pg_upgrade.c
> keys are copied correctly, but as pg_upgrade progresses further, it will try to start the new_cluster from "issue_warnings_and_set_wal_level()" function, which is called after key copy. The new cluster will fail to start due to the mismatch between cluster_passphrase_command and the newly copied keys. This causes pg_upgrade to always finish with failure. We could move "copy_master_encryption_key()" to be called after "issue_warnings_and_set_wal_level()" and this will make pg_upgrade to finish with success, but user will still have to manually correct the "cluster_passphrase_command" param on the new cluster in order for it to start up correctly. Should pg_upgrade also take care of copying "cluster_passphrase_command" param from old to new cluster after it has copied the encryption keys so users don't have to do this step? If the expectation is for users to manually correct "cluster_passphrase_command" param after successful pg_upgrade and key copy, then there should be a message to remind the users to do so.

I think both the old cluster and the new cluster must be initialized
with the same passphrase at initdb. Specifying the different
passphrase command to the new cluster at initdb and changing it after
pg_upgrade doesn't make sense. Also I don't think we need to copy
cluster_passphrase_command same as other GUC parameters.

I've changed the patch so that pg_upgrade copies the crypto keys only
if both new and old cluster enable the key management. User must
specify the same passphrase command to both old and new cluster, which
is not cumbersome, I think. I also added the description about this to
the doc.

> -->Kmgr.c
> + /*
> + * If there is only temporary directory, it means that the previous
> + * rotation failed after wrapping the all internal keys by the new
> + * passphrase. Therefore we use the new cluster passphrase.
> + */
> + if (stat(KMGR_DIR, &st) != 0)
> + {
> + ereport(DEBUG1,
> + (errmsg("both directories %s and %s exist, use the newly wrapped keys",
> I think the error message should say "there is only temporary directory exist" instead of "both directories exist"

You're right. Fixed.

I've attached the new version patch.


Masahiko Sawada
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
kms_doc_v10.patch application/octet-stream 13.1 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2020-05-01 23:51:34 Re: Remove unnecessary relabel stripping
Previous Message Tom Lane 2020-05-01 21:32:11 Re: do {} while (0) nitpick