[BUG] QueryFailedError: permission denied to create extension "earthdistance" #1704

Closed
opened 2026-02-05 03:12:02 +03:00 by OVERLORD · 7 comments
Owner

Originally created by @djjudas21 on GitHub (Nov 30, 2023).

The bug

I have deployed a greenfield installation of Immich v1.89.0 with Helm chart v0.2.0. I saw the warning in the release notes

    [!warning]
    If you are running your own Postgres database (not the one in our default docker-compose) and created the immich user yourself, you may need to enable the required extensions as the database superuser. You can do this by running the following two queries under the context of the immich database. This will only need to be run once.

    CREATE EXTENSION cube;
    CREATE EXTENSION earthdistance;

I figured a new installation with the Helm chart would behave the same as docker composer, but I am running into this error, which repeats a few times before Kubernetes kills the pod.

[Nest] 7  - 11/29/2023, 8:49:41 PM     LOG [NestFactory] Starting Nest application...
[Nest] 7  - 11/29/2023, 8:49:41 PM     LOG [InstanceLoader] TypeOrmModule dependencies initialized +40ms
[Nest] 7  - 11/29/2023, 8:49:41 PM     LOG [InstanceLoader] BullModule dependencies initialized +0ms
[Nest] 7  - 11/29/2023, 8:49:41 PM     LOG [InstanceLoader] ConfigHostModule dependencies initialized +1ms
[Nest] 7  - 11/29/2023, 8:49:41 PM     LOG [InstanceLoader] DiscoveryModule dependencies initialized +0ms
[Nest] 7  - 11/29/2023, 8:49:41 PM     LOG [InstanceLoader] ScheduleModule dependencies initialized +0ms
[Nest] 7  - 11/29/2023, 8:49:41 PM     LOG [InstanceLoader] ConfigModule dependencies initialized +6ms
[Nest] 7  - 11/29/2023, 8:49:41 PM     LOG [InstanceLoader] BullModule dependencies initialized +1ms
[Nest] 7  - 11/29/2023, 8:49:41 PM     LOG [InstanceLoader] ScheduleModule dependencies initialized +0ms
[Nest] 7  - 11/29/2023, 8:49:41 PM     LOG [InstanceLoader] BullModule dependencies initialized +0ms
[Nest] 7  - 11/29/2023, 8:49:49 PM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (1)...
QueryFailedError: permission denied to create extension "earthdistance"
    at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Geodata1700362016675.up (/usr/src/app/dist/infra/migrations/1700362016675-Geodata.js:10:9)
    at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
    at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:260:35)
    at async DataSource.initialize (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:148:17)

Yes I can run the CREATE EXTENSION commands by hand but this issue will affect every new installation.

The OS that Immich Server is running on

MicroK8s v1.26

Version of Immich Server

v1.89.0

Version of Immich Mobile App

Platform with the issue

  • Server
  • Web
  • Mobile

Your docker-compose.yml content

My Helm `values.yaml` for reference


image:
  tag: v1.89.0

# These entries are shared between all the Immich components
env:
  REDIS_HOSTNAME: '{{ printf "%s-redis-master" .Release.Name }}'
  DB_HOSTNAME: "{{ .Release.Name }}-postgresql"
  DB_USERNAME: "{{ .Values.postgresql.global.postgresql.auth.username }}"
  DB_DATABASE_NAME: "{{ .Values.postgresql.global.postgresql.auth.database }}"
  # -- You should provide your own secret outside of this helm-chart and use `postgresql.global.postgresql.auth.existingSecret` to provide credentials to the postgresql instance
  DB_PASSWORD: "{{ .Values.postgresql.global.postgresql.auth.password }}"
  TYPESENSE_ENABLED: "{{ .Values.typesense.enabled }}"
  TYPESENSE_API_KEY: "{{ .Values.typesense.env.TYPESENSE_API_KEY }}"
  TYPESENSE_HOST: '{{ printf "%s-typesense" .Release.Name }}'
  IMMICH_MACHINE_LEARNING_URL: '{{ printf "http://%s-machine-learning:3003" .Release.Name }}'

immich:
  persistence:
    # Main data store for all photos shared between different components.
    library:
      # Automatically creating the library volume is not supported by this chart
      # You have to specify an existing PVC to use
      existingClaim: immich-library

# Dependencies

