pgsodium

pgsodium is an encryption library extension for PostgreSQL using the libsodium library for high level cryptographic algorithms.

pgsodium can be used a straight interface to libsodium, but it can also use a powerful feature called Server Key Management where pgsodium loads an external secret key into memory that is never accessible to SQL. This inaccessible root key can then be used to derive sub-keys and keypairs by key id. This id (type bigint) can then be stored instead of the derived key.

Another advanced feature of pgsodium is Transparent Column Encryption which can automatically encrypt and decrypt one or more columns of data in a table.

pgsodium
- Installation
Usage
Server Key Management
Server Key Derivation
Key Management API
Security Roles
Transparent Column Encryption
Simple public key encryption with crypto_box()
Avoid secret logging
API Reference

Installation

pgsodium requires libsodium >= 1.0.18. In addition to the libsodium library and it's development headers, you may also need the PostgreSQL header files typically in the '-dev' packages to build the extension.

After installing the dependencies, clone the repo and run sudo make install. You can also install pgsodium through the pgxn extension network with pgxn install pgsodium.

pgTAP tests can be run with sudo -u postgres pg_prove test.sql or they can be run in a self-contained Docker image. Run ./test.sh if you have docker installed to run all tests.

As of version 3.0.0 pgsodium requires PostgreSQL 14+. Use pgsodium 2.0.* for earlier versions of Postgres. Once you have the extension correctly compiled you can install it into your database using the SQL:

CREATE EXTENSION pgsodium;

Note that pgsodium is very careful about the risk of search_path hacking and must go into a database schema named pgsodium. The above command will automatically create that schema. You are encouraged to always reference pgsodium functions by their fully qualified names, or by making sure that the pgsodium schema is first in your search_path.

Usage

Without using the optional Server Managed Keys feature pgsodium is a simple and straightforward interface to the libsodium API.

pgsodium arguments and return values for content and keys are of type bytea. If you wish to use text or varchar values for general content, you must make sure they are encoded correctly. The encode() and decode() and convert_to()/convert_from() binary string functions can convert from text to bytea. Simple ascii text strings without escape or unicode characters will be cast by the database implicitly, and this is how it is done in the tests to save time, but you should really be explicitly converting your text content if you wish to use pgsodium without conversion errors.

Most of the libsodium API is available as SQL functions. Keys that are generated in pairs are returned as a record type, for example:

postgres=# SELECT * FROM crypto_box_new_keypair();
                               public                               |                               secret
--------------------------------------------------------------------+--------------------------------------------------------------------
 \xa55f5d40b814ae4a5c7e170cd6dc0493305e3872290741d3be24a1b2f508ab31 | \x4a0d2036e4829b2da172fea575a568a74a9740e86a7fc4195fe34c6dcac99976
(1 row)

pgsodium is careful to use memory cleanup callbacks to zero out all allocated memory used when freed. In general it is a bad idea to store secrets in the database itself, although this can be done carefully it has a higher risk. To help with this problem, pgsodium has an optional Server Key Management function that can load a hidden server key at boot that other keys are derived from.

Server Key Management

If you add pgsodium to your shared_preload_libraries configuration and place a special script in your postgres shared extension directory, the server can preload a libsodium key on server start. This root secret key cannot be accessed from SQL. The only way to use the server secret key is to derive other keys from it using derive_key() or use the key_id variants of the API that take key ids and contexts instead of raw bytea keys.

Server managed keys are completely optional, pgsodium can still be used without putting it in shared_preload_libraries, but you will need to provide your own key management. Skip ahead to the API usage section if you choose not to use server managed keys.

See the file getkey_scripts/pgsodium_getkey_urandom.sh for an example script that returns a libsodium key using the linux /dev/urandom CSPRNG.

pgsodium also comes with example scripts for:

Next place pgsodium in your shared_preload_libraries. For docker containers, you can append this after the run:

docker run -d ... -c 'shared_preload_libraries=pgsodium'

When the server starts, it will load the secret key into memory, but this key is never accessible to SQL. It's possible that a sufficiently clever malicious superuser can access the key by invoking external programs, causing core dumps, looking in swap space, or other attack paths beyond the scope of pgsodium. Databases that work with encryption and keys should be extra cautious and use as many process hardening mitigations as possible.

