Creating and initializing the Guacamole database

If you haven’t already done so, a database specific to Guacamole needs to be created within MySQL. The database can be called anything you like; all that matters is that the database be dedicated to Guacamole, and not shared by different applications. Get to the MySQL prompt with the command:

sudo mysql -u root

Next, create the database:

CREATE DATABASE guacamole_db;

Then exit MySQL with the "exit" command.

Guacamole will not automatically initialize the database with the required schema. You will need to do this yourself using the SQL scripts provided with the kcm-guacamole-auth-jdbc-mysql package, which are located within the /opt/keeper/share/guacamole-auth-jdbc-mysql/schema directory:

The above scripts must be run in sequence, as it is the first script which actually creates the database schema. The second script, which defines a default administrative user, can only successfully run if the tables created by the first script exist. The simplest way to run both scripts in sequence is to concatenate them:

cat /opt/keeper/share/guacamole-auth-jdbc-mysql/schema/*.sql | mysql -u root -p guacamole_db

Alternatively, the scripts can be run individually, as long as the order is correct:

mysql -u root guacamole_db < /opt/keeper/share/guacamole-auth-jdbc-mysql/schema/001-create-schema.sqlmysql -u root guacamole_db < /opt/keeper/share/guacamole-auth-jdbc-mysql/schema/002-create-admin-user.sql

Connecting Guacamole to MySQL

To execute queries against the database, Guacamole will need its own database user with sufficient privileges. Because Guacamole does not automatically apply or update its own schema, the required privileges are minimal, dealing only with creation and maintenance of data within already-defined tables and indexes:

CREATE USER 'guacamole_user' IDENTIFIED BY 'some_password';
GRANT SELECT,INSERT,UPDATE,DELETE ON guacamole_db.* TO 'guacamole_user';
FLUSH PRIVILEGES;

Advanced Linux Install Method

Keeper Connection Manager packages Guacamole’s MySQL support within the kcm-guacamole-auth-jdbc-mysql package. This package must be installed before creating Guacamole’s database within MySQL, as it includes the SQL scripts necessary for doing so:

sudo yum install kcm-guacamole-auth-jdbc-mysql

Guacamole's main configuration file, /etc/guacamole/guacamole.properties, must now be modified to specify the credntials of the MySQL user and to point the MySQL database:

sudo vi /etc/guacamole/guacamole.properties

The guacamole.properties file provided with Keeper Connection Manager is organized into sections documented with blocks of comments and example properties. The first section which must be modified is marked "JDBC-1" and defines the TCP connection information for the database in use. Uncomment the mysql-hostname and mysql-port properties, modifying their values to point to your MySQL server:

##
## [JDBC-1] Database TCP connection information
##
## The TCP connection details for the PostgreSQL, MySQL / MariaDB, or SQL
## Server database.
##

mysql-hostname: localhost
mysql-port:     3306

The "JDBC-2" section, which defines the database name and associated credentials, must also be modified to specify the correct database name, username, and password. These values are given with the mysql-database, mysql-username, and mysql-password properties respectively:

##
## [JDBC-2] Database name and credentials
##
## The name of the database to use, as well as the credentials to use when
## connecting to the database. THESE PROPERTIES ARE REQUIRED if one of the
## database authentication extensions will be used.
##

mysql-database: guacamole_db
mysql-username: guacamole_user
mysql-password: some_password

Guacamole will generally only load new extensions and reread guacamole.properties during the startup process. To apply the configuration changes, Guacamole must be restarted:

$ sudo systemctl restart guacamole

$ sudo systemctl restart tomcat

$ sudo setsebool -P tomcat_can_network_connect_db 1

To make sure everything is working as expected, you should also visit your Guacamole instance with a web browser (most likely at http://HOSTNAME:8080/guacamole/, where “HOSTNAME” is the hostname or IP address of your server). If all is working correctly, you should see a login screen with a username/password prompt, and you will be able to log in using the default account created with the 002-create-admin-user.sql script:

CentOS and RHEL both provide a package for the MariaDB database server called "mariadb-server". Installing this package will install a version of MariaDB that is explicitly supported by Keeper Connection Manager. If you do not have an existing database instance or third-party database hosting provider that you would prefer to use, installing a fresh instance of MariaDB for use by Guacamole will work nicely:

$ sudo yum install mariadb-server

As with other standard CentOS / RHEL packages providing a service, the MariaDB service will not be started by default after the "mariadb-server" package is installed. It must be started manually, and then configured to automatically start if the system is rebooted:

$ sudo systemctl start mariadb
$ sudo systemctl enable mariadb

Dropping default anonymous users

If MariaDB is installed locally (on the same server as Apache Guacamole), its default configuration will prevent Guacamole from authenticating. This is due to the way that MariaDB handles authentication and anonymous database users: if an anonymous user is defined for the same hostname/address, MariaDB will use only the anonymous user, and authentication using a non-anonymous user and password from the same hostname/address will fail.

This can be checked by querying MariaDB's user table directly:

SELECT Host, User FROM mysql.user;

Any users with empty usernames in the results of the above query are anonymous users which may block authentication from succeeding:

+---------------------+----------------+
| Host                | User           |
+---------------------+----------------+
| %                   | guacamole_user |
| 127.0.0.1           | root           |
| ::1                 | root           |
| the.server.hostname |                |
| the.server.hostname | root           |
| localhost           |                |
| localhost           | root           |
+---------------------+----------------+

Dropping those users should allow non-anonymous authentication from those same hosts to succeed:

DROP USER ''@'localhost';
DROP USER ''@'the.server.hostname';
FLUSH PRIVILEGES;

Pointing Guacamole at the new MariaDB instance

Once MariaDB has been deployed, you should move forward with configuring Guacamole to use your new MariaDB instance. This process is documented in its entirety, and the default /etc/guacamole/guacamole.properties file also contains placeholders and comments to help guide administrators to the correct configuration properties. Overall, the process will involve:

• Installing the package providing MySQL / MariaDB support (kcm-guacamole-auth-jdbc-mysql).

• Creating a new database within your MariaDB instance using the provided schema files.

• Creating a database user that Guacamole can use to execute queries against your database.

• Editing /etc/guacamole/guacamole.properties to point Guacamole at your database (and to specify the credentials of the database user it should use).