~jan0sch/smederee
Showing details for patch b79828c692c091d6e8563b3b567618bcbfde8886.
diff -rN -u old-smederee/modules/hub/src/it/scala/de/smederee/hub/AuthenticationMiddlewareTest.scala new-smederee/modules/hub/src/it/scala/de/smederee/hub/AuthenticationMiddlewareTest.scala --- old-smederee/modules/hub/src/it/scala/de/smederee/hub/AuthenticationMiddlewareTest.scala 2025-02-03 04:59:02.850649632 +0000 +++ new-smederee/modules/hub/src/it/scala/de/smederee/hub/AuthenticationMiddlewareTest.scala 2025-02-03 04:59:02.854649640 +0000 @@ -40,89 +40,6 @@ val _ = flyway.clean() } - private def connectToDb(cfg: SmedereeHubConfig): Resource[IO, java.sql.Connection] = - Resource.make( - IO.delay(java.sql.DriverManager.getConnection(cfg.database.url, cfg.database.user, cfg.database.pass)) - )(c => IO.delay(c.close())) - - /** Create the given account in the database. - * - * @param account - * The account to be created. - * @param hash - * A password hash to be stored. - * @param unlockToken - * An optional unlock token to be stored. - * @param attempts - * Optional number of failed login attempts. - * @return - * The number of affected database rows. - */ - @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!") - private def createAccount( - account: Account, - hash: PasswordHash, - unlockToken: Option[UnlockToken], - attempts: Option[Int] - ): IO[Int] = - connectToDb(configuration).use { con => - for { - statement <- IO.delay { - unlockToken match { - case None => - con.prepareStatement( - """INSERT INTO "accounts" (uid, name, email, password, failed_attempts, created_at, updated_at) VALUES(?, ?, ?, ?, ?, NOW(), NOW())""" - ) - case Some(_) => - con.prepareStatement( - """INSERT INTO "accounts" (uid, name, email, password, failed_attempts, locked_at, unlock_token, created_at, updated_at) VALUES(?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())""" - ) - } - } - _ <- IO.delay(statement.setObject(1, account.uid)) - _ <- IO.delay(statement.setString(2, account.name.toString)) - _ <- IO.delay(statement.setString(3, account.email.toString)) - _ <- IO.delay(statement.setString(4, hash.toString)) - _ <- IO.delay(statement.setInt(5, attempts.getOrElse(1))) - _ <- IO.delay { - unlockToken.foreach { token => - statement.setString(6, token.toString) - } - } - r <- IO.delay(statement.executeUpdate()) - _ <- IO.delay(statement.close()) - } yield r - } - - /** Create the given user session in the database. - * - * @param session - * The session that shall be created. The corresponding user account must exist! - * @return - * The number of affected database rows. - */ - @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!") - private def createUserSession(session: Session): IO[Int] = - connectToDb(configuration).use { con => - for { - statement <- IO.delay( - con.prepareStatement( - """INSERT INTO "sessions" (id, uid, created_at, updated_at) VALUES(?, ?, ?, ?)""" - ) - ) - _ <- IO.delay(statement.setString(1, session.id.toString)) - _ <- IO.delay(statement.setObject(2, session.uid)) - _ <- IO.delay( - statement.setTimestamp(3, java.sql.Timestamp.from(session.createdAt.toInstant)) - ) - _ <- IO.delay( - statement.setTimestamp(4, java.sql.Timestamp.from(session.updatedAt.toInstant)) - ) - r <- IO.delay(statement.executeUpdate()) - _ <- IO.delay(statement.close()) - } yield r - } - test("extractSessionId must return the session id") { (genSignAndValidate.sample, genSessionId.sample) match { case (Some(signAndValidate), Some(sessionId)) => diff -rN -u old-smederee/modules/hub/src/it/scala/de/smederee/hub/BaseSpec.scala new-smederee/modules/hub/src/it/scala/de/smederee/hub/BaseSpec.scala --- old-smederee/modules/hub/src/it/scala/de/smederee/hub/BaseSpec.scala 2025-02-03 04:59:02.850649632 +0000 +++ new-smederee/modules/hub/src/it/scala/de/smederee/hub/BaseSpec.scala 2025-02-03 04:59:02.854649640 +0000 @@ -69,4 +69,130 @@ }.unsafeRunSync() } + /** Provide a resource with a database connection to allow db operations and proper resource release later. + * + * @param cfg + * The application configuration. + * @return + * A cats resource encapsulation a database connection as defined within the given configuration. + */ + protected def connectToDb(cfg: SmedereeHubConfig): Resource[IO, java.sql.Connection] = + Resource.make( + IO.delay(java.sql.DriverManager.getConnection(cfg.database.url, cfg.database.user, cfg.database.pass)) + )(c => IO.delay(c.close())) + + /** Create the given account in the database. + * + * @param account + * The account to be created. + * @param hash + * A password hash to be stored. + * @param unlockToken + * An optional unlock token to be stored. + * @param attempts + * Optional number of failed login attempts. + * @return + * The number of affected database rows. + */ + @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!") + protected def createAccount( + account: Account, + hash: PasswordHash, + unlockToken: Option[UnlockToken], + attempts: Option[Int] + ): IO[Int] = + connectToDb(configuration).use { con => + for { + statement <- IO.delay { + unlockToken match { + case None => + con.prepareStatement( + """INSERT INTO "accounts" (uid, name, email, password, failed_attempts, created_at, updated_at, verified_email) VALUES(?, ?, ?, ?, ?, NOW(), NOW(), ?)""" + ) + case Some(_) => + con.prepareStatement( + """INSERT INTO "accounts" (uid, name, email, password, failed_attempts, verified_email, locked_at, unlock_token, created_at, updated_at) VALUES(?, ?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())""" + ) + } + } + _ <- IO.delay(statement.setObject(1, account.uid)) + _ <- IO.delay(statement.setString(2, account.name.toString)) + _ <- IO.delay(statement.setString(3, account.email.toString)) + _ <- IO.delay(statement.setString(4, hash.toString)) + _ <- IO.delay(statement.setInt(5, attempts.getOrElse(1))) + _ <- IO.delay(statement.setBoolean(6, account.verifiedEmail)) + _ <- IO.delay { + unlockToken.foreach { token => + statement.setString(7, token.toString) + } + } + r <- IO.delay(statement.executeUpdate()) + _ <- IO.delay(statement.close()) + } yield r + } + + /** Create the given user session in the database. + * + * @param session + * The session that shall be created. The corresponding user account must exist! + * @return + * The number of affected database rows. + */ + @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!") + protected def createUserSession(session: Session): IO[Int] = + connectToDb(configuration).use { con => + for { + statement <- IO.delay( + con.prepareStatement( + """INSERT INTO "sessions" (id, uid, created_at, updated_at) VALUES(?, ?, ?, ?)""" + ) + ) + _ <- IO.delay(statement.setString(1, session.id.toString)) + _ <- IO.delay(statement.setObject(2, session.uid)) + _ <- IO.delay( + statement.setTimestamp(3, java.sql.Timestamp.from(session.createdAt.toInstant)) + ) + _ <- IO.delay( + statement.setTimestamp(4, java.sql.Timestamp.from(session.updatedAt.toInstant)) + ) + r <- IO.delay(statement.executeUpdate()) + _ <- IO.delay(statement.close()) + } yield r + } + + /** Load the account with the given uid from the database. + * + * @param uid + * The unique identifier for the account. + * @return + * An option to the account if it exists. + */ + @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!") + protected def loadAccount(uid: UserId): IO[Option[Account]] = + connectToDb(configuration).use { con => + for { + statement <- IO.delay( + con.prepareStatement( + """SELECT uid, name, email, password, created_at, updated_at, verified_email FROM "accounts" WHERE uid = ? LIMIT 1""" + ) + ) + _ <- IO.delay(statement.setObject(1, uid)) + result <- IO.delay(statement.executeQuery) + account <- IO.delay { + if (result.next()) { + Option( + Account( + uid = uid, + name = Username(result.getString("name")), + email = Email(result.getString("email")), + verifiedEmail = result.getBoolean("verified_email") + ) + ) + } else { + None + } + } + _ <- IO(statement.close()) + } yield account + } } diff -rN -u old-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieAuthenticationRepositoryTest.scala new-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieAuthenticationRepositoryTest.scala --- old-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieAuthenticationRepositoryTest.scala 2025-02-03 04:59:02.850649632 +0000 +++ new-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieAuthenticationRepositoryTest.scala 2025-02-03 04:59:02.854649640 +0000 @@ -36,89 +36,6 @@ val _ = flyway.clean() } - private def connectToDb(cfg: SmedereeHubConfig): Resource[IO, java.sql.Connection] = - Resource.make( - IO.delay(java.sql.DriverManager.getConnection(cfg.database.url, cfg.database.user, cfg.database.pass)) - )(c => IO.delay(c.close())) - - /** Create the given account in the database. - * - * @param account - * The account to be created. - * @param hash - * A password hash to be stored. - * @param unlockToken - * An optional unlock token to be stored. - * @param attempts - * Optional number of failed login attempts. - * @return - * The number of affected database rows. - */ - @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!") - private def createAccount( - account: Account, - hash: PasswordHash, - unlockToken: Option[UnlockToken], - attempts: Option[Int] - ): IO[Int] = - connectToDb(configuration).use { con => - for { - statement <- IO.delay { - unlockToken match { - case None => - con.prepareStatement( - """INSERT INTO "accounts" (uid, name, email, password, failed_attempts, created_at, updated_at) VALUES(?, ?, ?, ?, ?, NOW(), NOW())""" - ) - case Some(_) => - con.prepareStatement( - """INSERT INTO "accounts" (uid, name, email, password, failed_attempts, locked_at, unlock_token, created_at, updated_at) VALUES(?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())""" - ) - } - } - _ <- IO.delay(statement.setObject(1, account.uid)) - _ <- IO.delay(statement.setString(2, account.name.toString)) - _ <- IO.delay(statement.setString(3, account.email.toString)) - _ <- IO.delay(statement.setString(4, hash.toString)) - _ <- IO.delay(statement.setInt(5, attempts.getOrElse(1))) - _ <- IO.delay { - unlockToken.foreach { token => - statement.setString(6, token.toString) - } - } - r <- IO.delay(statement.executeUpdate()) - _ <- IO.delay(statement.close()) - } yield r - } - - /** Create the given user session in the database. - * - * @param session - * The session that shall be created. The corresponding user account must exist! - * @return - * The number of affected database rows. - */ - @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!") - private def createUserSession(session: Session): IO[Int] = - connectToDb(configuration).use { con => - for { - statement <- IO.delay( - con.prepareStatement( - """INSERT INTO "sessions" (id, uid, created_at, updated_at) VALUES(?, ?, ?, ?)""" - ) - ) - _ <- IO.delay(statement.setString(1, session.id.toString)) - _ <- IO.delay(statement.setObject(2, session.uid)) - _ <- IO.delay( - statement.setTimestamp(3, java.sql.Timestamp.from(session.createdAt.toInstant)) - ) - _ <- IO.delay( - statement.setTimestamp(4, java.sql.Timestamp.from(session.updatedAt.toInstant)) - ) - r <- IO.delay(statement.executeUpdate()) - _ <- IO.delay(statement.close()) - } yield r - } - test("createUserSession must create the user session") { (genValidSession.sample, genValidAccount.sample) match { case (Some(s), Some(account)) => diff -rN -u old-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieSignupRepositoryTest.scala new-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieSignupRepositoryTest.scala --- old-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieSignupRepositoryTest.scala 2025-02-03 04:59:02.850649632 +0000 +++ new-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieSignupRepositoryTest.scala 2025-02-03 04:59:02.854649640 +0000 @@ -35,68 +35,6 @@ val _ = flyway.clean() } - /** Create the given account in the database. - * - * @param account - * The account to be created. - */ - @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!") - private def createAccount(account: Account, hash: PasswordHash): IO[Int] = - for { - con <- IO.delay( - java.sql.DriverManager - .getConnection(configuration.database.url, configuration.database.user, configuration.database.pass) - ) - statement <- IO.delay( - con.prepareStatement( - """INSERT INTO "accounts" (uid, name, email, password, created_at, updated_at) VALUES(?, ?, ?, ?, NOW(), NOW())""" - ) - ) - _ <- IO.delay(statement.setObject(1, account.uid)) - _ <- IO.delay(statement.setString(2, account.name.toString)) - _ <- IO.delay(statement.setString(3, account.email.toString)) - _ <- IO.delay(statement.setString(4, hash.toString)) - r <- IO.delay(statement.executeUpdate()) - _ <- IO.delay(statement.close()) - _ <- IO.delay(con.close()) - } yield r - - /** Load the account with the given uid from the database. - * - * @param uid - * The unique identifier for the account. - * @return - * An option to the account if it exists. - */ - @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!") - private def loadAccount(uid: UserId): IO[Option[Account]] = - for { - con <- IO.delay( - java.sql.DriverManager - .getConnection(configuration.database.url, configuration.database.user, configuration.database.pass) - ) - statement <- IO.delay( - con.prepareStatement( - """SELECT uid, name, email, password, created_at, updated_at FROM "accounts" WHERE uid = ? LIMIT 1""" - ) - ) - _ <- IO.delay(statement.setObject(1, uid)) - result <- IO.delay(statement.executeQuery) - account <- IO.delay { - if (result.next()) { - Option( - Account( - uid = uid, - name = Username(result.getString("name")), - email = Email(result.getString("email")) - ) - ) - } else { - None - } - } - } yield account - test("createAccount must create a new account") { genValidAccount.sample match { case None => fail("Could not generate data samples!") @@ -125,7 +63,7 @@ val tx = Transactor.fromDriverManager[IO](dbConfig.driver, dbConfig.url, dbConfig.user, dbConfig.pass) val repo = new DoobieSignupRepository[IO](tx) val test = for { - c <- createAccount(existingAccount, PasswordHash("I am not a password hash!")) + c <- createAccount(existingAccount, PasswordHash("I am not a password hash!"), None, None) w <- repo.createAccount(newAccount, PasswordHash("I am not a password hash!")) } yield (c, w) test.attempt.map { @@ -149,7 +87,7 @@ val tx = Transactor.fromDriverManager[IO](dbConfig.driver, dbConfig.url, dbConfig.user, dbConfig.pass) val repo = new DoobieSignupRepository[IO](tx) val test = for { - c <- createAccount(existingAccount, PasswordHash("I am not a password hash!")) + c <- createAccount(existingAccount, PasswordHash("I am not a password hash!"), None, None) w <- repo.createAccount(newAccount, PasswordHash("I am not a password hash!")) } yield (c, w) test.attempt.map { diff -rN -u old-smederee/modules/hub/src/it/scala/de/smederee/hub/Generators.scala new-smederee/modules/hub/src/it/scala/de/smederee/hub/Generators.scala --- old-smederee/modules/hub/src/it/scala/de/smederee/hub/Generators.scala 2025-02-03 04:59:02.850649632 +0000 +++ new-smederee/modules/hub/src/it/scala/de/smederee/hub/Generators.scala 2025-02-03 04:59:02.854649640 +0000 @@ -87,10 +87,11 @@ } yield Username(prefix.toString + chars) val genValidAccount: Gen[Account] = for { - id <- genUserId - email <- genValidEmail - name <- genValidUsername - } yield Account(uid = id, name = name, email = email) + id <- genUserId + email <- genValidEmail + name <- genValidUsername + verifiedEmail <- Gen.oneOf(List(false, true)) + } yield Account(uid = id, name = name, email = email, verifiedEmail = verifiedEmail) given Arbitrary[Account] = Arbitrary(genValidAccount) diff -rN -u old-smederee/modules/hub/src/main/resources/db/migration/V1__base_tables.sql new-smederee/modules/hub/src/main/resources/db/migration/V1__base_tables.sql --- old-smederee/modules/hub/src/main/resources/db/migration/V1__base_tables.sql 2025-02-03 04:59:02.850649632 +0000 +++ new-smederee/modules/hub/src/main/resources/db/migration/V1__base_tables.sql 2025-02-03 04:59:02.854649640 +0000 @@ -11,6 +11,7 @@ "reset_token" TEXT DEFAULT NULL, "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, + "verified_email" BOOLEAN DEFAULT FALSE, CONSTRAINT "accounts_pk" PRIMARY KEY ("uid"), CONSTRAINT "accounts_unique_name" UNIQUE ("name"), CONSTRAINT "accounts_unique_email" UNIQUE ("email") @@ -31,6 +32,7 @@ COMMENT ON COLUMN "accounts"."reset_token" IS 'A token which can be used for a password reset.'; COMMENT ON COLUMN "accounts"."created_at" IS 'The timestamp of when the account was created.'; COMMENT ON COLUMN "accounts"."updated_at" IS 'A timestamp when the account was last changed.'; +COMMENT ON COLUMN "accounts"."verified_email" IS 'This flag indicates if the email address of the user has been verified via a verification email.'; CREATE TABLE "sessions" ( diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/Account.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/Account.scala --- old-smederee/modules/hub/src/main/scala/de/smederee/hub/Account.scala 2025-02-03 04:59:02.850649632 +0000 +++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/Account.scala 2025-02-03 04:59:02.854649640 +0000 @@ -338,8 +338,10 @@ * A unique name which can be used for login and to identify the user. * @param email * The email address of the user. + * @param verifiedEmail + * This flag indicates if the email address of the user has been verified via a verification email. */ -final case class Account(uid: UserId, name: Username, email: Email) +final case class Account(uid: UserId, name: Username, email: Email, verifiedEmail: Boolean) object Account { given Eq[Account] = diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala --- old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala 2025-02-03 04:59:02.850649632 +0000 +++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala 2025-02-03 04:59:02.854649640 +0000 @@ -28,7 +28,7 @@ private val lockedFilter = fr"""locked_at IS NOT NULL""" private val notLockedFilter = fr"""locked_at IS NULL""" - private val selectAccountColumns = fr"""SELECT uid, name, email FROM "accounts"""" + private val selectAccountColumns = fr"""SELECT uid, name, email, verified_email FROM "accounts"""" override def createUserSession(session: Session): F[Int] = sql"""INSERT INTO "sessions" (id, uid, created_at, updated_at) VALUES (${session.id}, ${session.uid}, ${session.createdAt}, ${session.updatedAt})""".update.run diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala --- old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala 2025-02-03 04:59:02.850649632 +0000 +++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala 2025-02-03 04:59:02.854649640 +0000 @@ -24,8 +24,7 @@ given Meta[Username] = Meta[String].timap(Username.apply)(_.toString) override def createAccount(account: Account, hash: PasswordHash): F[Int] = - sql"""INSERT INTO "accounts" (uid, name, email, password, created_at, updated_at) - VALUES(${account.uid}, ${account.name}, ${account.email}, $hash, NOW(), NOW())""".update.run + sql"""INSERT INTO "accounts" (uid, name, email, password, created_at, updated_at, verified_email) VALUES(${account.uid}, ${account.name}, ${account.email}, $hash, NOW(), NOW(), ${account.verifiedEmail})""".update.run .transact(tx) override def findEmail(address: Email): F[Option[Email]] = diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/SignupRoutes.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/SignupRoutes.scala --- old-smederee/modules/hub/src/main/scala/de/smederee/hub/SignupRoutes.scala 2025-02-03 04:59:02.854649640 +0000 +++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/SignupRoutes.scala 2025-02-03 04:59:02.854649640 +0000 @@ -99,7 +99,12 @@ for { uid <- Sync[F].delay(UserId.randomUserId) account <- Sync[F].delay( - Account(uid = uid, name = signupForm.name, email = signupForm.email) + Account( + uid = uid, + name = signupForm.name, + email = signupForm.email, + verifiedEmail = false + ) ) hash <- Sync[F].delay(signupForm.password.encode) _ <- Sync[F].delay(log.info(s"Going to create account for ${account.name}.")) diff -rN -u old-smederee/modules/hub/src/test/scala/de/smederee/hub/Generators.scala new-smederee/modules/hub/src/test/scala/de/smederee/hub/Generators.scala --- old-smederee/modules/hub/src/test/scala/de/smederee/hub/Generators.scala 2025-02-03 04:59:02.854649640 +0000 +++ new-smederee/modules/hub/src/test/scala/de/smederee/hub/Generators.scala 2025-02-03 04:59:02.854649640 +0000 @@ -87,10 +87,11 @@ } yield Username(prefix.toString + chars) val genValidAccount: Gen[Account] = for { - id <- genUserId - email <- genValidEmail - name <- genValidUsername - } yield Account(uid = id, name = name, email = email) + id <- genUserId + email <- genValidEmail + name <- genValidUsername + verifiedEmail <- Gen.oneOf(List(false, true)) + } yield Account(uid = id, name = name, email = email, verifiedEmail = verifiedEmail) given Arbitrary[Account] = Arbitrary(genValidAccount)