Modern cryptography for PostgreSQL using libsodium.

Overview

Tests

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.

pgsodium provides some convenience roles that can be used to enforce access to polymorphic functions for encrypting either with a bytekey or a key id. For example, as a database superuser (or if you have the pgsodium_keyholder role) you can see derived sub-keys and use them directly in encryption functions:

postgres=# select derive_key(42);
                             derive_key
--------------------------------------------------------------------
 \xdf2d989f7ca632b3165813a4e960749a207eab16926d792be7484aff9cfde322
(1 row)

postgres=# select crypto_aead_det_encrypt('sekret message', 'additional data', derive_key(42));
                                    crypto_aead_det_encrypt
------------------------------------------------------------------------------------------------
 \xe7fa66d918654e70ff0fc9a87e2144a31cdf34526cf7f2846b321f47af8c87de02d925ad2343058c12bbb254ac3a
(1 row)

But this means the sub-key 42 can be seen in SQL or logged (but never the root key!). In order to remove the ability for users to access raw byte keys at all, use the pgsodium_keyiduser role that can never derive or use raw keys, only key ids:

postgres=# set role pgsodium_keyiduser ;
SET

postgres=> select derive_key(42);
ERROR:  permission denied for function derive_key

postgres=> select crypto_aead_det_encrypt('sekret message', 'additional data', 42);
                                    crypto_aead_det_encrypt
------------------------------------------------------------------------------------------------
 \xe7fa66d918654e70ff0fc9a87e2144a31cdf34526cf7f2846b321f47af8c87de02d925ad2343058c12bbb254ac3a
(1 row)

Notice in the second form using the restricted pgsodium_keyiduser role, derive_key is not permitted, but the same encryption function can be called directly with the integer 42. Permission to call the form of crypto_aead_det_encrypt with a raw byte key is revoked from the pgsodium_keyiduser role.

Table of Contents

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.

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 images for five different major versions of PostgreSQL (10, 11, 12, 13, 14), 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 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 protection 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.

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

Security Roles

The pgsodium API has three nested layers of security roles:

  • pgsodium_keyiduser Is the least privileged role, it cannot create or use raw bytea keys, it can only create crypto_secretkey nonces and access the crypto_secretkey, crypto_auth and crypto_aead API functions that accept key ids only. This role can also access the randombytes API. This is the role you would typically give to a user facing application.

  • pgsodium_keyholder Is the next more privileged layer, it can do everything pgsodium_keyiduser can do, but it can also use, but not create, raw bytea encryption keys. This role can use public key APIs like crypto_box and crypto_sign, but it cannot create keypairs. This role is useful for when keys come from external sources and must be passed as bytea to API functions.

  • pgsodium_keymaker is the most privileged role, it can do everything the previous roles can do, but it can also create keys, keypairs and key seeds and derive keys from key ids. Be very careful how you grant access to this role, as it can create valid secret keys derived from the root key.

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.

Encrypting Columns

Here's an example script that encrypts a column in a table and provides a view that does on the fly decryption. Each row's stores the nonce and key id used to encrypt the column. Note how no keys are used in this example, only key ids, so this code can be run by the least privileged pgsodium_keyiduser role:

CREATE SCHEMA pgsodium;
CREATE EXTENSION pgsodium WITH SCHEMA pgsodium;

CREATE TABLE test (
    id bigserial primary key,
    key_id bigint not null default 1,
    nonce bytea not null,
    data bytea
    );

CREATE VIEW test_view AS
    SELECT id,
    convert_from(pgsodium.crypto_secretbox_open(
             data,
             nonce,
             key_id),
    'utf8') AS data FROM test;

CREATE OR REPLACE FUNCTION test_encrypt() RETURNS trigger
    language plpgsql AS
$$
DECLARE
    new_nonce bytea = pgsodium.crypto_secretbox_noncegen();
    test_id bigint;
BEGIN

    insert into test (nonce) values (new_nonce) returning id into test_id;
    update test set data = pgsodium.crypto_secretbox(
        convert_to(new.data, 'utf8'),
        new_nonce,
        key_id)
    where id = test_id;
    RETURN new;
END;
$$;

CREATE TRIGGER test_encrypt_trigger
    INSTEAD OF INSERT ON test_view
    FOR EACH ROW
    EXECUTE FUNCTION test_encrypt();

Use the view as if it were a normal table, but the underlying table is encrypted. Again, no keys are stored or even available to this code, only derived keys based on a key id are used.

The trigger test_encrypt_trigger is fired INSTEAD OF INSERT ON the wrapper test_view, newly inserted rows are encrypted with a key derived from the stored key_id which defaults to 1.

# insert into test_view (data) values ('this is one'), ('this is two');

# select * from test;
 id | key_id |                       nonce                        |                           data
----+--------+----------------------------------------------------+----------------------------------------------------------
  3 |      1 | \xa6b9c4bfbfe194541faa21f2d31565babff1a250a010fa79 | \xb1d0432b173eb7fbef315ba5dd961454a4e2eef1332f9847eaef68
  4 |      1 | \x0ad82e537d5422966c110ed65f60c6bada57c0be73476950 | \x8c29b12778b6bb5873c9f7fa123c4f105d6eb16e0c54dfae93da10

# select * from test_view;
 id |    data
----+-------------
  3 | this is one
  4 | this is two

Key rotation can be done with a rotation function that will re-encrypt a row with a new key id. This function also requires no access to keys, it works only by key id and thus can be run by the least privileged pgsodium_keyiduser:

CREATE OR REPLACE FUNCTION rotate_key(test_id bigint, new_key bigint)
    RETURNS void LANGUAGE plpgsql AS $$
