pgsodium

pgsodium is a PostgreSQL extension that exposes modern libsodium based cryptographic functions to SQL.

Installation

Travis CI tested with the official docker images for PostgreSQL 13, 12, 11, and 10. Requires libsodium >= 1.0.18. In addition to the libsodium library and it's development headers, you may also need the postgres header files typically in the '-dev' packages to build the extension.

Clone the repo and run 'sudo make install'.

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. Note that this will run the tests against and download docker imags for four different major versions of postgresql, so it takes a while and requires a lot of network bandwidth the first time you run it.

Usage

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 by the 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.

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 that is sent to the server, so it's possible they can show up in the database logs.

Avoid secret logging

A more paranoid 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:

-- 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;