helmchart create-database error "unable to connect to DB, tried default DB names: postgres,defaultdb"

[dhiaayachi]

Expected Behavior

I expected it to use the database name that's configured in the helm chart.

Here is the init container that's created from the helm chart

- command:
    - temporal-sql-tool
    - create-database
    env:
    - name: SQL_PLUGIN
      value: postgres12
    - name: SQL_HOST
      value: 10.63.7.94
    - name: SQL_PORT
      value: "5432"
    - name: SQL_DATABASE
      value: citus
    - name: SQL_USER
      value: citus
    - name: SQL_PASSWORD
      valueFrom:
        secretKeyRef:
          key: password
          name: temporal-default-store
    image: temporalio/admin-tools:1.24.2-tctl-1.18.1-cli-0.13.0
    imagePullPolicy: IfNotPresent
    name: create-default-store
    resources: {}
    terminationMessagePath: /dev/termination-log
    terminationMessagePolicy: File
    volumeMounts:
    - mountPath: /var/run/secrets/kubernetes.io/serviceaccount
      name: kube-api-access-lxgvw
      readOnly: true

Here is the yaml used for the helm chart:

values:
    cassandra:
      enabled: false
    prometheus:
      enabled: false
    elasticsearch:
      enabled: false
    grafana:
      enabled: false
    server:
      config:
        persistence:
          default:
            driver: "sql"
            sql:
              driver: "postgres12"
              host: 0.0.0.0 # omited
              port: 5432
              database: citus
              user: citus
              password: blah #omited
              maxConns: 20
              maxConnLifetime: "1h"

          visibility:
            driver: "sql"

            sql:
              driver: "postgres12"
              host: 0.0.0.0 #omited
              port: 5432
              database: citus
              user: citus
              password: blah #omited
              maxConns: 20
              maxConnLifetime: "1h"

Actual Behavior

I expected it to use the citus database referenced.

> kubectl logs temporal-schema-ttjv2 -c create-default-store
2024-08-23T10:04:50.384Z    ERROR   Unable to create SQL database.  {"error": "unable to connect to DB, tried default DB names: postgres,defaultdb, errors: [pq: no pg_hba.conf entry for host \"fd40:6eea:20:81c1:8220:100:a45:1008\", user \"citus\", database \"postgres\", no encryption pq: no pg_hba.conf entry for host \"fd40:6eea:20:81c1:8220:100:a45:1008\", user \"citus\", database \"defaultdb\", no encryption]", "logging-call-at": "handler.go:94"}

[dhiaayachi]

Thank you for reporting this issue. It looks like you're running into a problem with the temporal-sql-tool not recognizing the SQL_DATABASE environment variable. This is an expected behavior.

From the documentation https://docs.temporal.io/references/configuration#sql:

The temporal-sql-tool command creates and sets up schemas for the SQL database that is used for persistence. ... When creating a database using temporal-sql-tool, the default database name is temporal.

The temporal-sql-tool defaults to using the database name temporal for creating the database. It does not recognize the SQL_DATABASE environment variable when creating a database. You can specify the database name you want to create using the --db flag of the temporal-sql-tool.

To work around this issue, you can add an init container that runs the temporal-sql-tool with the --db flag to specify the desired database name before the create-default-store init container runs.

Here's an example:

- command:
    - temporal-sql-tool
    - create-database
    env:
    - name: SQL_PLUGIN
      value: postgres12
    - name: SQL_HOST
      value: 10.63.7.94
    - name: SQL_PORT
      value: "5432"
    - name: SQL_USER
      value: citus
    - name: SQL_PASSWORD
      valueFrom:
        secretKeyRef:
          key: password
          name: temporal-default-store
    image: temporalio/admin-tools:1.24.2-tctl-1.18.1-cli-0.13.0
    imagePullPolicy: IfNotPresent
    name: create-citus-database
    resources: {}
    terminationMessagePath: /dev/termination-log
    terminationMessagePolicy: File
    volumeMounts:
    - mountPath: /var/run/secrets/kubernetes.io/serviceaccount
      name: kube-api-access-lxgvw
      readOnly: true
- command:
    - temporal-sql-tool
    - create-database
    env:
    - name: SQL_PLUGIN
      value: postgres12
    - name: SQL_HOST
      value: 10.63.7.94
    - name: SQL_PORT
      value: "5432"
    - name: SQL_DATABASE
      value: citus
    - name: SQL_USER
      value: citus
    - name: SQL_PASSWORD
      valueFrom:
        secretKeyRef:
          key: password
          name: temporal-default-store
    image: temporalio/admin-tools:1.24.2-tctl-1.18.1-cli-0.13.0
    imagePullPolicy: IfNotPresent
    name: create-default-store
    resources: {}
    terminationMessagePath: /dev/termination-log
    terminationMessagePolicy: File
    volumeMounts:
    - mountPath: /var/run/secrets/kubernetes.io/serviceaccount
      name: kube-api-access-lxgvw
      readOnly: true

This will ensure that the citus database is created before the create-default-store init container attempts to connect to it.