merchant-0023.sql (3378B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025 Taler Systems SA 4 -- 5 -- TALER 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 -- TALER 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 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 -- @file merchant-0023.sql 18 -- @brief Create table to store MFA related information 19 -- @author Christian Grothoff 20 21 22 BEGIN; 23 24 -- Check patch versioning is in place. 25 SELECT _v.register_patch('merchant-0023', NULL, NULL); 26 27 SET search_path TO merchant; 28 29 30 -- See enum TALER_MERCHANT_MFA_Channel 31 CREATE TYPE tan_enum 32 AS ENUM ('sms', 'email', 'totp'); 33 34 -- See enum TALER_MERCHANT_MFA_CriticalOperation 35 CREATE TYPE op_enum 36 AS ENUM ('instance_provision', 'account_config', 'auth_config', 'instance_deletion', 'auth_token_creation'); 37 38 CREATE TABLE tan_challenges 39 (challenge_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 40 ,h_body BYTEA NOT NULL CHECK (LENGTH(h_body)=32) 41 ,salt BYTEA NOT NULL CHECK (LENGTH(salt)=16) 42 ,op op_enum NOT NULL 43 ,code TEXT NOT NULL 44 ,creation_date INT8 NOT NULL 45 ,expiration_date INT8 NOT NULL 46 ,retransmission_date INT8 NOT NULL DEFAULT 0 47 ,confirmation_date INT8 DEFAULT NULL 48 ,retry_counter INT4 NOT NULL 49 ,tan_channel tan_enum NOT NULL 50 ,required_address TEXT NOT NULL 51 ); 52 COMMENT ON TABLE tan_challenges 53 IS 'Stores multi-factor authorization (MFA) challenges'; 54 COMMENT ON COLUMN tan_challenges.challenge_id 55 IS 'Unique identifier for the challenge'; 56 COMMENT ON COLUMN tan_challenges.h_body 57 IS 'Salted hash of the body of the original request that triggered the challenge, to be replayed once the challenge is satisfied.'; 58 COMMENT ON COLUMN tan_challenges.salt 59 IS 'Salt used when hashing the original body.'; 60 COMMENT ON COLUMN tan_challenges.op 61 IS 'The protected operation to run after the challenge'; 62 COMMENT ON COLUMN tan_challenges.code 63 IS 'The pin code sent to the user and verified'; 64 COMMENT ON COLUMN tan_challenges.creation_date 65 IS 'Creation date of the code'; 66 COMMENT ON COLUMN tan_challenges.retransmission_date 67 IS 'When did we last transmit the challenge to the user'; 68 COMMENT ON COLUMN tan_challenges.expiration_date 69 IS 'When will the code expire'; 70 COMMENT ON COLUMN tan_challenges.confirmation_date 71 IS 'When was this challenge successfully verified, NULL if pending'; 72 COMMENT ON COLUMN tan_challenges.retry_counter 73 IS 'How many tries are left for this code; must be > 0'; 74 COMMENT ON COLUMN tan_challenges.tan_channel 75 IS 'TAN channel to use, if NULL use customer configured one'; 76 COMMENT ON COLUMN tan_challenges.required_address 77 IS 'Address to which the challenge will be sent'; 78 79 CREATE INDEX tan_challenges_expiration_index 80 ON tan_challenges (expiration_date); 81 COMMENT ON INDEX tan_challenges_expiration_index 82 IS 'for garbage collection'; 83 84 ALTER TABLE merchant_donau_instances 85 ADD CONSTRAINT merchant_donau_charity_unique 86 UNIQUE (donau_url, merchant_instance_serial, charity_id); 87 88 COMMIT;