email: cleanup expired auth codes

4 files changed, 45 insertions, 2 deletions

diff --git a/src/main/java/com/juick/EmailManager.java b/src/main/java/com/juick/EmailManager.java
index 62584273..6180668e 100644
--- a/src/main/java/com/juick/EmailManager.java
+++ b/src/main/java/com/juick/EmailManager.java
@@ -1,5 +1,5 @@
 /*
- * Copyright (C) 2008-2020, Juick
+ * Copyright (C) 2008-2023, Juick
  *
  * This program is free software: you can redistribute it and/or modify
  * it under the terms of the GNU Affero General Public License as
@@ -45,6 +45,7 @@ import org.slf4j.LoggerFactory;
 import org.springframework.beans.factory.annotation.Value;
 import org.springframework.context.event.EventListener;
 import org.springframework.scheduling.annotation.Async;
+import org.springframework.scheduling.annotation.Scheduled;
 
 import javax.annotation.Nonnull;
 import javax.inject.Inject;
@@ -196,4 +197,9 @@ public class EmailManager implements NotificationListener {
             return false;
         }
     }
+    @Scheduled(fixedRate = 3600000)
+    public void cleanupAuth() {
+        var rowsDeleted = emailService.cleanupAuthCodes();
+        logger.debug("{} auth codes deleted", rowsDeleted);
+    }
 }
diff --git a/src/main/java/com/juick/service/EmailService.java b/src/main/java/com/juick/service/EmailService.java
index bdd7d405..614cf412 100644
--- a/src/main/java/com/juick/service/EmailService.java
+++ b/src/main/java/com/juick/service/EmailService.java
@@ -1,5 +1,5 @@
 /*
- * Copyright (C) 2008-2020, Juick
+ * Copyright (C) 2008-2023, Juick
  *
  * This program is free software: you can redistribute it and/or modify
  * it under the terms of the GNU Affero General Public License as
@@ -38,4 +38,5 @@ public interface EmailService {
     List<String> getEmails(Integer userId, boolean active);
     String getEmailByAuthCode(String code);
     void deleteAuthCode(String code);
+    Integer cleanupAuthCodes();
 }
diff --git a/src/main/java/com/juick/service/EmailServiceImpl.java b/src/main/java/com/juick/service/EmailServiceImpl.java
index 977d9a75..7f358533 100644
--- a/src/main/java/com/juick/service/EmailServiceImpl.java
+++ b/src/main/java/com/juick/service/EmailServiceImpl.java
@@ -24,6 +24,10 @@ import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
 import org.springframework.stereotype.Repository;
 import org.springframework.transaction.annotation.Transactional;
 
+import java.sql.Types;
+import java.time.Instant;
+import java.time.ZoneOffset;
+import java.time.temporal.ChronoUnit;
 import java.util.List;
 
 /**
@@ -110,4 +114,11 @@ public class EmailServiceImpl extends BaseJdbcService implements EmailService {
         getJdbcTemplate().update("DELETE FROM auth WHERE authcode=?", code);
     }
 
+    @Override
+    public Integer cleanupAuthCodes() {
+        Instant day = Instant.now().minus(1, ChronoUnit.DAYS);
+        return getNamedParameterJdbcTemplate().update("DELETE FROM auth WHERE ts < :day",
+                new MapSqlParameterSource()
+                        .addValue("day", day.atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE));
+    }
 }
diff --git a/src/test/java/com/juick/server/tests/ServerTests.java b/src/test/java/com/juick/server/tests/ServerTests.java
index de85fbf3..f01f58a7 100644
--- a/src/test/java/com/juick/server/tests/ServerTests.java
+++ b/src/test/java/com/juick/server/tests/ServerTests.java
@@ -134,6 +134,8 @@ import java.security.spec.InvalidKeySpecException;
 import java.sql.Timestamp;
 import java.time.Instant;
 import java.time.OffsetDateTime;
+import java.time.ZoneOffset;
+import java.time.temporal.ChronoUnit;
 import java.util.*;
 import java.util.function.BiFunction;
 import java.util.stream.Collectors;
@@ -2767,6 +2769,29 @@ public class ServerTests {
         .param("uid", String.valueOf(ugnich.getUid())))
         .andExpect(status().isOk());
     }
+    @Test
+    @Transactional
+    public void authCodesCleanup() throws Exception {
+        emailService.addVerificationCode(ugnich.getUid(), "test@example.com", "123456");
+        String hash = userService.getHashByUID(ugnich.getUid());
+        mockMvc.perform(get("/settings")
+                        .param("hash", hash)
+                        .param("page", "auth-email")
+                        .param("code", "123456"))
+                .andExpect(status().isOk());
+        assertThat(emailService.getEmails(ugnich.getUid(), true).size(), is(1));
+        emailService.addVerificationCode(ugnich.getUid(), "test2@example.com", "12345678");
+        var count = jdbcTemplate.queryForObject(
+                "SELECT COUNT(*) FROM auth WHERE user_id=?", Integer.class, ugnich.getUid());
+        assertThat(count, is(1));
+        var timestamp = Instant.now().minus(3, ChronoUnit.DAYS).atOffset(ZoneOffset.UTC);
+        jdbcTemplate.update("UPDATE auth SET ts=? WHERE user_id=?", timestamp, ugnich.getUid());
+        emailService.cleanupAuthCodes();
+        count = jdbcTemplate.queryForObject(
+                "SELECT COUNT(*) FROM auth WHERE user_id=?", Integer.class, ugnich.getUid());
+        assertThat(count, is(0));
+        jdbcTemplate.execute("DELETE FROM emails");
+    }
 /*
     @Test
     public void tokenAuth() throws Exception {