PyExasol Connection Encryption

TomHel
Contributor

Hello all, 

my company has been switching from Teradata to Exasol and I am one of few working the database via Python. The question I have is about the Python driver PyExasol and how secure the connection is. With our Teradata Driver, all credentials were stored in two pre-encrypted key files that were handed over via a special connection syntax to the database for verification. 

From what I can see from in the Github documentation, this is not the case for PyExasol. It looks to me as if credentials have to be stored in a plain text file and the connection is completely unencrypted. This is less than ideal. 

From the following example

https://github.com/exasol/pyexasol/blob/master/examples/15_encryption.py

it looks like encryption has to be set to TRUE, but if I do so, my database seems to refuse connection. Is the connection generally completely unencrypted? Do credentials get transferred in plain text?

I would be very grateful if anyone could shine some light on that matter. 

Best regards, 
Tom

1 ACCEPTED SOLUTION

Accepted Solutions

littlekoi
Xpert

Hi TomHel,

The authorisation process in PyEXASOL is encrypted in all cases. Server sends public key which is used to encrypt a password. Password is never sent as plain text by design of WebSocket protocol.

You may find more details here: https://github.com/exasol/websocket-api/blob/master/docs/commands/loginV3.md

Also, you may add connection option "debug=True" for "pyexasol.connect()" and see JSON requests and responses.

"encryption=True" enables TLS encryption for all the communication happening after authorisation. Normally it does not require any extra setup. What error message do you get when trying to connect with encryption being enabled?

Thank you.

View solution in original post

7 REPLIES 7

littlekoi
Xpert

Hi TomHel,

The authorisation process in PyEXASOL is encrypted in all cases. Server sends public key which is used to encrypt a password. Password is never sent as plain text by design of WebSocket protocol.

You may find more details here: https://github.com/exasol/websocket-api/blob/master/docs/commands/loginV3.md

Also, you may add connection option "debug=True" for "pyexasol.connect()" and see JSON requests and responses.

"encryption=True" enables TLS encryption for all the communication happening after authorisation. Normally it does not require any extra setup. What error message do you get when trying to connect with encryption being enabled?

Thank you.

View solution in original post

littlekoi
Xpert

Here is an example of debug output:

2021-08-31 13:59:20.750 Connection attempt [10.10.211.83:8563]
2021-08-31 13:59:20.803 [WebSocket request #1]
{
    "command": "login",
    "protocolVersion": 1
}
2021-08-31 13:59:20.846 [WebSocket response #1]
{
    "status": "ok",
    "responseData": {
        "publicKeyPem": "-----BEGIN RSA PUBLIC KEY-----\nMIGJAoGBAMULy6So9Mg6yOGFOc552DSwtNtvRfux7nJdu7DVGXsF67cSWIJ/593i\nWpJs6QEbX4a7pyvnPTauI7ob32fz1eW1Z9oV/Tzo1lfhkdIlW8w5lE+aLT5JzpAl\n+I9UBpKyJV/13V7DiltkTHeCMx8E6ImzOWSGPcJodTRUSQNqzf9DAgMBAAE=\n-----END RSA PUBLIC KEY-----\n",
        "publicKeyExponent": "010001",
        "publicKeyModulus": "C50BCBA4A8F4C83AC8E18539CE79D834B0B4DB6F45FBB1EE725DBBB0D5197B05EBB71258827FE7DDE25A926CE9011B5F86BBA72BE73D36AE23BA1BDF67F3D5E5B567DA15FD3CE8D657E191D2255BCC39944F9A2D3E49CE9025F88F540692B2255FF5DD5EC38A5B644C7782331F04E889B33964863DC26875345449036ACDFF43"
    }
}
2021-08-31 13:59:20.875 [WebSocket request #2]
{
    "username": "SYS",
    "password": "n6pBDxpAALCchbjW2MVycY7hdgHwCJU1N/Bvb8oEiqPdfjdcLd5zPZQckkx6VoHxndFiYLS5Ddhtk78jET7esIDp80DZe7P3IEKNz2+btzFN9yzv/8+F3vq+EdUjJrEd/VHix5tIzcC2cIu9eDbLZdX9IOZI/G7hK9KIMI80fCc=",
    "driverName": "PyEXASOL 0.20.0",
    "clientName": "PyEXASOL",
    "clientVersion": "0.20.0",
    "clientOs": "macOS-11.3.1-x86_64-i386-64bit",
    "clientOsUsername": "vitalymarkov",
    "clientRuntime": "Python 3.9.6",
    "useCompression": false,
    "attributes": {
        "currentSchema": "PYEXASOL_TEST",
        "autocommit": true,
        "queryTimeout": 0
    }
}
2021-08-31 13:59:20.900 [WebSocket response #2]
{
    "status": "ok",
    "responseData": {
        "protocolVersion": 1,
        "timeZone": "ETC/UTC",
        "timeZoneBehavior": "INVALID SHIFT AMBIGUOUS ST",
        "sessionId": 1709613788356200936,
        "maxDataMessageSize": 67108864,
        "releaseVersion": "6.2.15",
        "databaseName": "badoo_dev",
        "productName": "EXASolution",
        "maxIdentifierLength": 128,
        "maxVarcharLength": 2000000,
        "identifierQuoteString": "\""
    }
}

TomHel
Contributor

Hey Vitaly, 

thank you for your detailed responses. This demonstrates that I do not have to worry about sending credentials around the network for everyone to see.

Regarding my comment about an error when I switch on encryption:

I am probably just to dumb to understand encryption but it probably means that I need some sort of certificate, no? When I simply turn on encryption=TRUE in my connection settings, I receive the following error. 

ExaConnectionFailedError:
(
message => Could not connect to Exasol: [SSL: LENGTH_MISMATCH] length mismatch (_ssl.c:1123)

littlekoi
Xpert

I am aware of two possible reasons for this error:

1) Key size of default certificate is incorrect. It was fixed in 7.0.6+ https://www.exasol.com/support/browse/EXASOL-2829

2) Local OpenSSL version is too old and should be updated. You may check it using command:

openssl version

 

TomHel
Contributor

Unfortunately I am forced to work on a windows machine... so life is difficult in the command line 🙂

exa-Kristof
Team Exasol
Team Exasol

Hi @TomHel, I'm not quite sure if @littlekoi 's answer solved your problem. Did it? 🙂

TomHel
Contributor

It did, thank you all!