Security¶

SQLAlchemy-Exasol is a dialect extension for SQLAlchemy, which utilizes PyExasol in the backend. It works with different Exasol database variants: on-premise 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 SQLAlchemy-Exasol is provided.

Authentication¶

For the various Exasol DBs, there are different ways to set up and use the access credentials for a connection made with the Instance of URL or URL string provided to the engine.

Exasol DB	Setting Credentials	PyExasol parameters
on-premise	on-premise authentication	`username` `password`
SaaS	SAAS authentication	`username` `password`
Docker (testing)	Docker authentication	`username` `password`

Note

While PyExasol supports connecting with SaaS database instances using access_token or refresh_token, the SQLAlchemy-Exasol dialect does not yet support these.

Connect to Exasol on-premise or Docker

from sqlalchemy import create_engine, URL

  url_object = URL.create(
      drivername="exa+websocket",
      username="sys",
      password="exasol",
      host="127.0.0.1",
      port="8563",
  )

  create_engine(url_object)

Transport Layer Security (TLS)¶

Similar to other Exasol connectors, SQLAlchemy-Exasol supports using the cryptographic protocol TLS. As a part of the TLS handshake, the drivers require the SSL/TLS certificate used by Exasol to be verified. This is a standard practice to increase the security of connections by preventing man-in-the-middle attacks.

Please check out Exasol’s user-friendly tutorials on TLS:

Additionally, Exasol provides the following technical articles relating to TLS:

Certificate Verification¶

Certificates verification provides proof of the authenticity of the database you are connecting to.

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 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:

Note

For setting up a certificate, see the information provided in PyExasol’s security documentation.

Handling in SQLAlchemy-Exasol¶

Fingerprint Verification¶

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

from sqlalchemy import create_engine, URL

fingerprint = "0ACD07D4E9CEEB122773A71B9C3BD01CE49FC99901DE7C0E0030C942805BA64C"

url_object = URL.create(
    drivername="exa+websocket",
    username="sys",
    password="exasol",
    host="127.0.0.1",
    port="8563",
    query={"FINGERPRINT": fingerprint},
)

create_engine(url_object)

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

from sqlalchemy import create_engine, URL

url_object = URL.create(
    drivername="exa+websocket",
    username="sys",
    password="exasol",
    host="127.0.0.1",
    port="8563",
    query={"FINGERPRINT": "nocertcheck"},
)

create_engine(url_object)

Warning

However, this should NEVER be used for production.

Custom Certificate Location¶

At this time, it is not possible to specify a custom certificate location into the connection URL. This is supported by the backend code (PyExasol) and is a feature that could be added to SQLAlchemy-Exasol.

Disabling Certificate Verification¶

As discussed in the Dialect-Specific Parameters, SQLAlchemy-Exasol 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 database, a user might want to temporarily disable certificate verification.

Warning

Due to the increased security risks, this change should NEVER be used for production.

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

from sqlalchemy import create_engine, URL

  url_object = URL.create(
      drivername="exa+websocket",
      username="sys",
      password="exasol",
      host="127.0.0.1",
      port="8563",
      query={"SSLCertificate": "SSL_VERIFY_NONE"},
  )

  create_engine(url_object)

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