Security¶

PyExasol works with different Exasol database variants: on-premise, SAAS, and, for testing purposes, Docker-based. Each of these have shared and unique Authentication methods and require a TLS/SSL certificate setup. Throughout this guide on security, an overview of the security features of PyExasol is provided, as well as examples.

Changed Defaults Over Time¶

As Exasol constantly improves its built-in and recommended security measures, so too is the default security of PyExasol improved in subsequent versions. These improvements affect all functionality which rely upon the pyexasol.connect() function (and the class which it wraps, pyexasol.ExaConnection).

PyExasol Version	Encryption	Certificate Verification
< 0.24.0	unencrypted	not validated
>= 0.24.0	encrypted	not validated
>= 1.0.0	encrypted	validated

Thus, the current defaults, in relation to the pyexasol.connect() are:

Encryption - To use SSL to encrypt client-server communications for WebSocket & HTTP Transport, the encryption parameter is set to True.
Certificate Verification - When pyexasol.connect() is executed with websocket_sslopt=None, then this is effectively mapped to websocket_sslopt={"cert_reqs": ssl.CERT_REQUIRED}. For more details, see Certificate Verification.

Authentication¶

For the various Exasol DBs, there are different ways to set up and use the access credentials for a connection made with the pyexasol.connect() function.

Exasol DB	Setting Credentials	PyExasol parameters
on-premise	on-premise authentication	`user` `password`
SAAS	SAAS authentication	`user` `access_token` or `refresh_token`
Docker (testing)	Docker authentication	`user` `password`

Connect to Exasol on-premise or Docker

pyexasol.connect(dsn='myexasol:8563'
                 , user='user'
                 , password='password')

Connect to Exasol SAAS (TLS encryption is REQUIRED for SAAS):

pyexasol.connect(dsn='abc.cloud.exasol.com:8563'
                 , user='user'
                 , refresh_token='token'
                 )

pyexasol.connect(dsn='myexasol:8563'
                 , user='user'
                 , access_token='personal_access_token'
                 )

Transport Layer Security (TLS)¶

Similar to other Exasol connectors, PyExasol is compatible with using TLS cryptographic protocol. As a part of the TLS handshake, the drivers require the SSL/TLS certificate used by Exasol to be validated. This is the standard practice that increases the security of connections by preventing man-in-the-middle attacks.

Please check out the following documentation for user-friendly tutorials on TLS from Exasol:

For technical articles made by Exasol relating to TLS, please see:

Certificate Verification¶

As further discussed in Certificate and Certificate Agencies, there are three kinds of certificates:

ones from a public Certificate Authority (CA)
ones from a private CA
ones that are self-signed

Before using a certificate for certificate verification, your IT Admin should ensure that whatever certificate your Exasol instance uses is the most secure:

Exasol running on-premise uses a default self-signed SSL certificate. Your IT Admin should replace the certificate with one provided by your organization. For further context and instructions, see: - Conceptual: Incoming TLS Connections - TLS Certificate Instructions. - confd_client cert_update
Exasol SAAS running in the cloud uses a proper certificate generated by a public CA. It does not require any extra setup.
Exasol Docker uses a self-signed SSL certificate by default. You, as a user, may generate a proper SSL certificate and submit it for use via the ConfD API. More details are available on:

Setup¶

In order to validate a certificate which will be provided in your PyExasol connection (see Certificate Handling in PyExasol), you will need to have the certificate setup for your PyExasol usage: either on your Client machine or Inside a User Defined Function (UDF).

Client machine¶

Public CA
- The certificate should already be in the operating system truststore of the client machine.
Private CA (Corporate CA)
- Your IT should add it to the operating system truststore of the client machine.
- Or, use the fingerprint of the certificate. See Fingerprint Verification for details.
Self-signed Certificate
- Your IT should add it to the operating system truststore of the client machine.
  
  DBA needs to fetch the certificate from the Exasol Cluster.
  
  Client Machine Admin needs to add it to the operating system truststore.
- Or, in case of a unprivileged user and the user can access the certificate of the Exasol database you can specify the certificate during connect.
- Or, use the fingerprint of the certificate. See Fingerprint Verification for details.
- For testing with a local DB you can disable the certificate verification (however, this should NEVER be used for production).

Inside a User Defined Function (UDF)¶

Public CA
- The certificate should already be in the operating system truststore of the Script Language Container (SLC) of the UDF.
Private CA (Corporate CA)
- Your DBA should upload the certificate to BucketFS and you should pass it to the connect inside of the UDF.
  
  Note: The operating system truststore is part of the SLC and can only be changed during SLC creation. While you run a UDF, the operating system truststore inside of the UDF is read-only.
- Or, use the fingerprint of the certificate. See Fingerprint Verification for details.
Self-signed Certificate
- Your DBA or you should upload the certificate to BucketFS and you should pass it to the connect inside of the UDF.
  
  Note: The operating system truststore is part of the SLC and can only be changed during SLC creation. While you run a UDF, the operating system truststore of the UDF is read-only.
- Or, use the fingerprint of the certificate. See Fingerprint Verification for details.
- For testing with a local DB you can disable the certificate verification (however, this should NEVER be used for production).

Handling in PyExasol¶

Fingerprint Verification¶

Similar to JDBC / ODBC drivers, PyExasol supports fingerprint verification. For more information, see the ODBC entry on fingerprint.

fingerprint = "135a1d2dce102de866f58267521f4232153545a075dc85f8f7596f57e588a181"
pyexasol.connect(dsn=f'myexasol/{fingerprint}:8563'
                 , user='user'
                 , password='password'
                 )

Additionally, you can disable the certificate check completely by setting “nocertcheck” (case-insenstive) as fingerprint value:

pyexasol.connect(dsn=f'myexasol/nocertcheck:8563'
                 , user='user'
                 , password='password'
                 )

However, this should NEVER be used for production.

Passing into the Connection¶

This is how an unprivileged user can specify the certificate when making the connection.

pyexasol.connect(dsn='myexasol:8563'
                 , user='user'
                 , password='password'
                 , websocket_sslopt={
                    "cert_reqs": ssl.CERT_REQUIRED,
                    "ca_certs": '/path/to/rootCA.crt',
                 })

Disabling Certificate Verification¶

As discussed in the Changed Defaults Over Time, PyExasol by default has certificate verification turned on. This is to improve security and prevent man-in-the-middle attacks. In the case of testing with a local DB, a user might want to temporarily disable certificate verification. Due to the increased security risks, this change should never be used for production.

Warning

For more context regarding the security risks of disabling certificate verification, see An introduction to TLS.

pyexasol.connect(dsn='myexasol:8563'
                 , user='user'
                 , password='password'
                 , websocket_sslopt={"cert_reqs": ssl.CERT_NONE})

Alternatively, you can disable the certificate check by setting “nocertcheck” as fingerprint value, see Fingerprint Verification.