diff options
-rw-r--r-- | bank/src/test/kotlin/DatabaseTest.kt | 100 | ||||
-rw-r--r-- | database-versioning/libeufin-bank-0002.sql | 50 | ||||
-rw-r--r-- | database-versioning/libeufin-bank-drop.sql | 1 | ||||
-rw-r--r-- | database-versioning/libeufin-bank-procedures.sql | 113 |
4 files changed, 264 insertions, 0 deletions
diff --git a/bank/src/test/kotlin/DatabaseTest.kt b/bank/src/test/kotlin/DatabaseTest.kt index 916e637a..803871ec 100644 --- a/bank/src/test/kotlin/DatabaseTest.kt +++ b/bank/src/test/kotlin/DatabaseTest.kt @@ -166,6 +166,106 @@ class DatabaseTest { } }} + @Test + fun tan_challenge() = bankSetup { db -> db.conn { conn -> + val createStmt = conn.prepareStatement("SELECT tan_challenge_create('',?,?,?,?,1,NULL,NULL)") + val markSentStmt = conn.prepareStatement("SELECT tan_challenge_mark_sent(?,?,?)") + val tryStmt = conn.prepareStatement("SELECT ok, no_retry FROM tan_challenge_try(?,?,?)") + val sendStmt = conn.prepareStatement("SELECT out_tan_code FROM tan_challenge_send(?,?,?,?,?)") + + val validityPeriod = Duration.ofHours(1) + val retransmissionPeriod: Duration = Duration.ofMinutes(1) + val retryCounter = 3 + + fun create(code: String, now: Instant): Long { + createStmt.setString(1, code) + createStmt.setLong(2, ChronoUnit.MICROS.between(Instant.EPOCH, now)) + createStmt.setLong(3, TimeUnit.MICROSECONDS.convert(validityPeriod)) + createStmt.setInt(4, retryCounter) + return createStmt.oneOrNull { it.getLong(1) }!! + } + + fun markSent(id: Long, now: Instant) { + markSentStmt.setLong(1, id) + markSentStmt.setLong(2, ChronoUnit.MICROS.between(Instant.EPOCH, now)) + markSentStmt.setLong(3, TimeUnit.MICROSECONDS.convert(retransmissionPeriod)) + return markSentStmt.oneOrNull { }!! + } + + fun cTry(id: Long, code: String, now: Instant): Pair<Boolean, Boolean> { + tryStmt.setLong(1, id) + tryStmt.setString(2, code) + tryStmt.setLong(3, ChronoUnit.MICROS.between(Instant.EPOCH, now)) + return tryStmt.oneOrNull { + Pair(it.getBoolean(1), it.getBoolean(2)) + }!! + } + + fun send(id: Long, code: String, now: Instant): String? { + sendStmt.setLong(1, id) + sendStmt.setString(2, code) + sendStmt.setLong(3, ChronoUnit.MICROS.between(Instant.EPOCH, now)) + sendStmt.setLong(4, TimeUnit.MICROSECONDS.convert(validityPeriod)) + sendStmt.setInt(5, retryCounter) + return sendStmt.oneOrNull { + it.getString(1) + } + } + + val now = Instant.now() + val expired = now + validityPeriod + val retransmit = now + retransmissionPeriod + + // Check basic + create("good-code", now).run { + // Bad code + assertEquals(Pair(false, false), cTry(this, "bad-code", now)) + // Good code + assertEquals(Pair(true, false), cTry(this, "good-code", now)) + // Never resend a confirmed challenge + assertNull(send(this, "new-code", expired)) + // Confirmed challenge always ok + assertEquals(Pair(true, false), cTry(this, "good-code", now)) + } + + // Check retry + create("good-code", now).run { + markSent(this, now) + // Bad code + repeat(retryCounter) { + assertEquals(Pair(false, false), cTry(this, "bad-code", now)) + } + // Good code fail + assertEquals(Pair(false, true), cTry(this, "good-code", now)) + // New code + assertEquals("new-code", send(this, "new-code", now)) + // Good code + assertEquals(Pair(true, false), cTry(this, "new-code", now)) + } + + // Check retransmission and expiration + create("good-code", now).run { + // Failed to send retransmit + assertEquals("good-code", send(this, "new-code", now)) + // Code successfully sent and still valid + markSent(this, now) + assertNull(send(this, "new-code", now)) + // Code is still valid but shoud be resent + assertEquals("good-code", send(this, "new-code", retransmit)) + // Good code fail because expired + assertEquals(Pair(false, false), cTry(this, "good-code", expired)) + // New code because expired + assertEquals("new-code", send(this, "new-code", expired)) + // Code successfully sent and still valid + markSent(this, expired) + assertNull(send(this, "another-code", expired)) + // Old code no longer workds + assertEquals(Pair(false, false), cTry(this, "good-code", expired)) + // New code works + assertEquals(Pair(true, false), cTry(this, "new-code", expired)) + } + }} + // Testing iban payto uri normalization @Test fun ibanPayto() = setup { _, _ -> diff --git a/database-versioning/libeufin-bank-0002.sql b/database-versioning/libeufin-bank-0002.sql new file mode 100644 index 00000000..54a48ef0 --- /dev/null +++ b/database-versioning/libeufin-bank-0002.sql @@ -0,0 +1,50 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2023 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> + +BEGIN; + +SELECT _v.register_patch('libeufin-bank-0002', NULL, NULL); +SET search_path TO libeufin_bank; + +-- TODO remove challenges table +-- TODO remove challenge and status columns from cashout_operations + +CREATE TABLE tan_challenges + (challenge_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,body TEXT NOT NULL + ,code TEXT NOT NULL + ,creation_date INT8 NOT NULL + ,expiration_date INT8 NOT NULL + ,retransmission_date INT8 NOT NULL DEFAULT 0 + ,confirmation_date INT8 DEFAULT NULL + ,retry_counter INT4 NOT NULL + ,customer INT8 NOT NULL + REFERENCES customers(customer_id) + ON DELETE CASCADE + ON UPDATE RESTRICT + ,tan_channel tan_enum NULL DEFAULT NULL + ,tan_info TEXT NULL DEFAULT NULL +); +COMMENT ON TABLE tan_challenges IS 'Stores 2FA challenges'; +COMMENT ON COLUMN tan_challenges.code IS 'The pin code sent to the user and verified'; +COMMENT ON COLUMN tan_challenges.creation_date IS 'Creation date of the code'; +COMMENT ON COLUMN tan_challenges.retransmission_date IS 'When did we last transmit the challenge to the user'; +COMMENT ON COLUMN tan_challenges.expiration_date IS 'When will the code expire'; +COMMENT ON COLUMN tan_challenges.confirmation_date IS 'When was this challenge successfully verified, NULL if pending'; +COMMENT ON COLUMN tan_challenges.retry_counter IS 'How many tries are left for this code must be > 0'; +COMMENT ON COLUMN tan_challenges.tan_channel IS 'TAN channel to use, if null use customer configured one'; +COMMENT ON COLUMN tan_challenges.tan_info IS 'TAN info to use, if null use customer configured one'; + +COMMIT;
\ No newline at end of file diff --git a/database-versioning/libeufin-bank-drop.sql b/database-versioning/libeufin-bank-drop.sql index 123481a1..3746c28d 100644 --- a/database-versioning/libeufin-bank-drop.sql +++ b/database-versioning/libeufin-bank-drop.sql @@ -4,6 +4,7 @@ BEGIN; -- legacy database schema too. That's acceptable as the -- legacy schema is being removed. SELECT _v.unregister_patch('libeufin-bank-0001'); +SELECT _v.unregister_patch('libeufin-bank-0002'); DROP SCHEMA libeufin_bank CASCADE; COMMIT; diff --git a/database-versioning/libeufin-bank-procedures.sql b/database-versioning/libeufin-bank-procedures.sql index 6714a656..c599be46 100644 --- a/database-versioning/libeufin-bank-procedures.sql +++ b/database-versioning/libeufin-bank-procedures.sql @@ -1304,6 +1304,119 @@ LANGUAGE sql as $$ $$; COMMENT ON FUNCTION challenge_try IS 'Try to confirm a challenge, return true if the challenge have been confirmed'; +CREATE FUNCTION tan_challenge_create ( + IN in_body TEXT, + IN in_code TEXT, + IN in_now_date INT8, + IN in_validity_period INT8, + IN in_retry_counter INT4, + IN in_customer_id INT8, + IN in_tan_channel tan_enum, + IN in_tan_info TEXT, + OUT out_challenge_id INT8 +) +LANGUAGE sql AS $$ + INSERT INTO tan_challenges ( + body, + code, + creation_date, + expiration_date, + retry_counter, + customer, + tan_channel, + tan_info + ) VALUES ( + in_body, + in_code, + in_now_date, + in_now_date + in_validity_period, + in_retry_counter, + in_customer_id, + in_tan_channel, + in_tan_info + ) RETURNING challenge_id +$$; +COMMENT ON FUNCTION tan_challenge_create IS 'Create a new challenge, return the generated id'; + +CREATE FUNCTION tan_challenge_send ( + IN in_challenge_id BIGINT, + IN in_code TEXT, -- New code to use if the old code expired + IN in_now_date INT8, + IN in_validity_period INT8, + IN in_retry_counter INT4, + OUT out_tan_code TEXT, -- TAN code to send, NULL if nothing should be sent + OUT out_tan_channel tan_enum, -- TAN channel to use, NULL if nothing should be sent + OUT out_tan_info TEXT -- TAN info to use, NULL if nothing should be sent +) +LANGUAGE plpgsql as $$ +DECLARE +account_id INT8; +expired BOOLEAN; +retransmit BOOLEAN; +BEGIN +-- Recover expiration date +SELECT + (in_now_date >= expiration_date OR retry_counter <= 0) AND confirmation_date IS NULL + ,in_now_date >= retransmission_date AND confirmation_date IS NULL + ,code, tan_channel, tan_info, customer +INTO expired, retransmit, out_tan_code, out_tan_channel, out_tan_info, account_id +FROM tan_challenges WHERE challenge_id = in_challenge_id; + +IF expired THEN + UPDATE tan_challenges SET + code = in_code + ,expiration_date = in_now_date + in_validity_period + ,retry_counter = in_retry_counter + WHERE challenge_id = in_challenge_id; + out_tan_code = in_code; +ELSIF NOT retransmit THEN + out_tan_code = NULL; +END IF; + +--IF out_tan_code IS NOT NULL AND out_tan_channel IS NULL THEN +-- SELECT +-- tan_channel, +-- CASE +-- WHEN tan_channel = 'sms' THEN phone +-- WHEN tan_channel = 'email' THEN email +-- END +-- INTO out_tan_channel, out_tan_info +-- FROM customers WHERE customer_id=account_id; +--END IF; +END $$; +COMMENT ON FUNCTION tan_challenge_send IS 'Get the challenge to send, return NULL if nothing should be sent'; + +CREATE FUNCTION tan_challenge_mark_sent ( + IN in_challenge_id BIGINT, + IN in_now_date INT8, + IN in_retransmission_period INT8 +) RETURNS void +LANGUAGE sql AS $$ + UPDATE tan_challenges SET + retransmission_date = in_now_date + in_retransmission_period + WHERE challenge_id = in_challenge_id; +$$; +COMMENT ON FUNCTION tan_challenge_mark_sent IS 'Register a challenge as successfully sent'; + +CREATE FUNCTION tan_challenge_try ( + IN in_challenge_id BIGINT, + IN in_code TEXT, + IN in_now_date INT8, + OUT ok BOOLEAN, + OUT no_retry BOOLEAN +) +LANGUAGE sql as $$ + UPDATE tan_challenges SET + confirmation_date = CASE + WHEN (retry_counter > 0 AND in_now_date < expiration_date AND code = in_code) THEN in_now_date + ELSE confirmation_date + END, + retry_counter = retry_counter - 1 + WHERE challenge_id = in_challenge_id + RETURNING confirmation_date IS NOT NULL, retry_counter < 0 AND confirmation_date IS NULL; +$$; +COMMENT ON FUNCTION tan_challenge_try IS 'Try to confirm a challenge, return true if the challenge have been confirmed'; + CREATE FUNCTION stats_get_frame( IN now TIMESTAMP, IN in_timeframe stat_timeframe_enum, |