DECLARE
    new_nonce bytea;
BEGIN
    new_nonce = pgsodium.crypto_secretbox_noncegen();
    UPDATE test SET
    nonce = new_nonce,
    key_id = new_key,
    data = pgsodium.crypto_secretbox(
        pgsodium.crypto_secretbox_open(
             test.data,
             test.nonce,
             test.key_id),
        new_nonce,
        new_key)
    WHERE test.id = test_id;
    RETURN;
END;
$$;

Call the rotation function by passing a row id and a new key id. The old row will be decrypted with the old derived key, then encrypted with the new derived key.

# select rotate_key(3, 2);
 rotate_key
------------


# select * from test;
 id | key_id |                       nonce                        |                           data
----+--------+----------------------------------------------------+----------------------------------------------------------
  4 |      1 | \x0ad82e537d5422966c110ed65f60c6bada57c0be73476950 | \x8c29b12778b6bb5873c9f7fa123c4f105d6eb16e0c54dfae93da10
  3 |      2 | \x775f6b2fb01195f8646656d7588e581856ea44353332068e | \x27da7b96f4eb611a0c8ad8e4cee0988714d14e830a9aaf8f282c2a

# select * from test_view;
 id |    data
----+-------------
  4 | this is two
  3 | this is one

If an attacker acquires a dump of the table or database, they will not be able to derive the keys used 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.

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.

The libsodium documentation is Copyright (c) 2014-2018, Frank Denis [email protected] and released under The ISC License.

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).

C API Documentation

Secret key cryptography

C API Documentation

Authenticated encryption

Functions:

    crypto_secretbox_keygen() -> bytea

    crypto_secretbox_noncegen() -> bytea

    crypto_secretbox(message bytea, nonce bytea, key bytea) -> bytea

    crypto_secretbox_open(ciphertext bytea, nonce bytea, key bytea) -> bytea

crypto_secretbox_keygen() generates a random secret key which can be used to encrypt and decrypt messages.

crypto_secretbox_noncegen() generates a random nonce which will be used when encrypting messages. For security, each nonce must be used only once, though it is not a secret. The purpose of the nonce is to add randomness to the message so that the same message encrypted multiple times with the same key will produce different ciphertexts.

crypto_secretbox() encrypts a message using a previously generated nonce and secret key or key id. The encrypted message can be decrypted using crypto_secretbox_open() Note that in order to decrypt the message, the original nonce will be needed.

crypto_secretbox_open() decrypts a message encrypted by crypto_secretbox().

C API Documentation

Authentication

Functions:

    crypto_auth_keygen() -> bytea

    crypto_auth(message bytea, key bytea) -> bytea

    crypto_auth_verify(mac bytea, message bytea, key bytea) -> boolean

crypto_auth_keygen() generates a message-signing key for use by crypto_auth().

crypto_auth() generates an authentication tag (mac) for a combination of message and secret key. This does not encrypt the message; it simply provides a means to prove that the message has not been tampered with. To verify a message tagged in this way, use crypto_auth_verify(). This function is deterministic: for a given message and key, the generated mac will always be the same.

Note that this requires access to the secret key, which is not something that should normally be shared. If many users need to verify message it is usually better to use Public Key Signatures rather than sharing secret keys.

crypto_auth_verify() verifies that the given mac (authentication tag) matches the supplied message and key. This tells us that the original message has not been tampered with.

C API Documentation

Public key cryptography

C API Documentation

Authenticated encryption

Functions:

    crypto_box_new_keypair() -> crypto_box_keypair

    crypto_box_noncegen() -> bytea

    crypto_box(message bytea, nonce bytea,
               public bytea, secret bytea) -> bytea

    crypto_box_open(ciphertext bytea, nonce bytea,
                    public bytea, secret bytea) -> bytea

crypto_box_new_keypair() returns a new, randomly generated, pair of keys for public key encryption. The public key can be shared with anyone. The secret key must never be shared.

crypto_box_noncegen() generates a random nonce which will be used when encrypting messages. For security, each nonce must be used only once, though it is not a secret. The purpose of the nonce is to add randomness to the message so that the same message encrypted multiple times with the same key will produce different ciphertexts.

crypto_box() encrypts a message using a nonce, the intended recipient's public key and the sender's secret key. The resulting ciphertext can only be decrypted by the intended recipient using their secret key. The nonce must be sent along with the ciphertext.

crypto_box_open() decrypts a ciphertext encrypted using crypto_box(). It takes the ciphertext, nonce, the sender's public key and the recipient's secret key as parameters, and returns the original message. Note that the recipient should ensure that the public key belongs to the sender.

C API Documentation

Public key signatures

Functions:

    crypto_sign_new_keypair() -> crypto_sign_keypair

  combined mode functions:

    crypto_sign(message bytea, key bytea) -> bytea

    crypto_sign_open(signed_message bytea, key bytea) -> bytea

  detached mode functions:

    crypto_sign_detached(message bytea, key bytea) -> bytea

    crypto_sign_verify_detached(sig bytea, message bytea, key bytea) -> boolean

  multi-part message functions:

    crypto_sign_init() -> bytea

    crypto_sign_update(state bytea, message bytea) -> bytea

    crypto_sign_final_create(state bytea, key bytea) -> bytea

    crypto_sign_final_verify(state bytea, signature bytea, key bytea) -> boolean

Aggregates:

    crypto_sign_update_agg(message bytea) -> bytea

    crypto_sign_update_agg(state, bytea message bytea) -> bytea

These functions are used to authenticate that messages have have come from a specific originator (the holder of the secret key for which you have the public key), and have not been tampered with.

