Transaction validation in database with Python (INCOMPLETE)

txn.sql

CREATE EXTENSION IF NOT EXISTS plpythonu;

-- plpython is untrusted. Access needs to be controlled carefully

CREATE OR REPLACE FUNCTION py_test () RETURNS varchar AS $$
  return 'hi'
$$ LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION py_create_ed25519_keypair ()
  RETURNS varchar[]
AS $$
  import axolotl_curve25519 as curve
  import os
  import base58

  randm32 = os.urandom(32)
  private_key = curve.generatePrivateKey(randm32)
  public_key = curve.generatePublicKey(private_key)
  return [base58.b58encode(private_key), base58.b58encode(public_key)]
$$ LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION py_create_ed25519_signature (message varchar, private_key varchar)
  RETURNS varchar
AS $$
  import axolotl_curve25519 as curve
  import os
  import base58
  randm64 = os.urandom(64)
  signature = curve.calculateSignature(randm64, base58.b58decode(private_key), message)
  return base58.b58encode(signature)
$$ LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION py_verify_ed25519_signature (message varchar, signature varchar, public_key varchar)
  RETURNS boolean
AS $$
  import axolotl_curve25519 as curve
  import base58
  valid = (curve.verifySignature(base58.b58decode(public_key), message, base58.b58decode(signature)) == 0)
  return valid
$$ LANGUAGE plpythonu;

SELECT py_test();

CREATE DOMAIN pubkey AS TEXT -- only run this if domain not exists
CHECK (
  VALUE ~ '^genesis$'
  OR VALUE ~ '^[a-zA-Z0-9+/=]+$'
) NOT NULL;

CREATE TABLE txns (
  id BIGSERIAL PRIMARY KEY, --  why not UUID?

  sender pubkey,
  receiver pubkey,
  asset VARCHAR(4) NOT NULL, -- 
  amount BIGINT NOT NULL CHECK (amount > 0), -- what should be the upper limit across assets?
  nonce BIGINT NOT NULL CHECK (nonce > 0), -- unique for a given sender to avoid replay attacks
  previous BIGINT, -- why not next? or both? unique? null?

  signature text NOT NULL,

  role VARCHAR(20) NOT NULL, -- which app inserted this row
  ts TIMESTAMP NOT NULL,
  CHECK (sender != receiver),
  CHECK (nonce > 0),
  CHECK ((sender = 'genesis' and previous IS NULL) or (sender != 'genesis' and previous IS NOT NULL)), -- creating a row with sender = genesis, creates a new asset
  UNIQUE (sender, nonce), -- same sender can use each nonce value only once
  UNIQUE (previous) -- forces a DAG into a linked list, what about first txn?
);

CREATE UNIQUE INDEX unique_genesis_asset ON txns (asset) WHERE (sender = 'genesis'); -- unique on subset of table

CREATE OR REPLACE FUNCTION validate_txn() RETURNS trigger AS $validate_txn$
    DECLARE
      message TEXT;
    BEGIN
    -- signature is valid
      message := row_to_json(row(NEW.sender, NEW.receiver, NEW.asset, NEW.amount, NEW.nonce, NEW.previous));
      IF (NEW.sender != 'genesis') AND NOT py_verify_ed25519_signature(message, NEW.signature, NEW.sender) THEN
        RAISE EXCEPTION 'signature invalid';
      END IF;
    -- nonce is valid: handled by the unique constraint
    -- previous pointer is valid
    
    -- sender has the balance
    -- receiver balance should not integer overflow
    -- update account balances
    -- set timestamp
      NEW.ts := now();
      NEW.role := 'test';
    RETURN NEW;
    END;
$validate_txn$ LANGUAGE plpgsql;

CREATE TRIGGER validate_txn BEFORE INSERT ON txns
    FOR EACH ROW EXECUTE PROCEDURE validate_txn();

insert into txns (sender, receiver, asset, amount, ts, signature, nonce, previous, role) values
('genesis','ORuGayR4yJx8ALKQngXGq2Ny3SYpWdkG0OTu6eILvSY=','INDM',5, now(), 'Ysr6oV8XJ56XL8jB05VMh/jvU8kGzy/rnhsPWGaP9KMhfR0e9fPhUn6Kn7n7I7MB/Ic3tH8hO6fI5iJyAwmjCg==', 1, NULL, 'app');

insert into txns (sender, receiver, asset, amount, ts, signature, nonce, previous, role) values
('ORuGayR4yJx8ALKQngXGq2Ny3SYpWdkG0OTu6eILvSY=','AAAGayR4yJx8ALKQngXGq2Ny3SYpWdkG0OTu6eILvSZ=','INDM',3, now(), 'NR0cFL2pHwq4w0HGQbGZuIvUOS7/b2lVMIlOB6ffHtRVFgzKP9/dzEy55Pzurs1LyqDP78ctl42x+9Wf1egLCQ=', 1, 0, 'app');

select py_create_ed25519_signature(row_to_json(row('ORuGayR4yJx8ALKQngXGq2Ny3SYpWdkG0OTu6eILvSY=','AAAGayR4yJx8ALKQngXGq2Ny3SYpWdkG0OTu6eILvSZ=','INDM',3,1,0))::text,'+PMFIwhZoZJduhnwg30Z2XEVE/OzDVxlycmZ//JG538=');

select * from txns;

delete from txns;

drop table txns;