Security

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,

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.

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

Recent Linux systems should include unix_chkpwd(8) to allow applications to check UNIX passwords without access to the /etc/shadow file. Otherwise, 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.

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:

Where action can be

And database objects are:

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:

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:

LeanXcale provides TLS 1.3. 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.

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

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.

LeanXcale uses AES256 GCM for encrypting the storage. 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):

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:

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:

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

Backups are encrypted with AES256 GCM. For using encrypted backups you should use the -C option. For encrypted backups a file with the secret key should be provided. For instance, to do an encrypted backup:

And to restore an encrypted backup:

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.

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.

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:

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:

Another examples is:

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

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

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

Authentication audit records are as follows:

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:

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

Authentication errors are reported in a similar way:

If MFA is to be used with PAM, the Google authenticator PAM module must be installed on the system running the DB:

The database uses PAM service lxmfa to verify authenticator codes. Add a new PAM configuration file /etc/pam.d/lxmfa, with the following contents

Finally, define the LXUSEMFA property as yes:

Each user must then create an authenticator key (if not already done) as described in the LeanXcale User’s Guide.

Each user requiring MFA must also configure their authenticator app (for instance the Google Authenticator app for Android). In each user’s home directory, run the google-authenticator command, specifying the time-based option:

It will create a new secret key in $HOME/.google_authenticator and display the new key both as a series of hexadecimal digits and as a QR code, to be entered into the authenticator app. When using lx sql, as well as the user name (with -n) and password (with -p) the current authenticator value should be given with the -P flag:

where 123456 should be replaced by the current code from the authenticator app.

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

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.

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:

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:

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

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.

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