whSxq844

1CREATE EXTENSION IF NOT EXISTS plpythonu;
2
3-- plpython is untrusted. Access needs to be controlled carefully
4
5CREATE OR REPLACE FUNCTION py_test () RETURNS varchar AS $$
6  return 'hi'
7$$ LANGUAGE plpythonu;
8
9CREATE OR REPLACE FUNCTION py_create_ed25519_keypair ()
10  RETURNS varchar[]
11AS $$
12  import axolotl_curve25519 as curve
13  import os
14  import base58
15
16  randm32 = os.urandom(32)
17  private_key = curve.generatePrivateKey(randm32)
18  public_key = curve.generatePublicKey(private_key)
19  return [base58.b58encode(private_key), base58.b58encode(public_key)]
20$$ LANGUAGE plpythonu;
21
22CREATE OR REPLACE FUNCTION py_create_ed25519_signature (message varchar, private_key varchar)
23  RETURNS varchar
24AS $$
25  import axolotl_curve25519 as curve
26  import os
27  import base58
28  randm64 = os.urandom(64)
29  signature = curve.calculateSignature(randm64, base58.b58decode(private_key), message)
30  return base58.b58encode(signature)
31$$ LANGUAGE plpythonu;
32
33CREATE OR REPLACE FUNCTION py_verify_ed25519_signature (message varchar, signature varchar, public_key varchar)
34  RETURNS boolean
35AS $$
36  import axolotl_curve25519 as curve
37  import base58
38  valid = (curve.verifySignature(base58.b58decode(public_key), message, base58.b58decode(signature)) == 0)
39  return valid
40$$ LANGUAGE plpythonu;
41
42SELECT py_test();
43
44CREATE DOMAIN pubkey AS TEXT -- only run this if domain not exists
45CHECK (
46  VALUE ~ '^genesis$'
47  OR VALUE ~ '^[a-zA-Z0-9+/=]+$'
48) NOT NULL;
49
50CREATE TABLE txns (
51  id BIGSERIAL PRIMARY KEY, --  why not UUID?
52
53  sender pubkey,
54  receiver pubkey,
55  asset VARCHAR(4) NOT NULL, -- 
56  amount BIGINT NOT NULL CHECK (amount > 0), -- what should be the upper limit across assets?
57  nonce BIGINT NOT NULL CHECK (nonce > 0), -- unique for a given sender to avoid replay attacks
58  previous BIGINT, -- why not next? or both? unique? null?
59
60  signature text NOT NULL,
61
62  role VARCHAR(20) NOT NULL, -- which app inserted this row
63  ts TIMESTAMP NOT NULL,
64  CHECK (sender != receiver),
65  CHECK (nonce > 0),
66  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
67  UNIQUE (sender, nonce), -- same sender can use each nonce value only once
68  UNIQUE (previous) -- forces a DAG into a linked list, what about first txn?
69);
70
71CREATE UNIQUE INDEX unique_genesis_asset ON txns (asset) WHERE (sender = 'genesis'); -- unique on subset of table
72
73CREATE OR REPLACE FUNCTION validate_txn() RETURNS trigger AS $validate_txn$
74    DECLARE
75      message TEXT;
76    BEGIN
77    -- signature is valid
78      message := row_to_json(row(NEW.sender, NEW.receiver, NEW.asset, NEW.amount, NEW.nonce, NEW.previous));
79      IF (NEW.sender != 'genesis') AND NOT py_verify_ed25519_signature(message, NEW.signature, NEW.sender) THEN
80        RAISE EXCEPTION 'signature invalid';
81      END IF;
82    -- nonce is valid: handled by the unique constraint
83    -- previous pointer is valid
84    
85    -- sender has the balance
86    -- receiver balance should not integer overflow
87    -- update account balances
88    -- set timestamp
89      NEW.ts := now();
90      NEW.role := 'test';
91    RETURN NEW;
92    END;
93$validate_txn$ LANGUAGE plpgsql;
94
95CREATE TRIGGER validate_txn BEFORE INSERT ON txns
96    FOR EACH ROW EXECUTE PROCEDURE validate_txn();
97
98insert into txns (sender, receiver, asset, amount, ts, signature, nonce, previous, role) values
99('genesis','ORuGayR4yJx8ALKQngXGq2Ny3SYpWdkG0OTu6eILvSY=','INDM',5, now(), 'Ysr6oV8XJ56XL8jB05VMh/jvU8kGzy/rnhsPWGaP9KMhfR0e9fPhUn6Kn7n7I7MB/Ic3tH8hO6fI5iJyAwmjCg==', 1, NULL, 'app');
100
101insert into txns (sender, receiver, asset, amount, ts, signature, nonce, previous, role) values
102('ORuGayR4yJx8ALKQngXGq2Ny3SYpWdkG0OTu6eILvSY=','AAAGayR4yJx8ALKQngXGq2Ny3SYpWdkG0OTu6eILvSZ=','INDM',3, now(), 'NR0cFL2pHwq4w0HGQbGZuIvUOS7/b2lVMIlOB6ffHtRVFgzKP9/dzEy55Pzurs1LyqDP78ctl42x+9Wf1egLCQ=', 1, 0, 'app');
103
104select py_create_ed25519_signature(row_to_json(row('ORuGayR4yJx8ALKQngXGq2Ny3SYpWdkG0OTu6eILvSY=','AAAGayR4yJx8ALKQngXGq2Ny3SYpWdkG0OTu6eILvSZ=','INDM',3,1,0))::text,'+PMFIwhZoZJduhnwg30Z2XEVE/OzDVxlycmZ//JG538=');
105
106select * from txns;
107
108delete from txns;
109
110drop table txns;