Merge pull request #23 from sebadob/users-language-column

add language column to users table
sebadob · Aug 15, 2023 · 7517693 · 7517693
2 parents cb5cead + 3678ef2
commit 7517693
Show file tree

Hide file tree

Showing 5 changed files with 207 additions and 18 deletions.
diff --git a/justfile b/justfile
@@ -33,7 +33,7 @@ run-sqlite:
 
 # runs the application with postgres feature
 run-postgres:
-    ATABASE_URL={{db_url_postgres}} cargo run --target x86_64-unknown-linux-musl --features postgres
+    DATABASE_URL={{db_url_postgres}} cargo run --target x86_64-unknown-linux-musl --features postgres
 
 
 # runs the UI in development mode

diff --git a/migrations/postgres/6_user_language.sql b/migrations/postgres/6_user_language.sql
@@ -0,0 +1,2 @@
+alter table users
+    add language varchar default 'en' not null;
diff --git a/migrations/sqlite/6_user_language.sql b/migrations/sqlite/6_user_language.sql
@@ -0,0 +1,187 @@
+alter table users
+    rename to users_old;
+
+drop index users_email_uindex;
+
+create table users
+(
+    id                    varchar              not null
+        constraint users_pk
+            primary key,
+    email                 varchar              not null
+        constraint users_email
+            unique,
+    given_name            varchar              not null,
+    family_name           varchar              not null,
+    password              varchar,
+    roles                 varchar              not null,
+    groups                varchar,
+    enabled               boolean              not null,
+    email_verified        boolean              not null,
+    password_expires      int,
+    created_at            int                  not null,
+    last_login            int,
+    last_failed_login     int,
+    failed_login_attempts int,
+    mfa_app               varchar,
+    sec_key_1             varchar
+        references webauthn,
+    sec_key_2             varchar
+        references webauthn,
+    language              varchar default 'en' not null
+);
+
+create unique index users_email_uindex
+    on users (email);
+
+insert into users(id, email, given_name, family_name, password, roles, groups, enabled, email_verified,
+                  password_expires, created_at, last_login, last_failed_login, failed_login_attempts, mfa_app,
+                  sec_key_1, sec_key_2)
+select id,
+       email,
+       given_name,
+       family_name,
+       password,
+       roles,
+       groups,
+       enabled,
+       email_verified,
+       password_expires,
+       created_at,
+       last_login,
+       last_failed_login,
+       failed_login_attempts,
+       mfa_app,
+       sec_key_1,
+       sec_key_2
+from users_old;
+
+-- recreate all tables with foreign keys to users
+
+alter table magic_links
+    rename to magic_links_old;
+
+drop index magic_links_exp_index;
+drop index magic_links_user_id_index;
+
+create table magic_links
+(
+    id         varchar not null
+        constraint magic_links_pk
+            primary key,
+    user_id    varchar not null
+        references users
+            on delete cascade
+            on update cascade,
+    csrf_token varchar not null,
+    cookie     varchar,
+    exp        integer not null,
+    used       bool    not null
+);
+
+create index magic_links_exp_index
+    on magic_links (exp);
+
+create index magic_links_user_id_index
+    on magic_links (user_id);
+
+insert into magic_links(id, user_id, csrf_token, cookie, exp, used)
+select id, user_id, csrf_token, cookie, exp, used
+from magic_links_old;
+
+drop table magic_links_old;
+
+--
+
+alter table recent_passwords
+    rename to recent_passwords_old;
+
+create table recent_passwords
+(
+    user_id   varchar not null
+        references users
+            on delete cascade
+            on update cascade
+        constraint recent_passwords_pk
+            primary key,
+    passwords varchar not null
+);
+
+insert into recent_passwords(user_id, passwords)
+select user_id, passwords
+from recent_passwords_old;
+
+drop table recent_passwords_old;
+
+--
+
+alter table refresh_tokens
+    rename to refresh_tokens_old;
+
+drop index refresh_tokens_exp_index;
+drop index refresh_tokens_user_id_index;
+
+create table refresh_tokens
+(
+    id      varchar not null
+        constraint refresh_tokens_pk
+            primary key,
+    user_id varchar not null
+        references users
+            on delete cascade
+            on update cascade,
+    nbf     integer not null,
+    exp     integer not null,
+    scope   varchar,
+    is_mfa bool default false not null
+);
+
+create index refresh_tokens_exp_index
+    on refresh_tokens (exp);
+
+create index refresh_tokens_user_id_index
+    on refresh_tokens (user_id);
+
+insert into refresh_tokens(id, user_id, nbf, exp, scope, is_mfa)
+select id, user_id, nbf, exp, scope, is_mfa
+from refresh_tokens_old;
+
+drop table refresh_tokens_old;
+
+--
+
+alter table sessions
+    rename to sessions_old;
+
+drop index sessions_exp_index;
+
+create table sessions
+(
+    id         varchar not null
+        constraint sessions_pk
+            primary key,
+    csrf_token varchar not null,
+    user_id    varchar
+        references users
+            on delete cascade
+            on update cascade,
+    roles      varchar,
+    groups     varchar,
+    is_mfa     bool    not null,
+    state      varchar not null,
+    exp        int     not null,
+    last_seen  int     not null
+);
+
+create index sessions_exp_index
+    on sessions (exp);
+
+insert into sessions(id, csrf_token, user_id, roles, groups, is_mfa, state, exp, last_seen)
+select id, csrf_token, user_id, roles, groups, is_mfa, state, exp, last_seen
+from sessions_old;
+
+drop table sessions_old;
+
+-- finally drop the old users table
+
+drop table users_old;
diff --git a/rauthy-models/src/entity/users.rs b/rauthy-models/src/entity/users.rs
@@ -9,6 +9,7 @@ use crate::entity::refresh_tokens::RefreshToken;
 use crate::entity::roles::Role;
 use crate::entity::sessions::Session;
 use crate::entity::webauthn::PasskeyEntity;
+use crate::language::Language;
 use crate::request::{
     NewUserRegistrationRequest, NewUserRequest, UpdateUserRequest, UpdateUserSelfRequest,
 };
@@ -44,6 +45,7 @@ pub struct User {
     pub mfa_app: Option<String>,
     pub sec_key_1: Option<String>,
     pub sec_key_2: Option<String>,
+    pub language: String,
 }
 
 // CRUD
@@ -52,8 +54,9 @@ impl User {
     pub async fn create(data: &web::Data<AppState>, new_user: User) -> Result<Self, ErrorResponse> {
         sqlx::query!(
             r#"insert into users
-            (id, email, given_name, family_name, roles, groups, enabled, email_verified, created_at)
-            values ($1, $2, $3, $4, $5, $6, $7, $8, $9)"#,
+            (id, email, given_name, family_name, roles, groups, enabled, email_verified, created_at,
+            language)
+            values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)"#,
             new_user.id,
             new_user.email,
             new_user.given_name,
@@ -63,6 +66,7 @@ impl User {
             new_user.enabled,
             new_user.email_verified,
             new_user.created_at,
+            new_user.language,
         )
         .execute(&data.db)
         .await?;
@@ -288,7 +292,7 @@ impl User {
             email = $1, given_name = $2, family_name = $3, password = $4, roles = $5, groups = $6,
             enabled = $7, email_verified = $8, password_expires = $9, created_at = $10, last_login = $11,
             last_failed_login = $12, failed_login_attempts = $13, mfa_app = $14, sec_key_1 = $15,
-            sec_key_2 = $16 where id = $17"#)
+            sec_key_2 = $16, language = $17 where id = $18"#)
             .bind(&self.email)
             .bind(&self.given_name)
             .bind(&self.family_name)
@@ -305,6 +309,7 @@ impl User {
             .bind(&self.mfa_app)
             .bind(&self.sec_key_1)
             .bind(&self.sec_key_2)
+            .bind(&self.language)
             .bind(&self.id);
 
         if let Some(txn) = txn {
@@ -957,6 +962,7 @@ impl Default for User {
             mfa_app: None,
             sec_key_1: None,
             sec_key_2: None,
+            language: Language::En.to_string(),
         }
     }
 }
@@ -987,6 +993,7 @@ mod tests {
             mfa_app: None,
             sec_key_1: None,
             sec_key_2: None,
+            language: Language::En.to_string(),
         };
         let session = Session::new(Some(&user), 1);
 
@@ -1042,6 +1049,7 @@ mod tests {
             mfa_app: None,
             sec_key_1: None,
             sec_key_2: None,
+            language: Language::En.to_string(),
         };
 
         // enabled

diff --git a/rauthy-models/src/language.rs b/rauthy-models/src/language.rs
@@ -22,20 +22,6 @@ impl Language {
             Language::De => "de",
         }
     }
-
-    // pub fn set_cookie_str(&self) -> (&'static str, HeaderValue) {
-    //     (
-    //         "Set-Cookie",
-    //         HeaderValue::from_str(&format!(
-    //             "{}={};Path=/;Max-Age={};SameSite=Lax",
-    //             // "{}={};Path=/;Max-Age={};SameSite=Lax;Secure=true",
-    //             LOCALE_COOKIE,
-    //             self.as_str(),
-    //             15552000
-    //         ))
-    //             .unwrap(),
-    //     )
-    // }
 }
 
 impl Default for Language {
@@ -44,6 +30,12 @@ impl Default for Language {
     }
 }
 
+impl ToString for Language {
+    fn to_string(&self) -> String {
+        self.as_str().to_string()
+    }
+}
+
 impl From<&HeaderValue> for Language {
     fn from(value: &HeaderValue) -> Self {
         Language::from(value.to_str().unwrap_or_default())
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		alter table users
		add language varchar default 'en' not null;