SSL connection with certificate verification

[tkilias]

Hi @wildraid,

We had a look into SSL certificate verification with the websocket api, and we saw that pyexasol uses ssl.CERT_NONE in case of encryption with no way to change it.

https://github.com/badoo/pyexasol/blob/fbb1949f0ff613cae709b3f8d9e0d0d599fedc85/pyexasol/connection.py#L676

We tested if certificate verification works in general with websockets and it seems to work. To test it, we created our own private key and certificates (Server certificate testExasolChain.pem which was signed by the Root CA testRootCA.pem) and specified them as follows.

In the Exasol Database we need to add the following parameter:

-tlsPrivateKeyPath=/tmp/certs/testExasol.key -tlsCertificatePath=/tmp/certs/testExasolChain.pem

Websocket client:

ws = websocket.create_connection(host, sslopt={"cert_reqs": ssl.CERT_REQUIRED, "ca_certs": "/tmp/certs/testRootCA.pem"})

Can we add an option to the connection which allows the user to specify a RootCA?

[littleK0i]

@tkilias , please check a new version 0.15.1.

I've added connection option websocket_sslopt which goes directly to WebSocket client sslopt argument.

388ffd2

Is there any way I can configure a custom SSL certificate for Exasol in Docker container easily? I don't see an easy way to test it automatically right now and I would appreciate your help.

[tkilias]

Hi @wildraid,

Cool!
I'm happy to help with the test setup. I am going to gather all necessary information and get back to you later. We could also think about to include the necessary functions into the integration-test-docker-environment, but this is not mandatory.

[tkilias]

@wildraid Ok, here is the general plan

1. Create certificates to $PWD/certificates
2. Create a ExaConf template
   • https://github.com/exasol/docker-db#b-create-an-exaconf-template
3. Add the database parameter to the ExaConf
   • https://community.exasol.com/t5/environment-management/setting-a-database-parameter-in-a-docker-based-exasol-system/ta-p/1353
4. Create docker-db container which mounts the certificates to the correct directory
   • docker run -v "$PWD/certificates":/tmp/certificates --rm -i exasol/docker-db:<version>

The best is probably having a script for all of this, I will be back when it is ready.

[tkilias]

Hi @wildraid,

I started with creating certificates and starting a docker-db with modified config. And, it seems we don't need to set the command line parameters for Exasol. The EXAConf already provides a SSL section which setups everything for us, if we provide the correct paths.

You can find the current state in my fork (PLEASE NOTE: it is still work in progress and doesn't work yet, the websocket client returns currently a verification error):
https://github.com/tkilias/pyexasol/tree/pyexasol_ssl_test

I continue tomorrow with it.

[tkilias]

Hi @wildraid ,

I was now finally able to set up a docker-db with certificates, such that the certificate verification works. The create_docker_db_container.sh create docker-db and the corresponding certs and test.py connects to the docker-db with certificate verification activated. Currently, some things are still hard coded, so the next step is cleanup and removing hard coded parameters.

Let me know, what you think. Can you use this for tests? I am not sure, if I can simplify it much more, but I am going to add a few comments to explain what the scripts do.

[tkilias]

Hi @wildraid ,
so, I finished the cleanup of the setup scrip in https://github.com/tkilias/pyexasol/tree/pyexasol_ssl_test/pyexasol_ssl_testt. Let me know if you need something else.

[littleK0i]

@tkilias , thank you!

I'll check this out in the next few days and add an automated test to Travis.

[littleK0i]

@tkilias , could you send a current link to an example, how to run Exasol in docker with custom SSL certificate? The old link does not work anymore.

Thank you.

[allipatev]

Hi @littleK0i ,
If you are still interested, it seems that you just need to remove one trailing t from the link https://github.com/tkilias/pyexasol/tree/pyexasol_ssl_test/pyexasol_ssl_testt and it works.

[littleK0i]

SSL connection with certification verification & testing was fully implemented after series of patches.

Latest documentation: https://github.com/exasol/pyexasol/blob/master/docs/ENCRYPTION.md
Workflow for testing: https://github.com/exasol/pyexasol/blob/master/.github/workflows/ssl_cert.yml