1. Authentication

LeanXcale supports two ways to handle authentication, with native support, or using an external authentication server that can be an LDAP server or a PAM server. Native support is used via standard SQL.

1.1. Native Authentication

1.1.1. Predefined Users

There are two predefined users:

It is the superuser of the system. Its password is set during the installation.

  • LXADMIN: This is the predefined user with admin rights over all databases defined.

  • NONE: This is a predefined user with only rights to connect to the database. Only useful to test that the connection to the database works.

The predefined user rights cannot be delete nor its rights can be changed.

1.1.2. Users & Schemas

In LeanXcale each database has always a predefined schema APP. Each created user has an associated schema with the same name as the user. Deleting a user, deletes the associated schema as well.

1.1.3. Predefined Database & Schema

In LeanXcale there are one predefined database DB that has a predefined schema APP. They are provided to ease the use of the database. None of them can be dropped.

1.1.4. Creating & Dropping Databases

New databases can be created by means of the CREATE DATABASE SQL statement:

	CREATE DATABASE databaseName [USER userName IDENTIFIED BY stringLiteral]*

Only the LXADMIN user can create and drop databases. When creating the database it becomes possible to associate a user as owner of the provided database in the optional USER clause, where its name and password are provided.

To drop a database it can be done by the DROP DATABASE statement:

	DROP DATABASE databaseName

1.2. Using LDAP

Users defined in the DB correspond to schemas and must be created before they can be used.

When not using LDAP, authentication is performed using the credentials given when users are created.

To authenticate users using LDAP, define the LXLDAP property in the configuration file. This can be done globally or as a host property. For example,

LXLDAP	ldap://ldapsrv:389|simple|ou=People,dc=leanxcale,dc=com
host atlantis
        disk 100G
    kvds x4
        disk 100G

arranges for the system to authenticate user (other than lxadmin) using the given LDAP server and properties.

Note that before LDAP users can use leanXcale, a DB user must be created for them, with the LDAP uid as their name.

1.3. Using PAM

To use UNIX PAM as authentication, define the LXPAM property as yes. This authenticates the DB users using PAM.

For this to work, the user running the system must be a member of the shadow group or be able to read /etc/shadow.

Note that running as root is not an option. In fact, most systems would check the user id and prevent this from working, if implemented correctly.

host atlantis
        disk 100G
    kvds x4
        disk 100G

2. User Authorization

2.1. Permissions

User authorization is achieved by standard SQL GRANT and REVOKE statements. Permissions are expressed by the action and the database object. A GRANT statement gives one or more permissions to one or more users:

	GRANT permissionList TO userList
	permission [, permission]*
	action databaseObject

Where action can be


And database objects are:


2.2. Roles

Roles are also supported to enable to systematize the permissions given to users depending on their role. A role is just a collection of permissions. In that sense they are similar to a user. The syntax is:

	CREATE ROLE roleName IDENTIFIED BY 'password'

Permissions can be granted to a role and a user can be granted roles that is equivalent to grant to the user each of the permissions in the role.

A role can be deleted with:

	DROP ROLE roleName

3. Encryption

3.1. Using TLS

By default, communication between client processes and the DB service are not encrypted, for speed. This may be reasonable when the machine(s) involved are controlled by the installer and the network used is secure (e.g., an VPN already encrypting the packets exchanged).

For installs where the queries are performed from the internet or from an insecure network, leanXcale can be installed to use always TLS in communications between the query engine(s) and the DB client processes.

Just set the global tls property when installing.

host atlantis

Note this can be done also without using a configuration file. For example, like in

unix$ lxinst tls /usr/local/leanxcale

The SQL console for the installed system will use TLS in its connections. Other clients must use the tls=yes property in the connection to ask the client driver to use TLS.

3.2. Using Disk Compression and Encryption

To compress and/or encrypt the disk data, define the compress and/or the crypt properties as yes or no. Or define them without a value, meaning yes.

To use this, bare-metal installs require using a partition for installing. It will be setup with a ZFS file system using the compression and encryption settings required.

For AWS installs, it suffices to define the compress and/or the crypt properties, and it is neither needed nor permitted to specify a disk partition for the install. Also, the default in AWS is to compress but not encrypt, unlike in bare metal installs, where the default is neither to compress nor to encrypt.

For example, this configuration installs using /dev/sdc2 with both compression and encryption, mounting the installed partition at /usr/local/leanxcale (the default lxdir):

host atlantis
    lxpart /dev/sdc2

Note that compress and crypt properties might be defined for each host instead of being set globally for all the installation.

It is possible to achieve the same effect from the command line without creating a configuration file:

unix$ ./lxinst compress crypt atlantis:/dev/sdc2:/usr/local/leanxcale
install done.

To list installed file systems (on each host):
	zfs list -r lxpool -o name,encryption,compression
To remove installed file systems (on each host):
	sudo zpool destroy lxpool
To run lx commands:
Or adjust your path:
    # At localhost:~/.profile:
    export PATH=/usr/local/leanxcale/bin:$PATH
To start (with lx in your path):
    lx start

The final messages printed by the install program remind you of how to list or remove the installed file systems. For example, this can be used to list the file systems created and their settings:

unix$ zfs list -r lxpool -o name,encryption,compression
lxpool               off       off
lxpool/disk  aes-256-gcm       on
lxpool/dump  aes-256-gcm       on

Beware that destroying the installed file systems also removes all the data.

3.3. Encrypting Backups

For encrypting backups you should use the -C option.

4. Auditing (Audit Logs)

In this section we describe how to activate and deactivate the different auditing levels.

4.1. Auditing Levels

Auditing is set for all users, unless a user is specified. LeanXcale allows to set the auditing level at database level, table level, and schema level.