It is up to you to edit the get key script to get or generate the key however you want. pgsodium can be used to generate a new random key with select encode(randombytes_buf(32), 'hex'). Other common patterns including prompting for the key on boot, fetching it from an ssh server or managed cloud secret system, or using a command line tool to get it from a hardware security module.

You can specify the location of the get key script with a database configuration variable in either postgresql.conf or using ALTER SYSTEM:

pgsodium.getkey_script = 'path_to_script'

Server Key Derivation

New keys are derived from the primary server secret key by id and an optional context using the libsodium Key Derivation Functions. Key id are just bigint integers. If you know the key id, key length (default 32 bytes) and the context (default 'pgsodium'), you can deterministicly generate a derived key.

Derived keys can be used to encrypt data or as a seed for deterministicly generating keypairs using crypto_sign_seed_keypair() or crypto_box_seed_keypair(). It is wise not to store these secrets but only store or infer the key id, length and context. If an attacker steals your database image, they cannot generate the key even if they know the key id, length and context because they will not have the server secret key.

The key id, key length and context can be secret or not, if you store them then possibly logged in database users can generate the key if they have permission to call the derive_key() function. Keeping the key id and/or length context secret to a client avoid this possibility and make sure to set your database security model correctly so that only the minimum permission possible is given to users that interact with the encryption API.

Key rotation is up to you, whatever strategy you want to go from one key to the next. A simple strategy is incrementing the key id and re-encrypting from N to N+1. Newer keys will have increasing ids, you can always tell the order in which keys are superceded.

A derivation context is an 8 byte bytea. The same key id in different contexts generate different keys. The default context is the ascii encoded bytes pgsodium. You are free to use any 8 byte context to scope your keys, but remember it must be a valid 8 byte bytea which automatically cast correctly for simple ascii string. For encoding other characters, see the encode() and decode() and convert_to()/convert_from() binary string functions. The derivable keyspace is huge given one bigint keyspace per context and 2^64 contexts.

To derive a key:

# select derive_key(1);
                          derive_key
--------------------------------------------------------------------
 \x84fa0487750d27386ad6235fc0c4bf3a9aa2c3ccb0e32b405b66e69d5021247b

# select derive_key(1, 64);
                                                          derive_key
------------------------------------------------------------------------------------------------------------------------------------
 \xc58cbe0522ac4875707722251e53c0f0cfd8e8b76b133f399e2c64c9999f01cb1216d2ccfe9448ed8c225c8ba5db9b093ff5c1beb2d1fd612a38f40e362073fb

# select derive_key(1, 32, '__auth__');
                          derive_key
--------------------------------------------------------------------
 \xa9aadb2331324f399fb58576c69f51727901c651c970f3ef6cff47066ea92e95

The default keysize is 32 and the default context is 'pgsodium'.

Derived keys can be used either directly in crypto_secretbox_* functions for "symmetric" encryption or as seeds for generating other keypairs using for example crypto_box_seed_new_keypair() and crypto_sign_seed_new_keypair().

# select * from crypto_box_seed_new_keypair(derive_key(1));
                               public                               |                               secret
--------------------------------------------------------------------+--------------------------------------------------------------------
 \x01d0e0ec4b1fa9cc8dede88e0b43083f7e9cd33be4f91f0b25aa54d70f562278 | \x066ec431741a9d39f38c909de4a143ed39b09834ca37b6dd2ba3d015206f14ca

Key Management API

pgsodium provides an API and internal table and view for simple key id and context managment. This table provides a number of useful columns including experation capability. Keys generated with this API must be used for the Transparent Column Encryption features.

Managed Keys have UUIDs for indentifiers, these UUIDs are used to lookup keys in the table. Note that the key management is based on the same Server Key Management that uses the internal hidden root key, so both the Key Management API and Transparent Column Encryption require it.

To create a new key, call the pgsodium.create_key() function:

# select * from pgsodium.create_key();
-[ RECORD 1 ]-------------------------------------
id          | 74d97ba2-f9e3-4a64-a032-8427cd6bd686
status      | valid
created     | 2022-08-04 05:06:53.878502
expires     | 
key_type    | aead-det
key_id      | 4
key_context | \x7067736f6469756d
comment     | This is an optional comment
user_data   |

