frosix-0001.sql (5719B)
1 -- 2 -- This file is part of Frosix 3 -- Copyright (C) 2022, 2023 Joel Urech 4 -- 5 -- Frosix is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU Affero General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- Frosix is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU Affero General Public License along with 14 -- Frosix; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 -- Everything in one big transaction 18 BEGIN; 19 20 -- Check patch versioning is in place. 21 SELECT _v.register_patch('frosix-0001', NULL, NULL); 22 23 CREATE SCHEMA frosix; 24 COMMENT ON SCHEMA frosix IS 'frosix backend data'; 25 26 SET search_path TO frosix; 27 28 29 CREATE TABLE IF NOT EXISTS frosix_key 30 (key_id BYTEA PRIMARY KEY CHECK(LENGTH(key_id)=64), -- hash of encryption key 31 identifier INT NOT NULL, 32 expiration INT NOT NULL, 33 encryption_nonce BYTEA CHECK(LENGTH(encryption_nonce)=24), 34 enc_key_data BYTEA CHECK(LENGTH(enc_key_data)=80), -- (secret share, public_key + MAC) 35 challenge_data BYTEA CHECK(LENGTH(challenge_data)=64)); -- salted_hash H(auth_data, auth_nonce) auth_data=answer, phone number, etc. 36 37 COMMENT ON TABLE frosix_key 38 IS 'Saves all needed data to participate in a signing process'; 39 COMMENT ON COLUMN frosix_key.key_id 40 IS 'The key id identifies this key record. It is the hash of the key which was used to encrypt the data in this record'; 41 COMMENT ON COLUMN frosix_key.identifier 42 IS 'Identifier in the signing group'; 43 COMMENT ON COLUMN frosix_key.expiration 44 IS 'At which date the key record expire'; 45 COMMENT ON COLUMN frosix_key.encryption_nonce 46 IS 'Nonce used in encryption and decryption of the key data'; 47 COMMENT ON COLUMN frosix_key.enc_key_data 48 IS 'Stores the encrypted secret key share and the corresponding public key which is used in the signing process'; 49 COMMENT ON COLUMN frosix_key.challenge_data 50 IS 'Stores the salted hash of the challenge data which are used to authenticate a signing request.'; 51 52 53 CREATE TABLE IF NOT EXISTS frosix_public_commitments 54 (dkg_id BYTEA PRIMARY KEY CHECK(LENGTH(dkg_id)=64), -- h(context_string || public provider salt) 55 time_stamp TIMESTAMP NOT NULL, 56 commitments BYTEA NOT NULL); 57 58 COMMENT ON TABLE frosix_public_commitments 59 IS 'Saves commitments used in distributed key generation'; 60 COMMENT ON COLUMN frosix_public_commitments.dkg_id 61 IS 'ID of the corresponding dkg'; 62 COMMENT ON COLUMN frosix_public_commitments.time_stamp 63 IS 'Timestamp from the creation of this record. Used for garbage collection'; 64 COMMENT ON COLUMN frosix_public_commitments.commitments 65 IS 'The stored commitments from all other participants'; 66 67 68 CREATE INDEX IF NOT EXISTS frosix_public_commitments_expiration_index 69 ON frosix_public_commitments 70 (dkg_id,time_stamp); 71 72 COMMENT ON INDEX frosix_public_commitments_expiration_index 73 IS 'for dkg commitments garbage collection'; 74 75 76 CREATE TABLE IF NOT EXISTS frosix_challengecode 77 (challenge_id BYTEA CHECK(LENGTH(challenge_id)=64) NOT NULL, 78 code INT8 NOT NULL, 79 creation_date INT8 NOT NULL, 80 expiration_date INT8 NOT NULL, 81 retransmission_date INT8 NOT NULL DEFAULT 0, 82 retry_counter INT4 NOT NULL, 83 satisfied BOOLEAN NOT NULL DEFAULT FALSE); 84 85 COMMENT ON TABLE frosix_challengecode 86 IS 'Stores a code which is checked for the authentication by SMS, E-Mail..'; 87 COMMENT ON COLUMN frosix_challengecode.challenge_id 88 IS 'ID of the challenge, is unique for each message and key pair'; 89 COMMENT ON COLUMN frosix_challengecode.code 90 IS 'The pin code which is sent to the user and verified'; 91 COMMENT ON COLUMN frosix_challengecode.creation_date 92 IS 'Creation date of the code'; 93 COMMENT ON COLUMN frosix_challengecode.retransmission_date 94 IS 'When did we last transmit the challenge to the user'; 95 COMMENT ON COLUMN frosix_challengecode.expiration_date 96 IS 'When will the code expire'; 97 COMMENT ON COLUMN frosix_challengecode.retry_counter 98 IS 'How many tries are left for this code must be > 0'; 99 COMMENT ON COLUMN frosix_challengecode.satisfied 100 IS 'Has this challenge been satisfied by the user, used if it is not enough for the user to know the code (like for video identification or SEPA authentication). For SMS/E-mail/Post verification, this field being FALSE does not imply that the user did not meet the challenge.'; 101 102 103 CREATE INDEX IF NOT EXISTS frosix_challengecode_expiration_index 104 ON frosix_challengecode 105 (challenge_id,expiration_date); 106 107 COMMENT ON INDEX frosix_challengecode_expiration_index 108 IS 'for challenge garbage collection'; 109 110 111 CREATE TABLE IF NOT EXISTS frosix_seed 112 (seed_id BYTEA PRIMARY KEY CHECK(LENGTH(seed_id)=64) NOT NULL, --H(H(enc_key), i, D, E) 113 seed BYTEA CHECK(LENGTH(seed)=32) NOT NULL, 114 time_stamp TIMESTAMP NOT NULL); 115 116 COMMENT ON TABLE frosix_seed 117 IS 'Stores the random seed to generate the commitment in Frosix Sign between round 1 and 2'; 118 COMMENT ON COLUMN frosix_seed.seed_id 119 IS 'Identifier of the stored seed, instantiated as hash over the resulting commitment and the encryption key'; 120 COMMENT ON COLUMN frosix_seed.seed 121 IS 'This seed must never be used for two signatures! Delete immediately after use!'; 122 COMMENT ON COLUMN frosix_seed.time_stamp 123 IS 'Timestamp of creation, used for garbage collection'; 124 125 CREATE INDEX IF NOT EXISTS frosix_seed_expiration_index 126 ON frosix_seed 127 (seed_id,time_stamp); 128 129 COMMENT ON INDEX frosix_seed_expiration_index 130 IS 'For seed garbage collection'; 131 132 -- Complete transaction 133 COMMIT;