SQLite: Cannot use Google OIDC due to dynamic typing #4951
Description
1. What is not working as documented?
On PhotoPrism configured with SQLite 3 database and Google OIDC authentication, once an user account is created, then it cannot login second time.
2. How can we reproduce it?
Steps to reproduce the behavior:
- Use default docker compose file
- Configure OIDC by setting environment variables:
PHOTOPRISM_SITE_URL: "https://your.public.domain"
PHOTOPRISM_OIDC_URI: "https://accounts.google.com"
PHOTOPRISM_OIDC_CLIENT: ********"
PHOTOPRISM_OIDC_SECRET: "*********"
PHOTOPRISM_OIDC_PROVIDER: "Google"
PHOTOPRISM_OIDC_REGISTER: "true"
- Configure SQLite database by setting environment variables:
PHOTOPRISM_DATABASE_DRIVER: "sqlite"
- Start the container:
docker compose up photoprism - Open a browser and navigate to configured public PhotoPrism address
- Login with Google account - login is successful and account is created
- Log out
- Login with Google account - cannot login, error message "Invalid credentials" is shown
3. What behavior do you expect?
User can login with the Google account. The same Google OIDC configuration works properly with MariaDB database.
4. What could be the cause of your problem?
Login status shows an error related to the auth id:
> photoprism audit logins
|----------------|----------------------|-------|----------------------------------|---------------------|---------------------|
| Client IP | Username | Realm | Status | Last Login | Failed At |
|----------------|----------------------|-------|----------------------------------|---------------------|---------------------|
| 192.168.80.154 | john.doe | oidc | error: invalid auth id (3 times) | 2025-04-18 09:52:34 | 2025-04-18 10:04:57 |
|----------------|----------------------|-------|----------------------------------|---------------------|---------------------|
The login error is caused by the fact that auth_id is stored incorrectly on Sqlite3 database:
> photoprism users show
|---------------|--------------------------------------------------|
| Name | Value |
|---------------|--------------------------------------------------|
| AuthID | "1.0047673928577674e+20" |
| AuthIssuer | "https://accounts.google.com" |
| AuthMethod | "" |
| AuthProvider | "oidc" |
...
For comparison on MariaDB:
> photoprism users show
|---------------|--------------------------------------------------|
| Name | Value |
|---------------|--------------------------------------------------|
| AuthID | "100476739285776742802" |
| AuthIssuer | "https://accounts.google.com" |
| AuthMethod | "" |
| AuthProvider | "oidc" |
...
Manually setting auth_id for any number with 20 or more digits, leads to te same results:
photoprism users mod --auth-id=12345678901234567890 john.doe
It seems that, in SQLite case, the ORM layer converts the auth_id to the integer value and store it in the exponential form.
5. Can you provide us with example files for testing, error logs, or screenshots?
Login details are stored in an audit log, however the audit log is not available in the community edition.
6. Which software versions do you use?
(a) PhotoPrism Architecture & Build Number: 250321-57590c48b-Linux-AMD64-Plus
(b) Database Type & Version: SQLite v3.45.1
(c) Operating System Types & Versions: docker image: photoprism/photoprism:latest
(d) Browser Types & Versions: Vivaldi
(e) Ad Blockers, Browser Plugins, and/or Firewall Software? no
7. On what kind of device is PhotoPrism installed?
(a) Device / Processor Type: Intel(R) Celeron(R) N5095 @ 2.00GHz, 16 GB memory detected
(b) Physical Memory & Swap Space in GB: 16 GB RAM
(c) Storage Type: SSD
8. Do you use a Reverse Proxy, Firewall, VPN, or CDN?
nginx/1.27.4