pgsodium.create_key() takes the following arguments, all of them are optional:

key_type pgsodium.key_type = 'aead-det': The type of key to create.If you do not specify a raw_key argument, a new derived key_id of the correct type will be automatically generated in key_context argument context. Possible values are:
- aead-det
- aead-ietf
- hmacsha512
- hmacsha256
- auth
- shorthash
- generichash
- kdf
- generichash
- kdf
- secretbox
- secretstream
name text = null: The optional unique name of the key.
raw_key bytea = null: A raw key to store encrypted, if not specified, the raw key is derived from key_id and key_context.
raw_key_nonce bytea = null: The nonce used to encrypt the raw key with, if not specified a new random nonce will be generated.
key_context bytea = 'pgsodium': The libsodium context to use for derivation if key_id is not null.
parent_key uuid = null: The parent key use to encrypt the raw key. If not specified, a new unnamed key is created.
expires timestamptz = null: The expiration time checked by the pgsodium.valid_key view.
associated_data text = '': Extra user data you can associate with the encrypted raw key. This data is appended to the key UUID, and mixed into the encryption signature and can be authenticated with it.

Keys of the type aead-det can be used for Transparent Column Encryption. The view pgsodium.valid_keys filters the key table for only keys that are valid and not expired.

Security Roles

The pgsodium API has two nested layers of security roles:

pgsodium_keyiduser Is the less privileged role that can only access keys by their UUID. This is the role you would typically give to a user facing application.
pgsodium_keymaker is the more privileged role and can work with raw bytea And managed server keys. You would not typically give this role to a user facing application.

Note that public key apis like crypto_box and crypto_sign do not have "key id" variants, because they work with a combination of four keys, two keypairs for each of two parties.

As the point of public key encryption is for each party to keep their secrets and for that secret to not be centrally derivable. You can certainly call something like SELECT * FROM crypto_box_seed_new_keypair(derive_key(1)) and make deterministic keypairs, but then if an attacker steals your root key they can derive all keypair secrets, so this approach is not recommended.

Transparent Column Encryption

pgsodium provides a useful pattern where a trigger is used to encrypt a column of data in a table which is then decrypted using a view. This is called Transparent Column Encryption and can be enabled with pgsodium using the SECURITY LABEL ... PostgreSQL command.

If an attacker acquires a dump of the table or database, they will not be able to derive the keys used to encrypt the data since they will not have the root server managed key, which is never revealed to SQL See the example file for more details.

In order to use TCE you must use keys created from the Key Management Table API. This API returns key ids that are UUIDs for use with the internal encryption functions used by the TCE functionality. Creating a key to use is the first step:

# select * from pgsodium.create_key();
-[ RECORD 1 ]-------------------------------------
id                | dfc44293-fa78-4a1a-9ef9-7e600e63e101
status            | valid
created           | 2022-08-03 18:50:53.355099
expires           | 
key_type          | aead-det
key_id            | 5
key_context       | \x7067736f6469756d
comment           | 
associated_data   |

This key is now stored in the pgsodium.key table, and can be accessed via the pgsodium.valid_key view:

# select EXISTS (select 1 from pgsodium.valid_key where id = 'dfc44293-fa78-4a1a-9ef9-7e600e63e101');
-[ RECORD 1 ]
exists | t

Now this key id can be used for simple TCE as shown in the next section.

One Key Id for the Entire Column

For the simplest case, a column can be encrypted with one key id which must be of the type aead-det (as created above):

CREATE TABLE private.users (
	id bigserial primary key,
	secret text);

SECURITY LABEL FOR pgsodium	ON COLUMN private.users.secret
  IS 'ENCRYPT WITH KEY ID dfc44293-fa78-4a1a-9ef9-7e600e63e101';

The advantage of this approach is it is very simple, the user creates one key and labels a column with it. The cryptographic algorithm for this approach uses a nonceless encryption algorithm called crypto_aead_det_xchacha20(). If you wish to use a nonce value, see below.

One Key ID per Row

Using one key for an entire column means that whoever can decrypt one row can decrypt them all from a database dump. Also changing (rotating) the key means rewriting the whole table.

A more fine grained approach is to store one key id per row:

CREATE TABLE private.users (
	id bigserial primary key,
	secret text,
	key_id uuid not null,
  nonce bytea
);

SECURITY LABEL FOR pgsodium
	ON COLUMN private.users.secret
  IS 'ENCRYPT WITH KEY COLUMN key_id;

This approach ensures that “cracking” the key for one row does not help decrypt any others. It also acts as a natural partition that can work in conjunction with RLS to share distinct keys between owners.

One Key ID per Row with Nonce Support

The default cryptographic algorithm for the above approach uses a nonceless encryption algorithm called crypto_aead_det_xchacha20(). This scheme has the advantage that it does not require nonce values, the disadvantage is that duplicate plaintexts will produce duplicate ciphertexts, but this information can not be used to attack the key it can only reveal the duplication.

However duplication is still information, and if you want more security, slightly better performance, or you require duplicate plaintexts to have different ciphertexts, a unique nonce can be provided that mixes in some additional non-secret data that deduplicates ciphertexts for duplicate plaintext:

CREATE TABLE private.users (
	id bigserial primary key,
	secret text,
	key_id uuid not null,
    nonce bytea
);

SECURITY LABEL FOR pgsodium
	ON COLUMN private.users.secret
  IS 'ENCRYPT WITH KEY COLUMN key_id NONCE nonce';

One Key ID per Row with Nonce Support and Associated Data

The aead-det algorithm can mix user provided data into the authentication signature for the encrypted secret. This "authenticates" the plaintext and ensures that it has not been altered (or the decryption will fail). This is useful for associated useful metadata with your secrets:

CREATE TABLE private.users (
	id bigserial primary key,
	secret text,
	key_id uuid not null,
    nonce bytea,
    associated_data text
);

SECURITY LABEL FOR pgsodium
	ON COLUMN private.users.secret
  IS 'ENCRYPT WITH KEY COLUMN key_id NONCE nonce ASSOCIATED (id, associated_data)';

You can specify multiple columns as shown above with both the id and associated data column. Columns used for associated data must be deterministicly castable to text.

Simple public key encryption with `crypto_box()`

Here's an example usage from the test.sql that uses command-line psql client commands (which begin with a backslash) to create keypairs and encrypt a message from Alice to Bob.

-- Generate public and secret keypairs for bob and alice
-- \gset [prefix] is a psql command that will create local
-- script variables

SELECT public, secret FROM crypto_box_new_keypair() \gset bob_
SELECT public, secret FROM crypto_box_new_keypair() \gset alice_

-- Create a boxnonce

SELECT crypto_box_noncegen() boxnonce \gset

-- Alice encrypts the box for bob using her secret key, the nonce and his public key

SELECT crypto_box('bob is your uncle', :'boxnonce', :'bob_public', :'alice_secret') box \gset

-- Bob decrypts the box using his secret key, the nonce, and Alice's public key

SELECT crypto_box_open(:'box', :'boxnonce', :'alice_public', :'bob_secret');

Note in the above example, no secrets are stored in the db, but they are interpolated into the sql by the psql client that is sent to the server, so it's possible they can show up in the database logs. You can avoid this by using derived keys.

Avoid secret logging

If you choose to work with your own keys and not restrict yourself to the pgsodium_keyiduser role, a useful approach is to keep keys in an external storage and disables logging while injecting the keys into local variables with SET LOCAL. If the images of database are hacked or stolen, the keys will not be available to the attacker.

To disable logging of the key injections, SET LOCAL is also used to disable log_statements and then re-enable normal logging afterwards. as shown below. Setting log_statement requires superuser privileges:

-- SET LOCAL must be done in a transaction block
BEGIN;

-- Generate a boxnonce, and public and secret keypairs for bob and alice
-- This creates secrets that are sent back to the client but not stored
-- or logged.  Make sure you're using an encrypted database connection!

SELECT crypto_box_noncegen() boxnonce \gset
SELECT public, secret FROM crypto_box_new_keypair() \gset bob_
SELECT public, secret FROM crypto_box_new_keypair() \gset alice_

-- Turn off logging and inject secrets
-- into session with set local, then resume logging.

