encrypted_column.sql

CREATE SCHEMA IF NOT EXISTS pgsodium;
CREATE EXTENSION IF NOT EXISTS pgsodium WITH SCHEMA pgsodium;

-- This is a demonstration user to show that the pgsodium_keyiduser
-- role can be used to access only encryption functions by key_id,
-- this role can never access raw encryption keys.
	
CREATE ROLE auser;
GRANT pgsodium_keyiduser TO auser;
GRANT USAGE ON SCHEMA pgsodium TO auser;

SET ROLE auser;

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

CREATE OR REPLACE 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();

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;
$$;
\echo
\echo Try inserting some data in test_view like:
\echo "    postgres=> insert into test_view (data) values ('this is one'), ('this is two');"
\echo Type RESET ROLE; to get back to previous user

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