Security Internals

LeanXcale is a scalable relational database that can be accessed through 2 different interfaces.

  1. SQL access using the Query Engine

  2. KiVi access

KiVi access is recommended for fast, simple access, while SQL access is recommended to run complex queries on the database. LeanXcale provides APIs for multiple programming languages in order to directly access its key-value KiVi. SQL access is supported with the standard JDBC, ODBC and SQLAlchemy.

This document has been divided into 2 sections: configuring security on KiVi accesses and configuring security on SQL accesses. LeanXcale published a document describing the dual interface and some common usages. As both interfaces are available for the users, both interfaces need to be secure.

Security is a must for databases and, at LeanXcale, that is something we care about. Security in LeanXcale addresses the following goals:

  1. Network encryption ensures that all the communications between the LeanXcale cluster and the clients are encrypted. In order to fulfill the network security requirements, we need to ensure

    1. Confidentiality the data is encoded and it is readable only by the client and LeanXcale.

    2. Authentication verifies that the message is sent from LeanXcale or a valid client.

    3. Integrity proves that none of the messages have been changed since it was sent.

    4. Nonrepudiation provides a guarantee to the receiver of a message, ensuring that the message was sent from a valid sender, and not by someone passing themself off as a sender. Nonrepudiation prevents senders from denying having sent the encrypted message.

  2. Data encryption ensures that the data stored in hard drives is encrypted by a well known encryption algorithm.

  3. Authentication validating the user that is accessing the database.

  4. Authorization managing roles and permissions per table and user in the database.

In the following sections, we specify how security is implemented in LeanXcale in different pieces: network and data encryption, authentication and authorization. Additionally, we specify how to enable it in the on-premise deployment. The documentation for the on-cloud deployment can be found in the LeanXcale documentation . Generally speaking, on cloud we support full security by default. The LeanXcale Cloud Console has specific sections to manage users and application permissions hiding the complexity of the configuration. Additionally, it can be configured with no security to start a LeanXcale deployment with security disabled.

The following table summarizes the protocols used for each interface on each of the levels that are detailed in this document.

LeanXcale Security

SQL interface

KiVi interface

Network encryption

HTTPS with TLS v1.2

AES algorithm (ECB and CBC modes) with symmetric functions with AES 256 keys

Data encryption

Provided by OpenZFS, aes-256-gcm. Done at the Storage Engine (KiVi)

Provided by OpenZFS, aes-256-gcm

Authentication

LDAP v3 (recommended OpenLDAP)

Based on certificates using AES 256 keys

Authorization

Based on user permissions - Table level

Based on certificates using AES 256 keys - Schema level

1. LeanXcale Architecture

LeanXcale consists of multiple components to provide all the capabilities LeanXcale has. This post, available in the LeanXcale blog, explains all of the components and shows some of the different deployments that LeanXcale can have, to adapt its deployment to the workload that it will need to process. To summarize, the components of LeanXcale can be grouped into:

  1. Transaction Manager:

    1. Mastermind: in charge of providing transactionality. This component provides the capacity to commit transactions in a distributed manner, in parallel.

    2. Conflict Manager: in charge of managing the write-write conflict checks.

    3. Logger: in charge of providing persistence of the transactions.

  2. Storage Engine (KiVi datastore)

    1. KiVi Meta Server (KVMS) keeps the metadata of the tables and its structures. This component does not store actual data.

    2. KiVi Data Server (KVDS) keeps the actual data for each table.

    3. KiVi Proxy (KVP) used as a proxy to provide a unique entry point for all the KiVi clients. This avoids opening many ports to use the KiVi interface.

  3. Query Engine (SQL) this component parses the SQL queries, optimizes them and runs them over the KiVi Storage Engine. This component provides JDBC, ODBC and SQLAlchemy interfaces.

    1. HAProxy is used to balance the SQL client load across multiple SQL servers. This component is optional, because in case the client needs only one SQL Server, this component can be skipped in order to reduce the network latency. However, in this document we also explain how HAProxy can be configured to provide a secure HTTPS connection.

