Server Key Management

python %load_ext sql

python %config %config SqlMagic.displaycon=False %sql postgresql://postgres@/

The core feature of pgsodium its its ability to manage encryption keys for you, you so that you never reference a raw encryption key, but instead you reference keys by ID. A key id is a UUID that uniquely identifies the key used. An example of using Server Key Management can be found in the section on Transparent Column Encryption and is most of the API examples that can take key UUIDs are arguments.

The hidden root key

Create a new Key

pgsodium can manage two types of keys, derived keys, and external keys. Derived keys use libsodium to

Server managed keys are created with the pgsodium.create_key() function. This function takes a few optional parameters: - key_type: The type of key to create, the default is aead-det. Can be one of: - aead-det - aead-ietf - hmacsha512 - hmacsha256 - auth - secretbox - secretstream - shorthash - generichash - kdf - name: An optional unique name for the key. The default is NULL which makes an "anonymous" key. - derived_key: An optional raw external key, for example an hmac key from an external service. pgsodium will store this key encrypted with TCE. - derived_key_nonce: An optional nonce for the raw key, if none is provided a new random aead-det nonce will be generated using pgsodium.crypto_aead_det_noncegen(). - parent_key: If raw_key is not null, then this key id is used to encrypt the raw key. The default is to generate a new aead-det key. - derived_context - expires - associated_data

pgsodium.create_key() returns a new row in the pgsodium.valid_key view. For most purposes, you usually just need the new key's ID to start using it. For example, here's a new external shahmac256 key being created and used to verify a payload:

python external_key = %sql select pgsodium.crypto_auth_hmacsha256_keygen() external_key = bytes(external_key[0][0]) print(external_key)

b'\x1d\xa5\xf2\xa0a\xa8\x03 \x9b\x88J\xfe\xd2Xc\x0cG\xc5{\xc3W\xd7\x91KXp\x87\x15\x02"\xd6\xf6'

python %sql select * from pgsodium.create_key('hmacsha256', raw_key:=:external_key)

id name status key_type key_id key_context created expires associated_data
92e24493-2df6-422b-8c75-00c82b1097c4 None valid hmacsha256 None None 2022-09-05 19:46:07.340760+00:00 None

python %sql select id, key_type, parent_key, length(decrypted_raw_key) from pgsodium.decrypted_key where key_type = 'hmacsha256';

id key_type parent_key length
5a8720af-50aa-4bd9-a9f1-c71065e75a88 hmacsha256 451843b3-74f8-4458-bc2d-5a88c6024832 32
9fac7ff7-10d7-4139-966b-f7317e4486b2 hmacsha256 a1f91b67-2793-4788-ab8e-4fca32e360da 32
92e24493-2df6-422b-8c75-00c82b1097c4 hmacsha256 cc815230-06c6-4d57-9780-ac2a2dc026bc 32