crypto_sign_new_keypair() returns a new, randomly generated, pair of keys for public key signatures. The public key can be shared with anyone. The secret key must never be shared.

crypto_sign() and crypto_sign_verify() operate in combined mode. In this mode the message that is being signed is combined with its signature as a single unit.

crypto_sign() creates a signature, using the signer's secret key, which it prepends to the message. The result can be authenticated using crypto_sign_open().

crypto_sign_open() takes a signed message created by crypto_sign(), checks its validity using the sender's public key and returns the original message if it is valid, otherwise raises a data exception.

crypto_sign_detached() and crypto_sign_verify_detached() operate in detached mode. In this mode the message is kept independent from its signature. This can be useful when wishing to sign objects that have already been stored, or where multiple signatures are desired for an object.

crypto_sign_detached() generates a signature for message using the signer's secret key. The result is a signature which exists independently of the message, which can be verified using crypto_sign_verify_detached().

crypto_sign_verify_detached() is used to verify a signature generated by crypto_sign_detached(). It takes the generated signature, the original message, and the signer's public key and returns true if the signature matches the message and key, and false otherwise.

crypto_sign_init(), crypto_sign_update(), crypto_sign_final_create(), crypto_sign_final_verify(), and the aggregates crypto_sign_update_agg() handle signatures for multi-part messages. To create or verify a signature for a multi-part message crypto_sign_init() is used to start the process, and then each message-part is passed to crypto_sign_update() or crypto_sign_update_agg(). Finally the signature is generated using crypto_sign_final_update() or verified using crypto_sign_final_verify().

crypto_sign_init() creates an initial state value which will be passed to crypto_sign_update() or crypto_sign_update_agg().

crypto_sign_update() or crypto_sign_update_agg() will be used to update the state for each part of the multi-part message. crypto_sign_update() takes as a parameter the state returned from crypto_sign_init() or the preceding call to crypto_sign_update() or crypto_sign_update_agg(). crypto_sign_update_agg() has two variants: one takes a previous state value, allowing multiple aggregates to be processed sequentially, and one takes no state parameter, initialising the state itself. Note that the order in which the parts of a multi-part message are processed is critical. They must be processed in the same order for signing and verifying.

crypto_sign_final_update() takes the state returned from the last call to crypto_sign_update() or crypto_sign_update_agg() and the signer's secret key and produces the final signature. This can be checked using crypto_sign_final_verify().

crypto_sign_final_verify() is used to verify a multi-part message signature created by crypto_sign_final_update(). It must be preceded by the same set of calls to crypto_sign_update() or crypto_sign_update_agg() (with the same message-parts, in the same order) that were used to create the signature. It takes the state returned from the last such call, along with the signature and the signer's public key and returns true if the messages, key and signature all match.

To sign or verify multi-part messages in SQL, CTE (Common Table Expression) queries are particularly effective. For example to sign a message consisting of a timestamp and several message_parts:

with init as
  (
    select crypto_sign_init() as state
  ),
timestamp_part as
  (
    select crypto_sign_update(i.state, m.timestamp::bytea) as state
      from init i
     cross join messages m
     where m.message_id = 42
  ),
remaining_parts as
  (
    select crypto_sign_update(t.state, p.message_part::bytea) as state
      from timestamp_part t
     cross join (
       select message_part
         from message_parts
        where message_id = 42
        order by message_part_num) p
  )
select crypto_sign_final_create(r.state, k.secret_key) as sig
  from remaining_parts r
 cross join keys k
 where k.key_name = 'xyzzy';

Note that storing secret keys in a table, as is done in the example above, is a bad practice unless you have effective row-level security in place.

C API Documentation

Sealed boxes

Sealed boxes are designed to anonymously send messages to a recipient given its public key. Only the recipient can decrypt these messages, using its private key. While the recipient can verify the integrity of the message, it cannot verify the identity of the sender.

SELECT public, secret FROM crypto_box_new_keypair() \gset bob_

SELECT crypto_box_seal('bob is your uncle', :'bob_public') sealed \gset

The sealed psql variable is now the encrypted sealed box. To unseal it, bob needs his public and secret key:

SELECT is(crypto_box_seal_open(:'sealed', :'bob_public', :'bob_secret'),
          'bob is your uncle', 'crypto_box_seal/open');

C API Documentation

Hashing

This API computes a fixed-length fingerprint for an arbitrary long message. Sample use cases:

  • File integrity checking
  • Creating unique identifiers to index arbitrary long data

The crypto_generichash and crypto_shorthash functions can be used to generate hashes. crypto_generichash takes an optional hash key argument which can be NULL. In this case, a message will always have the same fingerprint, similar to the MD5 or SHA-1 functions for which crypto_generichash() is a faster and more secure alternative.

But a key can also be specified. A message will always have the same fingerprint for a given key, but different keys used to hash the same message are very likely to produce distinct fingerprints. In particular, the key can be used to make sure that different applications generate different fingerprints even if they process the same data.

SELECT is(crypto_generichash('bob is your uncle'),
          '\x6c80c5f772572423c3910a9561710313e4b6e74abc0d65f577a8ac1583673657',
          'crypto_generichash');

SELECT is(crypto_generichash('bob is your uncle', NULL),
          '\x6c80c5f772572423c3910a9561710313e4b6e74abc0d65f577a8ac1583673657',
          'crypto_generichash NULL key');

SELECT is(crypto_generichash('bob is your uncle', 'super sekret key'),
          '\xe8e9e180d918ea9afe0bf44d1945ec356b2b6845e9a4c31acc6c02d826036e41',
          'crypto_generichash with key');

