New TAN challenge table

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,