pgsodium

pig build pkg pgsodium;		# build rpm/deb

pig repo add pgsql -u   # add both repo and update cache

pig install pgsodium;		# install via package name, for the active PG version

pig install pgsodium -v 18;   # install for PG 18
pig install pgsodium -v 17;   # install for PG 17
pig install pgsodium -v 16;   # install for PG 16
pig install pgsodium -v 15;   # install for PG 15
pig install pgsodium -v 14;   # install for PG 14

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