From 67d64ec4b368c48188c746f2dba2967ec4615fe5 Mon Sep 17 00:00:00 2001 From: Ellie Huxtable Date: Mon, 24 Jun 2024 14:54:54 +0100 Subject: feat: add user account verification (#2190) * add verified column to users table * add database functions to check if verified, or to verify * getting there * verification check * use base64 urlsafe no pad * add verification client * clippy * correct docs * fix integration tests --- .../migrations/20240621110731_user-verified.sql | 8 ++ crates/atuin-server-postgres/src/lib.rs | 99 ++++++++++++++++++++-- crates/atuin-server-postgres/src/wrappers.rs | 1 + 3 files changed, 100 insertions(+), 8 deletions(-) create mode 100644 crates/atuin-server-postgres/migrations/20240621110731_user-verified.sql (limited to 'crates/atuin-server-postgres') diff --git a/crates/atuin-server-postgres/migrations/20240621110731_user-verified.sql b/crates/atuin-server-postgres/migrations/20240621110731_user-verified.sql new file mode 100644 index 00000000..6eba02ec --- /dev/null +++ b/crates/atuin-server-postgres/migrations/20240621110731_user-verified.sql @@ -0,0 +1,8 @@ +alter table users add verified_at timestamp with time zone default null; + +create table user_verification_token( + id bigserial primary key, + user_id bigint unique references users(id), + token text, + valid_until timestamp with time zone +); diff --git a/crates/atuin-server-postgres/src/lib.rs b/crates/atuin-server-postgres/src/lib.rs index 8a010195..7aa87424 100644 --- a/crates/atuin-server-postgres/src/lib.rs +++ b/crates/atuin-server-postgres/src/lib.rs @@ -3,6 +3,7 @@ use std::ops::Range; use async_trait::async_trait; use atuin_common::record::{EncryptedData, HostId, Record, RecordIdx, RecordStatus}; +use atuin_common::utils::crypto_random_string; use atuin_server_database::models::{History, NewHistory, NewSession, NewUser, Session, User}; use atuin_server_database::{Database, DbError, DbResult}; use futures_util::TryStreamExt; @@ -11,7 +12,7 @@ use sqlx::postgres::PgPoolOptions; use sqlx::Row; use time::{OffsetDateTime, PrimitiveDateTime, UtcOffset}; -use tracing::instrument; +use tracing::{instrument, trace}; use uuid::Uuid; use wrappers::{DbHistory, DbRecord, DbSession, DbUser}; @@ -100,18 +101,100 @@ impl Database for Postgres { #[instrument(skip_all)] async fn get_user(&self, username: &str) -> DbResult { - sqlx::query_as("select id, username, email, password from users where username = $1") - .bind(username) - .fetch_one(&self.pool) - .await - .map_err(fix_error) - .map(|DbUser(user)| user) + sqlx::query_as( + "select id, username, email, password, verified_at from users where username = $1", + ) + .bind(username) + .fetch_one(&self.pool) + .await + .map_err(fix_error) + .map(|DbUser(user)| user) + } + + #[instrument(skip_all)] + async fn user_verified(&self, id: i64) -> DbResult { + let res: (bool,) = + sqlx::query_as("select verified_at is not null from users where id = $1") + .bind(id) + .fetch_one(&self.pool) + .await + .map_err(fix_error)?; + + Ok(res.0) + } + + #[instrument(skip_all)] + async fn verify_user(&self, id: i64) -> DbResult<()> { + sqlx::query( + "update users set verified_at = (current_timestamp at time zone 'utc') where id=$1", + ) + .bind(id) + .execute(&self.pool) + .await + .map_err(fix_error)?; + + Ok(()) + } + + /// Return a valid verification token for the user + /// If the user does not have any token, create one, insert it, and return + /// If the user has a token, but it's invalid, delete it, create a new one, return + /// If the user already has a valid token, return it + #[instrument(skip_all)] + async fn user_verification_token(&self, id: i64) -> DbResult { + const TOKEN_VALID_MINUTES: i64 = 15; + + // First we check if there is a verification token + let token: Option<(String, sqlx::types::time::OffsetDateTime)> = sqlx::query_as( + "select token, valid_until from user_verification_token where user_id = $1", + ) + .bind(id) + .fetch_optional(&self.pool) + .await + .map_err(fix_error)?; + + let token = if let Some((token, valid_until)) = token { + trace!("Token for user {id} valid until {valid_until}"); + + // We have a token, AND it's still valid + if valid_until > time::OffsetDateTime::now_utc() { + token + } else { + // token has expired. generate a new one, return it + let token = crypto_random_string::<24>(); + + sqlx::query("update user_verification_token set token = $2, valid_until = $3 where user_id=$1") + .bind(id) + .bind(&token) + .bind(time::OffsetDateTime::now_utc() + time::Duration::minutes(TOKEN_VALID_MINUTES)) + .execute(&self.pool) + .await + .map_err(fix_error)?; + + token + } + } else { + // No token in the database! Generate one, insert it + let token = crypto_random_string::<24>(); + + sqlx::query("insert into user_verification_token (user_id, token, valid_until) values ($1, $2, $3)") + .bind(id) + .bind(&token) + .bind(time::OffsetDateTime::now_utc() + time::Duration::minutes(TOKEN_VALID_MINUTES)) + .execute(&self.pool) + .await + .map_err(fix_error)?; + + token + }; + + Ok(token) } #[instrument(skip_all)] async fn get_session_user(&self, token: &str) -> DbResult { sqlx::query_as( - "select users.id, users.username, users.email, users.password from users + "select users.id, users.username, users.email, users.password, users.verified_at from users inner join sessions on users.id = sessions.user_id and sessions.token = $1", diff --git a/crates/atuin-server-postgres/src/wrappers.rs b/crates/atuin-server-postgres/src/wrappers.rs index 3ccf9c19..80f95f21 100644 --- a/crates/atuin-server-postgres/src/wrappers.rs +++ b/crates/atuin-server-postgres/src/wrappers.rs @@ -16,6 +16,7 @@ impl<'a> FromRow<'a, PgRow> for DbUser { username: row.try_get("username")?, email: row.try_get("email")?, password: row.try_get("password")?, + verified: row.try_get("verified_at")?, })) } } -- cgit v1.3.1