SONAR-13327 Fix SSF-107

* SONAR-13327 Create 'SAML_MESSAGE_IDS' table and DAO
* SONAR-13327 Check SAML Message id not already exist during auth
* SONAR-13327 Clean expired SAML Message ids daily

9 files changed, 324 insertions, 0 deletions

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/DaoModule.java b/server/sonar-db-dao/src/main/java/org/sonar/db/DaoModule.java
index bba7ecd2731..aa3ffa7667d 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/DaoModule.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/DaoModule.java
@@ -84,6 +84,7 @@ import org.sonar.db.source.FileSourceDao;
 import org.sonar.db.user.GroupDao;
 import org.sonar.db.user.GroupMembershipDao;
 import org.sonar.db.user.RoleDao;
+import org.sonar.db.user.SamlMessageIdDao;
 import org.sonar.db.user.SessionTokensDao;
 import org.sonar.db.user.UserDao;
 import org.sonar.db.user.UserGroupDao;
@@ -155,6 +156,7 @@ public class DaoModule extends Module {
     RoleDao.class,
     RuleDao.class,
     RuleRepositoryDao.class,
+    SamlMessageIdDao.class,
     SnapshotDao.class,
     SchemaMigrationDao.class,
     SessionTokensDao.class,
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/DbClient.java b/server/sonar-db-dao/src/main/java/org/sonar/db/DbClient.java
index 2d3f79aad57..24718f69174 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/DbClient.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/DbClient.java
@@ -82,6 +82,7 @@ import org.sonar.db.source.FileSourceDao;
 import org.sonar.db.user.GroupDao;
 import org.sonar.db.user.GroupMembershipDao;
 import org.sonar.db.user.RoleDao;
+import org.sonar.db.user.SamlMessageIdDao;
 import org.sonar.db.user.SessionTokensDao;
 import org.sonar.db.user.UserDao;
 import org.sonar.db.user.UserGroupDao;
@@ -164,6 +165,7 @@ public class DbClient {
   private final NewCodePeriodDao newCodePeriodDao;
   private final ProjectDao projectDao;
   private final SessionTokensDao sessionTokensDao;
+  private final SamlMessageIdDao samlMessageIdDao;
 
   public DbClient(Database database, MyBatis myBatis, DBSessions dbSessions, Dao... daos) {
     this.database = database;
@@ -242,6 +244,7 @@ public class DbClient {
     newCodePeriodDao = getDao(map, NewCodePeriodDao.class);
     projectDao = getDao(map, ProjectDao.class);
     sessionTokensDao = getDao(map, SessionTokensDao.class);
+    samlMessageIdDao = getDao(map, SamlMessageIdDao.class);
   }
 
   public DbSession openSession(boolean batch) {
@@ -534,4 +537,8 @@ public class DbClient {
     return sessionTokensDao;
   }
 
+  public SamlMessageIdDao samlMessageIdDao() {
+    return samlMessageIdDao;
+  }
+
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java b/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java
index ad5d5310b2f..8d31e0f9735 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java
@@ -140,6 +140,7 @@ import org.sonar.db.user.GroupMapper;
 import org.sonar.db.user.GroupMembershipDto;
 import org.sonar.db.user.GroupMembershipMapper;
 import org.sonar.db.user.RoleMapper;
+import org.sonar.db.user.SamlMessageIdMapper;
 import org.sonar.db.user.SessionTokenMapper;
 import org.sonar.db.user.UserDto;
 import org.sonar.db.user.UserGroupDto;
@@ -286,6 +287,7 @@ public class MyBatis implements Startable {
       RoleMapper.class,
       RuleMapper.class,
       RuleRepositoryMapper.class,
+      SamlMessageIdMapper.class,
       SchemaMigrationMapper.class,
       SessionTokenMapper.class,
       SnapshotMapper.class,
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/user/SamlMessageIdDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/user/SamlMessageIdDao.java
new file mode 100644
index 00000000000..3cb6729c871
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/user/SamlMessageIdDao.java
@@ -0,0 +1,57 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2020 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program 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
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.user;
+
+import java.util.Optional;
+import org.sonar.api.utils.System2;
+import org.sonar.core.util.UuidFactory;
+import org.sonar.db.Dao;
+import org.sonar.db.DbSession;
+
+public class SamlMessageIdDao implements Dao {
+
+  private final System2 system2;
+  private final UuidFactory uuidFactory;
+
+  public SamlMessageIdDao(System2 system2, UuidFactory uuidFactory) {
+    this.system2 = system2;
+    this.uuidFactory = uuidFactory;
+  }
+
+  public Optional<SamlMessageIdDto> selectByMessageId(DbSession session, String messageId) {
+    return Optional.ofNullable(mapper(session).selectByMessageId(messageId));
+  }
+
+  public SamlMessageIdDto insert(DbSession session, SamlMessageIdDto dto) {
+    long now = system2.now();
+    mapper(session).insert(dto
+      .setUuid(uuidFactory.create())
+      .setCreatedAt(now));
+    return dto;
+  }
+
+  public int deleteExpired(DbSession dbSession) {
+    return mapper(dbSession).deleteExpired(system2.now());
+  }
+
+  private static SamlMessageIdMapper mapper(DbSession session) {
+    return session.getMapper(SamlMessageIdMapper.class);
+  }
+}
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/user/SamlMessageIdDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/user/SamlMessageIdDto.java
new file mode 100644
index 00000000000..89b00ab4a86
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/user/SamlMessageIdDto.java
@@ -0,0 +1,75 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2020 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program 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
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.user;
+
+public class SamlMessageIdDto {
+
+  private String uuid;
+
+  /**
+   * Message ID from the SAML response received during authentication.
+   */
+  private String messageId;
+
+  /**
+   * Expiration date is coming from the NotOnOrAfter attribute of the SAML response.
+   *
+   * A row that contained an expired date can be safely deleted from database.
+   */
+  private long expirationDate;
+
+  private long createdAt;
+
+  public String getUuid() {
+    return uuid;
+  }
+
+  SamlMessageIdDto setUuid(String uuid) {
+    this.uuid = uuid;
+    return this;
+  }
+
+  public String getMessageId() {
+    return messageId;
+  }
+
+  public SamlMessageIdDto setMessageId(String messageId) {
+    this.messageId = messageId;
+    return this;
+  }
+
+  public long getExpirationDate() {
+    return expirationDate;
+  }
+
+  public SamlMessageIdDto setExpirationDate(long expirationDate) {
+    this.expirationDate = expirationDate;
+    return this;
+  }
+
+  public long getCreatedAt() {
+    return createdAt;
+  }
+
+  SamlMessageIdDto setCreatedAt(long createdAt) {
+    this.createdAt = createdAt;
+    return this;
+  }
+}
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/user/SamlMessageIdMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/user/SamlMessageIdMapper.java
new file mode 100644
index 00000000000..0cd1a0a0186
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/user/SamlMessageIdMapper.java
@@ -0,0 +1,34 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2020 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program 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
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.user;
+
+import javax.annotation.CheckForNull;
+import org.apache.ibatis.annotations.Param;
+
+public interface SamlMessageIdMapper {
+
+  @CheckForNull
+  SamlMessageIdDto selectByMessageId(String messageId);
+
+  void insert(@Param("dto") SamlMessageIdDto dto);
+
+  int deleteExpired(@Param("now") long now);
+
+}
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/user/SamlMessageIdMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/user/SamlMessageIdMapper.xml
new file mode 100644
index 00000000000..5e090f3bd1b
--- /dev/null
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/user/SamlMessageIdMapper.xml
@@ -0,0 +1,40 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd">
+
+<mapper namespace="org.sonar.db.user.SamlMessageIdMapper">
+
+  <sql id="columns">
+    smi.uuid as uuid,
+    smi.message_id as "messageId",
+    smi.expiration_date as "expirationDate",
+    smi.created_at as "createdAt"
+  </sql>
+
+  <select id="selectByMessageId" parameterType="String" resultType="org.sonar.db.user.SamlMessageIdDto">
+    select
+    <include refid="columns"/>
+    from saml_message_ids smi
+    where smi.message_id=#{messageId, jdbcType=VARCHAR}
+  </select>
+
+  <insert id="insert" parameterType="Map" useGeneratedKeys="false">
+    insert into saml_message_ids
+    (
+      uuid,
+      message_id,
+      expiration_date,
+      created_at
+    )
+    values (
+      #{dto.uuid, jdbcType=VARCHAR},
+      #{dto.messageId, jdbcType=VARCHAR},
+      #{dto.expirationDate, jdbcType=BIGINT},
+      #{dto.createdAt, jdbcType=BIGINT}
+    )
+  </insert>
+
+  <delete id="deleteExpired" parameterType="Long" >
+    delete from saml_message_ids where expiration_date &lt; #{now, jdbcType=BIGINT}
+  </delete>
+
+</mapper>
diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl
index 1bc82047b1e..106647743ed 100644
--- a/server/sonar-db-dao/src/schema/schema-sq.ddl
+++ b/server/sonar-db-dao/src/schema/schema-sq.ddl
@@ -871,6 +871,15 @@ CREATE TABLE "RULES_PROFILES"(
 );
 ALTER TABLE "RULES_PROFILES" ADD CONSTRAINT "PK_RULES_PROFILES" PRIMARY KEY("UUID");
 
+CREATE TABLE "SAML_MESSAGE_IDS"(
+    "UUID" VARCHAR(40) NOT NULL,
+    "MESSAGE_ID" VARCHAR(255) NOT NULL,
+    "EXPIRATION_DATE" BIGINT NOT NULL,
+    "CREATED_AT" BIGINT NOT NULL
+);
+ALTER TABLE "SAML_MESSAGE_IDS" ADD CONSTRAINT "PK_SAML_MESSAGE_IDS" PRIMARY KEY("UUID");
+CREATE UNIQUE INDEX "SAML_MESSAGE_IDS_UNIQUE" ON "SAML_MESSAGE_IDS"("MESSAGE_ID");
+
 CREATE TABLE "SESSION_TOKENS"(
     "UUID" VARCHAR(40) NOT NULL,
     "USER_UUID" VARCHAR(255) NOT NULL,
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/user/SamlMessageIdDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/user/SamlMessageIdDaoTest.java
new file mode 100644
index 00000000000..f7dd0b6920f
--- /dev/null
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/user/SamlMessageIdDaoTest.java
@@ -0,0 +1,98 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2020 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program 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
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+
+package org.sonar.db.user;
+
+import java.util.Optional;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.api.impl.utils.TestSystem2;
+import org.sonar.core.util.SequenceUuidFactory;
+import org.sonar.core.util.UuidFactory;
+import org.sonar.db.DbSession;
+import org.sonar.db.DbTester;
+
+import static org.assertj.core.api.Assertions.assertThat;
+
+public class SamlMessageIdDaoTest {
+
+  private static final long NOW = 1_000_000_000L;
+
+  private TestSystem2 system2 = new TestSystem2().setNow(NOW);
+  @Rule
+  public DbTester db = DbTester.create(system2);
+
+  private DbSession dbSession = db.getSession();
+  private UuidFactory uuidFactory = new SequenceUuidFactory();
+
+  private SamlMessageIdDao underTest = new SamlMessageIdDao(system2, uuidFactory);
+
+  @Test
+  public void selectByMessageId() {
+    SamlMessageIdDto dto = new SamlMessageIdDto()
+      .setMessageId("ABCD")
+      .setExpirationDate(15_000_000_000L);
+    underTest.insert(dbSession, dto);
+
+    Optional<SamlMessageIdDto> result = underTest.selectByMessageId(dbSession, dto.getMessageId());
+
+    assertThat(result).isPresent();
+    assertThat(result.get().getMessageId()).isEqualTo("ABCD");
+    assertThat(result.get().getExpirationDate()).isEqualTo(15_000_000_000L);
+    assertThat(result.get().getCreatedAt()).isEqualTo(NOW);
+  }
+
+  @Test
+  public void uuid_created_at_and_updated_at_are_ignored_during_insert() {
+    SamlMessageIdDto dto = new SamlMessageIdDto()
+      .setMessageId("ABCD")
+      .setExpirationDate(15_000_000_000L)
+      // Following fields should be ignored
+      .setUuid("SHOULD_NOT_BE_USED")
+      .setCreatedAt(8_000_000_000L);
+    underTest.insert(dbSession, dto);
+
+    Optional<SamlMessageIdDto> result = underTest.selectByMessageId(dbSession, dto.getMessageId());
+
+    assertThat(result).isPresent();
+    assertThat(result.get().getUuid()).isNotEqualTo("SHOULD_NOT_BE_USED");
+    assertThat(result.get().getCreatedAt()).isEqualTo(NOW);
+  }
+
+  @Test
+  public void deleteExpired() {
+    SamlMessageIdDto expiredSamlMessageId1 = underTest.insert(dbSession, new SamlMessageIdDto()
+      .setMessageId("MESSAGE_1")
+      .setExpirationDate(NOW - 2_000_000_000L));
+    SamlMessageIdDto expiredSamlMessageId2 = underTest.insert(dbSession, new SamlMessageIdDto()
+      .setMessageId("MESSAGE_2")
+      .setExpirationDate(NOW - 2_000_000_000L));
+    SamlMessageIdDto validSamlMessageId = underTest.insert(dbSession, new SamlMessageIdDto()
+      .setMessageId("MESSAGE_3")
+      .setExpirationDate(NOW + 1_000_000_000L));
+
+    int result = underTest.deleteExpired(dbSession);
+
+    assertThat(underTest.selectByMessageId(dbSession, expiredSamlMessageId1.getMessageId())).isNotPresent();
+    assertThat(underTest.selectByMessageId(dbSession, expiredSamlMessageId2.getMessageId())).isNotPresent();
+    assertThat(underTest.selectByMessageId(dbSession, validSamlMessageId.getMessageId())).isPresent();
+    assertThat(result).isEqualTo(2);
+  }
+}