pgsodium: libsodium inside PostgreSQL, with a root key that SQL cannot reach

Most of libsodium, exposed as SQL functions

pgsodium is an encryption library extension for PostgreSQL using libsodium for high level cryptographic algorithms, and the table of contents in the README reads like the libsodium API reference. It covers secret key cryptography with authenticated encryption and authentication, public key cryptography with authenticated encryption and public key signatures, sealed boxes, hashing, password hashing, key derivation, key exchange, HMAC512, an advanced stream API, XChaCha20-SIV and signcryption. Keys generated in pairs come back as a record type, and the README shows crypto_box_new_keypair returning a public and a secret column of hex-encoded bytea. The stated design position is that pgsodium can be used as a straight interface to libsodium, and the advanced features are additions on top of that rather than replacements. Two conventions matter for anyone writing queries against it. Arguments and return values for content and keys are of type bytea, so text and varchar have to be converted explicitly with encode and decode or convert_to and convert_from, and simple ASCII text without escapes or unicode is cast implicitly, which is what the tests do to save time. The second is that pgsodium uses memory cleanup callbacks to zero out allocated memory when it is freed.

A root key that no query can read

The feature that distinguishes pgsodium from a thin wrapper is Server Key Management. If you add pgsodium to the shared_preload_libraries configuration and place a special script in the PostgreSQL shared extension directory, the server preloads a libsodium key at startup, and the root secret key cannot be accessed from SQL. The only ways to use it are to derive other keys from it with derive_key, or to use the key_id variants of the API, which take key ids and contexts instead of raw bytea keys. The README is explicit that server managed keys are completely optional and that pgsodium still works without preloading, but then you have to provide your own key management. The distinction matters because the general advice in the same document is that storing secrets in the database is a bad idea in general, and it can be done carefully but carries a higher risk. The point of the design is that the root key never enters the database and never appears in a query, and only derived material does. The example script for a local key uses the Linux /dev/urandom CSPRNG, and there are further example scripts for Amazon Web Services KMS, Google Cloud KMS, Doppler SecretOps and the Zymbit Zymkey 4i hardware security module, so the root key can come from a managed or hardware source rather than a file.

Store a key id in the table, not the key

The consequence of the server key model is the design of the key identifiers. The inaccessible root key is used to derive sub-keys and key pairs by key id, and that id, of type bigint, is what you store instead of the derived key. The table of contents separates Server Key Management, Server Key Derivation, the Key Management API and Security Roles, and that last one is worth reading carefully, because the role model is what stops a SQL user from deriving the key they should not have. The README also flags a practical detail about how the server is started, since a key loaded at boot only exists if the extension is in the preload list:

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

The documented script location is getkey_scripts/pgsodium_getkey_urandom.sh for the local case, with the other providers alongside it. Two operational cautions follow from the design. The key id is a handle, so anyone who can read the table learns the id of every derived key, and the derivation context becomes part of your security model whether you intended it or not. And because the key is loaded at server start, rotating it is a restart, not an update, which is a different maintenance routine from rotating a row in a key table.

Transparent Column Encryption, and the extension's own schema rules

The second headline feature is Transparent Column Encryption, which can automatically encrypt and decrypt one or more columns of data in a table. The repository ships examples for it, including example/tce.sql and example/encrypted_column.sql alongside notebooks for an anonymizer and for a secret box, and a PgSodiumAnonymizer notebook. The idea is that the column type carries the policy and the extension does the work on read and write, which removes the discipline of remembering to call an encrypt function on every insert path. The other behaviour to know about is how the extension is installed and addressed. Once the extension is compiled you create it with:

CREATE EXTENSION pgsodium;

The README warns that pgsodium is very careful about the risk of search_path hacking and must go into a database schema named pgsodium, which the create command sets up automatically, and it encourages always referencing pgsodium functions by their fully qualified names, or making sure that the pgsodium schema is first in your search_path. This is not fussiness. An extension that decrypts data on behalf of a caller needs to be certain that the function being called is the real one, and a schema-qualified call is the mechanism that makes that guarantee checkable.

Building it needs libsodium, headers and a recent PostgreSQL

The installation requirements are stated precisely. pgsodium requires libsodium 1.0.18 or later, and in addition to the libsodium library and its development headers you may also need the PostgreSQL header files, typically in the -dev packages, to build the extension. Once the dependencies are in place, you clone the repository and run sudo make install, or you install it through the pgxn extension network with pgxn install pgsodium. The version floor is a hard one. As of version 3.0.0 pgsodium requires PostgreSQL 14 or newer, and the README says to use pgsodium 2.0.* for earlier versions of Postgres. The extension build itself is a conventional PGXS Makefile, where the module name, the source object files and the linkage against libsodium are all declared, and the SQL test files are collected from the sql directory as regression tests. There is also a dist target that archives the tree into a zip named for the extension and version, which is what the release artefacts are made from.

pgTAP tests, a test script and a Docker image that builds everything

Testing is done with pgTAP, and the README gives two ways to run it. The tests can be run with sudo -u postgres pg_prove test.sql, or they can be run in a self-contained Docker image, where running ./test.sh executes all of them if you have Docker installed. The Dockerfile in the repository is the more interesting artefact, because it builds the entire stack from source rather than assuming a distribution package. It starts from debian:bookworm-slim, installs the build toolchain, then fetches and compiles PostgreSQL from the official source tarball, then libsodium 1.0.22-RELEASE, then pgTAP v1.2.0, then pgddl from its repository, and finally builds pgsodium itself with make and make install against the freshly built PostgreSQL. The library versions are therefore pinned by the Dockerfile, which is what makes the image reproducible for testing purposes, and there is a second Dockerfile-debug alongside it for development. The repository also carries a docgen.py and a doctestify.py, which suggests the SQL documentation and the embedded test cases are generated from the source rather than written twice.

Editorial conclusion

pgsodium fits a PostgreSQL deployment that has to encrypt sensitive columns without pushing the key material into the database, and that can put a key in the server's preload list so a derived key id is all that appears in a table. It does not fit a team that wants encryption with no extension and no changes to how secrets are provisioned, and it does not fit an older server, since version 3.0.0 requires PostgreSQL 14 and up. Before adopting it, decide whether you want the server managed key path at all, because the plain libsodium interface needs no preloading, and read the requirement about fully qualifying pgsodium function names so that a search_path change cannot silently break your queries.

Frequently asked questions

Official sources

1. Issues
2. michelp/pgsodium on GitHub
3. README
4. Releases