commit 3a4c4b61ef871788252b7a258aa94aa82db9670f
parent ec3273434e99ef6877388e86ed6671d28b099991
Author: Antoine A <>
Date: Sat, 23 Dec 2023 01:04:14 +0000
First working 2fa flow
Diffstat:
9 files changed, 330 insertions(+), 92 deletions(-)
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/CoreBankApi.kt b/bank/src/main/kotlin/tech/libeufin/bank/CoreBankApi.kt
@@ -40,6 +40,7 @@ import tech.libeufin.bank.CashoutDAO.*
import tech.libeufin.bank.ExchangeDAO.*
import tech.libeufin.bank.TransactionDAO.*
import tech.libeufin.bank.WithdrawalDAO.*
+import tech.libeufin.bank.TanDAO.*
import tech.libeufin.util.*
private val logger: Logger = LoggerFactory.getLogger("tech.libeufin.bank.accountsMgmtHandlers")
@@ -71,6 +72,7 @@ fun Routing.coreBankApi(db: Database, ctx: BankConfig) {
coreBankTransactionsApi(db, ctx)
coreBankWithdrawalApi(db, ctx)
coreBankCashoutApi(db, ctx)
+ coreBankTanApi(db, ctx)
}
private fun Routing.coreBankTokenApi(db: Database) {
@@ -189,7 +191,7 @@ suspend fun createAccount(db: Database, ctx: BankConfig, req: RegisterAccountReq
}
}
-suspend fun patchAccount(db: Database, ctx: BankConfig, req: AccountReconfiguration, username: String, isAdmin: Boolean): AccountPatchResult {
+suspend fun patchAccount(db: Database, ctx: BankConfig, req: AccountReconfiguration, username: String, isAdmin: Boolean, is2fa: Boolean): AccountPatchResult {
req.debit_threshold?.run { ctx.checkRegionalCurrency(this) }
val contactData = req.contact_data ?: req.challenge_contact_data
@@ -209,6 +211,7 @@ suspend fun patchAccount(db: Database, ctx: BankConfig, req: AccountReconfigurat
isPublic = req.is_public,
debtLimit = req.debit_threshold,
isAdmin = isAdmin,
+ is2fa = is2fa,
allowEditName = ctx.allowEditName,
allowEditCashout = ctx.allowEditCashout
)
@@ -267,8 +270,8 @@ private fun Routing.coreBankAccountsApi(db: Database, ctx: BankConfig) {
}
auth(db, TokenScope.readwrite, allowAdmin = true) {
patch("/accounts/{USERNAME}") {
- val req = call.receive<AccountReconfiguration>()
- val res = patchAccount(db, ctx, req, username, isAdmin)
+ val (req, is2fa) = call.receiveChallenge<AccountReconfiguration>(db)
+ val res = patchAccount(db, ctx, req, username, isAdmin, is2fa)
when (res) {
AccountPatchResult.Success -> call.respond(HttpStatusCode.NoContent)
AccountPatchResult.TanRequired -> call.respondChallenge(db, req)
@@ -683,3 +686,87 @@ private fun Routing.coreBankCashoutApi(db: Database, ctx: BankConfig) = conditio
}
}
}
+
+
+private fun Routing.coreBankTanApi(db: Database, ctx: BankConfig) {
+ auth(db, TokenScope.readwrite) {
+ post("/accounts/{USERNAME}/challenge/{CHALLENGE_ID}") {
+ val id = call.longUriComponent("CHALLENGE_ID")
+ val res = db.tan.send(
+ id = id,
+ login = username,
+ code = Tan.genCode(),
+ now = Instant.now(),
+ retryCounter = TAN_RETRY_COUNTER,
+ validityPeriod = TAN_VALIDITY_PERIOD
+ )
+ when (res) {
+ TanSendResult.NotFound -> throw notFound(
+ "Challenge $id not found",
+ TalerErrorCode.BANK_TRANSACTION_NOT_FOUND
+ )
+ is TanSendResult.Success -> {
+ res.tanCode?.run {
+ val tanScript = ctx.tanChannels.get(res.tanChannel) ?: throw libeufinError(
+ HttpStatusCode.NotImplemented,
+ "Unsupported tan channel ${res.tanChannel}",
+ TalerErrorCode.BANK_TAN_CHANNEL_NOT_SUPPORTED
+ )
+ val exitValue = withContext(Dispatchers.IO) {
+ val process = ProcessBuilder(tanScript, res.tanInfo).start()
+ try {
+ process.outputWriter().use { it.write(res.tanCode) }
+ process.onExit().await()
+ } catch (e: Exception) {
+ process.destroy()
+ }
+ process.exitValue()
+ }
+ if (exitValue != 0) {
+ throw libeufinError(
+ HttpStatusCode.BadGateway,
+ "Tan channel script failure with exit value $exitValue",
+ TalerErrorCode.BANK_TAN_CHANNEL_SCRIPT_FAILED
+ )
+ }
+ db.tan.markSent(id, Instant.now(), TAN_RETRANSMISSION_PERIOD)
+ }
+ call.respond(TanTransmission(
+ tan_info = res.tanInfo,
+ tan_channel = res.tanChannel
+ ))
+ }
+ }
+ }
+ post("/accounts/{USERNAME}/challenge/{CHALLENGE_ID}/confirm") {
+ val id = call.longUriComponent("CHALLENGE_ID")
+ val req = call.receive<CashoutConfirm>()
+ val res = db.tan.solve(
+ id = id,
+ login = username,
+ code = req.tan,
+ now = Instant.now()
+ )
+ when (res) {
+ TanSolveResult.NotFound -> throw notFound(
+ "Challenge $id not found",
+ TalerErrorCode.BANK_TRANSACTION_NOT_FOUND
+ )
+ TanSolveResult.BadCode -> throw conflict(
+ "Incorrect TAN code",
+ TalerErrorCode.BANK_TAN_CHALLENGE_FAILED
+ )
+ TanSolveResult.NoRetry -> throw libeufinError(
+ HttpStatusCode.TooManyRequests,
+ "Too many failed confirmation attempt",
+ TalerErrorCode.BANK_TAN_RATE_LIMITED
+ )
+ TanSolveResult.Expired -> throw conflict( // TODO
+ "Challenge expired",
+ TalerErrorCode.BANK_TAN_CHALLENGE_FAILED
+ )
+ TanSolveResult.Success -> call.respond(HttpStatusCode.NoContent)
+ }
+ }
+ }
+}
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/TalerMessage.kt b/bank/src/main/kotlin/tech/libeufin/bank/TalerMessage.kt
@@ -116,6 +116,12 @@ data class TanChallenge(
val challenge_id: Long
)
+@Serializable
+data class TanTransmission(
+ val tan_info: String,
+ val tan_channel: TanChannel
+)
+
/**
* HTTP response type of successful token refresh.
* access_token is the Crockford encoding of the 32 byte
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/Tan.kt b/bank/src/main/kotlin/tech/libeufin/bank/Tan.kt
@@ -24,6 +24,7 @@ import java.time.Duration
import java.text.DecimalFormat
import kotlinx.serialization.json.Json
import io.ktor.http.*
+import io.ktor.server.request.*
import io.ktor.server.response.*
import io.ktor.server.application.*
@@ -45,6 +46,16 @@ inline suspend fun <reified B> ApplicationCall.respondChallenge(db: Database, bo
)
}
+inline suspend fun <reified B> ApplicationCall.receiveChallenge(db: Database): Pair<B, Boolean> {
+ val challengeId: Long? = request.headers.get("TODO")?.run { toLongOrNull() } // TODO Handle not long
+ return if (challengeId != null) {
+ val body = db.tan.body(challengeId, username) ?: throw Exception("TODO")
+ Pair(Json.decodeFromString<B>(body), true)
+ } else {
+ Pair(receive(), false)
+ }
+}
+
object Tan {
private val CODE_FORMAT = DecimalFormat("00000000");
private val SECURE_RNG = SecureRandom()
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/AccountDAO.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/AccountDAO.kt
@@ -217,6 +217,7 @@ class AccountDAO(private val db: Database) {
isPublic: Boolean?,
debtLimit: TalerAmount?,
isAdmin: Boolean,
+ is2fa: Boolean,
allowEditName: Boolean,
allowEditCashout: Boolean,
): AccountPatchResult = db.serializable { it.transaction { conn ->
@@ -273,7 +274,7 @@ class AccountDAO(private val db: Database) {
it.getBoolean("phone_change") -> return@transaction AccountPatchResult.NonAdminContact
it.getBoolean("email_change") -> return@transaction AccountPatchResult.NonAdminContact
it.getBoolean("missing_tan_info") -> return@transaction AccountPatchResult.MissingTanInfo
- it.getBoolean("tan_required") -> return@transaction AccountPatchResult.TanRequired
+ it.getBoolean("tan_required") && !is2fa -> return@transaction AccountPatchResult.TanRequired
else -> it.getLong("customer_id")
}
}
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/TanDAO.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/TanDAO.kt
@@ -27,7 +27,7 @@ import java.time.Instant
/** Data access logic for tan challenged */
class TanDAO(private val db: Database) {
- /** Update in-db conversion config */
+ /** Create new TAN challenge */
suspend fun new(
login: String,
body: String,
@@ -35,27 +35,114 @@ class TanDAO(private val db: Database) {
now: Instant,
retryCounter: Int,
validityPeriod: Duration
- ): Long = db.serializable {
- it.transaction { conn ->
- // Get user ID
- val customer_id = conn.prepareStatement("""
- SELECT customer_id FROM customers WHERE login = ?
- """).run {
- setString(1, login);
- oneOrNull {
- it.getLong(1)
- }!! // TODO handle case where account is deleted ? - HTTP status asking to retry
+ ): Long = db.serializable { conn ->
+ val stmt = conn.prepareStatement("SELECT tan_challenge_create(?, ?, ?, ?, ?, ?, NULL, NULL)")
+ stmt.setString(1, body)
+ stmt.setString(2, code)
+ stmt.setLong(3, now.toDbMicros() ?: throw faultyTimestampByBank())
+ stmt.setLong(4, TimeUnit.MICROSECONDS.convert(validityPeriod))
+ stmt.setInt(5, retryCounter)
+ stmt.setString(6, login)
+ stmt.oneOrNull {
+ it.getLong(1)
+ }!! // TODO handle database weirdness
+ }
+
+ /** Result of TAN challenge transmission */
+ sealed class TanSendResult {
+ data class Success(val tanInfo: String, val tanChannel: TanChannel, val tanCode: String?): TanSendResult()
+ object NotFound: TanSendResult()
+ }
+
+ /** Request TAN challenge transmission */
+ suspend fun send(
+ id: Long,
+ login: String,
+ code: String,
+ now: Instant,
+ retryCounter: Int,
+ validityPeriod: Duration
+ ) = db.serializable { conn ->
+ val stmt = conn.prepareStatement("SELECT out_no_op, out_tan_code, out_tan_channel, out_tan_info FROM tan_challenge_send(?,?,?,?,?,?)")
+ stmt.setLong(1, id)
+ stmt.setString(2, login)
+ stmt.setString(3, code)
+ stmt.setLong(4, now.toDbMicros() ?: throw faultyTimestampByBank())
+ stmt.setLong(5, TimeUnit.MICROSECONDS.convert(validityPeriod))
+ stmt.setInt(6, retryCounter)
+ stmt.executeQuery().use {
+ when {
+ !it.next() -> throw internalServerError("TAN send returned nothing.")
+ it.getBoolean("out_no_op") -> TanSendResult.NotFound
+ else -> TanSendResult.Success(
+ tanInfo = it.getString("out_tan_info"),
+ tanChannel = it.getString("out_tan_channel").run { TanChannel.valueOf(this) },
+ tanCode = it.getString("out_tan_code")
+ )
+ }
+ }
+ }
+
+ /** Mark TAN challenge transmission */
+ suspend fun markSent(
+ id: Long,
+ now: Instant,
+ retransmissionPeriod: Duration
+ ) = db.serializable { conn ->
+ val stmt = conn.prepareStatement("SELECT tan_challenge_mark_sent(?,?,?)")
+ stmt.setLong(1, id)
+ stmt.setLong(2, now.toDbMicros() ?: throw faultyTimestampByBank())
+ stmt.setLong(3, TimeUnit.MICROSECONDS.convert(retransmissionPeriod))
+ stmt.executeQuery()
+ }
+
+ /** Result of TAN challenge solution */
+ enum class TanSolveResult {
+ Success,
+ NotFound,
+ NoRetry,
+ Expired,
+ BadCode
+ }
+
+ /** Solve TAN challenge */
+ suspend fun solve(
+ id: Long,
+ login: String,
+ code: String,
+ now: Instant
+ ) = db.serializable { conn ->
+ val stmt = conn.prepareStatement("SELECT out_ok, out_no_op, out_no_retry, out_expired FROM tan_challenge_try(?,?,?,?)")
+ stmt.setLong(1, id)
+ stmt.setString(2, login)
+ stmt.setString(3, code)
+ stmt.setLong(4, now.toDbMicros() ?: throw faultyTimestampByBank())
+ stmt.executeQuery().use {
+ when {
+ !it.next() -> throw internalServerError("TAN try returned nothing")
+ it.getBoolean("out_ok") -> TanSolveResult.Success
+ it.getBoolean("out_no_op") -> TanSolveResult.NotFound
+ it.getBoolean("out_no_retry") -> TanSolveResult.NoRetry
+ it.getBoolean("out_expired") -> TanSolveResult.Expired
+ else -> TanSolveResult.BadCode
}
- var stmt = conn.prepareStatement("SELECT tan_challenge_create(?, ?, ?, ?, ?, ?, NULL, NULL)")
- stmt.setString(1, body)
- stmt.setString(2, code)
- stmt.setLong(3, now.toDbMicros() ?: throw faultyTimestampByBank())
- stmt.setLong(4, TimeUnit.MICROSECONDS.convert(validityPeriod))
- stmt.setInt(5, retryCounter)
- stmt.setLong(6, customer_id)
- stmt.oneOrNull {
- it.getLong(1)
- }!! // TODO handle database weirdness
+ }
+ }
+
+ /** Get body of a solved TAN challenge */
+ suspend fun body(
+ id: Long,
+ login: String
+ ) = db.conn { conn ->
+ val stmt = conn.prepareStatement("""
+ SELECT body
+ FROM tan_challenges JOIN customers ON customer=customer_id
+ WHERE challenge_id=? AND login=? AND confirmation_date IS NOT NULL
+ """)
+ stmt.setLong(1, id)
+ stmt.setString(2, login)
+ stmt.oneOrNull {
+ it.getString(1)
}
}
}
\ No newline at end of file
diff --git a/bank/src/test/kotlin/CoreBankApiTest.kt b/bank/src/test/kotlin/CoreBankApiTest.kt
@@ -484,13 +484,23 @@ class CoreBankAccountsApiTest {
client.patchA("/accounts/merchant") {
json { "tan_channel" to "sms" }
}.assertNoContent()
- client.patchA("/accounts/merchant") {
+ val challengeId = client.patchA("/accounts/merchant") {
json { "is_public" to false }
- }.assertAccepted()
+ }.assertAcceptedJson<TanChallenge>().challenge_id;
client.getA("/accounts/merchant").assertOkJson<AccountData> { obj ->
// Check request patch did not happen
assert(obj.is_public)
}
+ client.postA("/accounts/merchant/challenge/$challengeId").assertOk()
+ client.postA("/accounts/customer/challenge/$challengeId/confirm") {
+ json { "tan" to smsCode("+99") }
+ }.assertNoContent()
+ client.patchA("/accounts/merchant") {
+ header("TODO", "$challengeId")
+ }.assertNoContent()
+ client.getA("/accounts/merchant").assertOkJson<AccountData> { obj ->
+ assert(!obj.is_public)
+ }
}
// Test admin-only account patch
diff --git a/bank/src/test/kotlin/DatabaseTest.kt b/bank/src/test/kotlin/DatabaseTest.kt
@@ -167,11 +167,11 @@ class DatabaseTest {
}}
@Test
- fun tan_challenge() = bankSetup { db -> db.conn { conn ->
- val createStmt = conn.prepareStatement("SELECT tan_challenge_create('',?,?,?,?,1,NULL,NULL)")
+ fun tanChallenge() = bankSetup { db -> db.conn { conn ->
+ val createStmt = conn.prepareStatement("SELECT tan_challenge_create('',?,?,?,?,'customer',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 tryStmt = conn.prepareStatement("SELECT out_ok, out_no_retry, out_expired FROM tan_challenge_try(?,'customer',?,?)")
+ val sendStmt = conn.prepareStatement("SELECT out_tan_code FROM tan_challenge_send(?,'customer',?,?,?,?)")
val validityPeriod = Duration.ofHours(1)
val retransmissionPeriod: Duration = Duration.ofMinutes(1)
@@ -192,12 +192,12 @@ class DatabaseTest {
return markSentStmt.oneOrNull { }!!
}
- fun cTry(id: Long, code: String, now: Instant): Pair<Boolean, Boolean> {
+ fun cTry(id: Long, code: String, now: Instant): Triple<Boolean, 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))
+ Triple(it.getBoolean(1), it.getBoolean(2), it.getBoolean(3))
}!!
}
@@ -219,28 +219,29 @@ class DatabaseTest {
// Check basic
create("good-code", now).run {
// Bad code
- assertEquals(Pair(false, false), cTry(this, "bad-code", now))
+ assertEquals(Triple(false, false, false), cTry(this, "bad-code", now))
// Good code
- assertEquals(Pair(true, false), cTry(this, "good-code", now))
+ assertEquals(Triple(true, false, 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))
+ assertEquals(Triple(true, false, 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))
+ repeat(retryCounter-1) {
+ assertEquals(Triple(false, false, false), cTry(this, "bad-code", now))
}
+ assertEquals(Triple(false, true, false), cTry(this, "bad-code", now))
// Good code fail
- assertEquals(Pair(false, true), cTry(this, "good-code", now))
+ assertEquals(Triple(false, true, false), 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))
+ assertEquals(Triple(true, false, false), cTry(this, "new-code", now))
}
// Check retransmission and expiration
@@ -253,16 +254,16 @@ class DatabaseTest {
// 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))
+ assertEquals(Triple(false, false, true), 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))
+ assertEquals(Triple(false, false, false), cTry(this, "good-code", expired))
// New code works
- assertEquals(Pair(true, false), cTry(this, "new-code", expired))
+ assertEquals(Triple(true, false, false), cTry(this, "new-code", expired))
}
}}
diff --git a/bank/src/test/kotlin/helpers.kt b/bank/src/test/kotlin/helpers.kt
@@ -361,6 +361,13 @@ inline suspend fun <reified B> HttpResponse.assertOkJson(lambda: (B) -> Unit = {
return body
}
+inline suspend fun <reified B> HttpResponse.assertAcceptedJson(lambda: (B) -> Unit = {}): B {
+ assertAccepted()
+ val body = json<B>()
+ lambda(body)
+ return body
+}
+
/* ----- Auth ----- */
/** Auto auth get request */
diff --git a/database-versioning/libeufin-bank-procedures.sql b/database-versioning/libeufin-bank-procedures.sql
@@ -1310,40 +1310,50 @@ CREATE FUNCTION tan_challenge_create (
IN in_now_date INT8,
IN in_validity_period INT8,
IN in_retry_counter INT4,
- IN in_customer_id INT8,
+ IN in_login TEXT,
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
-$$;
+LANGUAGE plpgsql as $$
+DECLARE
+account_id INT8;
+BEGIN
+-- Retreive account id
+SELECT customer_id INTO account_id FROM customers WHERE login = in_login;
+-- Create challenge
+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,
+ account_id,
+ in_tan_channel,
+ in_tan_info
+) RETURNING challenge_id INTO out_challenge_id;
+END $$;
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_challenge_id BIGINT,
+ IN in_login TEXT,
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,
+ -- Error status
+ OUT out_no_op BOOLEAN,
+ -- Success return
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
@@ -1354,13 +1364,25 @@ account_id INT8;
expired BOOLEAN;
retransmit BOOLEAN;
BEGIN
+-- Retreive account id
+SELECT customer_id, tan_channel, CASE
+ WHEN tan_channel = 'sms' THEN phone
+ WHEN tan_channel = 'email' THEN email
+ END
+ INTO account_id, out_tan_channel, out_tan_info
+ FROM customers WHERE login = in_login;
+
-- 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;
+ ,code, COALESCE(tan_channel, out_tan_channel), COALESCE(tan_info, out_tan_info)
+INTO expired, retransmit, out_tan_code, out_tan_channel, out_tan_info
+FROM tan_challenges WHERE challenge_id = in_challenge_id AND customer = account_id;
+IF NOT FOUND THEN
+ out_no_op = true;
+ RETURN;
+END IF;
IF expired THEN
UPDATE tan_challenges SET
@@ -1372,17 +1394,6 @@ IF expired THEN
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';
@@ -1400,21 +1411,38 @@ COMMENT ON FUNCTION tan_challenge_mark_sent IS 'Register a challenge as successf
CREATE FUNCTION tan_challenge_try (
IN in_challenge_id BIGINT,
+ IN in_login TEXT,
IN in_code TEXT,
IN in_now_date INT8,
- OUT ok BOOLEAN,
- OUT no_retry BOOLEAN
+ OUT out_ok BOOLEAN,
+ OUT out_no_op BOOLEAN,
+ OUT out_no_retry BOOLEAN,
+ OUT out_expired 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;
-$$;
+LANGUAGE plpgsql as $$
+DECLARE
+account_id INT8;
+BEGIN
+-- Retreive account id
+SELECT customer_id INTO account_id FROM customers WHERE login = in_login;
+-- Check challenge
+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,
+ in_now_date >= expiration_date AND confirmation_date IS NULL
+INTO out_ok, out_no_retry, out_expired;
+IF NOT FOUND THEN
+ out_no_op = true;
+ RETURN;
+END IF;
+END $$;
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(
