stasis-0001.sql (10452B)
1 -- 2 -- This file is part of Anastasis 3 -- Copyright (C) 2020, 2021, 2022, 2023 Anastasis SARL SA 4 -- 5 -- ANASTASIS is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- ANASTASIS 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 General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- ANASTASIS; 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('stasis-0001', NULL, NULL); 22 23 CREATE SCHEMA anastasis; 24 COMMENT ON SCHEMA anastasis IS 'anastasis backend data'; 25 26 SET search_path TO anastasis; 27 28 29 CREATE TYPE taler_amount 30 AS 31 (val INT8 32 ,frac INT4 33 ); 34 COMMENT ON TYPE taler_amount 35 IS 'Stores an amount, fraction is in units of 1/100000000 of the base value'; 36 37 38 CREATE TABLE IF NOT EXISTS anastasis_truth_payment 39 (truth_uuid BYTEA PRIMARY KEY CHECK(LENGTH(truth_uuid)=32), 40 amount taler_amount NOT NULL, 41 expiration INT8 NOT NULL); 42 COMMENT ON TABLE anastasis_truth_payment 43 IS 'Records about payments for truth uploads'; 44 COMMENT ON COLUMN anastasis_truth_payment.truth_uuid 45 IS 'Identifier of the truth'; 46 COMMENT ON COLUMN anastasis_truth_payment.amount 47 IS 'Amount we were paid'; 48 COMMENT ON COLUMN anastasis_truth_payment.expiration 49 IS 'At which date will the truth payment expire'; 50 51 52 CREATE TABLE IF NOT EXISTS anastasis_truth 53 (truth_uuid BYTEA PRIMARY KEY CHECK(LENGTH(truth_uuid)=32), 54 key_share_data BYTEA CHECK(LENGTH(key_share_data)=72) NOT NULL, 55 method_name TEXT NOT NULL, 56 encrypted_truth BYTEA NOT NULL, 57 truth_mime TEXT NOT NULL, 58 expiration INT8 NOT NULL); 59 COMMENT ON TABLE anastasis_truth 60 IS 'Truth data is needed to authenticate clients during recovery'; 61 COMMENT ON COLUMN anastasis_truth.truth_uuid 62 IS 'The truth UUID uniquely identifies this truth record. Not a foreign key as we may offer storing truth for free.'; 63 COMMENT ON COLUMN anastasis_truth.key_share_data 64 IS 'Stores the encrypted key share used to recover the key (nonce, tag and keyshare)'; 65 COMMENT ON COLUMN anastasis_truth.method_name 66 IS 'Defines the authentication method (SMS, E-Mail, Question..)'; 67 COMMENT ON COLUMN anastasis_truth.encrypted_truth 68 IS 'Stores the encrypted authentication data'; 69 COMMENT ON COLUMN anastasis_truth.truth_mime 70 IS 'Defines the mime type of the stored authentcation data'; 71 COMMENT ON COLUMN anastasis_truth.expiration 72 IS 'At which date will the truth record expire'; 73 74 75 CREATE TABLE IF NOT EXISTS anastasis_user 76 (user_id BYTEA PRIMARY KEY CHECK(LENGTH(user_id)=32), 77 expiration_date INT8 NOT NULL); 78 COMMENT ON TABLE anastasis_user 79 IS 'Saves a user which is using Anastasis'; 80 COMMENT ON COLUMN anastasis_user.user_id 81 IS 'Identifier of the user account'; 82 COMMENT ON COLUMN anastasis_user.expiration_date 83 IS 'At which date will the user record expire'; 84 85 86 CREATE TABLE IF NOT EXISTS anastasis_recdoc_payment 87 (payment_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 88 user_id BYTEA NOT NULL REFERENCES anastasis_user(user_id), 89 post_counter INT4 NOT NULL DEFAULT 0 CHECK(post_counter >= 0), 90 amount taler_amount NOT NULL, 91 payment_identifier BYTEA NOT NULL CHECK(LENGTH(payment_identifier)=32), 92 creation_date INT8 NOT NULL, 93 paid BOOLEAN NOT NULL DEFAULT FALSE); 94 COMMENT ON TABLE anastasis_recdoc_payment 95 IS 'Records a payment for a recovery document'; 96 COMMENT ON COLUMN anastasis_recdoc_payment.payment_id 97 IS 'Serial number which identifies the payment'; 98 COMMENT ON COLUMN anastasis_recdoc_payment.user_id 99 IS 'Link to the corresponding user who paid'; 100 COMMENT ON COLUMN anastasis_recdoc_payment.post_counter 101 IS 'For how many posts does the user pay'; 102 COMMENT ON COLUMN anastasis_recdoc_payment.amount 103 IS 'Amount we were paid'; 104 COMMENT ON COLUMN anastasis_recdoc_payment.payment_identifier 105 IS 'Payment identifier which the user has to provide'; 106 COMMENT ON COLUMN anastasis_recdoc_payment.creation_date 107 IS 'Creation date of the payment'; 108 COMMENT ON COLUMN anastasis_recdoc_payment.paid 109 IS 'Is the payment finished'; 110 111 112 CREATE TABLE IF NOT EXISTS anastasis_challenge_payment 113 (payment_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 114 truth_uuid BYTEA CHECK(LENGTH(truth_uuid)=32) NOT NULL, 115 amount taler_amount NOT NULL, 116 payment_identifier BYTEA NOT NULL CHECK(LENGTH(payment_identifier)=32), 117 creation_date INT8 NOT NULL, 118 counter INT4 NOT NULL DEFAULT 3, 119 paid BOOLEAN NOT NULL DEFAULT FALSE, 120 refunded BOOLEAN NOT NULL DEFAULT FALSE 121 ); 122 COMMENT ON TABLE anastasis_recdoc_payment 123 IS 'Records a payment for a challenge'; 124 COMMENT ON COLUMN anastasis_challenge_payment.payment_id 125 IS 'Serial number which identifies the payment'; 126 COMMENT ON COLUMN anastasis_challenge_payment.truth_uuid 127 IS 'Link to the corresponding challenge which is paid'; 128 COMMENT ON COLUMN anastasis_challenge_payment.amount 129 IS 'Amount we were paid'; 130 COMMENT ON COLUMN anastasis_challenge_payment.payment_identifier 131 IS 'Payment identifier which the user has to provide'; 132 COMMENT ON COLUMN anastasis_challenge_payment.counter 133 IS 'How many more times will we issue the challenge for the given payment'; 134 COMMENT ON COLUMN anastasis_challenge_payment.creation_date 135 IS 'Creation date of the payment'; 136 COMMENT ON COLUMN anastasis_challenge_payment.paid 137 IS 'Is the payment finished'; 138 COMMENT ON COLUMN anastasis_challenge_payment.refunded 139 IS 'Was the payment refunded'; 140 141 142 CREATE TABLE IF NOT EXISTS anastasis_recoverydocument 143 (user_id BYTEA NOT NULL REFERENCES anastasis_user(user_id), 144 version INT4 NOT NULL, 145 account_sig BYTEA NOT NULL CHECK(LENGTH(account_sig)=64), 146 recovery_data_hash BYTEA NOT NULL CHECK(length(recovery_data_hash)=64), 147 recovery_data BYTEA NOT NULL, 148 recovery_meta_data BYTEA NOT NULL, 149 creation_date INT8 NOT NULL, 150 PRIMARY KEY (user_id, version)); 151 COMMENT ON TABLE anastasis_recoverydocument 152 IS 'Stores a recovery document which contains the policy and the encrypted core secret'; 153 COMMENT ON COLUMN anastasis_recoverydocument.user_id 154 IS 'Link to the owner of this recovery document'; 155 COMMENT ON COLUMN anastasis_recoverydocument.version 156 IS 'The version of this recovery document'; 157 COMMENT ON COLUMN anastasis_recoverydocument.account_sig 158 IS 'Signature of the recovery document'; 159 COMMENT ON COLUMN anastasis_recoverydocument.recovery_data_hash 160 IS 'Hash of the recovery document to prevent unnecessary uploads'; 161 COMMENT ON COLUMN anastasis_recoverydocument.creation_date 162 IS 'Creation date of the recovery document (when it was uploaded)'; 163 COMMENT ON COLUMN anastasis_recoverydocument.recovery_data 164 IS 'Contains the encrypted policy and core secret'; 165 COMMENT ON COLUMN anastasis_recoverydocument.recovery_meta_data 166 IS 'Contains an encrypted human-readable and sometimes user-generated description of the backup'; 167 168 169 CREATE TABLE IF NOT EXISTS anastasis_challengecode 170 (truth_uuid BYTEA CHECK(LENGTH(truth_uuid)=32) NOT NULL, 171 code INT8 NOT NULL, 172 creation_date INT8 NOT NULL, 173 expiration_date INT8 NOT NULL, 174 retransmission_date INT8 NOT NULL DEFAULT 0, 175 retry_counter INT4 NOT NULL, 176 satisfied BOOLEAN NOT NULL DEFAULT FALSE); 177 COMMENT ON TABLE anastasis_challengecode 178 IS 'Stores a code which is checked for the authentication by SMS, E-Mail..'; 179 COMMENT ON COLUMN anastasis_challengecode.truth_uuid 180 IS 'Link to the corresponding challenge which is solved'; 181 COMMENT ON COLUMN anastasis_challengecode.code 182 IS 'The pin code which is sent to the user and verified'; 183 COMMENT ON COLUMN anastasis_challengecode.creation_date 184 IS 'Creation date of the code'; 185 COMMENT ON COLUMN anastasis_challengecode.retransmission_date 186 IS 'When did we last transmit the challenge to the user'; 187 COMMENT ON COLUMN anastasis_challengecode.expiration_date 188 IS 'When will the code expire'; 189 COMMENT ON COLUMN anastasis_challengecode.retry_counter 190 IS 'How many tries are left for this code must be > 0'; 191 COMMENT ON COLUMN anastasis_challengecode.satisfied 192 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.'; 193 194 CREATE INDEX IF NOT EXISTS anastasis_challengecode_uuid_index 195 ON anastasis_challengecode 196 (truth_uuid,expiration_date); 197 COMMENT ON INDEX anastasis_challengecode_uuid_index 198 IS 'for challenge lookup'; 199 200 CREATE INDEX IF NOT EXISTS anastasis_challengecode_expiration_index 201 ON anastasis_challengecode 202 (truth_uuid,expiration_date); 203 COMMENT ON INDEX anastasis_challengecode_expiration_index 204 IS 'for challenge garbage collection'; 205 206 207 CREATE TABLE IF NOT EXISTS anastasis_auth_iban_in 208 (auth_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE 209 ,wire_reference INT8 NOT NULL PRIMARY KEY 210 ,wire_subject TEXT NOT NULL 211 ,credit taler_amount NOT NULL 212 ,debit_account_details TEXT NOT NULL 213 ,credit_account_details TEXT NOT NULL 214 ,execution_date INT8 NOT NULL 215 ); 216 COMMENT ON TABLE anastasis_auth_iban_in 217 IS 'list of IBAN wire transfers for authentication using the IBAN plugin'; 218 COMMENT ON COLUMN anastasis_auth_iban_in.wire_reference 219 IS 'Unique number identifying the wire transfer in LibEuFin/Nexus'; 220 COMMENT ON COLUMN anastasis_auth_iban_in.wire_subject 221 IS 'For authentication, this contains the code, but also additional text'; 222 COMMENT ON COLUMN anastasis_auth_iban_in.credit 223 IS 'Amount we were credited'; 224 COMMENT ON COLUMN anastasis_auth_iban_in.execution_date 225 IS 'Used both for (theoretical) garbage collection and to see if the transfer happened on time'; 226 COMMENT ON COLUMN anastasis_auth_iban_in.credit_account_details 227 IS 'Identifies the bank account of the Anastasis provider, which could change over time'; 228 COMMENT ON COLUMN anastasis_auth_iban_in.debit_account_details 229 IS 'Identifies the bank account of the customer, which must match what was given in the truth'; 230 231 CREATE INDEX IF NOT EXISTS anastasis_auth_iban_in_lookup_index 232 ON anastasis_auth_iban_in 233 (debit_account_details 234 ,execution_date 235 ); 236 237 -- Complete transaction 238 COMMIT;