[bug] Non-admin login always fails with "Authentication service error" — missing \`roles\` column in password-check query

[chokoblitz]

Summary

All login attempts for non-admin users return HTTP 401 "Authentication service error" on every image published from the add_role_to_user branch. Admin login continues to work. The bug is confirmed on both add_role_to_user-arm64 and pr-6-arm64 (latest available image at time of writing).

---

Environment

• Images tested:
  • ghcr.io/cosmian/auth-server:add_role_to_user-arm64 (sha256:95757ac52ff91…)
  • ghcr.io/cosmian/auth-server:pr-6-arm64 (sha256:5cf7847829e20…)
• Backends tested: PostgreSQL, SQLite
• Architecture: ARM64

---

Steps to Reproduce

1. Start auth-server with a PostgreSQL or SQLite backend and a valid config.
2. Create a realm with username/password auth:

curl -k -X POST "https://<host>/admins/realms" \
  -H "Cookie: <admin-session>" \
  -H "Content-Type: application/json" \
  -d '{
    "id": "my-realm",
    "auth_params": {
      "jwt_params": null,
      "username_password_params": { "allow_expired_passwords": true }
    },
    "session_max_age_seconds": 28800,
    "session_max_stale_age_seconds": 3600
  }'

3. Insert a user into userpass for that realm (the POST /{realm_id}/userpass endpoint returns 404 — see note below — so a direct DB insert is required):

-- SQLite
INSERT INTO userpass (realm, username, password, change_password, roles)
VALUES (
  'my-realm',
  'testuser',
  '$argon2id$v=19$m=4096,t=3,p=1$<salt>$<hash>',
  0,
  '[]'
);

4. Attempt login:

curl -k -X POST "https://<host>/login?realm=my-realm" \
  -u "testuser:mypassword" \
  -H "Content-Type: application/json" -d "{}"

Expected: 200 {"next_step":"Authenticated","session_id":"..."} (or {"next_step":"ChangePassword"} if change_password=1)

Actual: 401 Authentication service error

---

Root Cause

The add_role_to_user branch added a roles column to the userpass table and updated the UserPass Rust struct to include it. However, the password-check query used during the non-admin login path was not updated.

Broken query (both PG and SQLite variants):

-- PostgreSQL
SELECT password, change_password
FROM userpass
WHERE realm = $1 AND username = $2

-- SQLite
SELECT password, change_password
FROM userpass
WHERE realm = ? AND username = ?

When sqlx tries to deserialize the result into the UserPass struct, it fails with ColumnNotFound("roles"), which is caught and returned as the generic "Authentication service error" 401.

This query appears 3 times in the compiled binary (confirmed by binary analysis of both add_role_to_user-arm64 and pr-6-arm64):

• 1× PostgreSQL variant ($1, $2)
• 2× SQLite variant (?, ?)

Query already present elsewhere (correct — used only in the admin login path):

SELECT realm, username, change_password, roles
FROM userpass
WHERE realm = $1 AND username = $2

This version is only reached as a 4th step after verifying that the user is in the admin table — which is why admin login works.

Fix

Add roles to the broken password-check query in both the PostgreSQL and SQLite database implementations (likely in server/src/database/):

SELECT password, change_password, roles
FROM userpass
WHERE realm = $1 AND username = $2

---

Additional Notes

• Workaround: there is currently no viable workaround. Switching to SQLite does not help — the same broken query is present. Adding the user to the admin table does not help either, as the failure occurs at the deserialization of the result from the password-check query (step 2), before the admin-table lookup (step 3).

• Second issue — POST /{realm_id}/userpass returns 404: it is impossible to create non-admin users via the API on this branch (the endpoint appears unrouted). This compounds the first bug since even testing requires a direct DB insert.

• Verification method: the bug was identified by binary analysis (strings on the extracted ELF binary) and confirmed by direct functional testing (login attempts with known-good argon2 hashes, correct DB schema, multiple realm configurations).