SET LOCAL log_statement = 'none';
SET LOCAL app.bob_secret = :'bob_secret';
SET LOCAL app.alice_secret = :'alice_secret';
RESET log_statement;

-- Now call the `current_setting()` function to get the secrets, these are not
-- stored in the db but only in session memory, when the session is closed they are no longer
-- accessible.

-- Alice encrypts the box for bob using her secret key and his public key

SELECT crypto_box('bob is your uncle', :'boxnonce', :'bob_public',
                  current_setting('app.alice_secret')::bytea) box \gset

-- Bob decrypts the box using his secret key and Alice's public key.

SELECT crypto_box_open(:'box', :'boxnonce', :'alice_public',
                          current_setting('app.bob_secret')::bytea);

COMMIT;

For additional paranoia you can use a function to check that the connection being used is secure or a unix domain socket.

CREATE FUNCTION is_ssl_or_domain_socket() RETURNS bool
LANGUAGE plpgsql AS $$
DECLARE
    addr text;
    ssl text;
BEGIN
    SELECT inet_client_addr() INTO addr;
    SELECT current_setting('ssl', true) INTO ssl;
    IF NOT FOUND OR ((ssl IS NULL OR ssl != 'on')
        AND (addr IS NOT NULL OR length(addr) != 0))
    THEN
        RETURN false;
    END IF;
    RETURN true;
END;
$$;

This doesn't guarantee the secret won't leak out in some way of course, but it can useful if you never store secrets and send them only through secure channels back to the client, for example using the psql client \gset command shown above, or by only storing a derived key id and context.

API Reference

The reference below is adapted from and uses some of the same language found at the libsodium C API Documentation. Refer to those documents for details on algorithms and other libsodium specific details.

Generating Random Data

Functions:

    randombytes_random() -> integer

    randombytes_uniform(upper_bound integer) -> integer

    randombytes_buf(size integer) -> bytea

The library provides a set of functions to generate unpredictable data, suitable for creating secret keys.

# select randombytes_random();
 randombytes_random
--------------------
         1229887405
(1 row)

The randombytes_random() function returns an unpredictable value between 0 and 0xffffffff (included).

# select randombytes_uniform(42);
 randombytes_uniform
---------------------
                  23
(1 row)

The randombytes_uniform() function returns an unpredictable value between 0 and upper_bound (excluded). Unlike randombytes_random() % upper_bound, it guarantees a uniform distribution of the possible output values even when upper_bound is not a power of 2. Note that an upper_bound < 2 leaves only a single element to be chosen, namely 0.

# select randombytes_buf(42);
                                    randombytes_buf
----------------------------------------------------------------------------------------
 \x27cec8d2c3de16317074b57acba2109e43b5623e1fb7cae12e8806daa21a72f058430f22ec993986fcb2
(1 row)

The randombytes_buf() function returns a bytea with an unpredictable sequence of bytes.

# select randombytes_new_seed() bufseed \gset
# select randombytes_buf_deterministic(42, :'bufseed');
                             randombytes_buf_deterministic
----------------------------------------------------------------------------------------
 \xa183e8d4acd68119ab2cacd9e46317ec3a00a6a8820b00339072f7c24554d496086209d7911c3744b110
(1 row)

The randombytes_buf_deterministic() returns a size bytea containing bytes indistinguishable from random bytes without knowing the seed. For a given seed, this function will always output the same sequence. size can be up to 2^38 (256 GB).

michelp/pgsodium

michelp

Reviews

Repository Details

pgsodium

Table of Contents

Installation

Usage

Server Key Management

Server Key Derivation

Key Management API

Security Roles

Transparent Column Encryption

One Key Id for the Entire Column

One Key ID per Row

One Key ID per Row with Nonce Support

One Key ID per Row with Nonce Support and Associated Data

Simple public key encryption with crypto_box()

Avoid secret logging

API Reference

Generating Random Data

Secret key cryptography

Authenticated encryption

Authentication

Public key cryptography

Authenticated encryption

Public key signatures

Sealed boxes

Hashing

Password hashing

Key Derivation

Key Exchange

HMAC512/256

Advanced Stream API (XChaCha20)

XChaCha20-SIV

SignCryption

More Repositories

Simple public key encryption with `crypto_box()`