Chapter 6. Database authentication

Guacamole supports authentication via MySQL or PostgreSQL databases through extensions available from the project website. These extensions allow users and connections to be managed from within the web application. Unlike the default, XML-driven authentication module, all changes to users and connections take effect immediately; users need not logout and back in to see new connections.

The official database authentication also supports load balancing through the use of "balancing groups". When a balancing group is created, it can be used like any other connection, but will use the least-used of its underlying connections, spreading load evenly across any connections contained within.

To use the database authentication extension, you will need:

  1. A supported database - currently MariaDB, MySQL, or PostgreSQL.

  2. Sufficient permission to create new databases, to create new users, and to grant those users permissions.

  3. Network access to the database from the Guacamole server.

Important

This chapter involves modifying the contents of GUACAMOLE_HOME - the Guacamole configuration directory. If you are unsure where GUACAMOLE_HOME is located on your system, please consult Chapter 5, Configuring Guacamole before proceeding.

Downloading the database authentication extension

The database authentication extension is available separately from the main guacamole.war. The link for this and all other officially-supported and compatible extensions for a particular version of Guacamole are provided on the release notes for that version. You can find the release notes for current versions of Guacamole here: http://guac-dev.org/releases/.

The database authentication extension is packaged as a .tar.gz file containing:

mysql/

Contains the MySQL/MariaDB authentication extension, guacamole-auth-jdbc-mysql-0.9.7.jar, along with a schema/ directory containing MySQL-specific SQL scripts required to set up the database. The guacamole-auth-jdbc-mysql-0.9.7.jar file will ultimately need to be placed within GUACAMOLE_HOME/extensions, while the MySQL JDBC driver must be placed within GUACAMOLE_HOME/lib.

The MySQL JDBC driver is not included with the extension. You must obtain the JDBC driver .jar yourself from MySQL's website. The driver is known as "Connector/J", and the required .jar will be within a .tar.gz archive.

postgresql/

Contains the PostgreSQL authentication extension, guacamole-auth-jdbc-postgresql-0.9.7.jar, along with a schema/ directory containing PostgreSQL-specific SQL scripts required to set up the database. The guacamole-auth-jdbc-postgresql-0.9.7.jar file will ultimately need to be placed within GUACAMOLE_HOME/extensions, while the PostgreSQL JDBC driver must be placed within GUACAMOLE_HOME/lib.

The PostgreSQL JDBC driver is not included with the extension. You must obtain the JDBC driver .jar yourself from PostgreSQL's website. The proper .jar file depends on the version of Java you have installed.

Only one of the directories within the archive will be applicable to you, depending on whether you are using MariaDB, MySQL, or PostgreSQL.

Creating the Guacamole database

The database authentication module will need a database to store all authentication data and a user to use only for data access and manipulation. You can use an existing database and existing user, but for the sake of simplicity and security, these instructions assume you will be creating a new database and new user that will be used only by Guacamole and only for this authentication module.

You need MariaDB, MySQL, or PostgreSQL installed, and must have sufficient access to create and administer databases. If this is not the case, install your database of choice now. Most distributions will provide a convenient MySQL or PostgreSQL package which will set up everything for you, including the root database user, if applicable.

For the sake of clarity, these instructions will refer to the database as "guacamole_db" and the user as "guacamole_user", but the database and user can be named whatever you like. Naturally, you should also choose a real password for your user rather than the string "some_password" used as a placeholder below.

MySQL

If using MySQL, you must create your database and user first:

$ mysql -u root -p
Enter password: password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 233
Server version: 5.5.29-0ubuntu0.12.10.1 (Ubuntu)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE guacamole_db;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'guacamole_user'@'localhost' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON guacamole_db.* TO 'guacamole_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

mysql> quit
Bye
$

Once the database and user are created, the database schema must be applied by running the supplied SQL scripts. These SQL scripts are included in the mysql/schema/ directory of the archive you downloaded from the Guacamole website. They are named such that they can be run in order with one command:

