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.
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
audit= [NO] AUDIT [USER userName] [ (TABLE tableName) | (SCHEMA schemaName) ] [ BY ( SESSION | TRANSACTION | [ READ | WRITE | READ WRITE | DDL | ANY ] ) ]
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 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 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: 127.0.0.1:39018: local: no:auth failed
Another examples is:
AUDIT TABLE MYTBL AUDIT USER USR1 AUDIT USER USR1 TABLE MYTBL
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: 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.