Repository: sebadob/rauthy (language: rust)

Issue:
After a user registers, the uid does not exists, but the email is occupied.
I have a case where after a user registers, they receive an email to reset their password, but it returns a 404 "no rows returned".
I also cannot view this user's information in the user management interface, as the API returns a 404.

This issue occurs with Rauthy version 0.27.3 + Hiqlite, deployed on Kubernetes.

![image](https://github.com/user-attachments/assets/80a507ae-a074-4bc1-ba94-411100989a99)
![image](https://github.com/user-attachments/assets/455b52a4-44c4-4a23-8eb0-28d0e597e599)




Discussion / hints:
I can't reproduce this error. I need to know the exact steps and more information about it.

> I have a case where after a user registers, they receive an email to reset their password, but it returns a 404 "no rows returned".

This on it's own makes sense, if the magic link has expired in the meantime.

> I also cannot view this user's information in the user management interface, as the API returns a 404.

This on top though is super weird.

Does it happen for all registrations or only for very specific inputs? Do you have any errors in the logs? Is it consistent and reproducible?

The issue is, that this error does not make too much sense to me right now. You see the user in the UI, which means the row can be found of course. The overview uses the same dataset under the hood. The only other query that will be done is one to the `users_values` table, which takes care of possibly existing additional values. However, this query uses `query_as_optional` / `fetch_optional` and therefore can't return "no rows".

If you have the possibility, it would be very helpful if you could set `HQL_LOG_STATEMENTS=true` to see which query exactly produces this error, because it can't be the one to `users` which just worked. Then it may only be something with cached data.

If you set `HQL_LOG_STATEMENTS=true` though, please keep in mind that this might log sensitive data.
Something else that came to my mind is: do you have the same issue after restarting Rauthy? Because if not, it's related to caching.
@sebadob 
`HQL_LOG_STATEMENTS=true`

It's quite strange; when I query a user by their ID, the result is empty, but when I perform a query in the list of users, the user does appear.
```
2025-01-08T09:36:34.302911Z INFO hiqlite::store::state_machine::sqlite::writer: Query::ExecuteReturning:
SELECT 1
[]
2025-01-08T09:36:35.353386Z INFO hiqlite::store::state_machine::sqlite::writer: Query::ExecuteReturning:
SELECT 1
[]
2025-01-08T09:36:35.556977Z INFO hiqlite::store::state_machine::sqlite::writer: Query::Execute:
INSERT INTO
sessions (id, csrf_token, user_id, roles, groups, is_mfa, state, exp, last_seen, remote_ip)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
ON CONFLICT(id) DO UPDATE
SET user_id = $3, roles = $4, groups = $5, is_mfa = $6, state = $7, exp = $8, last_seen = $9,
remote_ip = $10
[Text("31AzgnGA3K1Mo3BfyGrhod27gcMu9kE9"), Text("mCtHbcerTDFkiaUQiKvXjxBGJOzED4gi"), Text("za9UxpH7XVxqrtpEbThoqvn2"), Text("admin,rauthy_admin"), Text("admin"), Integer(1), Text("auth"), Integer(1736336480), Integer(1736328995), Text("10.42.0.1")]
2025-01-08T09:36:35.557412Z INFO hiqlite::query: query_as:
SELECT * FROM users WHERE id = $1
[Text("za9UxpH7XVxqrtpEbThoqvn2")]
2025-01-08T09:36:35.557739Z INFO hiqlite::query: query_as:
SELECT * FROM users_values WHERE id = $1
[Text("za9UxpH7XVxqrtpEbThoqvn2")]
2025-01-08T09:36:35.915251Z INFO hiqlite::store::state_machine::sqlite::writer: Query::Execute:
INSERT INTO config (id, data) VALUES ('latest_version', $1)
ON CONFLICT(id) DO UPDATE SET data = $1
[Blob([35, 71, 126, 103, 0, 0, 0, 0, 6, 0, 0, 0, 0, 0, 0, 0, 48, 46, 50, 55, 46, 51, 54, 0, 0, 0, 0, 0, 0, 0, 104, 116, 116, 112, 115, 58, 47, 47, 103, 105, 116, 104, 117, 98, 46, 99, 111, 109, 47, 115, 101, 98, 97, 100, 111, 98, 47, 114, 97, 117, 116, 104, 121, 47, 114, 101, 108, 101, 97, 115, 101, 115, 47, 116, 97, 103, 47, 118, 48, 46, 50, 55, 46, 51])]
2025-01-08T09:36:36.180180Z INFO hiqlite::store::state_machine::sqlite::writer: Query::Execute:
INSERT INTO config (id, data) VALUES ('latest_version', $1)
ON CONFLICT(id) DO UPDATE SET data = $1
[Blob([36, 71, 126, 103, 0, 0, 0, 0, 6, 0, 0, 0, 0, 0, 0, 0, 48, 46, 50, 55, 46, 51, 54, 0, 0, 0, 0, 0, 0, 0, 104, 116, 116, 112, 115, 58, 47, 47, 103, 105, 116, 104, 117, 98, 46, 99, 111, 109, 47, 115, 101, 98, 97, 100, 111, 98, 47, 114, 97, 117, 116, 104, 121, 47, 114, 101, 108, 101, 97, 115, 101, 115, 47, 116, 97, 103, 47, 118, 48, 46, 50, 55, 46, 51])]
2025-01-08T09:36:42.534777Z INFO hiqlite::query: query_as:
SELECT * FROM users WHERE id = $1
[Text("zfIVZSvMYpOyj2cLFhHD8ZQe")]

```
> Something else that came to my mind is: do you have the same issue after restarting Rauthy? Because if not, it's related to caching.


I restarted after setting HQL_LOG_STATEMENTS=true, but I'm still getting the same result.
I need to know which of the queries produces the error. 

You posted 

```
2025-01-08T09:36:35.557412Z INFO hiqlite::query: query_as:
SELECT * FROM users WHERE id = $1
[Text("za9UxpH7XVxqrtpEbThoqvn2")]
2025-01-08T09:36:35.557739Z INFO hiqlite::query: query_as:
SELECT * FROM users_values WHERE id = $1
[Text("za9UxpH7XVxqrtpEbThoqvn2")]
```

Which means the query to the `users` table must have been successful, because otherwise the 2nd would not have been executed. They don't run in parallel.
Does this mean you see an Error log directly after the 

```
SELECT * FROM users_values WHERE id = $1
[Text("za9UxpH7XVxqrtpEbThoqvn2")]
```
?

Btw I hope this is a test instance, because the session query just leaked id + csrf token.
@sebadob 
zfIVZSvMYpOyj2cLFhHD8ZQe is bad
za9UxpH7XVxqrtpEbThoqvn2 is admin user

The query id = 'zfIVZSvMYpOyj2cLFhHD8ZQe ' on the users table returns empty, and no subsequent queries are executed.
> The query id = 'zfIVZSvMYpOyj2cLFhHD8ZQe ' on the users table returns empty, and no subsequent queries are executed.

But it's still returned on GET `/users` in the overview, even after a restart?
Are you running a single instance, or a HA deployment?
> Are you running a single instance, or a HA deployment?

single instance
Restarting still results in an error.
has any cli tool for hiqlite？
> has any cli tool for hiqlite？

Even better, a dashboard. ;)

There was a bug with the cookie though which I fixed just now for Hiqlite. This still exists in rauthy 0.27.3 though which makes it probably impossible to log in to a remote dashboard via TLS. But, if you do it via a `kubectl port-forward` and use insecure cookies, it should work just fine.

All you need to do is set `HQL_PASSWORD_DASHBOARD` to make it work, like mentioned in the [migration docs](https://github.com/sebadob/rauthy/releases/tag/v0.27.0) -> **Hiqlite Dashboard** section.

As mentioned, it will probably not work via TLS right now because of a missing `path` to the secure cookie, but you can set `HQL_INSECURE_COOKIE=true` and use `kubectl port-forward` to make it work on your localhost. This will be fixed with the next rauthy release.

With it you should be able to easily inspect the database. This would be helpful, because something about this error is super weird and I can't debug it without being able to reproduce it.

Edit:

Forgot to mention, the dashboard is not exposed on Rauthys API / port, but on the hiqlite internal API. By default, it would be `localhost:8200`
>localhost:8200

curl: (1) Received HTTP/0.9 when not allowed

use example password
```
# The password for the Hiqlite dashboard as Argon2ID hash.
# '123SuperMegaSafe' in this example
HQL_PASSWORD_DASHBOARD=JGFyZ29uMmlkJHY9MTkkbT0zMix0PTIscD0xJE9FbFZURnAwU0V0bFJ6ZFBlSEZDT0EkTklCN0txTy8vanB4WFE5bUdCaVM2SlhraEpwaWVYOFRUNW5qdG9wcXkzQQ==

```
If you don't see a log at startup like `HQL_PASSWORD_DASHBOARD has not been set and the dashboard will be disabled`, something with your network setup is probably wrong.

You can make sure that you reach the API via `/ping` or `/health`. These e.g. are always exposed.
```


2025-01-08T10:57:48.260487Z INFO openraft::engine::engine_impl: openraft::engine::engine_impl::Engine<_>::handle_vote_resp resp={T1-N1:committed, last_log:None} target=1 my_vote=T1-N1:committed my_last_log_id=Some(T1-N1-1)
--
Wed, Jan 8 2025 6:57:48 pm | 2025-01-08T10:57:48.260520Z INFO hiqlite::start: rpc internal listening on 0.0.0.0:8100
Wed, Jan 8 2025 6:57:48 pm | 2025-01-08T10:57:48.261500Z INFO hiqlite::start: api external listening on 0.0.0.0:8200
Wed, Jan 8 2025 6:57:48 pm | 2025-01-08T10:57:48.262305Z INFO become_cluster_member{raft_type=Sqlite this_node=1 is_pristine_cache_node_1=false election_timeout_max=1500}: hiqlite::init: sqlite raft is already initialized - skipping become_cluster_member()
Wed, Jan 8 2025 6:57:48 pm | 2025-01-08T10:57:48.262316Z INFO become_cluster_member{raft_type=Cache this_node=1 is_pristine_cache_node_1=true election_timeout_max=1500}: hiqlite::init: cache raft is already initialized - skipping become_cluster_member()
Wed, Jan 8 2025 6:57:48 pm | 2025-01-08T10:57:48.262389Z INFO hiqlite::client::stream: Client API WebSocket trying to connect to: https://localhost:8200/stream/cache
Wed, Jan 8 2025 6:57:48 pm | 2025-01-08T10:57:48.262509Z INFO hiqlite::client::stream: Client API WebSocket trying to connect to: https://localhost:8200/stream/sqlite
Wed, Jan 8 2025 6:57:48 pm | 2025-01-08T10:57:48.262530Z INFO hiqlite::backup: Backup cron task started
Wed, Jan 8 2025 6:57:48 pm | 2025-01-08T10:57:48.262621Z INFO rauthy_models::app_state: Listen URL: http://0.0.0.0:8080

```
Can only localhost access the hiqlite dashboard?


> Can only localhost access the hiqlite dashboard?

No, it's exposed on `0.0.0.0` by default, as you can see in the logs.
![image](https://github.com/user-attachments/assets/797de425-d88e-45f2-827c-1c5f200ce5ec)
success query , but  rauthy  still response 404
> success query , but rauthy still response 404

That does not make any sense to me, since Rauthy is executing the exact same query and I cannot reproduce this at all.

Can you reproduce this behavior, even locally? If so, could you please note down the exact steps on how I can get into that situation?
access register page
/auth/v1/users/register

Users are consistently encountering a 404 error when registering on their own. However, there are no issues when adding them through the console.

Wait a moment, I will remove the sensitive information and then send out the configuration file that will be used.


I don't think that the config is the issue. This should never happen, regardless of the config. In fact, I never encountered this and have no way to reproduce it.

The best help would be, if you can create a config / setup that you can show and will produce the same behavior on localhost, because otherwise I cannot debug this further. I need to be able to get into the same situation while having access to the code and being able to set debug breakpoints.
Reproduce locally, pay attention to setting the SMTP parameters.


`podman run -d -p 8088:8080 -e PUB_URL=localhost:8088 -e COOKIE_MODE=danger-insecure -e SMTP_URL=xxx -e SMTP_USERNAME=xxx -e SMTP_PASSWORD=xxx -e EMAIL_SUB_PREFIX="IAM" -e OPEN_USER_REG=true -e USER_REG_DOMAIN_RESTRICTION=email.domain ghcr.io/sebadob/rauthy:0.27.3`
> Reproduce locally, pay attention to setting the SMTP parameters.

Works as expected, without any issues. The only thing I needed to add was the `SMTP_FROM` because of my very strict smtp server. And I changed the `USER_REG_DOMAIN_RESTRICTION` to another domain to make it actually work.

Edit:

And I used `docker` instead, but this should not change anything in this context.
It's really strange. Since rauthy can't be debugged on Windows, I will run and test it natively on the server tomorrow to see why it keeps returning 404.

> Since rauthy can't be debugged on Windows

What do you mean with that? It should build just fine on Windows (I guess).

Do you maybe have some weird reverse proxy setup with path rewrites or things like that?
 `sha2-asm`  unsupported msvc

> sha2-asm unsupported msvc

Ahh, okay. Coming from `hiqlite`, right? I can make this optional for the next release, good to know.
I'm busy with other things today and can't debug the code.

It seems I forgot to explain my steps above.

1. Start rauthy using podman or docker and enable registration (I tested the pg deployment, and there is no issue there).
2. Register an account on the "/auth/v1/users/register" page.
3. Log in to your email and click "Set Password," or log in to the admin account and click on the newly registered user on the "/users" page.
4. It results in a 404 error and the page logic fails, but refreshing the page restores it.


Additionally, there is another difference between us: my system language is Chinese, and the emails I receive are all in Chinese. I can't think of any other differences.

> Additionally, there is another difference between us: my system language is Chinese, and the emails I receive are all in Chinese. I can't think of any other differences.

That's it! If I overwrite my locale in the UI and set it to `ZH`, I can reproduce the issue.
Now I need to figure out why this happens...
So, it's about the language actually. It fails to find the user and build the proper internal `Language` struct if it's set to `zh-Hans`. If I simply switch it to `en` and restart Rauthy (to clear caches), everything works again.

Edit:

On the short term, just to fix your current broken users again, you can execute the following query:

```sql
UPDATE users SET language = 'en' WHERE language = 'zh-Hans';
```

I will build a fix that is way more stable in future versions, and will take care of the `Language` deserialization manually, but this will require a new nightly build for testing of course, and the UI is a bit messy right now, at least until I am done with the current round of CSS fixes, so it may take a few days.
Until then, it would be the best idea if users use `en` as the language, at least until the issue is resolved.
What also works for both DBs is `zhhans` as column value. Hiqlite uses `serde` under the hood to deserialize column values and the `enum` is `Language::ZhHans` with a `lowercase` conversion. However, when the value is inserted into the DB, the `str` is built manually as `zh-Hans`, which later on fails to deserialize.

It seems that the `sqlx::Type` derive tries a lot more cases under the hood, because it works in any situation, which is a bit werid (and probably throws performance out of the window). The fix is super easy in a future version.

For now, it would be the best idea to use `en` via the LanguageSelector component in the UI, or if your users can't live with `en`, it needs a manual DB query to change them back. But keep in mind, that anytime they modify their own values, it will be switched back to the "wrong" version inside the DB.

The query to switch them to the working `zh-Hans` would be 

```sql
UPDATE users SET language = 'zhhans' WHERE language = 'zh-Hans';
```

The other solution would be to use Postgres until I have an image with the fix, as I would need to resolve the current CSS issues first to make the UI useable again.

Fix patch:
diff --git a/frontend/src/lib/LangSelector.svelte b/frontend/src/lib/LangSelector.svelte
index 616f3e15e..e8b498b31 100644
--- a/frontend/src/lib/LangSelector.svelte
+++ b/frontend/src/lib/LangSelector.svelte
@@ -1,12 +1,12 @@
 <script>
-    import { run } from 'svelte/legacy';
+    import {run} from 'svelte/legacy';
 
     import {onMount} from "svelte";
     import OptionSelect from "$lib/OptionSelect.svelte";
     import {LANGUAGES} from "../utils/constants.js";
     import {postUpdateUserLanguage} from "../utils/dataFetching.js";
 
-    
+
     /**
      * @typedef {Object} Props
      * @property {boolean} [absolute]
@@ -15,7 +15,7 @@
      */
 
     /** @type {Props} */
-    let { absolute = false, absoluteRight = false, updateBackend = false } = $props();
+    let {absolute = false, absoluteRight = false, updateBackend = false} = $props();
 
     const attrs = ';Path=/;SameSite=Lax;Max-Age=157680000';
     let lang = $state();
@@ -25,9 +25,8 @@
         readLang();
     });
 
-
     function readLang() {
-        let l = document.documentElement.lang.toUpperCase();
+        let l = document.documentElement.lang.toUpperCase().slice(0, 2);
         lang = l;
         langSelected = l;
     }
@@ -47,6 +46,7 @@
 
         window.location.reload();
     }
