When Temporal wants to communicate with a postgresql database, a connection (no TLS) is created between the Haproxy and Temporal (it could be temporal directly or temporal-sql-tool).
Then the data is sent to the Haproxy and Haproxy will create a new mTLS connection between Haproxy and the postgresql database.
In that case, it works.
But we would like to set up a mTLS connection between Temporal and the Haproxy.
Actual Behavior
When we try to set up a mTLS connection between Temporal and the Haproxy, we receive a plain error message that says just TLS handshake fails without more details. We did not manage to have a more detailed message from Temporal or Haproxy.
Nevertheless, we manage to call our databse postgresql from the Temporal worker container through the Haproxy by doing a GET call with curl in mTLS.
Our question is :
Is Temporal designed to communicate with a postrgresql server through haproxy in mTLS ?
Steps to Reproduce the Problem
For example the following command which is executed inside the temporal worker container, does not work
hp is the hostname to contact haproxy inside the k8s cluster
In the same container (temporal worker), the following curl works:
2. curl https://hp:ourPort --cacert ca.crt --cert ourCert.crt --key ourPrivateKey.key
Hi @Laurent I see that you opened issue here with same information.
Could you provide more info, for example what errors are you getting and your tls config.
I’m able to verify that mTLS between temporal-sql-tool (version 1.16.0) and postgresql (version 14) works on my local machine. I don’t have haproxy in the picture.
To help you trouble shoot your problem, you can do:
Verify you can connect to your postgresql from within temporal container via haproxy using mTLS. To do this, you should use psql as an independent tool, this will isolate the problem from temporal. (I’m not sure you can confirm the mTLS works with the curl that you were using, at least it cannot confirm that it can create a db.) If you can not do this, then the problem has nothing to do with temporal. Below is the command line that I used to verify this on my local machine:
Obviously, you need to run with your own host/port/user/dbname/sslcert/sslkey/sslrootcert.
If you see any errors, you need to fix them before you processed.
Once you confirm that last step works, you can use below command to run temporal-sql-tool:
The temporal-sql-tool should give you clear error message if it does not work.
A few example of errors I got when I was tying to do this:
== private key file permissions should be u=rw (0600) or less.
$ ./temporal-sql-tool --tls --tls-cert-file client.pem --tls-key-file client-key.pem --tls-ca-file ca.pem --plugin postgres -p 5432 -u test create --db temporal
2022-04-15T23:10:26.930-0700 ERROR Unable to create SQL database. {"error": "unable to connect to DB, tried default DB names: postgres,defaultdb, errors: [pq: Private key file has group or world access. Permissions should be u=rw (0600) or less pq: Private key file has group or world access. Permissions should be u=rw (0600) or less]", "logging-call-at": "handler.go:98"}
== certificate usage should include 'client auth'
$ ./temporal-sql-tool --tls --tls-cert-file client.pem --tls-key-file client-key.pem --tls-ca-file ca.pem --plugin postgres -p 5432 -u test create --db temporal
2022-04-15T23:11:12.381-0700 ERROR Unable to create SQL database. {"error": "unable to connect to DB, tried default DB names: postgres,defaultdb, errors: [remote error: tls: unsupported certificate remote error: tls: unsupported certificate]", "logging-call-at": "handler.go:98"}
== certificate cn should match user name
$ ./temporal-sql-tool --tls --tls-cert-file client.pem --tls-key-file client-key.pem --tls-ca-file ca.pem --plugin postgres -p 5432 -u test create --db temporal
2022-04-15T23:26:14.760-0700 ERROR Unable to create SQL database. {"error": "unable to connect to DB, tried default DB names: postgres,defaultdb, errors: [pq: certificate authentication failed for user \"test\" pq: certificate authentication failed for user \"test\"]", "logging-call-at": "handler.go:98"}
== test user does not exist
$ ./temporal-sql-tool --tls --tls-cert-file client.pem --tls-key-file client-key.pem --tls-ca-file ca.pem --plugin postgres -p 5432 -u test create --db temporal
2022-04-15T23:38:35.078-0700 ERROR Unable to create SQL database. {"error": "unable to connect to DB, tried default DB names: postgres,defaultdb, errors: [pq: role \"test\" does not exist pq: role \"test\" does not exist]", "logging-call-at": "handler.go:98"}
== test user does not have permission to create db
$ ./temporal-sql-tool --tls --tls-cert-file client.pem --tls-key-file client-key.pem --tls-ca-file ca.pem --plugin postgres -p 5432 -u test create --db temporal
2022-04-15T23:41:41.798-0700 ERROR Unable to create SQL database. {"error": "pq: permission denied to create database", "logging-call-at": "handler.go:98"}