starred/photoprism

Fork 0

mirror of https://github.com/photoprism/photoprism.git synced 2026-03-02 22:57:18 -05:00

SQLite: Cannot use Google OIDC due to dynamic typing #2304

New issue

Open

opened 2026-02-20 01:09:25 -05:00 by deekerman · 3 comments

deekerman commented

2026-02-20 01:09:25 -05:00

Owner

Originally created by @jbortkiewicz on GitHub (Apr 18, 2025).

Originally assigned to: @jbortkiewicz on GitHub.

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?

> 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?

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

(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

8. Do you use a Reverse Proxy, Firewall, VPN, or CDN?

nginx/1.27.4

Originally created by @jbortkiewicz on GitHub (Apr 18, 2025). Originally assigned to: @jbortkiewicz on GitHub. #### 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: 1. Use default [docker compose file](https://dl.photoprism.app/docker/compose.yaml) 2. 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" ``` 3. Configure SQLite database by setting environment variables: ``` PHOTOPRISM_DATABASE_DRIVER: "sqlite" ``` 4. Start the container: `docker compose up photoprism` 5. Open a browser and navigate to configured public PhotoPrism address 6. Login with Google account - login is successful and account is created 7. Log out 8. 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

deekerman added the

labels

2026-02-20 01:09:25 -05:00

deekerman commented

2026-02-20 01:09:26 -05:00

Author

Owner

@lastzero commented on GitHub (Apr 18, 2025):

Since SQLite uses a "dynamic" typing system, I believe there is currently no feasible way (other than a dirty hack like adding a text prefix to all IDs) for us to work around this if you want to use SQLite AND OIDC with Google as your Identify Provider:

STRING or BLOB operands automatically convert into REAL or INTEGER values. If the STRING or BLOB looks like a real number (if it has a decimal point or an exponent) or if the value is outside the range that can be represented as a 64-bit signed integer, then it converts to REAL. Otherwise the operand converts to INTEGER.

see https://sqlite.org/datatype3.html

@jbortkiewicz If you know how to do it without dirty hacks and without breaking backwards compatibility, we are open to merging a pull request! 👍

@lastzero commented on GitHub (Apr 18, 2025): Since SQLite uses a "dynamic" typing system, I believe there is currently no feasible way (other than a dirty hack like adding a text prefix to all IDs) for us to work around this if you want to use SQLite AND OIDC with Google as your Identify Provider: > STRING or BLOB operands automatically convert into REAL or INTEGER values. If the STRING or BLOB looks like a real number (if it has a decimal point or an exponent) or if the value is outside the range that can be represented as a 64-bit signed integer, then it converts to REAL. Otherwise the operand converts to INTEGER. see https://sqlite.org/datatype3.html @jbortkiewicz If you know how to do it without dirty hacks and without breaking backwards compatibility, we are open to merging a pull request! 👍

deekerman commented

2026-02-20 01:09:26 -05:00

Author

Owner

@jbortkiewicz commented on GitHub (Apr 18, 2025):

Oh yes, you are right, the problem is caused by dynamic typing feature of SQLite.
The SQLite has a suport of strict typing, however I haven't found information how to enable it with GORM.
Unfortunately my experience with Golang is less than basic, thus I will be not able to help here and propose a solution other than the prefix hack motioned by you.

@jbortkiewicz commented on GitHub (Apr 18, 2025): Oh yes, you are right, the problem is caused by dynamic typing feature of SQLite. The SQLite has a suport of [strict typing](https://sqlite.org/stricttables.html), however I haven't found information how to enable it with GORM. Unfortunately my experience with Golang is less than basic, thus I will be not able to help here and propose a solution other than the prefix hack motioned by you.

deekerman commented

2026-02-20 01:09:26 -05:00

Author

Owner

@keif888 commented on GitHub (Nov 6, 2025):

I tried a proof of concept to do this using Gorm V2 and SQLite in the Postgres branch which could in theory support this, but in practice it can not.

I manually recreated the auth_users table using the ANY type and STRICT setting to ensure that SQLite wouldn't do the type conversions. I had to use the ANY time on most of the columns to allow the test data to be inserted. When using STRICT only very basic types are supported, which does not include datetime, and blob can't be used for text data as it requires a conversion that SQLite refuses to do.
Once I had the table and data loaded, I attempted to run the users show command against a user john that I had set the auth_id to the number above. (Which was read quite happily out by DB Browser for SQLite so I know it was stored in "string" format not "number" format).

PhotoPrism threw some non resolvable errors from Gorm when it tried to access the auth_users table.

./photoprism --auth-mode="public" -c "./storage/sqlite/acceptance/config-active" users show john
DEBU[2025-11-06T13:03:03Z] config: running on 'AMD Ryzen 7 5700X 8-Core Processor', 8.3 GB memory detected 
DEBU[2025-11-06T13:03:03Z] settings: loaded from /go/src/github.com/photoprism/photoprism/storage/sqlite/acceptance/config-active/settings.yml 
DEBU[2025-11-06T13:03:03Z] cluster: no bootstrap configuration found    
WARN[2025-11-06T13:03:03Z] system: config › options › DatabaseDsn has been deprecated in favor of DatabaseDSN 
DEBU[2025-11-06T13:03:03Z] isdbopen: c.db == nil                        
DEBU[2025-11-06T13:03:03Z] database: opened connection to SQLite v3.50.4 
DEBU[2025-11-06T13:03:03Z] vips: max cache size is 256 MB, using up to 2 workers 
INFO[2025-11-06T13:03:03Z] Become a member today, support our mission and enjoy our member benefits! 💎 
INFO[2025-11-06T13:03:03Z] Visit https://www.photoprism.app/membership to learn more. 
DEBU[2025-11-06T13:03:03Z] config: successfully initialized [69.945539ms] 
INFO[2025-11-06T13:03:03Z] /go/src/github.com/photoprism/photoprism/internal/entity/auth_user.go:171 sql: Scan error on column index 13, name "login_at": unsupported Scan, storing driver.Value type string into type *time.Time
[1.232ms] [rows:1] SELECT * FROM `auth_users` WHERE user_name = ? ORDER BY `auth_users`.`id` LIMIT 1 
DEBU[2025-11-06T13:03:03Z] config: closing database                     
DEBU[2025-11-06T13:03:03Z] closed database connection                   
ERRO[2025-11-06T13:03:03Z] user 'john' not found

The unsupported Scan is coming from Gorm, and indicates that Gorm treats the ANY datatype as a string, and can't apply the returned data into the struct.

Which means that it will not be possible to fix this problem when using the SQLite database, at the database level.

It will require a hacky, add a magic string at the start/end of an auth_id, to allow this to work.

IMHO, the best (TBC via testing) way to do the hacky is to use Gorm Hooks on BeforeSave, BeforeUpdate and AfterFind which could dynamically handle the addition and removal of the magic strings.
The AuthID struct column would need to be made longer to include the magic string.

@keif888 commented on GitHub (Nov 6, 2025): I tried a proof of concept to do this using Gorm V2 and SQLite in the Postgres branch which could in theory support this, but in practice it can not. I manually recreated the auth_users table using the ANY type and STRICT setting to ensure that SQLite wouldn't do the type conversions. I had to use the ANY time on most of the columns to allow the test data to be inserted. When using STRICT only very basic types are supported, which does not include datetime, and blob can't be used for text data as it requires a conversion that SQLite refuses to do. Once I had the table and data loaded, I attempted to run the users show command against a user john that I had set the auth_id to the number above. (Which was read quite happily out by DB Browser for SQLite so I know it was stored in "string" format not "number" format). PhotoPrism threw some non resolvable errors from Gorm when it tried to access the auth_users table. ``` ./photoprism --auth-mode="public" -c "./storage/sqlite/acceptance/config-active" users show john DEBU[2025-11-06T13:03:03Z] config: running on 'AMD Ryzen 7 5700X 8-Core Processor', 8.3 GB memory detected DEBU[2025-11-06T13:03:03Z] settings: loaded from /go/src/github.com/photoprism/photoprism/storage/sqlite/acceptance/config-active/settings.yml DEBU[2025-11-06T13:03:03Z] cluster: no bootstrap configuration found WARN[2025-11-06T13:03:03Z] system: config › options › DatabaseDsn has been deprecated in favor of DatabaseDSN DEBU[2025-11-06T13:03:03Z] isdbopen: c.db == nil DEBU[2025-11-06T13:03:03Z] database: opened connection to SQLite v3.50.4 DEBU[2025-11-06T13:03:03Z] vips: max cache size is 256 MB, using up to 2 workers INFO[2025-11-06T13:03:03Z] Become a member today, support our mission and enjoy our member benefits! 💎 INFO[2025-11-06T13:03:03Z] Visit https://www.photoprism.app/membership to learn more. DEBU[2025-11-06T13:03:03Z] config: successfully initialized [69.945539ms] INFO[2025-11-06T13:03:03Z] /go/src/github.com/photoprism/photoprism/internal/entity/auth_user.go:171 sql: Scan error on column index 13, name "login_at": unsupported Scan, storing driver.Value type string into type *time.Time [1.232ms] [rows:1] SELECT * FROM `auth_users` WHERE user_name = ? ORDER BY `auth_users`.`id` LIMIT 1 DEBU[2025-11-06T13:03:03Z] config: closing database DEBU[2025-11-06T13:03:03Z] closed database connection ERRO[2025-11-06T13:03:03Z] user 'john' not found ``` The unsupported Scan is coming from Gorm, and indicates that Gorm treats the ANY datatype as a string, and can't apply the returned data into the struct. **Which means that it will not be possible to fix this problem when using the SQLite database, at the database level.** It will require a hacky, add a magic string at the start/end of an auth_id, to allow this to work. IMHO, the best (TBC via testing) way to do the hacky is to use Gorm Hooks on BeforeSave, BeforeUpdate and AfterFind which could dynamically handle the addition and removal of the magic strings. The AuthID struct column would need to be made longer to include the magic string.