Key store with triggers

key_manager.sql

-- ---------------------------------------
-- Preparation
-- ---------------------------------------
CREATE SCHEMA pgcrypto;
CREATE EXTENSION pgcrypto WITH SCHEMA pgcrypto;

CREATE USER key_manager;
CREATE SCHEMA key_manager;
ALTER SCHEMA key_manager OWNER TO key_manager;
ALTER ROLE key_manager SET search_path TO key_manager;

GRANT USAGE ON SCHEMA pgcrypto TO key_manager;
GRANT EXECUTE ON FUNCTION pgcrypto.gen_random_bytes(integer) TO key_manager;

-- ---------------------------------------
-- Run the following as 'key_manager' user
-- ---------------------------------------

--
-- Create user-defined type for symmetric keys. This is a
-- stand-in for a key provided by a key vault or similar
-- application.
--
CREATE TYPE key_manager.skey AS (
  key_id int4,
  key    bytea,
  type   text
);

--
-- Create local keystore table. In practice it is common for
-- the keys in the local keystore to be encrypted with a
-- 'key-encrypting key (KEK)' that is only kept in memory.
-- Skip that for now...
--
CREATE SEQUENCE IF NOT EXISTS key_manager.keystore_id_seq;

CREATE TABLE IF NOT EXISTS key_manager.keystore1 (
   key_id      int4 PRIMARY KEY,
   key         key_manager.skey,
   type        text,
   creation_ts timestamp not null,
   deletion_ts timestamp
);

--
-- Generate a new symmetric key. This is a stand-in for a call to an
-- external key vault or similar application.
--
CREATE OR REPLACE FUNCTION key_manager.gen_symmetric_key() RETURNS int4 AS $$
  INSERT INTO key_manager.keystore(key) values (
    ROW(nextval('key_manager.keystore_id_seq'), pgcrypto.gen_random_bytes(16), 'aes-cbc/pad:pkcs'::text)::key_manager.skey) RETURNING key_id;
$$ LANGUAGE SQL
SECURITY DEFINER
PARALLEL SAFE;

--
-- Stored procedure called when inserting a new key in the keystore table.
-- It demonstrates how information about the key can be extracted and cached
-- in additional columns for efficiency.
--
CREATE OR REPLACE FUNCTION key_manager.keystore_on_insert() RETURNS trigger AS $$
BEGIN
  IF NEW.key IS NOT NULL THEN
    NEW.key_id = (NEW.key).key_id;
    NEW.type = (NEW.key).type;
    NEW.creation_ts := now();
    NEW.deletion_ts := null;
  ELSE
    RAISE EXCEPTION 'no key provided';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql
PARALLEL SAFE;

--
-- Don't allow any updates
--
CREATE OR REPLACE FUNCTION key_manager.keystore_on_update() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'UPDATE is not permitted on this table';
END;
$$ LANGUAGE plpgsql
PARALLEL SAFE;

--
-- Deletion is not permitted in order to avoid problems with broken
-- referential integrity constraints. Instead the entry is re-inserted
-- but with a null value for the 'key' and 'type'. In addition the
-- 'deletion_ts' field is et.
--
CREATE OR REPLACE FUNCTION key_manager.keystore_on_delete() RETURNS trigger AS $$
BEGIN
  IF OLD.key IS NOT NULL THEN
    INSERT INTO keystore(key_id, key, type, creation_ts, deletion_ts) values (OLD.key_id, null, null, OLD.creation_ts, now());
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql
PARALLEL SAFE;

--
-- Don't allow truncation
--
CREATE OR REPLACE FUNCTION key_manager.keystore_on_truncation() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'TRUNCATE is not permitted on this table';
END;
$$ LANGUAGE plpgsql
PARALLEL SAFE;

--
-- Create the trigger on insert
--
CREATE TRIGGER keystore_insert_trigger
  BEFORE INSERT ON key_manager.keystore
  FOR EACH ROW
  EXECUTE PROCEDURE key_manager.keystore_on_insert();

CREATE TRIGGER keystore_update_trigger
  BEFORE UPDATE ON key_manager.keystore
  FOR EACH ROW
  EXECUTE PROCEDURE key_manager.keystore_on_update();

CREATE TRIGGER keystore_delete_trigger
  AFTER DELETE ON key_manager.keystore
  FOR EACH ROW
  WHEN (OLD.key IS NOT NULL)
  EXECUTE PROCEDURE key_manager.keystore_on_delete();

CREATE TRIGGER keystore_truncate_trigger
  BEFORE TRUNCATE ON key_manager.keystore
  EXECUTE PROCEDURE key_manager.keystore_on_truncate();


-- REVOKE ALL ON SCHEMA key_manager FROM PUBLIC;

---
--with s as (select * from key_manager.keys limit 1)
--insert into key_manager.keys(key_id, key) select 10, s.key from s;
--ERROR:  duplicate key value violates unique constraint "keys_pkey"
--DETAIL:  Key (key_id)=(4) already exists.