SONAR-14175 Add 'reset_password' column to 'users' table

 - add and populate 'reset_password' column in users table
 - add boolean 'resetPassword' to UserDto

10 files changed, 483 insertions, 1 deletions

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/AddResetPasswordColumnToUsers.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/AddResetPasswordColumnToUsers.java
new file mode 100644
index 00000000000..100afd4ae7e
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/AddResetPasswordColumnToUsers.java
@@ -0,0 +1,48 @@
+/*
+ * 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.server.platform.db.migration.version.v86;
+
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.def.BooleanColumnDef;
+import org.sonar.server.platform.db.migration.sql.AddColumnsBuilder;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+import static org.sonar.server.platform.db.migration.def.BooleanColumnDef.newBooleanColumnDefBuilder;
+
+public class AddResetPasswordColumnToUsers extends DdlChange {
+  private static final String TABLE = "users";
+  private static final String NEW_COLUMN = "reset_password";
+
+  public AddResetPasswordColumnToUsers(Database db) {
+    super(db);
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    BooleanColumnDef column = newBooleanColumnDefBuilder()
+      .setColumnName(NEW_COLUMN)
+      .setIsNullable(true)
+      .build();
+    context.execute(new AddColumnsBuilder(getDialect(), TABLE)
+      .addColumn(column)
+      .build());
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/DbVersion86.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/DbVersion86.java
index f826e79eb6e..f4bd257b29a 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/DbVersion86.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/DbVersion86.java
@@ -53,6 +53,9 @@ public class DbVersion86 implements DbVersion {
       .add(4121, "Create index for 'app_branch_project_branch'", AddIndexToApplicationBranchProjs.class)
 
       .add(4122, "Migrate view definitions from xml to db", MigrateApplicationDefinitionsFromXmlToDb.class)
-    ;
+
+      .add(4123, "Add 'reset_password' column to 'users' table", AddResetPasswordColumnToUsers.class)
+      .add(4124, "Populate 'reset_password' column with default value", PopulateResetPasswordDefaultValue.class)
+      .add(4125, "Make 'reset_password' column in 'users' table not nullable", MakeResetPasswordColumnNotNull.class);
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/MakeResetPasswordColumnNotNull.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/MakeResetPasswordColumnNotNull.java
new file mode 100644
index 00000000000..021fad5aa9e
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/MakeResetPasswordColumnNotNull.java
@@ -0,0 +1,49 @@
+/*
+ * 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.server.platform.db.migration.version.v86;
+
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.def.BooleanColumnDef;
+import org.sonar.server.platform.db.migration.sql.AlterColumnsBuilder;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+import static org.sonar.server.platform.db.migration.def.BooleanColumnDef.newBooleanColumnDefBuilder;
+
+public class MakeResetPasswordColumnNotNull extends DdlChange {
+  private static final String TABLE = "users";
+  private static final String RESET_PASSWORD_COLUMN_NAME = "reset_password";
+
+  private static final BooleanColumnDef columnDefinition = newBooleanColumnDefBuilder()
+    .setColumnName(RESET_PASSWORD_COLUMN_NAME)
+    .setIsNullable(false)
+    .build();
+
+  public MakeResetPasswordColumnNotNull(Database db) {
+    super(db);
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    context.execute(new AlterColumnsBuilder(getDialect(), TABLE)
+      .updateColumn(columnDefinition)
+      .build());
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/PopulateResetPasswordDefaultValue.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/PopulateResetPasswordDefaultValue.java
new file mode 100644
index 00000000000..b1224294722
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v86/PopulateResetPasswordDefaultValue.java
@@ -0,0 +1,51 @@
+/*
+ * 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.server.platform.db.migration.version.v86;
+
+import java.sql.SQLException;
+import org.sonar.api.utils.System2;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.step.DataChange;
+import org.sonar.server.platform.db.migration.step.MassUpdate;
+
+public class PopulateResetPasswordDefaultValue extends DataChange {
+
+  private final System2 system2;
+
+  public PopulateResetPasswordDefaultValue(Database db, System2 system2) {
+    super(db);
+    this.system2 = system2;
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select uuid from users where reset_password is null");
+    massUpdate.update("update users set reset_password = ?, updated_at = ? where uuid = ?");
+
+    massUpdate.execute((row, update) -> {
+      update.setBoolean(1, false)
+        .setLong(2, system2.now())
+        .setString(3, row.getString(1));
+      return true;
+    });
+
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v86/AddResetPasswordColumnToUsersTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v86/AddResetPasswordColumnToUsersTest.java
new file mode 100644
index 00000000000..46a9d0ab425
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v86/AddResetPasswordColumnToUsersTest.java
@@ -0,0 +1,82 @@
+/*
+ * 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.server.platform.db.migration.version.v86;
+
+import java.sql.SQLException;
+import java.util.Objects;
+import javax.annotation.Nullable;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.db.CoreDbTester;
+import org.sonar.server.platform.db.migration.step.MigrationStep;
+
+import static java.sql.Types.BOOLEAN;
+import static java.util.stream.Collectors.toList;
+import static org.assertj.core.api.Assertions.assertThat;
+
+public class AddResetPasswordColumnToUsersTest {
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(AddResetPasswordColumnToUsersTest.class, "schema.sql");
+
+  private MigrationStep underTest = new AddResetPasswordColumnToUsers(db.database());
+
+  @Test
+  public void execute_on_empty_db() throws SQLException {
+    db.assertColumnDoesNotExist("users", "reset_password");
+    underTest.execute();
+    db.assertColumnDefinition("users", "reset_password", BOOLEAN, null, true);
+  }
+
+  @Test
+  public void execute_on_db_with_user_rows() throws SQLException {
+    insertUser("uuid-1");
+    insertUser("uuid-2");
+    insertUser("uuid-3");
+    db.assertColumnDoesNotExist("users", "reset_password");
+    underTest.execute();
+    db.assertColumnDefinition("users", "reset_password", BOOLEAN, null, true);
+
+    assertThatAllUsersResetPasswordFlagAreNotSet();
+  }
+
+  private void assertThatAllUsersResetPasswordFlagAreNotSet() {
+    assertThat(db.select("select reset_password from users")
+      .stream()
+      .map(r -> r.get("RESET_PASSWORD"))
+      .map(this::getBooleanValue)
+      .filter(Objects::nonNull)
+      .collect(toList())).isEmpty();
+  }
+
+  private Boolean getBooleanValue(@Nullable Object value) {
+    return value == null ? null : Boolean.parseBoolean(value.toString());
+  }
+
+  private void insertUser(String uuid) {
+    db.executeInsert("users",
+      "UUID", uuid,
+      "LOGIN", uuid + "login",
+      "EXTERNAL_LOGIN", uuid + "-external-login",
+      "EXTERNAL_IDENTITY_PROVIDER", "sonarqube",
+      "EXTERNAL_ID", uuid + "-external-id",
+      "IS_ROOT", false,
+      "ONBOARDED", false);
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v86/MakeResetPasswordColumnNotNullTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v86/MakeResetPasswordColumnNotNullTest.java
new file mode 100644
index 00000000000..862754d6d9e
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v86/MakeResetPasswordColumnNotNullTest.java
@@ -0,0 +1,42 @@
+/*
+ * 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.server.platform.db.migration.version.v86;
+
+import java.sql.SQLException;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.db.CoreDbTester;
+import org.sonar.server.platform.db.migration.step.MigrationStep;
+
+import static java.sql.Types.BOOLEAN;
+
+public class MakeResetPasswordColumnNotNullTest {
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(MakeResetPasswordColumnNotNullTest.class, "schema.sql");
+
+  private MigrationStep underTest = new MakeResetPasswordColumnNotNull(db.database());
+
+  @Test
+  public void execute_on_empty_db() throws SQLException {
+    db.assertColumnDefinition("users", "reset_password", BOOLEAN, null, true);
+    underTest.execute();
+    db.assertColumnDefinition("users", "reset_password", BOOLEAN, null, false);
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v86/PopulateResetPasswordDefaultValueTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v86/PopulateResetPasswordDefaultValueTest.java
new file mode 100644
index 00000000000..2993cee776f
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v86/PopulateResetPasswordDefaultValueTest.java
@@ -0,0 +1,121 @@
+/*
+ * 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.server.platform.db.migration.version.v86;
+
+import java.sql.SQLException;
+import javax.annotation.Nullable;
+import org.assertj.core.groups.Tuple;
+import org.junit.Before;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.api.impl.utils.TestSystem2;
+import org.sonar.db.CoreDbTester;
+import org.sonar.server.platform.db.migration.step.MigrationStep;
+
+import static java.util.stream.Collectors.toList;
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.tuple;
+
+public class PopulateResetPasswordDefaultValueTest {
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(PopulateResetPasswordDefaultValueTest.class, "schema.sql");
+
+  private final TestSystem2 system2 = new TestSystem2();
+  private final long NOW = 1606375781L;
+
+  private MigrationStep underTest = new PopulateResetPasswordDefaultValue(db.database(), system2);
+
+  @Before
+  public void before() {
+    system2.setNow(NOW);
+  }
+
+  @Test
+  public void execute_on_empty_db() throws SQLException {
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable("users")).isZero();
+  }
+
+  @Test
+  public void execute_on_db_with_user_rows() throws SQLException {
+    insertUser("uuid-1", null, NOW);
+    insertUser("uuid-2", null, NOW);
+    insertUser("uuid-3", null, NOW);
+
+    long expectedUpdatedRowTime = 2_000_000_000L;
+    system2.setNow(expectedUpdatedRowTime);
+
+    underTest.execute();
+
+    assertThatUserResetPasswordFlagIsEqualTo(
+      tuple("uuid-1", false, expectedUpdatedRowTime),
+      tuple("uuid-2", false, expectedUpdatedRowTime),
+      tuple("uuid-3", false, expectedUpdatedRowTime));
+  }
+
+  @Test
+  public void does_not_update_already_set_flags() throws SQLException {
+    insertUser("uuid-1", true, NOW);
+    insertUser("uuid-2", false, NOW);
+    insertUser("uuid-3", null, NOW);
+
+    long expectedUpdatedRowTime = 2_000_000_000L;
+    system2.setNow(expectedUpdatedRowTime);
+
+    underTest.execute();
+
+    insertUser("uuid-4", null, NOW);
+    // re-entrant
+    underTest.execute();
+
+    assertThatUserResetPasswordFlagIsEqualTo(
+      tuple("uuid-1", true, NOW),
+      tuple("uuid-2", false, NOW),
+      tuple("uuid-3", false, expectedUpdatedRowTime),
+      tuple("uuid-4", false, expectedUpdatedRowTime));
+  }
+
+  private void assertThatUserResetPasswordFlagIsEqualTo(Tuple... tuples) {
+    assertThat(db.select("select uuid, reset_password, updated_at from users")
+      .stream()
+      .map(r -> tuple(r.get("UUID"), getBooleanValue(r.get("RESET_PASSWORD")), r.get("UPDATED_AT")))
+      .collect(toList()))
+        .containsExactlyInAnyOrder(tuples);
+  }
+
+  private Boolean getBooleanValue(@Nullable Object value) {
+    return value == null ? null : Boolean.parseBoolean(value.toString());
+  }
+
+  private void insertUser(String uuid, @Nullable Boolean resetPassword, long updatedAt) {
+    db.executeInsert("users",
+      "UUID", uuid,
+      "LOGIN", uuid + "login",
+      "EXTERNAL_LOGIN", uuid + "-external-login",
+      "EXTERNAL_IDENTITY_PROVIDER", "sonarqube",
+      "EXTERNAL_ID", uuid + "-external-id",
+      "IS_ROOT", false,
+      "RESET_PASSWORD", resetPassword,
+      "ONBOARDED", false,
+      "UPDATED_AT", updatedAt);
+  }
+}
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v86/AddResetPasswordColumnToUsersTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v86/AddResetPasswordColumnToUsersTest/schema.sql
new file mode 100644
index 00000000000..542deb026c8
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v86/AddResetPasswordColumnToUsersTest/schema.sql
@@ -0,0 +1,28 @@
+CREATE TABLE "USERS"(
+    "UUID" VARCHAR(255) NOT NULL,
+    "LOGIN" VARCHAR(255) NOT NULL,
+    "NAME" VARCHAR(200),
+    "EMAIL" VARCHAR(100),
+    "CRYPTED_PASSWORD" VARCHAR(100),
+    "SALT" VARCHAR(40),
+    "HASH_METHOD" VARCHAR(10),
+    "ACTIVE" BOOLEAN DEFAULT TRUE,
+    "SCM_ACCOUNTS" VARCHAR(4000),
+    "EXTERNAL_LOGIN" VARCHAR(255) NOT NULL,
+    "EXTERNAL_IDENTITY_PROVIDER" VARCHAR(100) NOT NULL,
+    "EXTERNAL_ID" VARCHAR(255) NOT NULL,
+    "IS_ROOT" BOOLEAN NOT NULL,
+    "USER_LOCAL" BOOLEAN,
+    "ONBOARDED" BOOLEAN NOT NULL,
+    "HOMEPAGE_TYPE" VARCHAR(40),
+    "HOMEPAGE_PARAMETER" VARCHAR(40),
+    "LAST_CONNECTION_DATE" BIGINT,
+    "CREATED_AT" BIGINT,
+    "UPDATED_AT" BIGINT
+);
+ALTER TABLE "USERS" ADD CONSTRAINT "PK_USERS" PRIMARY KEY("UUID");
+CREATE UNIQUE INDEX "USERS_LOGIN" ON "USERS"("LOGIN");
+CREATE INDEX "USERS_UPDATED_AT" ON "USERS"("UPDATED_AT");
+CREATE UNIQUE INDEX "UNIQ_EXTERNAL_ID" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER", "EXTERNAL_ID");
+CREATE UNIQUE INDEX "UNIQ_EXTERNAL_LOGIN" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER", "EXTERNAL_LOGIN");
+
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v86/MakeResetPasswordColumnNotNullTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v86/MakeResetPasswordColumnNotNullTest/schema.sql
new file mode 100644
index 00000000000..be6b1ef0e77
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v86/MakeResetPasswordColumnNotNullTest/schema.sql
@@ -0,0 +1,29 @@
+CREATE TABLE "USERS"(
+    "UUID" VARCHAR(255) NOT NULL,
+    "LOGIN" VARCHAR(255) NOT NULL,
+    "NAME" VARCHAR(200),
+    "EMAIL" VARCHAR(100),
+    "CRYPTED_PASSWORD" VARCHAR(100),
+    "SALT" VARCHAR(40),
+    "HASH_METHOD" VARCHAR(10),
+    "ACTIVE" BOOLEAN DEFAULT TRUE,
+    "SCM_ACCOUNTS" VARCHAR(4000),
+    "EXTERNAL_LOGIN" VARCHAR(255) NOT NULL,
+    "EXTERNAL_IDENTITY_PROVIDER" VARCHAR(100) NOT NULL,
+    "EXTERNAL_ID" VARCHAR(255) NOT NULL,
+    "IS_ROOT" BOOLEAN NOT NULL,
+    "USER_LOCAL" BOOLEAN,
+    "ONBOARDED" BOOLEAN NOT NULL,
+    "RESET_PASSWORD" BOOLEAN,
+    "HOMEPAGE_TYPE" VARCHAR(40),
+    "HOMEPAGE_PARAMETER" VARCHAR(40),
+    "LAST_CONNECTION_DATE" BIGINT,
+    "CREATED_AT" BIGINT,
+    "UPDATED_AT" BIGINT
+);
+ALTER TABLE "USERS" ADD CONSTRAINT "PK_USERS" PRIMARY KEY("UUID");
+CREATE UNIQUE INDEX "USERS_LOGIN" ON "USERS"("LOGIN");
+CREATE INDEX "USERS_UPDATED_AT" ON "USERS"("UPDATED_AT");
+CREATE UNIQUE INDEX "UNIQ_EXTERNAL_ID" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER", "EXTERNAL_ID");
+CREATE UNIQUE INDEX "UNIQ_EXTERNAL_LOGIN" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER", "EXTERNAL_LOGIN");
+
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v86/PopulateResetPasswordDefaultValueTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v86/PopulateResetPasswordDefaultValueTest/schema.sql
new file mode 100644
index 00000000000..be6b1ef0e77
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v86/PopulateResetPasswordDefaultValueTest/schema.sql
@@ -0,0 +1,29 @@
+CREATE TABLE "USERS"(
+    "UUID" VARCHAR(255) NOT NULL,
+    "LOGIN" VARCHAR(255) NOT NULL,
+    "NAME" VARCHAR(200),
+    "EMAIL" VARCHAR(100),
+    "CRYPTED_PASSWORD" VARCHAR(100),
+    "SALT" VARCHAR(40),
+    "HASH_METHOD" VARCHAR(10),
+    "ACTIVE" BOOLEAN DEFAULT TRUE,
+    "SCM_ACCOUNTS" VARCHAR(4000),
+    "EXTERNAL_LOGIN" VARCHAR(255) NOT NULL,
+    "EXTERNAL_IDENTITY_PROVIDER" VARCHAR(100) NOT NULL,
+    "EXTERNAL_ID" VARCHAR(255) NOT NULL,
+    "IS_ROOT" BOOLEAN NOT NULL,
+    "USER_LOCAL" BOOLEAN,
+    "ONBOARDED" BOOLEAN NOT NULL,
+    "RESET_PASSWORD" BOOLEAN,
+    "HOMEPAGE_TYPE" VARCHAR(40),
+    "HOMEPAGE_PARAMETER" VARCHAR(40),
+    "LAST_CONNECTION_DATE" BIGINT,
+    "CREATED_AT" BIGINT,
+    "UPDATED_AT" BIGINT
+);
+ALTER TABLE "USERS" ADD CONSTRAINT "PK_USERS" PRIMARY KEY("UUID");
+CREATE UNIQUE INDEX "USERS_LOGIN" ON "USERS"("LOGIN");
+CREATE INDEX "USERS_UPDATED_AT" ON "USERS"("UPDATED_AT");
+CREATE UNIQUE INDEX "UNIQ_EXTERNAL_ID" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER", "EXTERNAL_ID");
+CREATE UNIQUE INDEX "UNIQ_EXTERNAL_LOGIN" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER", "EXTERNAL_LOGIN");
+