1. Encrypting SQL connections
The Query Engine uses the java library Jetty version 9.4.28 to provide the HTTPS interface. This library uses the well known Java mechanisms for security, TrustStore and KeyStore.
TrustStore is used to store certificates from Certified Authorities (CA) that verify the certificate presented by the server in the SSL or TLS connections. Conversely, Keystore is used to store private key and identity certificates that a specific program should present to both parties (server or client) for verification.
A TrustStore and KeyStore need to be created to set HTTPS on the Query Engine. The following code generates a certificate and adds it to a new TrustStore and KeyStore.
# Export private/public pair server.pem into PKCS12 store with PASSWORD
$ openssl pkcs12 -export -name lxqeserver -in server.pem -out keystore.p12 -password pass:PASSWORD
# Import PKCS12 into java keystore
$ keytool -importkeystore -srcalias lxqeserver -srckeystore keystore.p12 -srcstorepass PASSWORD -destkeystore keystore.jks -deststorepass PASSWORD
# Import the client certificate in the truststore
$ keytool -importcert -v -noprompt -trustcacerts -alias lxqeserver -file client.crt -keystore truststore.ts -deststorepass PASSWORDThe TrustStore and KeyStore passwords can be written clearly or obfuscated (string starting with “OBF:”). In this example we used the jetty-util library. It can be downloaded from the official repository.
# with this command you can get the obfuscated password
$ java -cp $BASEDIR/LX-BIN/lib/jetty-util-9.4.28.v20200408.jar org.eclipse.jetty.util.security.Password PASSWORDThe following property SECURE_COMMUNICATION_QE needs to be set in section [all:vars] of the LeanXcale inventory file, specifying the keystore and truststore paths and passwords.
SECURE_COMMUNICATION_QE = {"KEYSTORE": "/home/ubuntu/lxs/conf/keystore.jks", "TRUSTSTORE":
"/home/ubuntu/lxs/conf/truststore.ts", "KEYSTORE_PASSWORD": "OBF:1v2j1uum1xtv1zej1zer1xtn1uvk1v1", "TRUSTSORE_PASSWORD": "OBF:1v2j1uum1xtv1zej1zer1xtn1uvk1v1"}