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

[djjudas21]

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

[bo0tzz]

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

[djjudas21]

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

[happyxhw]

adding superuser permisson works

alter user immich with superuser;

start immich, then drop

alter user immich with nosuperuser;
``

[Deses]

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]

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;

[akhan23wgu]

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.

[NiklasRosenstein]

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)                            ```