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.

Table of Contents

• 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
  • 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
  • Advanced Stream API
  • XChaCha20-SIV
  • Signcryption

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:

• Amazon Web Service's Key Management Service.

• Google Cloud's Cloud Key Management.

• Doppler SecretOps Platform.

• Zymbit Zymkey 4i Hardware Security Module.

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 include 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 f