pgsodium

pig build pkg pgsodium         # build RPM / DEB packages

pig repo add pgdg -u          # Add PGDG repo and update cache

shared_preload_libraries = 'pgsodium';

CREATE EXTENSION pgsodium;

CREATE EXTENSION pgsodium;

SELECT pgsodium.randombytes_random();
SELECT pgsodium.randombytes_buf(16);         -- 16 random bytes
SELECT pgsodium.randombytes_uniform(100);    -- random int 0-99

SELECT * FROM pgsodium.crypto_secretbox_keygen();
SELECT pgsodium.crypto_secretbox('message', nonce, key);
SELECT pgsodium.crypto_secretbox_open(ciphertext, nonce, key);

SELECT * FROM pgsodium.crypto_box_new_keypair();
SELECT pgsodium.crypto_box('message', nonce, public_key, secret_key);
SELECT pgsodium.crypto_box_open(ciphertext, nonce, public_key, secret_key);

SELECT * FROM pgsodium.crypto_sign_new_keypair();
SELECT pgsodium.crypto_sign('message', secret_key);
SELECT pgsodium.crypto_sign_open(signed_message, public_key);

SELECT pgsodium.crypto_pwhash_str('my_password');
SELECT pgsodium.crypto_pwhash_str_verify(hash, 'my_password');

SELECT pgsodium.crypto_generichash('data');
SELECT pgsodium.crypto_shorthash('data', key);

SELECT * FROM pgsodium.create_key();
-- Returns a UUID key id for use with TCE or encryption functions

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