For the level chosen then the auditing granularity can be set per session (session is the default granularity, if none specified), per transaction, or per operation. Session granularity means that accesses to a table is logged once per session, the first time the access happen in the session. Transaction granularity means that accesses are logged once per transaction, that is, the first access that happens to an audited table. *Operation granularity means that only accesses with a specific kind of operation will be audited: READ, WRITE, DDL, and ANY. In case all kinds of operations should be audited, then ANY can be used.

4.2. Auditing SQL Syntax

	[NO] AUDIT [USER userName] [ (TABLE tableName) | (SCHEMA schemaName) ]
		[ BY
		) ]

To activate audit use AUDIT and to deactivate audit use NO AUDIT. The auditing can be set for a particular user using the USER clause, or for all users if the USER clause is omitted.

To audit the full database to which you connected omit table and schema clauses, and to audit a full schema use the SCHEMA clause and to audit a particular table use the TABLE clause. Note that a tableName no qualified with the schema name is referring to the schema indicated in the connection. To refer to tables in another schema the table name has to be qualified with the schema name: schemaName.tableName. When no audit granularity is specified, that is, there is no BY clause, the default audit granularity will be SESSION.

The BY clause enables to specify the audit granularity as SESSION, TRANSACTION, or operation. In the last case, the operation kind to be audited is indicated, or ANY if all kinds of operations should be audited.

4.3. Audit Logs

The audit log file contains auditing entries, including user authentication. User authentication is always enabled and cannot be disabled. When there is no audit, one can still find the authentication audit records in the lxqe log. When the audit is on, authentication audit records will also appear in the audit log.

Logs are accessed via lx logs command. The command provides the -g option that performs a grep with the pattern specified after -g. For example, to locate authentication entries for the user lxadmin the flag -g (for grep) can be used as shown here:

	unix$ lx logs -g auth.*lxadmin audit
	atlantis: [
		log/audit.240221.0708.log	1.75K [
		1:	2024-02-21 07:08:10 lxqe100: audit: auth: lxadmin: local: yes
		2:	2024-02-21 07:08:12 lxqe100: audit: auth: db-USR2: local: yes
		3:	2024-02-21 07:08:13 lxqe100: audit: auth: lxadmin: local: yes
		4:	2024-02-21 07:08:13 lxqe100: audit: auth: lxadmin: local: yes
		5:	2024-02-21 07:08:13 lxqe100: audit: auth: lxadmin: local: yes

The user names include both the database and the user name, as a convenience. An exception to this rule is lxadmin that is a user across all databases, while the rest of the users are local to one database.

Authentication failures are reported as follows:

2024-02-29 06:40:52 lxqe100: audit: auth: db-FOO: local: no:auth failed

Another examples is:


And after trying to update the table MYTABL by USR1, the audit log file will include this audit record:

2024-02-29 06:40:51 lxqe100: audit: write: db-USR1: /db/USR1/tbl/MYTBL

When setting auditing with operation granularity, the audit log will contain audit records like:

2024-02-29 06:40:51 lxqe100: audit: write: db-USR1: SELECT count(*) FROM MYTBL

When permission is denied to execute a statement, an audit record is added, if audit is enabled:

2024-02-29 06:40:51 lxqe100: audit: perm: db-TEST1: /db/APP/tbl/PERSONS: select on table: permission denied

Authentication audit records are as follows:

2024-02-16 08:45:08,752 INFO lxqe: authenticate: lxadmin: local: yes

report that the user lxadmin (or whoever it was) was authenticated by the local (i.e., the DB) user entry.

When using LDAP, the audit record shows the following:

2024-02-16 08:45:08,752 INFO lxqe: authenticate: db-USR1: ldap: yes

This reports both the database used (db) and the user involved (USR1).

Authentication errors are reported in a similar way:

2024-02-16 08:48:09,992 INFO lxqe: authenticate: db-USR2: ldap: no: [LDAP: error code 49 - Invalid Credentials]

5. Hardening Guide

5.1. Network Security

The host in which resides LeanXcale should only have opened the ports for connecting client applications with LeanXcale. The firewall of the host has to be exclusively opened to the IPs from the hosts where the client application(s) are running. It must not be opened to anyone in the Internet. In Ubuntu, you can set up and manage the firewall using a utility called ufw (Uncomplicated Firewall), which provides a straightforward command-line interface for configuring firewall rules.

In here you can find how to setup the ubuntu firewall:

5.2. Least Privilege Principle

Each role in your organization should get only the minimum privileges for performing his/her role, and no more. LXADMIN access should be limited to the administrators of the database. Create roles for each different activity on the database, such as developer, user, etc. Each person should have a different user. Strong password selection should be enforced.

5.3. Change Defaults

A good practice is to change the default ports, so the attacker has harder time to identify them. You can change the default ports in the configuration file. In particular, the network address used by LeanXcale to listen for client connections is 14420. You can change the port indicating in the configuration file which port you want to use for the query engine (lxque), for instance, port 3444:

host atlantis
        addr atlantis!3444

5.4. Use Encryption

Use encryption in the client-server communication activating TLS. It will prevent an attacker that gained access to your network from seeing the contents of the messages exchanged between your application and the database server.

You can enforce to always used encryption to communicate with LeanXcale setting the tls global property in the configuration file when installing:

host atlantis

Or it can be indicated as a parameter when installing with lxinst:

unix$ lxinst tls /usr/local/leanxcale

This will enforce to SQL console to use TLS in its connection.

Client applications should use the 'tls=yes' property in the driver connection string.

Activate storage encryption to prevent that an attacker getting access to the device physically or through the host to see the stored data. Also note you have to use encrypted backups.

5.5. Regular Patching

Apply critical security updates and patches is highly recommended to eliminate fixed vulnerabilities.