postgresql:
  enabled: true
  global:
    storageClass: ceph-block
  primary:
    resources:
      requests:
        memory: 64Mi
        cpu: 10m
    priorityClassName: "database"
    persistence:
      enabled: true
      storageClass: ceph-block
      accessModes:
        - ReadWriteOnce
      size: 1Gi
  metrics:
    enabled: true
    resources:
      requests:
        cpu: 1m
        memory: 8Mi
    serviceMonitor:
      enabled: true
    prometheusRule:
      enabled: true

redis:
  enabled: true
  global:
    storageClass: ceph-block
  master:
    resources:
      limits:
        memory: 256Mi
      requests:
        cpu: 20m
        memory: 32Mi
    persistence:
      enabled: true
      retain: true
      storageClass: ceph-block
      accessModes:
        - ReadWriteOnce
      size: 1Gi
  priorityClassName: database
  metrics:
    enabled: true
    serviceMonitor:
      enabled: true
    prometheusRule:
      enabled: true
    resources:
      requests:
        cpu: 1m
        memory: 16Mi

typesense:
  enabled: true
  persistence:
    tsdata:
      # Enabling typesense persistence is recommended to avoid slow reindexing
      enabled: true
      accessMode: ReadWriteOnce
      size: 1Gi
      storageClass: ceph-block

# Immich components

server:
  enabled: true
  image:
    repository: ghcr.io/immich-app/immich-server
    pullPolicy: IfNotPresent
  ingress:
    main:
      enabled: true
      annotations:
        # proxy-body-size is set to 0 to remove the body limit on file uploads
        nginx.ingress.kubernetes.io/proxy-body-size: "0"
        cert-manager.io/cluster-issuer: "letsencrypt-prod"
      hosts:
        - host: immich.example.com
          paths:
            - path: "/"
      tls:
        - secretName: ingress-tls
          hosts:
            - immich.example.com

microservices:
  enabled: true
  persistence:
    geodata-cache:
      enabled: true
      size: 1Gi
      # Optional: Set this to pvc to avoid downloading the geodata every start.
      type: pvc
      accessMode: ReadWriteMany
      storageClass: truenas

machine-learning:
  enabled: true
  persistence:
    cache:
      enabled: true
      size: 10Gi
      # Optional: Set this to pvc to avoid downloading the ML models every start.
      type: pvc
      accessMode: ReadWriteMany
      storageClass: truenas

Your .env content

N/A

Reproduction steps

# Delete previous installation
kubectl delete ns immich

# Create PVC
kubectl apply -f pvc.yaml

# Install new deployment
helm upgrade -i --create-namespace \
    -n immich immich \
    -f values.yaml \
    immich/immich

Additional information

No response

