Immich does not work without superuser access to DB #3003

Closed
opened 2026-02-20 02:02:31 -05:00 by deekerman · 0 comments
Owner

Originally created by @jvanbruegge on GitHub (Jun 10, 2024).

The bug

Contrary to what the documentation states (https://immich.app/docs/administration/postgres-standalone/#without-superuser-permission), immich unconditionally tries to create the extension, refusing to start.

The OS that Immich Server is running on

NixOS

Version of Immich Server

v1.105.1

Version of Immich Mobile App

N/A

Platform with the issue

  • Server
  • Web
  • Mobile

Your docker-compose.yml content

N/A

Your .env content

N/A

Reproduction steps

Use prexisting database, follow instructions in documentation, do not give immich user superuser access

Relevant log output

Jun 10 11:55:18 caladan server[2694]:         Failed to activate pgvecto.rs extension.
Jun 10 11:55:18 caladan server[2694]:         Please ensure the Postgres instance has pgvecto.rs installed.
Jun 10 11:55:18 caladan server[2694]:         If the Postgres instance already has pgvecto.rs installed, Immich may not have the necessary permissions to activate it.
Jun 10 11:55:18 caladan server[2694]:         In this case, please run 'CREATE EXTENSION IF NOT EXISTS vectors' manually as a superuser.
Jun 10 11:55:18 caladan server[2694]:         See https://immich.app/docs/guides/database-queries for how to query the database.
Jun 10 11:55:18 caladan server[2694]:         Alternatively, if your Postgres instance has pgvector, you may use this instead by setting the environment variable 'DB_VECTOR_EXTENSION=pgvector'.
Jun 10 11:55:18 caladan server[2694]:         Note that switching between the two extensions after a successful startup is not supported.
Jun 10 11:55:18 caladan server[2694]:         The exception is if your version of Immich prior to upgrading was 1.90.2 or earlier.
Jun 10 11:55:18 caladan server[2694]:         In this case, you may set either extension now, but you will not be able to switch to the other extension following a successful startup.
Jun 10 11:55:18 caladan server[2694]:
Jun 10 11:55:18 caladan server[2694]: /nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219
Jun 10 11:55:18 caladan server[2694]:             throw new QueryFailedError_1.QueryFailedError(query, parameters, err);
Jun 10 11:55:18 caladan server[2694]:                   ^
Jun 10 11:55:18 caladan server[2694]: QueryFailedError: permission denied to create extension "vectors"
Jun 10 11:55:18 caladan server[2694]:     at PostgresQueryRunner.query (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
Jun 10 11:55:18 caladan server[2694]:     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
Jun 10 11:55:18 caladan server[2694]:     at async DataSource.query (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/node_modules/typeorm/data-source/DataSource.js:350:20)
Jun 10 11:55:18 caladan server[2694]:     at async DatabaseRepository.createExtension (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/repositories/database.repository.js:67:9)
Jun 10 11:55:18 caladan server[2694]:     at async DatabaseService.createVectorExtension (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/services/database.service.js:69:13)
Jun 10 11:55:18 caladan server[2694]:     at async /nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/services/database.service.js:39:13
Jun 10 11:55:18 caladan server[2694]:     at async /nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/repositories/database.repository.js:189:23 {
Jun 10 11:55:18 caladan server[2694]:   query: 'CREATE EXTENSION IF NOT EXISTS vectors',
Jun 10 11:55:18 caladan server[2694]:   parameters: undefined,
Jun 10 11:55:18 caladan server[2694]:   driverError: error: permission denied to create extension "vectors"
Jun 10 11:55:18 caladan server[2694]:       at /nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/node_modules/pg/lib/client.js:526:17
Jun 10 11:55:18 caladan server[2694]:       at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
Jun 10 11:55:18 caladan server[2694]:       at async PostgresQueryRunner.query (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)
Jun 10 11:55:18 caladan server[2694]:       at async DataSource.query (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/node_modules/typeorm/data-source/DataSource.js:350:20)
Jun 10 11:55:18 caladan server[2694]:       at async DatabaseRepository.createExtension (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/repositories/database.repository.js:67:9)
Jun 10 11:55:18 caladan server[2694]:       at async DatabaseService.createVectorExtension (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/services/database.service.js:69:13)
Jun 10 11:55:18 caladan server[2694]:       at async /nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/services/database.service.js:39:13
Jun 10 11:55:18 caladan server[2694]:       at async /nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/repositories/database.repository.js:189:23 {
Jun 10 11:55:18 caladan server[2694]:     length: 164,
Jun 10 11:55:18 caladan server[2694]:     severity: 'ERROR',
Jun 10 11:55:18 caladan server[2694]:     code: '42501',
Jun 10 11:55:18 caladan server[2694]:     detail: undefined,
Jun 10 11:55:18 caladan server[2694]:     hint: 'Must be superuser to create this extension.',
Jun 10 11:55:18 caladan server[2694]:     position: undefined,
Jun 10 11:55:18 caladan server[2694]:     internalPosition: undefined,
Jun 10 11:55:18 caladan server[2694]:     internalQuery: undefined,
Jun 10 11:55:18 caladan server[2694]:     where: undefined,
Jun 10 11:55:18 caladan server[2694]:     schema: undefined,
Jun 10 11:55:18 caladan server[2694]:     table: undefined,
Jun 10 11:55:18 caladan server[2694]:     column: undefined,
Jun 10 11:55:18 caladan server[2694]:     dataType: undefined,
Jun 10 11:55:18 caladan server[2694]:     constraint: undefined,
Jun 10 11:55:18 caladan server[2694]:     file: 'extension.c',
Jun 10 11:55:18 caladan server[2694]:     line: '894',
Jun 10 11:55:18 caladan server[2694]:     routine: 'execute_extension_script'
Jun 10 11:55:18 caladan server[2694]:   },
Jun 10 11:55:18 caladan server[2694]:   length: 164,
Jun 10 11:55:18 caladan server[2694]:   severity: 'ERROR',
Jun 10 11:55:18 caladan server[2694]:   code: '42501',
Jun 10 11:55:18 caladan server[2694]:   detail: undefined,
Jun 10 11:55:18 caladan server[2694]:   hint: 'Must be superuser to create this extension.',
Jun 10 11:55:18 caladan server[2694]:   position: undefined,
Jun 10 11:55:18 caladan server[2694]:   internalPosition: undefined,
Jun 10 11:55:18 caladan server[2694]:   internalQuery: undefined,
Jun 10 11:55:18 caladan server[2694]:   where: undefined,
Jun 10 11:55:18 caladan server[2694]:   schema: undefined,
Jun 10 11:55:18 caladan server[2694]:   table: undefined,
Jun 10 11:55:18 caladan server[2694]:   column: undefined,
Jun 10 11:55:18 caladan server[2694]:   dataType: undefined,
Jun 10 11:55:18 caladan server[2694]:   constraint: undefined,
Jun 10 11:55:18 caladan server[2694]:   file: 'extension.c',
Jun 10 11:55:18 caladan server[2694]:   line: '894',
Jun 10 11:55:18 caladan server[2694]:   routine: 'execute_extension_script'
Jun 10 11:55:18 caladan server[2694]: }
Jun 10 11:55:18 caladan server[2694]: Node.js v20.12.2

Additional information

No response

Originally created by @jvanbruegge on GitHub (Jun 10, 2024). ### The bug Contrary to what the documentation states (https://immich.app/docs/administration/postgres-standalone/#without-superuser-permission), immich [unconditionally tries to create the extension](https://github.com/immich-app/immich/blob/84da9abcbcb853dd853e2995ec944fc6e934da39/server/src/services/database.service.ts#L92), refusing to start. ### The OS that Immich Server is running on NixOS ### Version of Immich Server v1.105.1 ### Version of Immich Mobile App N/A ### Platform with the issue - [X] Server - [ ] Web - [ ] Mobile ### Your docker-compose.yml content ```YAML N/A ``` ### Your .env content ```Shell N/A ``` ### Reproduction steps ```bash Use prexisting database, follow instructions in documentation, do not give immich user superuser access ``` ### Relevant log output ```shell Jun 10 11:55:18 caladan server[2694]: Failed to activate pgvecto.rs extension. Jun 10 11:55:18 caladan server[2694]: Please ensure the Postgres instance has pgvecto.rs installed. Jun 10 11:55:18 caladan server[2694]: If the Postgres instance already has pgvecto.rs installed, Immich may not have the necessary permissions to activate it. Jun 10 11:55:18 caladan server[2694]: In this case, please run 'CREATE EXTENSION IF NOT EXISTS vectors' manually as a superuser. Jun 10 11:55:18 caladan server[2694]: See https://immich.app/docs/guides/database-queries for how to query the database. Jun 10 11:55:18 caladan server[2694]: Alternatively, if your Postgres instance has pgvector, you may use this instead by setting the environment variable 'DB_VECTOR_EXTENSION=pgvector'. Jun 10 11:55:18 caladan server[2694]: Note that switching between the two extensions after a successful startup is not supported. Jun 10 11:55:18 caladan server[2694]: The exception is if your version of Immich prior to upgrading was 1.90.2 or earlier. Jun 10 11:55:18 caladan server[2694]: In this case, you may set either extension now, but you will not be able to switch to the other extension following a successful startup. Jun 10 11:55:18 caladan server[2694]: Jun 10 11:55:18 caladan server[2694]: /nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219 Jun 10 11:55:18 caladan server[2694]: throw new QueryFailedError_1.QueryFailedError(query, parameters, err); Jun 10 11:55:18 caladan server[2694]: ^ Jun 10 11:55:18 caladan server[2694]: QueryFailedError: permission denied to create extension "vectors" Jun 10 11:55:18 caladan server[2694]: at PostgresQueryRunner.query (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19) Jun 10 11:55:18 caladan server[2694]: at process.processTicksAndRejections (node:internal/process/task_queues:95:5) Jun 10 11:55:18 caladan server[2694]: at async DataSource.query (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/node_modules/typeorm/data-source/DataSource.js:350:20) Jun 10 11:55:18 caladan server[2694]: at async DatabaseRepository.createExtension (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/repositories/database.repository.js:67:9) Jun 10 11:55:18 caladan server[2694]: at async DatabaseService.createVectorExtension (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/services/database.service.js:69:13) Jun 10 11:55:18 caladan server[2694]: at async /nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/services/database.service.js:39:13 Jun 10 11:55:18 caladan server[2694]: at async /nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/repositories/database.repository.js:189:23 { Jun 10 11:55:18 caladan server[2694]: query: 'CREATE EXTENSION IF NOT EXISTS vectors', Jun 10 11:55:18 caladan server[2694]: parameters: undefined, Jun 10 11:55:18 caladan server[2694]: driverError: error: permission denied to create extension "vectors" Jun 10 11:55:18 caladan server[2694]: at /nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/node_modules/pg/lib/client.js:526:17 Jun 10 11:55:18 caladan server[2694]: at process.processTicksAndRejections (node:internal/process/task_queues:95:5) Jun 10 11:55:18 caladan server[2694]: at async PostgresQueryRunner.query (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25) Jun 10 11:55:18 caladan server[2694]: at async DataSource.query (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/node_modules/typeorm/data-source/DataSource.js:350:20) Jun 10 11:55:18 caladan server[2694]: at async DatabaseRepository.createExtension (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/repositories/database.repository.js:67:9) Jun 10 11:55:18 caladan server[2694]: at async DatabaseService.createVectorExtension (/nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/services/database.service.js:69:13) Jun 10 11:55:18 caladan server[2694]: at async /nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/services/database.service.js:39:13 Jun 10 11:55:18 caladan server[2694]: at async /nix/store/kk4072jcci4q4xg92vylpdh4q5816w56-immich-1.105.1/dist/repositories/database.repository.js:189:23 { Jun 10 11:55:18 caladan server[2694]: length: 164, Jun 10 11:55:18 caladan server[2694]: severity: 'ERROR', Jun 10 11:55:18 caladan server[2694]: code: '42501', Jun 10 11:55:18 caladan server[2694]: detail: undefined, Jun 10 11:55:18 caladan server[2694]: hint: 'Must be superuser to create this extension.', Jun 10 11:55:18 caladan server[2694]: position: undefined, Jun 10 11:55:18 caladan server[2694]: internalPosition: undefined, Jun 10 11:55:18 caladan server[2694]: internalQuery: undefined, Jun 10 11:55:18 caladan server[2694]: where: undefined, Jun 10 11:55:18 caladan server[2694]: schema: undefined, Jun 10 11:55:18 caladan server[2694]: table: undefined, Jun 10 11:55:18 caladan server[2694]: column: undefined, Jun 10 11:55:18 caladan server[2694]: dataType: undefined, Jun 10 11:55:18 caladan server[2694]: constraint: undefined, Jun 10 11:55:18 caladan server[2694]: file: 'extension.c', Jun 10 11:55:18 caladan server[2694]: line: '894', Jun 10 11:55:18 caladan server[2694]: routine: 'execute_extension_script' Jun 10 11:55:18 caladan server[2694]: }, Jun 10 11:55:18 caladan server[2694]: length: 164, Jun 10 11:55:18 caladan server[2694]: severity: 'ERROR', Jun 10 11:55:18 caladan server[2694]: code: '42501', Jun 10 11:55:18 caladan server[2694]: detail: undefined, Jun 10 11:55:18 caladan server[2694]: hint: 'Must be superuser to create this extension.', Jun 10 11:55:18 caladan server[2694]: position: undefined, Jun 10 11:55:18 caladan server[2694]: internalPosition: undefined, Jun 10 11:55:18 caladan server[2694]: internalQuery: undefined, Jun 10 11:55:18 caladan server[2694]: where: undefined, Jun 10 11:55:18 caladan server[2694]: schema: undefined, Jun 10 11:55:18 caladan server[2694]: table: undefined, Jun 10 11:55:18 caladan server[2694]: column: undefined, Jun 10 11:55:18 caladan server[2694]: dataType: undefined, Jun 10 11:55:18 caladan server[2694]: constraint: undefined, Jun 10 11:55:18 caladan server[2694]: file: 'extension.c', Jun 10 11:55:18 caladan server[2694]: line: '894', Jun 10 11:55:18 caladan server[2694]: routine: 'execute_extension_script' Jun 10 11:55:18 caladan server[2694]: } Jun 10 11:55:18 caladan server[2694]: Node.js v20.12.2 ``` ### Additional information _No response_
Sign in to join this conversation.
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
starred/immich#3003
No description provided.