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 seems the issue is related to the way the temporal-sql-tool init container tries to connect to the database. The error message indicates that the container is trying to connect to the database using the default database names postgres and defaultdb, rather than the one configured in the Helm chart (citus).

The init container is using the SQL_DATABASE environment variable to specify the database name, but it's possible that this variable isn't being correctly set or overridden.

To troubleshoot this, please provide the following information:

1. Helm chart version: What version of the Temporal Helm chart are you using?
2. Temporal version: What version of the Temporal server are you running?
3. Logs from the Temporal server: Can you share the logs from the Temporal server itself? This might provide more context about the database connection issue.

Once you provide this information, I can help you determine the root cause of the issue and provide a solution.

[dhiaayachi]

Thank you for reporting this issue.

Based on the logs you have provided, the problem seems to be related to the PostgreSQL configuration in your Kubernetes deployment. The error message indicates that the database can't be reached from the create-default-store init container.

Could you please provide the following information to help us understand the situation better:

• Is the PostgreSQL database exposed as a service within your Kubernetes cluster? If so, please share the service definition.
• What is the IP address of the PostgreSQL service within your Kubernetes cluster?
• Are there any firewall rules on the PostgreSQL server that might be blocking the init container's connection?

Once you provide this information, we'll be able to assist you further in resolving the issue.

[dhiaayachi]

Thanks for reporting this issue. It seems the temporal-sql-tool init container is unable to connect to your PostgreSQL instance.

From the logs:

> 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"}

You're getting pq: no pg_hba.conf entry error. This means your PostgreSQL server is not configured to allow connections from the host running the temporal-sql-tool container. You need to update your pg_hba.conf file on your PostgreSQL server to allow connections from the Kubernetes pod network where temporal-sql-tool is running.

To resolve this:

1. Identify the Kubernetes Pod IP: Use kubectl get pods -n <namespace> command to get the pod name and then use kubectl describe pod <pod-name> -n <namespace> command to find the pod's IP address.

2. Update pg_hba.conf: Add a new entry in your pg_hba.conf file on your PostgreSQL server to allow connections from the pod IP. You can use host authentication method, for example:

   host    citus    citus    10.63.7.94/32    trust

   Replace 10.63.7.94 with the identified pod IP and ensure citus user has necessary privileges.

3. Restart PostgreSQL: Restart your PostgreSQL service for the changes to take effect.

After making these changes, run your init container again and verify if it successfully connects to your PostgreSQL instance.

For more information on pg_hba.conf configuration, refer to the PostgreSQL documentation: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html