prod-create-default-database fails b/c pg_hba.conf

[ctriley]

I'm trying to spin up a temporal cluster but the job prod-create-default-database fails with the error

2024-06-25T19:05:36.348Z 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 "ip", user "username", database "postgres", no encryption pq: no pg_hba.conf entry for host "ip", user "user", database "defaultdb", no encryption]", "logging-call-at": "handler.go:94"}

This job seems to be trying to run the db init script temporal provides

# in https://github.com/temporalio/temporal git repo dir
export SQL_PLUGIN=postgres12
export SQL_HOST=
export SQL_PORT=
export SQL_USER=
export SQL_PASSWORD=

make temporal-sql-tool

./temporal-sql-tool --database temporal create-database
SQL_DATABASE=temporal ./temporal-sql-tool setup-schema -v 0.0
SQL_DATABASE=temporal ./temporal-sql-tool update -schema-dir schema/postgresql/v12/temporal/versioned

./temporal-sql-tool --database temporal_visibility create-database
SQL_DATABASE=temporal_visibility ./temporal-sql-tool setup-schema -v 0.0
SQL_DATABASE=temporal_visibility ./temporal-sql-tool update -schema-dir schema/postgresql/v12/visibility/versioned

When I try to run this script locally, I get a similar error. The way to fix this is to add the following env vars:

export SQL_TLS='true'
export SQL_TLS_DISABLE_HOST_VERIFICATION='true'
export SQL_TLS_ENABLED='true'
export SQL_HOST_VERIFICATION='false'

I got these vars from a discussion in the temporal repo airbytehq/airbyte#11157 (comment)

How can I add these env vars to the job in kubernetes?

[alexandrevilain]

Hi @ctriley !

Yes the operator provides such options in each datastores spec, see: https://temporal-operator.pages.dev/api/v1beta1/#temporal.io/v1beta1.DatastoreTLSSpec

[ctriley]

Thanks! Followed your suggestion -- here is the yaml that fixed the issue for me for anyone else that comes across this discussion, specifically this part:

      tls:
        enabled: true
        enableHostVerification: false

Full yaml file:

apiVersion: temporal.io/v1beta1
kind: TemporalCluster
metadata:
  name: prod
  namespace: temporal-system
spec:
  version: 1.23.1
  numHistoryShards: 1
  persistence:
    defaultStore:
      sql:
        user:
        pluginName: postgres
        databaseName: temporal
        connectAddr: address
        connectProtocol: tcp
      tls:
        enabled: true
        enableHostVerification: false
      passwordSecretRef:
        name: postgres-password
        key: postgres-password
    visibilityStore:
      sql:
        user: 
        pluginName: postgres
        databaseName: temporal_visibility
        connectAddr: 
        connectProtocol: tcp
      tls:
        enabled: true
        enableHostVerification: false
      passwordSecretRef:
        name: postgres-password
        key: postgres-password