Originally created by @djjudas21 on GitHub (Nov 30, 2023). ### The bug I have deployed a greenfield installation of Immich v1.89.0 with Helm chart v0.2.0. I saw the warning in the release notes ``` [!warning] If you are running your own Postgres database (not the one in our default docker-compose) and created the immich user yourself, you may need to enable the required extensions as the database superuser. You can do this by running the following two queries under the context of the immich database. This will only need to be run once. CREATE EXTENSION cube; CREATE EXTENSION earthdistance; ``` I figured a new installation with the Helm chart would behave the same as docker composer, but I am running into this error, which repeats a few times before Kubernetes kills the pod. ``` [Nest] 7 - 11/29/2023, 8:49:41 PM LOG [NestFactory] Starting Nest application... [Nest] 7 - 11/29/2023, 8:49:41 PM LOG [InstanceLoader] TypeOrmModule dependencies initialized +40ms [Nest] 7 - 11/29/2023, 8:49:41 PM LOG [InstanceLoader] BullModule dependencies initialized +0ms [Nest] 7 - 11/29/2023, 8:49:41 PM LOG [InstanceLoader] ConfigHostModule dependencies initialized +1ms [Nest] 7 - 11/29/2023, 8:49:41 PM LOG [InstanceLoader] DiscoveryModule dependencies initialized +0ms [Nest] 7 - 11/29/2023, 8:49:41 PM LOG [InstanceLoader] ScheduleModule dependencies initialized +0ms [Nest] 7 - 11/29/2023, 8:49:41 PM LOG [InstanceLoader] ConfigModule dependencies initialized +6ms [Nest] 7 - 11/29/2023, 8:49:41 PM LOG [InstanceLoader] BullModule dependencies initialized +1ms [Nest] 7 - 11/29/2023, 8:49:41 PM LOG [InstanceLoader] ScheduleModule dependencies initialized +0ms [Nest] 7 - 11/29/2023, 8:49:41 PM LOG [InstanceLoader] BullModule dependencies initialized +0ms [Nest] 7 - 11/29/2023, 8:49:49 PM ERROR [TypeOrmModule] Unable to connect to the database. Retrying (1)... QueryFailedError: permission denied to create extension "earthdistance" at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19) at process.processTicksAndRejections (node:internal/process/task_queues:95:5) at async Geodata1700362016675.up (/usr/src/app/dist/infra/migrations/1700362016675-Geodata.js:10:9) at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17) at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:260:35) at async DataSource.initialize (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:148:17) ``` Yes I can run the `CREATE EXTENSION` commands by hand but this issue will affect every new installation. ### The OS that Immich Server is running on MicroK8s v1.26 ### Version of Immich Server v1.89.0 ### Version of Immich Mobile App - ### Platform with the issue - [X] Server - [ ] Web - [ ] Mobile ### Your docker-compose.yml content ```YAML My Helm `values.yaml` for reference image: tag: v1.89.0 # These entries are shared between all the Immich components env: REDIS_HOSTNAME: '{{ printf "%s-redis-master" .Release.Name }}' DB_HOSTNAME: "{{ .Release.Name }}-postgresql" DB_USERNAME: "{{ .Values.postgresql.global.postgresql.auth.username }}" DB_DATABASE_NAME: "{{ .Values.postgresql.global.postgresql.auth.database }}" # -- You should provide your own secret outside of this helm-chart and use `postgresql.global.postgresql.auth.existingSecret` to provide credentials to the postgresql instance DB_PASSWORD: "{{ .Values.postgresql.global.postgresql.auth.password }}" TYPESENSE_ENABLED: "{{ .Values.typesense.enabled }}" TYPESENSE_API_KEY: "{{ .Values.typesense.env.TYPESENSE_API_KEY }}" TYPESENSE_HOST: '{{ printf "%s-typesense" .Release.Name }}' IMMICH_MACHINE_LEARNING_URL: '{{ printf "http://%s-machine-learning:3003" .Release.Name }}' immich: persistence: # Main data store for all photos shared between different components. library: # Automatically creating the library volume is not supported by this chart # You have to specify an existing PVC to use existingClaim: immich-library # Dependencies postgresql: enabled: true global: storageClass: ceph-block primary: resources: requests: memory: 64Mi cpu: 10m priorityClassName: "database" persistence: enabled: true storageClass: ceph-block accessModes: - ReadWriteOnce size: 1Gi metrics: enabled: true resources: requests: cpu: 1m memory: 8Mi serviceMonitor: enabled: true prometheusRule: enabled: true redis: enabled: true global: storageClass: ceph-block master: resources: limits: memory: 256Mi requests: cpu: 20m memory: 32Mi persistence: enabled: true retain: true storageClass: ceph-block accessModes: - ReadWriteOnce size: 1Gi priorityClassName: database metrics: enabled: true serviceMonitor: enabled: true prometheusRule: enabled: true resources: requests: cpu: 1m memory: 16Mi typesense: enabled: true persistence: tsdata: # Enabling typesense persistence is recommended to avoid slow reindexing enabled: true accessMode: ReadWriteOnce size: 1Gi storageClass: ceph-block # Immich components server: enabled: true image: repository: ghcr.io/immich-app/immich-server pullPolicy: IfNotPresent ingress: main: enabled: true annotations: # proxy-body-size is set to 0 to remove the body limit on file uploads nginx.ingress.kubernetes.io/proxy-body-size: "0" cert-manager.io/cluster-issuer: "letsencrypt-prod" hosts: - host: immich.example.com paths: - path: "/" tls: - secretName: ingress-tls hosts: - immich.example.com microservices: enabled: true persistence: geodata-cache: enabled: true size: 1Gi # Optional: Set this to pvc to avoid downloading the geodata every start. type: pvc accessMode: ReadWriteMany storageClass: truenas machine-learning: enabled: true persistence: cache: enabled: true size: 10Gi # Optional: Set this to pvc to avoid downloading the ML models every start. type: pvc accessMode: ReadWriteMany storageClass: truenas ``` ### Your .env content ```Shell N/A ``` ### Reproduction steps ```bash # Delete previous installation kubectl delete ns immich # Create PVC kubectl apply -f pvc.yaml # Install new deployment helm upgrade -i --create-namespace \ -n immich immich \ -f values.yaml \ immich/immich ``` ### Additional information _No response_
Author
Owner

