Security

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:

createDatabaseStatement=
	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:

dropDatabaseStatement=
	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
    lxqe
        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.

LXPAM	yes
host atlantis
    lxqe
        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:

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

Where action can be

action=
	CREATE | ALTER | DROP | SELECT | USAGE | INSERT | DELETE | UPDATE | REFERENCES
	| COMMENT | BACKUP | BECOME ANY USER | ALL PRIVILEGES

And database objects are:

databaseObjects=
	TABLE | INDEX | VIEW | SEQUENCE | TRIGGER | PROCEDURE | ANY

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:

createRoleStatement=
	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:

dropRoleStatement=
	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.

tls
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):

compress
crypt
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:
    localhost:/usr/local/leanxcale/bin/lx
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
NAME          ENCRYPTION  COMPRESS
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. Audit Logs

In this section we describe some information that can be found in the system log files.

4.1. Authentication Diagnostics

The lxqe log files include authentication information that can be checked out when in doubt.

Lines like

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 line would be

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]

We folded the line to make it easier to read.

4.2. Login and Auditing

The audit log file contains auditing entries, including user authentication.

These can be enabled using the AUDIT SQL statement, although authentication auditing is always enabled and cannot be disabled.

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
	logs...
	atlantis: [
		log/audit.240221.0708.log	1.75K [
		1:	2024-02-21 07:08:10 lxqe100: audit: auth: lxadmin: 127.0.0.1: local: yes
		2:	2024-02-21 07:08:12 lxqe100: audit: auth: db-USR2: 127.0.0.1: local: yes
		3:	2024-02-21 07:08:13 lxqe100: audit: auth: lxadmin: 127.0.0.1: local: yes
		4:	2024-02-21 07:08:13 lxqe100: audit: auth: lxadmin: 127.0.0.1: local: yes
		5:	2024-02-21 07:08:13 lxqe100: audit: auth: lxadmin: 127.0.0.1: local: yes
		...
		]
	]

The user names include both the database and the user name, as a convenience. An exception to this rule is lxadmin.

Authentication failures are reported with lines like

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

If user or table auditing are enabled on a per-session basis, read, write, and change (alter) accesses are reported once per session. For example, after executing any of these:

	AUDIT TABLE MYTBL
	AUDIT USER USR1
	AUDIT USER USR1 TABLE MYTBL

the audit log file will include reports like:

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

More expensive auditing reports the statements executed (even if they fail), and can be enabled using, for example

	AUDIT USER USR1 BY ANY STATEMENT

Although it is more sensible to audit just READ, WRITE, or DDL statements instead of auditing all of them.

In this case, the audit log will contain lines like the following, and it may grow quickly.

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 any audit has been enabled.

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

The line has been folded for readability, although it is a single line in the audit log.

It is important to know that the lxqe log file reports failed permission checks even when auditing is disabled. There is no need to enable auditing just to search for failed permission checks.

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: https://ubuntu.com/server/docs/security-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
    lxqe
        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:

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