Many applications and programming language implementations were recently found to be vulnerable to denial-of-service attacks when a hash function with weak security guarantees, such as Murmurhash 3, was used to construct a hash table .

In order to address this, Sodium provides the crypto_shorthash() function, which outputs short but unpredictable (without knowing the secret key) values suitable for picking a list in a hash table for a given key. This function is optimized for short inputs. The output of this function is only 64 bits. Therefore, it should not be considered collision-resistant.

Use cases:

  • Hash tables Probabilistic
  • data structures such as Bloom filters
  • Integrity checking in interactive protocols

Example:

SELECT is(crypto_shorthash('bob is your uncle', 'super sekret key'),
          '\xe080614efb824a15',
          'crypto_shorthash');

C API Documentation

Password hashing

SELECT lives_ok($$SELECT crypto_pwhash_saltgen()$$, 'crypto_pwhash_saltgen');

SELECT is(crypto_pwhash('Correct Horse Battery Staple', '\xccfe2b51d426f88f6f8f18c24635616b'),
        '\x77d029a9b3035c88f186ed0f69f58386ad0bd5252851b4e89f0d7057b5081342',
        'crypto_pwhash');

SELECT ok(crypto_pwhash_str_verify(crypto_pwhash_str('Correct Horse Battery Staple'),
          'Correct Horse Battery Staple'),
          'crypto_pwhash_str_verify');

C API Documentation

Key Derivation

Multiple secret subkeys can be derived from a single primary key. Given the primary key and a key identifier, a subkey can be deterministically computed. However, given a subkey, an attacker cannot compute the primary key nor any other subkeys.

SELECT crypto_kdf_keygen() kdfkey \gset
SELECT length(crypto_kdf_derive_from_key(64, 1, '__auth__', :'kdfkey')) kdfsubkeylen \gset
SELECT is(:kdfsubkeylen, 64, 'kdf byte derived subkey');

SELECT length(crypto_kdf_derive_from_key(32, 1, '__auth__', :'kdfkey')) kdfsubkeylen \gset
SELECT is(:kdfsubkeylen, 32, 'kdf 32 byte derived subkey');

SELECT is(crypto_kdf_derive_from_key(32, 2, '__auth__', :'kdfkey'),
    crypto_kdf_derive_from_key(32, 2, '__auth__', :'kdfkey'), 'kdf subkeys are deterministic.');

C API Documentation

Key Exchange

Using the key exchange API, two parties can securely compute a set of shared keys using their peer's public key and their own secret key.

SELECT crypto_kx_new_seed() kxseed \gset

SELECT public, secret FROM crypto_kx_seed_new_keypair(:'kxseed') \gset seed_bob_
SELECT public, secret FROM crypto_kx_seed_new_keypair(:'kxseed') \gset seed_alice_

SELECT tx, rx FROM crypto_kx_client_session_keys(
    :'seed_bob_public', :'seed_bob_secret',
    :'seed_alice_public') \gset session_bob_

SELECT tx, rx FROM crypto_kx_server_session_keys(
    :'seed_alice_public', :'seed_alice_secret',
    :'seed_bob_public') \gset session_alice_

SELECT crypto_secretbox('hello alice', :'secretboxnonce', :'session_bob_tx') bob_to_alice \gset

SELECT is(crypto_secretbox_open(:'bob_to_alice', :'secretboxnonce', :'session_alice_rx'),
          'hello alice', 'secretbox_open session key');

SELECT crypto_secretbox('hello bob', :'secretboxnonce', :'session_alice_tx') alice_to_bob \gset

SELECT is(crypto_secretbox_open(:'alice_to_bob', :'secretboxnonce', :'session_bob_rx'),
          'hello bob', 'secretbox_open session key');

C API Documentation

HMAC512/256