@bo0tzz commented on GitHub (Nov 30, 2023):

The default user created by the bitnami postgres chart doesn't have CREATE EXTENSION permissions (https://github.com/bitnami/containers/issues/30065). I don't know if there's much we can do about that.

@bo0tzz commented on GitHub (Nov 30, 2023): The default user created by the bitnami postgres chart doesn't have CREATE EXTENSION permissions (https://github.com/bitnami/containers/issues/30065). I don't know if there's much we can do about that.
Author
Owner

@djjudas21 commented on GitHub (Nov 30, 2023):

Oh that's a pain. As a workaround, can you use the privileged postgres user rather than the limited user?

https://github.com/bitnami/charts/blob/main/bitnami/postgresql/values.yaml#L31

@djjudas21 commented on GitHub (Nov 30, 2023): Oh that's a pain. As a workaround, can you use the privileged `postgres` user rather than the limited user? https://github.com/bitnami/charts/blob/main/bitnami/postgresql/values.yaml#L31
Author
Owner

@happyxhw commented on GitHub (Nov 30, 2023):

adding superuser permisson works

alter user immich with superuser;

start immich, then drop

alter user immich with nosuperuser;
``
@happyxhw commented on GitHub (Nov 30, 2023): adding superuser permisson works ``` alter user immich with superuser; ``` start immich, then drop ``` alter user immich with nosuperuser; ``
Author
Owner

@Deses commented on GitHub (Dec 1, 2023):

I'm doing that through my root user using dbeaver, giving superuser perms to my immich user, but I still get the error that I cannot create the extension earthdistance.

Using root user:

alter user immich with superuser;

Using Immich user:

CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT exists earthdistance;

Error:

SQL Error [42501]: ERROR: permission denied to create extension "earthdistance"
  Hint: Must be superuser to create this extension.

And if it's any use, I created the DB and User like so:

CREATE DATABASE immich;
CREATE USER immich WITH ENCRYPTED PASSWORD 'verygoodpassword';
GRANT ALL PRIVILEGES ON DATABASE immich TO immich;

What could be wrong? :(

@Deses commented on GitHub (Dec 1, 2023): I'm doing that through my root user using dbeaver, giving superuser perms to my immich user, but I still get the error that I cannot create the extension `earthdistance`. Using root user: ```SQL alter user immich with superuser; ``` Using Immich user: ```SQL CREATE EXTENSION IF NOT EXISTS cube; CREATE EXTENSION IF NOT exists earthdistance; ``` Error: ``` SQL Error [42501]: ERROR: permission denied to create extension "earthdistance" Hint: Must be superuser to create this extension. ``` And if it's any use, I created the DB and User like so: ```SQL CREATE DATABASE immich; CREATE USER immich WITH ENCRYPTED PASSWORD 'verygoodpassword'; GRANT ALL PRIVILEGES ON DATABASE immich TO immich; ``` What could be wrong? :(
Author
Owner

@Deses commented on GitHub (Dec 1, 2023):

I fixed my issue by doing the following steps. If anyone is running postgress and immich in different containers like you would do if you use Unraid, please read:

  • First open a terminal session to your unraid machine.
  • Connect to your postgres docker:
    docker exec -it postgresql14 bash postgresql14 is the name of my container.
  • Connect to your root database:
    psql -U root -W postgres to connect to the postgres database using root user.
    Now you should be in the DB promp postgres=#
  • Now run alter user immich with superuser;
  • Do the CREATE EXTENSION commands with your preferred tool (I used DBeaver)
  • If successful, run
    alter user immich with nosuperuser;
@Deses commented on GitHub (Dec 1, 2023): I fixed my issue by doing the following steps. If anyone is running postgress and immich in different containers like you would do if you use Unraid, please read: - First open a terminal session to your unraid machine. - Connect to your postgres docker: `docker exec -it postgresql14 bash` postgresql14 is the name of my container. - Connect to your root database: `psql -U root -W postgres` to connect to the `postgres` database using `root` user. Now you should be in the DB promp `postgres=#` - Now run `alter user immich with superuser;` - Do the `CREATE EXTENSION` commands with your preferred tool (I used DBeaver) - If successful, run `alter user immich with nosuperuser;`
Author
Owner

@akhan23wgu commented on GitHub (Dec 2, 2023):

I ran with the solution provided in https://github.com/bitnami/charts/blob/main/bitnami/postgresql/values.yaml#L31 and changed to the postgresql user w/ my own DB credentials. Obviously not ideal, but waiting for a better workaround.