comps

LeanXcale is provided in a tar.gz package that needs to be uncompressed in a folder. That folder, where all the LeanXcale binaries are located, will be named $BASEDIR. LeanXcale database is configured through a single file named inventory file located in $BASEDIR/conf/inventory. Some examples of the inventory file are provided in the $BASEDIR/conf folder.

The section that needs to be adapted to configure security in the LeanXcale configuration file is the [all:vars] section. In that section, all the configuration to set security needs to be added. On its default setting, LeanXcale does not have activated security, but it can easily be activated. In the following sections, we define the different security mechanisms we support at LeanXcale and how these are implemented. In addition, we explain how the security has to be configured in the LeanXcale inventory file.

2. SQL interface

2.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.

  1. Configuring the Query Engine to start a HTTPS server with TLS v1.2.

  2. Accessing through HAProxy and making HAProxy to cypher the connections with TLS v1.2.

net sql

2.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"}

2.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

2.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.

2.2. Data encryption

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

2.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

2.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.

auth

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.

3. KiVi interface

The KiVi interface offers the user the ability to retrieve data directly, by accessing the Storage Engine of LeanXcale while keeping ACID transactions. This interface is typically used for fast ingestion.

KiVi is divided into multiple components:

  • KiVi Meta Server (KVMS) stores the metadata information.

  • KiVi Data Server (KVDS) stores the actual data.

  • KiVi Proxy (KVP) is used as a proxy to hide the connections between KiVi clients and KiVi Servers providing an unique endpoint for the clients.

3.1. Network encryption

All the communication between all the KiVi components and the clients are encoded using an implementation of the AES algorithm, specifically ECB and CBC mode. This encoding protocol is based on symmetric functions with AES 256 keys.

The implementation is verified against the test vectors in National Institute of Standards and Technology Special Publication 800-38A 2001 ED.

net kv

3.2. Data encryption

KiVi data encryption is delegated to the filesystem where KiVi stores its data. Usually, we recommend using OpenZFS that supports multiple encryption types:

  • aes-128-ccm

  • aes-192-ccm

  • aes-256-ccm (default in OpenZFS < 0.8.4)

  • aes-128-gcm

  • aes-192-gcm

  • aes-256-gcm (default in OpenZFS >= 0.8.4)

LeanXcale recommends using the last stable version of OpenZFS 2.1.0, that uses by default aes-256-gcm.

3.3. Authentication

To connect with KiVi with security enabled, an application certificate is needed. This certificate has to be provided to the KiVi API application to authenticate and authorize itself with KiVi. This certificate is also used to encode the network communication.

LeanXcale uses the following concept for its structure:

  1. Database: name of the database where all the tables are created

  2. Schema: under this schema, some tables may be created. These tables will only be accessible by the certificates that have access to this schema. Note that schemas are named after the SQL users by default.

To create a valid certificate for an application that uses the KiVi interface, the following is required:

  1. Create the user using the administration console, lxConsole. This has to be done with the following command. This command creates the user alice to use the database db. This step can be skipped if you already have a user created.

$ lxConsole addUser alice iAm@dmin db

  1. After creating the user, the certificate can be created. The following command shows an example to create the certificate application1 giving it permissions to access the tables created by the user alice. The command returns a string with the full path to the new certificate created. With this certificate, the application application1 has access to all the tables of the user alice.

$ lxConsole createCert application1 alice
/home/ubuntu/lxs/conf/certificates/application1.kcf

  1. The application that uses this certificate will have permissions to all the tables created by the alice user.

3.4. Authorization

When the certificate is created, the administrator has to specify the list of users, from which the application that uses the certificate will have access. The permissions of this application are restricted to the tables created by the users allowed in the certificate. In this case, the owner of the certificate has access to all the tables created from the list of users provided when the certificate was created. If these permissions need to be changed, it is necessary to create a new certificate. In this case, the granularity of permissions is granted per schema.