[https://en.wikipedia.org/wiki/HMAC]

In cryptography, an HMAC (sometimes expanded as either keyed-hash message authentication code or hash-based message authentication code) is a specific type of message authentication code (MAC) involving a cryptographic hash function and a secret cryptographic key. As with any MAC, it may be used to simultaneously verify both the data integrity and authenticity of a message.

select crypto_auth_hmacsha512_keygen() hmac512key \gset
select crypto_auth_hmacsha512('food', :'hmac512key') hmac512 \gset

select is(crypto_auth_hmacsha512_verify(:'hmac512', 'food', :'hmac512key'), true, 'hmac512 verified');
select is(crypto_auth_hmacsha512_verify(:'hmac512', 'fo0d', :'hmac512key'), false, 'hmac512 not verified');

C API Documentation

Advanced Stream API (XChaCha20)

The stream API is for advanced users only and only provide low level encryption without authentication.

C API Documentation

XChaCha20-SIV

Deterministic/nonce-reuse resistant authenticated encryption scheme using XChaCha20.

C API Documentation

SignCryption

Traditional authenticated encryption with a shared key allows two or more parties to decrypt a ciphertext and verify that it was created by a member of the group knowing that secret key.

However, it doesn't allow verification of who in a group originally created a message.

In order to do so, authenticated encryption has to be combined with signatures.

The Toorani-Beheshti signcryption scheme achieves this using a single key pair per device, with forward security and public verifiability.

C API Documentation

Comments
  • Wrong memory space allocated

    Wrong memory space allocated

    Hi,

    I have a memory warning when using PostgreSQL 15.1 compiled with --enabled-cassert (enables MEMORY_CONTEXT_CHECKING) and current HEAD of pgsodium. See:

    nacl=# SELECT convert_from(
        pgsodium.crypto_aead_det_decrypt(
            decode('TA3aB8kpo4tZFbonlD6UPS3WeOMD6QxiAMDfWZ0bu+nkMtZQ', 'base64'), 
            '', 
            'acf73c9c-a1f3-473b-ae3a-9daff68f05fa'::uuid, 
            NULL::bytea), 
        'utf8'::name
    );
    WARNING:  problem in alloc set ExprContext: detected write past chunk end in block 0x20b29c0, chunk 0x20b2a10
    WARNING:  problem in alloc set ExprContext: detected write past chunk end in block 0x20b29c0, chunk 0x20b2a10
     convert_from 
    --------------
     blah
    (1 row)
    

    I might be wrong, but I suspect some missing header size or something related when allocating space in pgsodium_crypto_aead_det_encrypt_by_id and other equivalent funcs. Which means more data are written to in the allocated space than asked when the data AND its header are written there.

    By the way, note that if I give NULL as additional data, it just crash:

    nacl=# SELECT convert_from(
        pgsodium.crypto_aead_det_decrypt(
            decode('TA3aB8kpo4tZFbonlD6UPS3WeOMD6QxiAMDfWZ0bu+nkMtZQ', 'base64'), 
            NULL, 
            'acf73c9c-a1f3-473b-ae3a-9daff68f05fa'::uuid, 
            NULL::bytea), 
        'utf8'::name
    );
    server closed the connection unexpectedly
    	This probably means the server terminated abnormally
    	before or while processing the request.
    The connection to the server was lost. Attempting reset: Failed.
    The connection to the server was lost. Attempting reset: Failed.
    

    The backtrace:

    (gdb) bt
    #0  0x0000000000b35976 in pg_detoast_datum (datum=0x0) at fmgr.c:1710
    #1  0x00007fd48dd8d0df in pgsodium_crypto_aead_det_decrypt_by_id (fcinfo=0x2090440) at src/aead.c:304
    [...]
    

    The source code being:

    pgsodium_crypto_aead_det_decrypt_by_id (PG_FUNCTION_ARGS)
    {
    	bytea      *ciphertext = PG_GETARG_BYTEA_P (0);
    	bytea      *additional = PG_GETARG_BYTEA_P (1); // <- 304
    

    Regards,

    opened by ioguix 5
  • Where my PGXN Release?

    Where my PGXN Release?

    Details here. Draft META.json for you (check the license; "ISC" is not known to PGXN):

    {
       "name": "pgsodium",
       "abstract": "Postgres extension for libsodium functions",
       "description": "pgsodium is a PostgreSQL extension that exposes modern libsodium based cryptographic functions to SQL.",
       "version": "1.0.0",
       "maintainer": [
          "Michel Pelletier <[email protected]>"
       ],
       "license": "postgresql",
       "provides": {
          "pgsodium": {
             "abstract": "Postgres extension for libsodium functions",
             "file": "src/pgsodium.h",
             "docfile": "README.md",
             "version": "1.0.0"
          }
       },
       "prereqs": {
          "runtime": {
             "requires": {
                "PostgreSQL": "10.0.0"
             }
          }
       },
       "resources": {
          "bugtracker": {
             "web": "https://github.com/michelp/pgsodium/issues/"
          },
          "repository": {
             "url": "git://github.com/michelp/pgsodium.git",
             "web": "https://github.com/michelp/pgsodium/",
             "type": "git"
          }
       },
       "generated_by": "David E. Wheeler",
       "meta-spec": {
          "version": "1.0.0",
          "url": "https://pgxn.org/meta/spec.txt"
       },
       "tags": [
          "sodium",
          "crypto",
          "cryptography",
          "encryption",
          "random",
          "asymmetric encryption",
          "public key"
       ]
    }
    
    opened by theory 4
  • Better NULL input checking?

    Better NULL input checking?

    Reported by @ioguix in #43

    By the way, note that if I give NULL as additional data, it just crash:

    pgsodium.crypto_aead_det_decrypt was incorrectly not labeled STRICT so it crashed on NULL input, I can fix that, or I'm thinking it makes more sense for all functions (in most cases) to throw errors on NULL input instead of returning NULL, which means removing STRICT and doing NULL checking as shown in this branch:

    https://github.com/michelp/pgsodium/compare/fix/better-null-checking?expand=1

    this is just one example of many that would need to be added. Thoughts?

    opened by michelp 3
  • Improve startup log messages

    Improve startup log messages

    When the getkey_script file is missing, previous startup error message was wrongly showing "Permission denied for %s". This shows a relevant error message with addition of some details and hints.

    The patch also improves other startup error/log messages so they repect the PostgreSQL log format if stderr is collected.

    opened by ioguix 3
  • Add support for a HashiCorp Vault getkey script

    Add support for a HashiCorp Vault getkey script

    Hi!

    Was reading some stuff about Supabase Vault and wondered how to use HashiCorp Vault for the key, so decided to take a crack at something basic, inspired by the other getkey scripts.

    Like the other getkey scripts, it hopes to find some environment variables, pertaining to Vault, and of course, uses env vars for the regular configuration.

    opened by apg 3
  • Port pgsodium to Windows

    Port pgsodium to Windows

    I have ported pgsodium to Windows. It need some minor changes in order to build. It needs plenty of clean-up in order to be in such a state that it can be upstreamed.

    Do these changes have a chance of being accepted? Or would you prefer to keep Windows-support out?

    opened by Godwottery 2
  • Error installing the pgsodium version 3.0.4

    Error installing the pgsodium version 3.0.4

    Error installing the pgsodium version 3.0.4 on PostgreSQL 14

    psql (14.5 (Debian 14.5-2.pgdg110+2))
    Type "help" for help.
    
    postgres=# CREATE EXTENSION pgsodium;
    ERROR:  relation "pgsodium.key" does not exist
    LINE 1: CREATE EXTENSION pgsodium;
    
    postgres=# select name, default_version as version, comment from pg_available_extensions where name like '%pgsodium%' order by 1;
       name   | version |                  comment                   
    ----------+---------+--------------------------------------------
     pgsodium | 3.0.4   | Postgres extension for libsodium functions
    (1 row)
    
    

    no problem installing the extension version 3.0.2

    postgres=# select * from pg_available_extension_versions where name = 'pgsodium' order by version desc;
       name   | version | installed | superuser | trusted | relocatable | schema | requires |                  comment                   
    ----------+---------+-----------+-----------+---------+-------------+--------+----------+--------------------------------------------
     pgsodium | 3.0.4   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
     pgsodium | 3.0.3   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
     pgsodium | 3.0.2   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
     pgsodium | 3.0.0   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
     pgsodium | 2.0.2   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
     pgsodium | 2.0.1   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
     pgsodium | 2.0.0   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
     pgsodium | 1.2.0   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
     pgsodium | 1.1.1   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
     pgsodium | 1.1.0   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
     pgsodium | 1.0.0   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
    (11 rows)
    
    postgres=# CREATE EXTENSION pgsodium WITH VERSION '3.0.3'; 
    ERROR:  relation "pgsodium.key" does not exist
    LINE 1: CREATE EXTENSION pgsodium WITH VERSION '3.0.3';
                         ^
    postgres=# CREATE EXTENSION pgsodium WITH VERSION '3.0.2'; 
    CREATE EXTENSION
    postgres=# \dx
                                   List of installed extensions
       Name   | Version |   Schema   |                       Description                       
    ----------+---------+------------+---------------------------------------------------------
     pgsodium | 3.0.2   | public     | Pgsodium is a modern cryptography library for Postgres.
     plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
    (2 rows)
    
    opened by vitabaks 2
  • Last Version on PGXN is 1.1

    Last Version on PGXN is 1.1

    Hi there. Would you consider releasing updates of pgsodium on PGXN? The latest release there is currently v1.1.1, and it looks like there have been a few releases since then.

    If you'd like to automate releasing it on PGXN and GitHub, here's how to do it with GitHub Actions.

    opened by theory 2
  • Postgres crash running tests on latest master

    Postgres crash running tests on latest master

    ... ok 32 - secretbox_open session key ok 33 - secretbox_open session key ok 34 - sha256 ok 35 - sha512 ok 36 - hmac512 verified ok 37 - hmac512 not verified ok 38 - hmac256 verified ok 39 - hmac256 not verified psql:test/test.sql:275: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:test/test.sql:275: fatal: connection to server was lost marc:pgsodium$

    Command: psql -d test -f test/test.sql (database is newly created for the tests)

    Postgres version: marc:pgsodium$ psql psql (12.3 (Debian 12.3-1.pgdg90+1))

    __ Marc

    opened by marcmunro 2
  • Fix for crypto_sign_init bug found by marc.

    Fix for crypto_sign_init bug found by marc.

    Michel, I found a bug in crypto_sign_init(). I had incorrectly set the VARSIZE attribute of the bytea result. This is now fixed. I noticed also that VARSIZE was not explicitly set in crypto_sign_final_create(). I assume that _pgsodium_zalloc_bytea() does this, but I prefer to see the explicit assignment inline. Feel free to edit as you see fit.

    __ Marc

    opened by marcmunro 1
  • Multipart

    Multipart

    Michel, This provides additional functions for multi-part message signing along with tests.

    I moved the create extension statements in test.sql into the transaction block. This means that after rollback, the extensions have been cleared out. It makes stand-alone testing a little easier.

    opened by marcmunro 1
  • TCE : Update with using Old Value

    TCE : Update with using Old Value

    Hello,

    Looks like table gets corrupted when I'm trying to update an encrypted column using this column. CREATE TABLE users ( id bigserial primary key, secret text, key_id uuid not null default 'e3496f2a-787f-45a0-9717-f648496179d1', nonce bytea default pgsodium.crypto_aead_det_noncegen() );

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

    insert into users( secret ) values ( '12345' );

    For now, everything is right Then update users set secret = secret || 'test' where id = 1;

    At this stage, my secret has been corrupted and is not accessible anymore... Do I miss anything ?

    Thanks

    opened by pafiti 0
  • Transparent Column Encryption with Empty string

    Transparent Column Encryption with Empty string

    Hello,

    I created a simple table for TCE testing. I got everything created automatically (function, trigger, decrypted view). However, when I insert empty string in the column to be encrypted, the decryption fails.

    CREATE TABLE users ( id bigserial primary key, secret text, key_id uuid not null default 'e3496f2a-787f-45a0-9717-f648496179d1', nonce bytea default pgsodium.crypto_aead_det_noncegen() );

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

    insert into users( secret ) values ( '12345' ); select * from decrypted_users; insert into users( secret ) values ( '' ); select * from decrypted_users;

    SQL Error [22000]: ERROR: pgsodium_crypto_aead_det_decrypt_by_id: invalid message Where: PL/pgSQL function pgsodium.crypto_aead_det_decrypt(bytea,bytea,uuid,bytea) line 12 at RETURN

    Regards,

    opened by pafiti 0
  • Update secret when updating associated data

    Update secret when updating associated data

    Any time associated data is updated,the secret must also be updated. Add triggers for associted columns that also re-encrypt secret with new association.

    opened by michelp 0
  • Make extension relocatable during installation

    Make extension relocatable during installation

    Hi,

    While studying pgsodium, I found that the extension schema location is using sometime the relocatable form @[email protected] and sometime the fixed one pgsodium. Sometime, both forms appear in the same function.

    If the extension is truly non relocatable, using @[email protected] is a useless pain. If it can be relocatable, I suppose we just have to fix it in the installation script and adjust tests to make sure it works as expected.

    I took the liberty to implement the relocatable form, just to start the discussion. If you want to run your tests with a non-default schema, just run: psql -f test/test.sql -v extschema=anotherschema.

    If the extension must not be relocatable, I can handle the cleanup as well.

    opened by ioguix 4
  • Avoid secret logging

    Avoid secret logging

    Hello

    Re https://github.com/michelp/pgsodium#avoid-secret-logging

    Setting log_statement to 'none' isn't enough to avoid secret logging.

    If, say, log_min_duration_statement is set to 0, the secret will be logged anyway.

    I don't know if there's a simple way to avoid completely secret logging (besides log_min_duration_statement, logging could be handled by an ad hoc extension).

    opened by yhuelf 1
Releases(v3.1.5)
  • v3.1.5(Dec 15, 2022)

    What's Changed

    • keep user privs on regenerated views. by @michelp in https://github.com/michelp/pgsodium/pull/58

    Full Changelog: https://github.com/michelp/pgsodium/compare/v3.1.4...v3.1.5

    Source code(tar.gz)
    Source code(zip)
  • v3.1.4(Dec 14, 2022)

    What's Changed

    • readd user_data column for bw compat with old dumps. by @michelp in https://github.com/michelp/pgsodium/pull/57

    Full Changelog: https://github.com/michelp/pgsodium/compare/v3.1.3...v3.1.4

    Source code(tar.gz)
    Source code(zip)
  • v3.1.3(Dec 13, 2022)

  • v3.1.2(Dec 13, 2022)

  • v3.1.1(Dec 13, 2022)

    What's Changed

    • refactor trigger generation so that there is one trigger per encrypte… by @michelp in https://github.com/michelp/pgsodium/pull/54

    Full Changelog: https://github.com/michelp/pgsodium/compare/v3.1.0...v3.1.1

    Source code(tar.gz)
    Source code(zip)
  • v3.1.0(Dec 1, 2022)

    What's Changed

    • Fix/bad varlena size by @michelp in https://github.com/michelp/pgsodium/pull/46
    • Improve startup log messages by @ioguix in https://github.com/michelp/pgsodium/pull/45
    • Fix valid_key view to filter out expired keys by @ioguix in https://github.com/michelp/pgsodium/pull/48
    • fix TCE trigger update regression and tests to cover it. by @michelp in https://github.com/michelp/pgsodium/pull/49
    • Fix/better null checking by @michelp in https://github.com/michelp/pgsodium/pull/50
    • tests for quoted table names. by @michelp in https://github.com/michelp/pgsodium/pull/51
    • bump version. by @michelp in https://github.com/michelp/pgsodium/pull/52

    New Contributors

    • @ioguix made their first contribution in https://github.com/michelp/pgsodium/pull/45

    Full Changelog: https://github.com/michelp/pgsodium/compare/v3.0.7...v3.1.0

    Source code(tar.gz)
    Source code(zip)
  • v3.0.7(Nov 22, 2022)

    What's Changed

    • Non-extension owners can label newly created tables, not just use them. by @michelp in https://github.com/michelp/pgsodium/pull/36
    • get keys by names and ids by @michelp in https://github.com/michelp/pgsodium/pull/38
    • fix: crash on macos 13.0.1 by @burmecia in https://github.com/michelp/pgsodium/pull/39
    • Feat/rebuild only session user objects by @michelp in https://github.com/michelp/pgsodium/pull/40

    New Contributors

    • @burmecia made their first contribution in https://github.com/michelp/pgsodium/pull/39

    Full Changelog: https://github.com/michelp/pgsodium/compare/v3.0.6...v3.0.7

    Source code(tar.gz)
    Source code(zip)
  • v3.0.5(Oct 18, 2022)

  • v3.0.4(Aug 17, 2022)

  • v3.0.2(Aug 8, 2022)

  • v3.0.0(Aug 5, 2022)

    This release is NOT b/w compatible with 2.0.2, and will only work with PostgreSQL version 14 or higher. If you wish to use pgsodium with earlier versions you must use a version in the 2.0.x line.

    This release contains many changes to the security of pgsodium functions, setting the search_path for plpgsql functions, and making the extension non-relocatable. The extension can be installed into any schema (including public) but it's recommended that you install it into a schema named pgsodium.

    This release provides no new libsodium related functionality, but instead provide a new Transparent Column Encryption function described in the documentation.

    Source code(tar.gz)
    Source code(zip)
  • 2.0.1(Jan 17, 2022)

  • v1.3.0(Jan 21, 2021)

  • 1.3.0-alpha(Dec 9, 2020)

  • 1.2.0(Jul 23, 2020)

    pgsodium is an encryption library for postgres that provides modern cryptographic functions based on the libsodium library.  This release brings many changes, cleanups and tests, and includes a lot of new functionality. There are a few breaking changes with 1.1.1, most noticeably, API access is revoked by default, and there are now three layered security roles with access to the API. 

    The least privileged role can only use secret key API functions by key id, it can never see or use raw bytea keys.  The next layer role can see and use keys and keypairs, but not make them, and the highest privilege role can do everything including make and derive keys and keypairs. 

    Many encryption patterns require only the minimal key id privilege and a column encryption example is provided in the documentation.

    1.2.0 also wraps the IETF ChaCha20-Poly1305 construction for Authenticated Encryption with Additional Data (AEAD) in libsodium as the crypto_aead_ietf API. pgsodium_derive() is deprecated and is not called derive_key().

    Several related fixes were made to the multipart public key signing, and many more tests and error checks have been added.

    Source code(tar.gz)
    Source code(zip)
  • v1.0.0(May 31, 2020)

    1.0.0 is the first post alpha release of pgsodium, including new detaching signing support by @marcmunro .

    If you're upgrading from 0.0.1 alpha, drop the extension first, then create 1.0.0.

    Source code(tar.gz)
    Source code(zip)
  • v1.0.0-alpha(May 30, 2020)

  • v0.0.1-alpha(Aug 16, 2019)

Owner
Michel Pelletier
Oh just writing some code.
Michel Pelletier
The PostgreSQL client API in modern C++

C++ client API to PostgreSQL {#mainpage} Dmitigr Pgfe (PostGres FrontEnd, hereinafter referred to as Pgfe) - is a C++ client API to PostgreSQL servers

Dmitry Igrishin 137 Dec 14, 2022
A PostgreSQL extension providing an async networking interface accessible via SQL using a background worker and curl.

pg_net is a PostgreSQL extension exposing a SQL interface for async networking with a focus on scalability and UX.

Supabase 49 Dec 14, 2022
A framework to monitor and improve the performance of PostgreSQL using Machine Learning methods.

pg_plan_inspector pg_plan_inspector is being developed as a framework to monitor and improve the performance of PostgreSQL using Machine Learning meth

suzuki hironobu 198 Dec 27, 2022
The official C++ client API for PostgreSQL.

libpqxx Welcome to libpqxx, the C++ API to the PostgreSQL database management system. Home page: http://pqxx.org/development/libpqxx/ Find libpqxx on

Jeroen Vermeulen 718 Jan 3, 2023
YugabyteDB is a high-performance, cloud-native distributed SQL database that aims to support all PostgreSQL features

YugabyteDB is a high-performance, cloud-native distributed SQL database that aims to support all PostgreSQL features. It is best to fit for cloud-native OLTP (i.e. real-time, business-critical) applications that need absolute data correctness and require at least one of the following: scalability, high tolerance to failures, or globally-distributed deployments.

yugabyte 7.4k Jan 7, 2023
Prometheus exporter for PostgreSQL

pgexporter pgexporter is a Prometheus exporter for PostgreSQL. pgexporter will connect to one or more PostgreSQL instances and let you monitor their o

null 19 Dec 22, 2022
PostgreSQL extension for pgexporter

pgexporter_ext pgexporter_ext is an extension for PostgreSQL to provide additional Prometheus metrics for pgexporter. Features Disk space metrics See

null 4 Apr 13, 2022
A friendly and lightweight C++ database library for MySQL, PostgreSQL, SQLite and ODBC.

QTL QTL is a C ++ library for accessing SQL databases and currently supports MySQL, SQLite, PostgreSQL and ODBC. QTL is a lightweight library that con

null 173 Dec 12, 2022
C++ client library for PostgreSQL

Welcome to taoPQ taoPQ is a lightweight C++ client library for accessing a PostgreSQL➚ database. It has no dependencies beyond libpq➚, the C applicati

The Art of C++ 232 Dec 22, 2022
Backup / restore solution for PostgreSQL

pgmoneta pgmoneta is a backup / restore solution for PostgreSQL. pgmoneta is named after the Roman Goddess of Memory. Features Full backup Restore Sym

null 41 Dec 22, 2022
recovery postgresql table data by update/delete/rollback/dropcolumn command

recovery postgresql table data by update/delete/rollback/dropcolumn command

RadonDB 6 Aug 4, 2022
pgagroal is a high-performance protocol-native connection pool for PostgreSQL.

pgagroal is a high-performance protocol-native connection pool for PostgreSQL.

Agroal 555 Dec 27, 2022
xxhash functions for PostgreSQL

pg_xxhash PostgreSQL ❤️ xxhash Tested with xxhash 0.8.1 and PostgreSQL 14.1 on Linux and macOS. Think twice before even considering to use it in any s

Igor Hatarist 6 Oct 27, 2022
Distributed PostgreSQL as an extension

What is Citus? Citus is a PostgreSQL extension that transforms Postgres into a distributed database—so you can achieve high performance at any scale.

Citus Data 7.7k Dec 30, 2022
High-performance time-series aggregation for PostgreSQL

PipelineDB has joined Confluent, read the blog post here. PipelineDB will not have new releases beyond 1.0.0, although critical bugs will still be fix

PipelineDB 2.5k Dec 26, 2022
Reliable PostgreSQL Backup & Restore

pgBackRest Reliable PostgreSQL Backup & Restore Introduction pgBackRest aims to be a reliable, easy-to-use backup and restore solution that can seamle

pgBackRest 1.5k Dec 31, 2022
upstream module that allows nginx to communicate directly with PostgreSQL database.

About ngx_postgres is an upstream module that allows nginx to communicate directly with PostgreSQL database. Configuration directives postgres_server

RekGRpth 1 Apr 29, 2022
Open Source Oracle Compatible PostgreSQL.

IvorySQL is advanced, fully featured, open source Oracle compatible PostgreSQL with a firm commitment to always remain 100% compatible and a Drop-in r

null 420 Dec 28, 2022
Modern, asynchronous, and wicked fast C++11 client for Redis

redox Modern, asynchronous, and wicked fast C++11 client for Redis [] (https://travis-ci.org/hmartiro/redox) Redox is a C++ interface to the Redis key

Hayk Martiros 380 Jan 7, 2023