@akhan23wgu commented on GitHub (Dec 2, 2023): I ran with the solution provided in https://github.com/bitnami/charts/blob/main/bitnami/postgresql/values.yaml#L31 and changed to the postgresql user w/ my own DB credentials. Obviously not ideal, but waiting for a better workaround.
Author
Owner

@NiklasRosenstein commented on GitHub (Dec 27, 2023):

I'm running into the same issue, but creating the extensions manually doesn't seem to fix the issue. Using immich-chart v0.3.1 and immich-app v1.91.0

postgres=# \dx
                                                    List of installed extensions
     Name      | Version |   Schema   |                                         Description
---------------+---------+------------+----------------------------------------------------------------------------------------------
 cube          | 1.5     | public     | data type for multidimensional cubes
 earthdistance | 1.1     | public     | calculate great-circle distances on the surface of the Earth
 plpgsql       | 1.0     | pg_catalog | PL/pgSQL procedural language
 vectors       | 0.1.13  | public     | vectors: Vector database plugin for Postgres, written in Rust, specifically designed for LLM
(4 rows)

But the immich-server still tries to create the extension on startup.

│ [Nest] 7  - 12/27/2023, 5:22:01 PM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (9)...                                                                                             │
│ QueryFailedError: permission denied to create extension "earthdistance"                                                                                                                                   │
│     at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19)                                                                                        │
│     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)                                                                                                                         │
│     at async Geodata1700362016675.up (/usr/src/app/dist/infra/migrations/1700362016675-Geodata.js:10:9)                                                                                                   │
│     at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)                                                                         │
│     at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:260:35)                                                                                                │
│     at async DataSource.initialize (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:148:17)                                                                                                   │
│ [Nest] 7  - 12/27/2023, 5:22:01 PM   ERROR [ExceptionHandler] permission denied to create extension "earthdistance"                                                                                       │
│ QueryFailedError: permission denied to create extension "earthdistance"                                                                                                                                   │
│     at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19)                                                                                        │
│     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)                                                                                                                         │
│     at async Geodata1700362016675.up (/usr/src/app/dist/infra/migrations/1700362016675-Geodata.js:10:9)                                                                                                   │
│     at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)                                                                         │
│     at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:260:35)                                                                                                │
│     at async DataSource.initialize (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:148:17)                                                                                                   │
│ Stream closed EOF for immich-dev/immich-server-67947c5b58-9558f (immich-server)                            ```
@NiklasRosenstein commented on GitHub (Dec 27, 2023): I'm running into the same issue, but creating the extensions manually doesn't seem to fix the issue. Using immich-chart v0.3.1 and immich-app v1.91.0 ``` postgres=# \dx List of installed extensions Name | Version | Schema | Description ---------------+---------+------------+---------------------------------------------------------------------------------------------- cube | 1.5 | public | data type for multidimensional cubes earthdistance | 1.1 | public | calculate great-circle distances on the surface of the Earth plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language vectors | 0.1.13 | public | vectors: Vector database plugin for Postgres, written in Rust, specifically designed for LLM (4 rows) ``` But the `immich-server` still tries to create the extension on startup. ``` │ [Nest] 7 - 12/27/2023, 5:22:01 PM ERROR [TypeOrmModule] Unable to connect to the database. Retrying (9)... │ │ QueryFailedError: permission denied to create extension "earthdistance" │ │ at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19) │ │ at process.processTicksAndRejections (node:internal/process/task_queues:95:5) │ │ at async Geodata1700362016675.up (/usr/src/app/dist/infra/migrations/1700362016675-Geodata.js:10:9) │ │ at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17) │ │ at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:260:35) │ │ at async DataSource.initialize (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:148:17) │ │ [Nest] 7 - 12/27/2023, 5:22:01 PM ERROR [ExceptionHandler] permission denied to create extension "earthdistance" │ │ QueryFailedError: permission denied to create extension "earthdistance" │ │ at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19) │ │ at process.processTicksAndRejections (node:internal/process/task_queues:95:5) │ │ at async Geodata1700362016675.up (/usr/src/app/dist/infra/migrations/1700362016675-Geodata.js:10:9) │ │ at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17) │ │ at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:260:35) │ │ at async DataSource.initialize (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:148:17) │ │ Stream closed EOF for immich-dev/immich-server-67947c5b58-9558f (immich-server) ```
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: immich-app/immich#1704