SSL postgres and temporal

can’t connect to PostgreSQL, odyssey (same Pgbouncer) requires ssl connection.

my Swarm stack file

services:
temporal:
environment:
- DB=postgresql
- DB_PORT=6432
- SSL=true
- SQL_TLS=true
- SQL_TLS_DISABLE_HOST_VERIFICATION=true
- POSTGRES_USER=xxx
- POSTGRES_PWD=xxx
- POSTGRES_SEEDS=1.1.1.1
- DYNAMIC_CONFIG_FILE_PATH=config/dynamicconfig/development_es.yaml
- ENABLE_ES=true
- ES_SEEDS=tasks.es2
- ES_PORT=9200
- ES_SCHEME=
- ES_USER=xxx
- ES_PWD=xxx
- ES_VIS_INDEX=temporal_visibility_v1
- ES_VERSION=v7
- BIND_ON_IP=0.0.0.0
- TEMPORAL_BROADCAST_ADDRESS=127.0.0.1
image: temporalio/auto-setup:1.15.0
configs:
- source: temporal.yaml
target: /etc/temporal/config/dynamicconfig/development_es.yaml
deploy:
placement:
constraints:
- node.labels.name==a1

stdout error log
temporal_temporal.1.4dcnlu562esn@a1 | + DB=postgresql
temporal_temporal.1.4dcnlu562esn@a1 | + SKIP_SCHEMA_SETUP=false
temporal_temporal.1.4dcnlu562esn@a1 | + KEYSPACE=temporal
temporal_temporal.1.4dcnlu562esn@a1 | + VISIBILITY_KEYSPACE=temporal_visibility
temporal_temporal.1.4dcnlu562esn@a1 | + CASSANDRA_SEEDS=
temporal_temporal.1.4dcnlu562esn@a1 | + CASSANDRA_PORT=9042
temporal_temporal.1.4dcnlu562esn@a1 | + CASSANDRA_USER=
temporal_temporal.1.4dcnlu562esn@a1 | + CASSANDRA_PASSWORD=
temporal_temporal.1.4dcnlu562esn@a1 | + CASSANDRA_TLS_ENABLED=
temporal_temporal.1.4dcnlu562esn@a1 | + CASSANDRA_CERT=
temporal_temporal.1.4dcnlu562esn@a1 | + CASSANDRA_CERT_KEY=
temporal_temporal.1.4dcnlu562esn@a1 | + CASSANDRA_CA=
temporal_temporal.1.4dcnlu562esn@a1 | + CASSANDRA_REPLICATION_FACTOR=1
temporal_temporal.1.4dcnlu562esn@a1 | + DBNAME=temporal
temporal_temporal.1.4dcnlu562esn@a1 | + VISIBILITY_DBNAME=temporal_visibility
temporal_temporal.1.4dcnlu562esn@a1 | + DB_PORT=6432
temporal_temporal.1.4dcnlu562esn@a1 | + MYSQL_SEEDS=
temporal_temporal.1.4dcnlu562esn@a1 | + MYSQL_USER=
temporal_temporal.1.4dcnlu562esn@a1 | + MYSQL_PWD=
temporal_temporal.1.4dcnlu562esn@a1 | + MYSQL_TX_ISOLATION_COMPAT=false
temporal_temporal.1.4dcnlu562esn@a1 | + POSTGRES_SEEDS=1.1.1.1
temporal_temporal.1.4dcnlu562esn@a1 | + POSTGRES_USER=xxx
temporal_temporal.1.4dcnlu562esn@a1 | + POSTGRES_PWD=‘xxx’
temporal_temporal.1.4dcnlu562esn@a1 | + ENABLE_ES=true
temporal_temporal.1.4dcnlu562esn@a1 | + ES_SCHEME=
temporal_temporal.1.4dcnlu562esn@a1 | + ES_SEEDS=tasks.es2
temporal_temporal.1.4dcnlu562esn@a1 | + ES_PORT=9200
temporal_temporal.1.4dcnlu562esn@a1 | + ES_USER=xxx
temporal_temporal.1.4dcnlu562esn@a1 | + ES_PWD=xxx
temporal_temporal.1.4dcnlu562esn@a1 | + ES_VERSION=v7
temporal_temporal.1.4dcnlu562esn@a1 | + ES_VIS_INDEX=temporal_visibility_v1
temporal_temporal.1.4dcnlu562esn@a1 | + ES_SCHEMA_SETUP_TIMEOUT_IN_SECONDS=0
temporal_temporal.1.4dcnlu562esn@a1 | + TEMPORAL_CLI_ADDRESS=0.0.0.0:7233
temporal_temporal.1.4dcnlu562esn@a1 | + SKIP_DEFAULT_NAMESPACE_CREATION=false
temporal_temporal.1.4dcnlu562esn@a1 | + DEFAULT_NAMESPACE=default
temporal_temporal.1.4dcnlu562esn@a1 | + DEFAULT_NAMESPACE_RETENTION=1
temporal_temporal.1.4dcnlu562esn@a1 | + SKIP_ADD_CUSTOM_SEARCH_ATTRIBUTES=false
temporal_temporal.1.4dcnlu562esn@a1 | + ‘[’ false ‘!=’ true ‘]’
temporal_temporal.1.4dcnlu562esn@a1 | + validate_db_env
temporal_temporal.1.4dcnlu562esn@a1 | + ‘[’ postgresql == mysql ‘]’
temporal_temporal.1.4dcnlu562esn@a1 | + ‘[’ postgresql == postgresql ‘]’
temporal_temporal.1.4dcnlu562esn@a1 | + ‘[’ -z 1.1.1.1’]’
temporal_temporal.1.4dcnlu562esn@a1 | + wait_for_db
temporal_temporal.1.4dcnlu562esn@a1 | + ‘[’ postgresql == mysql ‘]’
temporal_temporal.1.4dcnlu562esn@a1 | + ‘[’ postgresql == postgresql ‘]’
temporal_temporal.1.4dcnlu562esn@a1 | + wait_for_postgres
temporal_temporal.1.4dcnlu562esn@a1 | + nc -z 1.1.1.16432
temporal_temporal.1.4dcnlu562esn@a1 | + echo ‘PostgreSQL started.’
temporal_temporal.1.4dcnlu562esn@a1 | + setup_schema
temporal_temporal.1.4dcnlu562esn@a1 | PostgreSQL started.
temporal_temporal.1.4dcnlu562esn@a1 | + ‘[’ postgresql == mysql ‘]’
temporal_temporal.1.4dcnlu562esn@a1 | + ‘[’ postgresql == postgresql ‘]’
temporal_temporal.1.4dcnlu562esn@a1 | + echo ‘Setup PostgreSQL schema.’
temporal_temporal.1.4dcnlu562esn@a1 | + setup_postgres_schema
temporal_temporal.1.4dcnlu562esn@a1 | Setup PostgreSQL schema.
temporal_temporal.1.4dcnlu562esn@a1 | + SCHEMA_DIR=/etc/temporal/schema/postgresql/v96/temporal/versioned
temporal_temporal.1.4dcnlu562esn@a1 | + ‘[’ temporal ‘!=’ temporal ‘]’
temporal_temporal.1.4dcnlu562esn@a1 | + temporal-sql-tool --plugin postgres --ep 1.1.1.1-u temporal -p 6432 --db temporal setup-schema -v 0.0
temporal_temporal.1.4dcnlu562esn@a1 | 2022-03-18T02:22:54.415Z INFO Starting schema setup {“config”: {“SchemaFilePath”:"",“InitialVersion”:“0.0”,“Overwrite”:false,“DisableVersioning”:false}, “logging-call-at”: “setuptask.go:57”}
temporal_temporal.1.4dcnlu562esn@a1 | 2022-03-18T02:22:54.415Z DEBUG Setting up version tables {“logging-call-at”: “setuptask.go:67”}
temporal_temporal.1.4dcnlu562esn@a1 | 2022-03-18T02:22:54.421Z DEBUG Current database schema version 1.7 is greater than initial schema version 0.0. Skip version upgrade {“logging-call-at”: “setuptask.go:116”}
temporal_temporal.1.4dcnlu562esn@a1 | 2022-03-18T02:22:54.421Z INFO Schema setup complete {“logging-call-at”: “setuptask.go:131”}
temporal_temporal.1.4dcnlu562esn@a1 | + temporal-sql-tool --plugin postgres --ep 1.1.1.1-u temporal -p 6432 --db temporal update-schema -d /etc/temporal/schema/postgresql/v96/temporal/versioned
temporal_temporal.1.4dcnlu562esn@a1 | 2022-03-18T02:22:54.464Z INFO UpdateSchemeTask started {“config”: {“DBName”:"",“TargetVersion”:"",“SchemaDir”:"/etc/temporal/schema/postgresql/v96/temporal/versioned",“IsDryRun”:false}, “logging-call-at”: “updatetask.go:97”}
temporal_temporal.1.4dcnlu562esn@a1 | 2022-03-18T02:22:54.467Z DEBUG Schema Dirs: {“logging-call-at”: “updatetask.go:186”}
temporal_temporal.1.4dcnlu562esn@a1 | 2022-03-18T02:22:54.467Z DEBUG found zero updates from current version 1.7 {“logging-call-at”: “updatetask.go:127”}
temporal_temporal.1.4dcnlu562esn@a1 | 2022-03-18T02:22:54.467Z INFO UpdateSchemeTask done {“logging-call-at”: “updatetask.go:120”}
temporal_temporal.1.4dcnlu562esn@a1 | + VISIBILITY_SCHEMA_DIR=/etc/temporal/schema/postgresql/v96/visibility/versioned
temporal_temporal.1.4dcnlu562esn@a1 | + ‘[’ temporal_visibility ‘!=’ temporal ‘]’
temporal_temporal.1.4dcnlu562esn@a1 | + temporal-sql-tool --plugin postgres --ep 1.1.1.1-u temporal -p 6432 create --db temporal_visibility
temporal_temporal.1.4dcnlu562esn@a1 | 2022-03-18T02:22:54.502Z ERROR Unable to create SQL database. {“error”: “pq: permission denied to create database”, “logging-call-at”: “handler.go:98”}
temporal_temporal.1.4dcnlu562esn@a1 | 2022/03/18 02:22:54 Loading config; env=docker,zone=,configDir=config
temporal_temporal.1.4dcnlu562esn@a1 | 2022/03/18 02:22:54 Loading config files=[config/docker.yaml]
temporal_temporal.1.4dcnlu562esn@a1 | {“level”:“info”,“ts”:“2022-03-18T02:22:54.532Z”,“msg”:“Build info”,“timestamp”:“2022-02-09T04:42:51.000Z”,“git-revision”:“0e9f41593”,“platform”:“amd64”,“go-version”:“go1.17.6”,“server-version”:“1.15.0”,“logging-call-at”:“main.go:136”}
temporal_temporal.1.4dcnlu562esn@a1 | {“level”:“info”,“ts”:“2022-03-18T02:22:54.533Z”,“msg”:“Updated dynamic config”,“logging-call-at”:“file_based_client.go:142”}
temporal_temporal.1.4dcnlu562esn@a1 | Unable to start server. Error: could not build arguments for function “go.temporal_io/server/common/pprof”.LifetimeHooks (/temporal/common/pprof/fx.go:39): failed to build *pprof.PProfInitializerImpl: could not build arguments for function “go.temporal_io/server/common/pprof”.NewInitializer (/temporal/common/pprof/pprof.go:56): failed to build *config.PProf: could not build arguments for function “go.temporal_io/server/temporal”.SoExpander (/temporal/temporal/fx.go:480): failed to build *temporal.serverOptions: received non-nil error from function “go.temporal_io/server/temporal”.ServerOptionsProvider (/temporal/temporal/fx.go:506): sql schema version compatibility check failed: pq: odyssey: c54e478773553: SSL is required

if i use manual command
“temporal-sql-tool --plugin postgres --ep 1.1.1.1-u temporal -p 6432 --db temporal setup-schema -v 0.0”
ssl connection is used, but the Temporal does not use SSL when start.

ENV SQL_TLS and SQL_TLS_DISABLE_HOST_VERIFICATION have in container, i checked

Hi @arkasha18, I think you need to set
SQL_TLS_ENABLED=true
in your autosetup “environment” section, as its set to false by default.

temporal-sql-tool on the other hand uses SQL_TLS, which is a bit confusing. Opened issue here.

See also related issue here.

Work for me! Thanks!

1 Like