+
     run(() => {
         if (langSelected && langSelected !== lang) {
             switchLang(langSelected);
diff --git a/src/bin/src/main.rs b/src/bin/src/main.rs
index f352b760d..e669aef4b 100644
--- a/src/bin/src/main.rs
+++ b/src/bin/src/main.rs
@@ -7,13 +7,12 @@ use actix_web::rt::System;
 use actix_web::{middleware, web, App, HttpServer};
 use actix_web_prom::PrometheusMetricsBuilder;
 use cryptr::EncKeys;
-use hiqlite::params;
 use prometheus::Registry;
 use rauthy_common::constants::{
     BUILD_TIME, RAUTHY_VERSION, SWAGGER_UI_EXTERNAL, SWAGGER_UI_INTERNAL,
 };
 use rauthy_common::utils::UseDummyAddress;
-use rauthy_common::{is_hiqlite, is_sqlite, password_hasher};
+use rauthy_common::{is_sqlite, password_hasher};
 use rauthy_handlers::openapi::ApiDoc;
 use rauthy_handlers::{
     api_keys, auth_providers, blacklist, clients, events, fed_cm, generic, groups, oidc, roles,
@@ -26,7 +25,6 @@ use rauthy_middlewares::principal::RauthyPrincipalMiddleware;
 use rauthy_models::app_state::AppState;
 use rauthy_models::database::DB;
 use rauthy_models::email::EMail;
-use rauthy_models::entity::password::PasswordPolicy;
 use rauthy_models::events::event::Event;
 use rauthy_models::events::health_watch::watch_health;
 use rauthy_models::events::listener::EventListener;
@@ -41,12 +39,13 @@ use std::time::Duration;
 use std::{env, thread};
 use tokio::sync::mpsc;
 use tokio::time;
-use tracing::{debug, error, info, warn};
+use tracing::{debug, error, info};
 use utoipa_swagger_ui::SwaggerUi;
 
 mod dummy_data;
 mod logging;
 mod tls;
+mod version_migration;
 
 #[tokio::main]
 async fn main() -> Result<(), Box<dyn Error>> {
@@ -103,8 +102,8 @@ https://sebadob.github.io/rauthy/getting_started/main.html"#
         }
     }
 
-    debug!("Starting the persistence layer");
     // TODO Keep this check in place until v0.28.0 as info for migrations from older versions.
+    debug!("Starting the persistence layer");
     if is_sqlite() {
         // Hiqlite migration has been finished.
         panic!(
@@ -200,42 +199,7 @@ https://github.com/sebadob/rauthy/releases/tag/v0.27.0
         }
     };
 
-    // TODO remove this block check after `0.28`.
-    // 0.27.0 had a bug that could have inserted NULL for password policy on update.
-    if is_hiqlite() {
-        let mut row = DB::client()
-            .query_raw_one(
-                "SELECT data FROM config WHERE id = 'password_policy'",
-                params!(),
-            )
-            .await?;
-        if let Err(err) = row.try_get::<Vec<u8>>("data") {
-            warn!(
-                r#"
-
-Error looking up PasswordPolicy - this is most probably a known 0.27.0 bug.
-Inserting default Policy to fix it.
-You should visit the Admin UI -> Config -> Password Policy and adjust it to your needs.
-
-Error: {}
-"#,
-                err
-            );
-            PasswordPolicy {
-                length_min: 14,
-                length_max: 128,
-                include_lower_case: Some(1),
-                include_upper_case: Some(1),
-                include_digits: Some(1),
-                include_special: Some(1),
-                valid_days: Some(180),
-                not_recently_used: Some(3),
-            }
-            .save()
-            .await
-            .expect("Cannot fix default PasswordPolicy issue");
-        }
-    }
+    version_migration::manual_version_migrations().await?;
 
     // actix web
     let state = app_state.clone();
diff --git a/src/bin/src/version_migration.rs b/src/bin/src/version_migration.rs
new file mode 100644
index 000000000..f9d4e7efb
--- /dev/null
+++ b/src/bin/src/version_migration.rs
@@ -0,0 +1,60 @@
+use hiqlite::params;
+use rauthy_common::is_hiqlite;
+use rauthy_models::database::DB;
+use rauthy_models::entity::password::PasswordPolicy;
+use std::error::Error;
+use tracing::warn;
+
+/// If it's necessary to apply manual migrations between major versions, which are
+/// not handled automatically by database migrations, put them here. This function
+/// will be executed at startup after DB init and before the API start.
+pub async fn manual_version_migrations() -> Result<(), Box<dyn Error>> {
+    // TODO remove this block check after `0.28`.
+    // 0.27.0 had a bug that could have inserted NULL for password policy on update.
+    if is_hiqlite() {
+        let mut row = DB::client()
+            .query_raw_one(
+                "SELECT data FROM config WHERE id = 'password_policy'",
+                params!(),
+            )
+            .await?;
+        if let Err(err) = row.try_get::<Vec<u8>>("data") {
+            warn!(
+                r#"
+
+Error looking up PasswordPolicy - this is most probably a known 0.27.0 bug.
+Inserting default Policy to fix it.
+You should visit the Admin UI -> Config -> Password Policy and adjust it to your needs.
+
+Error: {}
+"#,
+                err
+            );
+            PasswordPolicy {
+                length_min: 14,
+                length_max: 128,
+                include_lower_case: Some(1),
+                include_upper_case: Some(1),
+                include_digits: Some(1),
+                include_special: Some(1),
+                valid_days: Some(180),
+                not_recently_used: Some(3),
+            }
+            .save()
+            .await
+            .expect("Cannot fix default PasswordPolicy issue");
+        }
+    }
+
+    // TODO remove this block with `0.29`
+    if is_hiqlite() {
+        DB::client()
+            .execute(
+                "UPDATE users SET language = 'zhhans' WHERE language = 'zh-Hans';",
+                params!(),
+            )
+            .await?;
+    }
+
+    Ok(())
+}
diff --git a/src/models/src/language.rs b/src/models/src/language.rs
index d5c51f9de..e3b0f8215 100644
--- a/src/models/src/language.rs
+++ b/src/models/src/language.rs
@@ -25,10 +25,11 @@ impl Language {
     }
 
     pub fn as_str(&self) -> &str {
+        // must return results that work with serde::Deserialize from lowercase
         match self {
             Language::En => "en",
             Language::De => "de",
-            Language::ZhHans => "zh-Hans",
+            Language::ZhHans => "zhhans",
             Language::Ko => "ko",
         }
     }
@@ -63,7 +64,7 @@ impl From<&str> for Language {
         match value {
             "de" | "de-DE" => Self::De,
             "en" | "en-US" => Self::En,
-            "zh" | "zh-hans" | "zh-Hans" => Self::ZhHans,
+            "zh" | "zhhans" | "zh-hans" | "zh-Hans" => Self::ZhHans,
             "ko" | "ko-KR" => Self::Ko,
             _ => Self::default(),
         }


Test patch: