LeanXcale Documentation :: LeanXcale Documentation

1. SQL interface

1.1. Network encryption

Depending on the deployment executed, LeanXcale can have one or more Query Engine servers to process SQL queries. If the deployment has more than one Query Engine server, a balancer is needed. LeanXcale fully supports HAProxy to balance the workload across all the available Query Engine servers. To secure the connections, we use the HAProxy capabilities to encode HTTP requests into HTTPS requests.

If the deployment has only one Query Engine server, it may not be necessary to have an HAProxy because it could add some latency to the SQL processing. In this case, our Query Engine provides a mechanism to serve the SQL queries using HTTPS.

The network encryption for the SQL interface can be configured in 2 different ways. The following diagram shows what is encoded in the communication between the SQL servers and the SQL clients.

Configuring the Query Engine to start a HTTPS server with TLS v1.2.
Accessing through HAProxy and making HAProxy to cypher the connections with TLS v1.2.

1.1.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 PASSWORD

The 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 PASSWORD

The 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"}

1.1.2. Encrypting HAProxy connections

LeanXcale recommends HAProxy version 2.3 which is the last stable version and provides TLS v1.2 using a certificate.

To configure HAProxy to encrypt the Query Engine connections, it is needed to have a certificate. This certificate can be provided by a Certificate Authority or a self-signed certificate. To configure HAProxy to encode the messages, it is necessary to add the property SECURE_COMMUNICATION_HAP specifying the certificate in the [all:vars] section of the inventory file.

SECURE_COMMUNICATION_HAP = {"certificate": "/home/ubuntu/lxs/conf/server.pem"}

We provide the following bash commands to create a self-signed certificate.

# Generate new private/public key pair if a trusted pair from a trusted CA is not available
# Option -nodes enable HAProxy run up without prompting for a password
$ openssl req -x509 -newkey rsa:2048 -sha256 -days 3650 -nodes -keyout private.key -out client.crt -subj "/CN=leanxcale.com/OU=ITDep/O=LeanXcale/L=Madrid/ST=Madrid/C=ES" -addext "subjectAltName=DNS:leanxcale.com,DNS:localhost,IP:127.0.0.1"
# Bundle Private_Key/Public_certificate into server.pem
$ cat private.key client.crt > server.pem

1.1.3. Configuring clients for connection

Depending on the certificate used, it is needed to add some configuration on the SQL clients or not. If a certificate is provided by a Certificate Authority, no extra configuration is needed on the clients, because it will be verified with that Certificate Authority automatically. If the certificate is a self-signed certificate, the client needs to add the certificate with the certificates that it trusts to establish the connection. More documentation about how to configure the clients can be found in the LeanXcale documentation.

1.2. Data encryption

The Query Engine uses KiVi Storage Engine; consequently, this responsibility is given to the storage engine.

1.3. Authentication

Authentication is provided by LDAP. LDAP (Lightweight Directory Access Protocol) is a protocol used for service authentication. LDAP uses usernames and passwords to authenticate users. The LDAP implementation that LeanXcale recommends is the latest OpenLDAP version, as of now OpenLDAP-2.5.7 that has been certified with LDAP v3. However, other LDAP available implementations can be used. For example, ApacheDS has been certified with LDAP v3 or Oracle LDAP, that also provides LDAP v3.

It is required that the administrator starts the LDAP service, and LeanXcale makes use of that LDAP server to provide authentication. The LDAP server used may typically be the LDAP server of the company, with all the usernames and passwords of the company.

The property SECURITY needs to be set in the [all:vars] section of the LeanXcale inventory file.

SECURITY={'ldap': {'leanxcale.queryEngine.authentication.type': 'ldap', 'java.naming.provider.url': '172.31.59.177', 'leanxcale.ldap.ldapBase': 'dc=bef55ef74b89c87e579a,dc=lxc-db,dc=com', 'leanxcale.ldap.conntype': 'simple', 'leanxcale.ldap.adminDn': 'cn=admin,dc=bef55ef74b89c87e579a,dc=lxc-db,dc=com', 'leanxcale.ldap.adminPwd': {'LDAP_ADMIN_USERNAME': 'admin', 'LDAP_ADMIN_PASSWORD': 'pass@lx-129034, 'LDAP_USERS': 'user01', 'LDAP_PASSWORDS': 'password1'}}}

To create users, the database administrator has to use the LeanXcale administration tool lxConsole. The following command creates the user alice with the password iAm@dmin on the database db.

$ lxConsole addUser alice iAm@dmin db

1.4. Authorization

For authorization, we use KiVi certificates. When a user connects with an SQL driver (JDBC, ODBC or SQLAlchemy), the user has to specify the username, password and database in the connection string. With this information, the Query Engine firstly authenticates the user with LDAP, then the user can connect to the database. Each time the user runs a query, the permissions are checked. Depending on the query, read or write permissions on tables are checked. If a query affects multiple tables, the permissions are checked for the affected tables. This permission check is delegated to KiVi’s certificates. The Query Engine runs with a bucket of certificates of users. With this bucket of certificates, the Query Engine can authorize each user´s access with KiVi.

To manage the permissions of the user, a bunch of SQL commands are available. To check the full documentation, check the LeanXcale user’s documentation.

GRANT <permission lists> ON TABLE <table name> TO <user>
GRANT <permission lists> ON SEQUENCE <sequence name> TO <user>
GRANT <permission lists> ON SCHEMA <schema name/user> TO <user>
REVOKE <permission lists> ON TABLE <table name> TO <user>
REVOKE <permission lists> ON SEQUENCE <sequence name> TO <user>
REVOKE <permission lists> ON SCHEMA <schema name/user> TO <user>

The following graph shows how permissions are checked when a client connects to the Query Engine (QE) from any of the available SQL drivers.

First, it connects to the Query Engine using a username and password. Then, the Query Engine authenticates the user against the LDAP server. Next, the client runs a query and the Query Engine accesses KiVi to retrieve or modify data. During that phase, whether the user is allowed to run the query on KiVi is checked. This protocol allows the database administrator to change the permissions of the users immediately.

Using the SQL interface, access is checked per table. A user, by default, has access to all the tables that this user creates. If the user needs to have access to tables from another user, permissions need to be granted. The user can revoke access to some tables, even owned by itself. Consequently, the granularity of the authorization using the SQL interface is ensured per table.