$ ls schema/
001-create-schema.sql  002-create-admin-user.sql  upgrade
$ cat schema/*.sql | mysql -u root -p guacamole_db
Enter password: password
$

If the operation is successful, all tables have been created successfully, and the database is now ready for use.

Important

If you are upgrading from an older version of Guacamole that lacked support for connection groups (older than 0.8.2), you should instead run the upgrade script located within the upgrade/ directory:

$ ls schema/upgrade/
upgrade-pre-0.8.2.sql  upgrade-pre-0.9.6.sql upgrade-pre-0.9.7.sql
$ mysql -u root -p guacamole_db < schema/upgrade/upgrade-pre-0.8.2.sql
Enter password: password
$

If you are upgrading from a version of Guacamole before 0.9.6, the default permissions associated with new users have changed such that users can change their own passwords. You may, if desired, run the upgrade script to migrate any existing users to the new permissions:

$ mysql -u root -p guacamole_db < schema/upgrade/upgrade-pre-0.9.6.sql
Enter password: password
$

If you are upgrading from a version of Guacamole before 0.9.7, schema changes regarding support for disabling users and expiring passwords need to be applied:

$ mysql -u root -p guacamole_db < schema/upgrade/upgrade-pre-0.9.7.sql
Enter password: password
$

PostgreSQL

If using PostgreSQL, the database and schema must be created first:

$ createdb guacamole_db
$ ls schema/
001-create-schema.sql  002-create-admin-user.sql
$ cat schema/*.sql | psql -d guacamole_db -f -
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TABLE
CREATE INDEX
...
INSERT 0 1
INSERT 0 4
INSERT 0 3
$

Once the database exists, you can safely create a new user for the database, and grant that user sufficient privileges to manage the contents of all tables in the database:

$ psql -d guacamole_db
psql (9.3.6)
Type "help" for help.

guacamole=# CREATE USER guacamole_user WITH PASSWORD 'some_password';
CREATE ROLE
guacamole=# GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO guacamole_user;
GRANT
guacamole=# GRANT SELECT,USAGE ON ALL SEQUENCES IN SCHEMA public TO guacamole_user;
GRANT
guacamole=# \q
$

Important

If you are upgrading from an older version of Guacamole that lacked support disabling users and expiring passwords (older than 0.9.7), you should instead run the upgrade script located within the upgrade/ directory:

$ ls schema/upgrade/
upgrade-pre-0.9.7.sql
$ mysql -u root -p guacamole_db < schema/upgrade/upgrade-pre-0.9.7.sql
Enter password: password
$

Installing database authentication

Guacamole extensions are self-contained .jar files which are located within the GUACAMOLE_HOME/extensions directory. To install the database authentication extension, you must:

  1. Create the GUACAMOLE_HOME/extensions directory, if it does not already exist.

  2. Remove any existing authentication extensions from GUACAMOLE_HOME/extensions. Guacamole does not currently support using multiple authentication extensions at the same time.

  3. Copy guacamole-auth-jdbc-mysql-0.9.7.jar or guacamole-auth-jdbc-postgresql-0.9.7.jar within GUACAMOLE_HOME/extensions, depending on whether you are using MySQL/MariaDB or PostgreSQL.

  4. Copy the JDBC driver for your database to GUACAMOLE_HOME/lib. Without a JDBC driver for your database, Guacamole will not be able to connect and authenticate users.

  5. Configure Guacamole to use database authentication, as described below.

Important

You will need to restart Guacamole by restarting your servlet container in order to complete the installation. Doing this will disconnect all active users, so be sure that it is safe to do so prior to attempting installation. If you do not configure the database authentication properly, Guacamole will not start up again until the configuration is fixed.

Configuring Guacamole for database authentication

Additional properties must be added to guacamole.properties for Guacamole to properly connect to your database. These properties are specific to the database being used, and must be set correctly for authentication to work.

To use a MySQL database, you will need to specify the following:

# MySQL properties
mysql-hostname: localhost
mysql-port: 3306
mysql-database: guacamole_db
mysql-username: guacamole_user
mysql-password: some_password

For PostgreSQL, the properties are similar, but with different prefixes:

# PostgreSQL properties
postgresql-hostname: localhost
postgresql-port: 5432
postgresql-database: guacamole_db
postgresql-username: guacamole_user
postgresql-password: some_password

The properties absolutely required by the database authentication extension are relatively few and self-explanatory, describing only how the connection to the database is to be established, and how Guacamole will authenticate when querying the database:

MySQL/MariaDB PropertyPostgreSQL PropertyDescription
mysql-hostnamepostgresql-hostname

The hostname or IP address of the server hosting your database.

mysql-portpostgresql-port

The port number of the database to connect to. For MySQL and MariaDB, this will likely be 3306. For PostgreSQL, this will likely be 5432.

mysql-databasepostgresql-database

The name of the database that you created for Guacamole. This is given as "guacamole_db" in the examples given in this chapter.

mysql-usernamepostgresql-username

The username of the user that Guacamole should use to connect to the database. This is given as "guacamole_user" in the examples given in this chapter.

mysql-passwordpostgresql-password

The password Guacamole should provide when authenticating with the database. This is given as "some_password" in the examples given in this chapter.

Be sure to specify the correct username and password for the database user you created, and to specify the correct database. Authentication will not work if these parameters are not correct.

Concurrent use of Guacamole connections

The database authentication module also provides configuration options to restrict concurrent use of connections if desired:

# MySQL
mysql-disallow-simultaneous-connections: true

# PostgreSQL
postgresql-disallow-simultaneous-connections: true

This is not required, but with the above property in place, users attempting to use a connection that is already in use by another user will be denied access. By default, concurrent access is allowed. This does not affect balancing groups, but does affect the usage of connections within balancing groups.

Concurrent access to balancing groups is restricted by default, and while balancing groups can be used by any number of users simultaneously, each individual user may only have one active connection to a balancing group. You can change this if you wish, but beware that individual users will then be able to exhaust the available connections of a balancing group:

# MySQL
mysql-disallow-duplicate-connections: false

# PostgreSQL
postgresql-disallow-duplicate-connections: false

Completing the installation

Guacamole will only reread guacamole.properties and load newly-installed extensions during startup, so your servlet container will need to be restarted before the database authentication will take effect. Restart your servlet container and give the new authentication a try.

Important

You only need to restart your servlet container. You do not need to restart guacd.

guacd is completely independent of the web application and does not deal with guacamole.properties or the authentication system in any way. Since you are already restarting the servlet container, restarting guacd as well technically won't hurt anything, but doing so is completely pointless.

If Guacamole does not come back online after restarting your servlet container, check the logs. Problems in the configuration of the database authentication extension will prevent Guacamole from starting up, and any such errors will be recorded in the logs of your servlet container.

Logging in

The default Guacamole user created by the provided SQL scripts is "guacadmin", with a default password of "guacadmin". Once you have verified that the database authentication is working, you should change your password immediately.

More detailed instructions for managing users and connections is given in Chapter 10, Administration.

Modifying data manually

If necessary, it is possible to modify the data backing the authentication module manually by executing SQL statements against the database. In general use, this will not be common, but if you need to bulk-insert a large number of users or connections, or you wish to translate an existing configuration automatically, you will need to know how everything is laid out at a high level.

This section assumes knowledge of SQL and your chosen database, and that whatever you need to do can be accomplished if only you had high-level information about Guacamole's SQL schema.

Users

Every user has a corresponding entry in the guacamole_user table. Each user has a corresponding unique username and salted password. The salted password is split into two columns: one containing the salt, and the other containing the password hashed with SHA-256.

The guacamole_user table contains the following columns:

user_id

The unique integer associated with each user. This value is generated automatically when a new entry is inserted into the guacamole_user table.

username

The unique name associated with each user. This value must be specified manually, and must be different from any existing username in the table. References to users in other tables use the value from user_id, not username.

password_hash

The result of hashing the user's password concatenated with the contents of password_salt using SHA-256. The salt is appended to the password prior to hashing.

Although passwords set through Guacamole will always be salted, it is possible to use unsalted password hashes when inserted manually or through an external system. If password_salt is NULL, the password_hash will be handled as a simple unsalted hash of the password.

password_salt

A 32-byte random value. When a new user is created from the web interface, this value is randomly generated using a cryptographically-secure random number generator.

This will always be set for users whose passwords are set through Guacamole, but it is possible to use unsalted password hashes when inserted manually or through an external system. If password_salt is NULL, the password_hash will be handled as a simple unsalted hash of the password.

disabled

Whether login attempts as this user account should be rejected. If this column is set to TRUE or 1, login attempts by this user will be rejected as if the user did not exist. By default, user accounts are not disabled, and login attempts will succeed if the user provides the correct password.

expired

If set to TRUE or 1, requires that the user reset their password prior to fully logging in. The user will be presented with a password reset form, and will not be allowed to log into Guacamole until the password has been changed. By default, user accounts are not expired, and no password reset will be required upon login.

Important

If you choose to manually set unsalted password hashes, please be sure you understand the security implications of doing so.

In the event that your database is compromised, finding the password for a salted hash is computationally infeasible, but finding the password for an unsalted hash is often not. In many cases, the password which corresponds to an unsalted hash can be found simply by entering the hash into a search engine like Google.

If creating a user manually, the main complication is the salt, which must be determined before the INSERT statement can be constructed, but this can be dealt with using variables. For MySQL:

-- Generate salt
SET @salt = UNHEX(SHA2(UUID(), 256));

-- Create user and hash password with salt
INSERT INTO guacamole_user (username, password_salt, password_hash)
     VALUES ('myuser', @salt, UNHEX(SHA2(CONCAT('mypassword', HEX(@salt)), 256)));

This sort of statement is useful for both creating new users or for changing passwords, especially if all administrators have forgotten theirs.

If you are not using MySQL, or you are using a version of MySQL that lacks the SHA2 function, you will need to calculate the SHA-256 value manually (by using the sha256sum command, for example).

Connections and parameters

Each connection has an entry in the guacamole_connection table, with a one-to-many relationship to parameters, stored as name/value pairs in the guacamole_connection_parameter table.

The guacamole_connection table is simply a pairing of a unique and descriptive name with the protocol to be used for the connection. It contains the following columns:

connection_id

The unique integer associated with each connection. This value is generated automatically when a new entry is inserted into the guacamole_connection table.

connection_name

The unique name associated with each connection. This value must be specified manually, and must be different from any existing connection name in the same connection group. References to connections in other tables use the value from connection_id, not connection_name.

protocol

The protocol to use with this connection. This is the name of the protocol that should be sent to guacd when connecting, for example "vnc" or "rdp".

parent_id

The unique integer associated with the connection group containing this connection, or NULL if this connection is within the root group.

As there are potentially multiple parameters per connection, where the names of each parameter are completely arbitrary and determined only by the protocol in use, every parameter for a given connection has an entry in table guacamole_connection_parameter table associated with its corresponding connection. This table contains the following columns:

connection_id

The connection_id value from the connection this parameter is for.

parameter_name

The name of the parameter to set. This is the name listed in the documentation for the protocol specified in the associated connection.

parameter_value

The value to assign to the parameter named. While this value is an arbitrary string, it must conform to the requirements of the protocol as documented for the connection to be successful.

Adding a connection and corresponding parameters is relatively easy compared to adding a user as there is no salt to generate nor password to hash:

-- Create connection
INSERT INTO guacamole_connection (connection_name, protocol) VALUES ('test', 'vnc');

-- Determine the connection_id
SELECT * FROM guacamole_connection WHERE connection_name = 'test' AND parent_id IS NULL;

-- Add parameters to the new connection
INSERT INTO guacamole_connection_parameter VALUES (1, 'hostname', 'localhost');
INSERT INTO guacamole_connection_parameter VALUES (1, 'port', '5901');

Usage history

When a connection is initiated or terminated, a corresponding entry in the guacamole_connection_history table is created or updated respectively. Each entry is associated with the user using the connection, the connection itself, and the time the connection started. If the connection has ended, the end time is also stored.

It is very unlikely that a user will need to update this table, but knowing the structure is potentially useful if you wish to generate a report of Guacamole usage. The guacamole_connection_history table has the following columns:

history_id

The unique integer associated with each history record. This value is generated automatically when a new entry is inserted into the guacamole_connection_history table.

user_id

The value of the user_id from the entry in guacamole_user associated with the user using the connection.

connection_id

The value of the connection_id from the entry in guacamole_connection associated the connection being used.

start_date

The time at which the connection was started by the user specified. Despite its name, this column also stores time information in addition to the date.

end_date

The time at which the connection ended. If the connection is still active, the value in this column will be NULL. Despite its name, this column also stores time information in addition to the date.

Connection groups

Each connection group has an entry in the guacamole_connection_group table, with a one-to-many relationship to other groups and connections.

The guacamole_connection_group table is simply a pairing of a unique and descriptive name with a group type, which can be either ORGANIZATIONAL or BALANCING. It contains the following columns:

connection_group_id

The unique integer associated with each connection group. This value is generated automatically when a new entry is inserted into the guacamole_connection_group table.

connection_group_name

The unique name associated with each connection group. This value must be specified manually, and must be different from any existing connection group name in the same connection group. References to connections in other tables use the value from connection_group_id, not connection_group_name.

type

The type of this connection group. This can be either ORGANIZATIONAL or BALANCING.

parent_id

The unique integer associated with the connection group containing this connection group, or NULL if this connection group is within the root group.

Adding a connection group is even simpler than adding a new connection as there are no associated parameters stored in a separate table:

-- Create connection group
INSERT INTO guacamole_connection_group (connection_group_name, type)
     VALUES ('test', 'ORGANIZATIONAL');

Permissions

There are three permissions tables in the schema which correspond to the three types of permissions in Guacamole's authentication model: system permissions, which control operations that affect the system as a whole, and user and connection permissions, which control operations that affect specific, existing users or connections respectively.

System permissions

System permissions are defined by entries in the guacamole_system_permission table. Each entry grants permission for a specific user to perform a specific system operation.

The guacamole_system_permission table contains the following columns:

user_id

The value of the user_id column of the entry associated with the user owning this permission.

permission

The permission being granted. This column can have one of three possible values: ADMINISTER, which grants the ability to administer the entire system (essentially a wildcard permission), CREATE_CONNECTION, which grants the ability to create connections, CREATE_CONNECTION_GROUP, which grants the ability to create connections groups, or CREATE_USER, which grants the ability to create users.

User permissions

User permissions are defined by entries in the guacamole_user_permission table. Each entry grants permission for a specific user to perform a specific operation on another existing user.

The guacamole_user_permission table contains the following columns:

user_id

The value of the user_id column of the entry associated with the user owning this permission.

affected_user_id

The value of the user_id column of the entry associated with the user affected by this permission. This is the user that would be the object of the operation represented by this permission.

permission

The permission being granted. This column can have one of four possible values: ADMINISTER, which grants the ability to add or remove permissions which affect the user, READ, which grants the ability to read data associated with the user, UPDATE, which grants the ability to update data associated with the user, or DELETE, which grants the ability to delete the user.

Connection permissions

Connection permissions are defined by entries in the guacamole_connection_permission table. Each entry grants permission for a specific user to perform a specific operation on an existing connection.

The guacamole_connection_permission table contains the following columns:

user_id

The value of the user_id column of the entry associated with the user owning this permission.

connection_id

The value of the connection_id column of the entry associated with the connection affected by this permission. This is the connection that would be the object of the operation represented by this permission.

permission

The permission being granted. This column can have one of four possible values: ADMINISTER, which grants the ability to add or remove permissions which affect the connection, READ, which grants the ability to read data associated with the connection (a prerequisite for connecting), UPDATE, which grants the ability to update data associated with the connection, or DELETE, which grants the ability to delete the connection.

Connection group permissions

Connection group permissions are defined by entries in the guacamole_connection_group_permission table. Each entry grants permission for a specific user to perform a specific operation on an existing connection group.

The guacamole_connection_group_permission table contains the following columns:

user_id

The value of the user_id column of the entry associated with the user owning this permission.

connection_group_id

The value of the connection_group_id column of the entry associated with the connection group affected by this permission. This is the connection group that would be the object of the operation represented by this permission.

permission

The permission being granted. This column can have one of four possible values: ADMINISTER, which grants the ability to add or remove permissions which affect the connection group, READ, which grants the ability to read data associated with the connection group, UPDATE, which grants the ability to update data associated with the connection group, or DELETE, which grants the ability to delete the connection group (and implicitly its contents).