libeufin-bank-0002.sql (3737B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2023 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 BEGIN; 17 18 SELECT _v.register_patch('libeufin-bank-0002', NULL, NULL); 19 SET search_path TO libeufin_bank; 20 21 -- Forget about all pending operations 22 DELETE FROM cashout_operations WHERE local_transaction IS NULL; 23 24 -- Remove challenge logic from cashout tables 25 ALTER TABLE cashout_operations 26 DROP COLUMN challenge, 27 DROP COLUMN tan_channel, 28 DROP COLUMN tan_info, 29 DROP COLUMN aborted, 30 ALTER COLUMN local_transaction SET NOT NULL; 31 32 DROP TABLE challenges; 33 34 ALTER TABLE customers 35 ADD tan_channel tan_enum NULL; 36 37 CREATE TYPE op_enum 38 AS ENUM ('account_reconfig', 'account_auth_reconfig', 'account_delete', 'bank_transaction', 'cashout', 'withdrawal'); 39 40 CREATE TABLE tan_challenges 41 (challenge_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 42 ,body TEXT NOT NULL 43 ,op op_enum NOT NULL 44 ,code TEXT NOT NULL 45 ,creation_date INT8 NOT NULL 46 ,expiration_date INT8 NOT NULL 47 ,retransmission_date INT8 NOT NULL DEFAULT 0 48 ,confirmation_date INT8 DEFAULT NULL 49 ,retry_counter INT4 NOT NULL 50 ,customer INT8 NOT NULL 51 REFERENCES customers(customer_id) 52 ON DELETE CASCADE 53 ,tan_channel tan_enum NULL DEFAULT NULL 54 ,tan_info TEXT NULL DEFAULT NULL 55 ); 56 COMMENT ON TABLE tan_challenges IS 'Stores 2FA challenges'; 57 COMMENT ON COLUMN tan_challenges.op IS 'The protected operation to run after the challenge'; 58 COMMENT ON COLUMN tan_challenges.code IS 'The pin code sent to the user and verified'; 59 COMMENT ON COLUMN tan_challenges.creation_date IS 'Creation date of the code'; 60 COMMENT ON COLUMN tan_challenges.retransmission_date IS 'When did we last transmit the challenge to the user'; 61 COMMENT ON COLUMN tan_challenges.expiration_date IS 'When will the code expire'; 62 COMMENT ON COLUMN tan_challenges.confirmation_date IS 'When was this challenge successfully verified, NULL if pending'; 63 COMMENT ON COLUMN tan_challenges.retry_counter IS 'How many tries are left for this code must be > 0'; 64 COMMENT ON COLUMN tan_challenges.tan_channel IS 'TAN channel to use, if null use customer configured one'; 65 COMMENT ON COLUMN tan_challenges.tan_info IS 'TAN info to use, if null use customer configured one'; 66 67 CREATE INDEX tan_challenges_expiration_index 68 ON tan_challenges (expiration_date); 69 COMMENT ON INDEX tan_challenges_expiration_index 70 IS 'for garbage collection'; 71 72 CREATE INDEX bearer_tokens_expiration_index 73 ON bearer_tokens (expiration_time); 74 COMMENT ON INDEX bearer_tokens_expiration_index 75 IS 'for garbage collection'; 76 77 CREATE INDEX bank_account_transactions_expiration_index 78 ON bank_account_transactions (transaction_date); 79 COMMENT ON INDEX bank_account_transactions_expiration_index 80 IS 'for garbage collection'; 81 82 ALTER TABLE taler_withdrawal_operations 83 ADD creation_date INT8 NOT NULL DEFAULT (extract(epoch from now())*1000000)::int8; 84 ALTER TABLE taler_withdrawal_operations 85 ALTER creation_date DROP DEFAULT; 86 CREATE INDEX taler_withdrawal_operations_expiration_index 87 ON taler_withdrawal_operations (creation_date); 88 COMMENT ON INDEX taler_withdrawal_operations_expiration_index 89 IS 'for garbage collection'; 90 91 COMMIT;