Chapter 6. Database authentication

Chapter 6. Database authentication
Prev	Part I. User's Guide	Next

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://guacamole.incubator.apache.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.13-incubating.jar, along with a schema/ directory containing MySQL-specific SQL scripts required to set up the database. The guacamole-auth-jdbc-mysql-0.9.13-incubating.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.13-incubating.jar, along with a schema/ directory containing PostgreSQL-specific SQL scripts required to set up the database. The guacamole-auth-jdbc-postgresql-0.9.13-incubating.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 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 and were already using MySQL, you may need to run one or more database schema upgrade scripts located within the schema/upgrade/ directory. Each of these scripts is named upgrade-pre-VERSION.sql where VERSION is the version of Guacamole where those changes were introduced. They need to be run when you are upgrading from a version of Guacamole older than VERSION:

$ ls schema/upgrade/
upgrade-pre-0.8.2.sql   upgrade-pre-0.9.13.sql  upgrade-pre-0.9.8.sql
upgrade-pre-0.9.10.sql  upgrade-pre-0.9.6.sql   upgrade-pre-0.9.9.sql
upgrade-pre-0.9.11.sql  upgrade-pre-0.9.7.sql
$

These scripts are incremental and, when relevant, must be run in order. For example, if you are upgrading an existing database from version 0.9.10-incubating, you would need to run the upgrade-pre-0.9.11.sql script (because 0.9.10 is older than 0.9.11), followed by the upgrade-pre-0.9.13.sql script (because 0.9.10 is also older than 0.9.13):

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

If there are no upgrade-pre-VERSION.sql scripts present in the schema/upgrade/ directory which apply to your existing Guacamole database, then the schema has not changed between your version and the version your are installing, and there is no need to run any database upgrade scripts.

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 and were already using PostgreSQL, you may need to run one or more database schema upgrade scripts located within the schema/upgrade/ directory. Each of these scripts is named upgrade-pre-VERSION.sql where VERSION is the version of Guacamole where those changes were introduced. They need to be run when you are upgrading from a version of Guacamole older than VERSION:

$ ls schema/upgrade/
upgrade-pre-0.9.10.sql  upgrade-pre-0.9.13.sql  upgrade-pre-0.9.8.sql
upgrade-pre-0.9.11.sql  upgrade-pre-0.9.7.sql   upgrade-pre-0.9.9.sql
$

These scripts are incremental and, when relevant, must be run in order. For example, if you are upgrading an existing database from version 0.9.10-incubating, you would need to run the upgrade-pre-0.9.11.sql script (because 0.9.10 is older than 0.9.11), followed by the upgrade-pre-0.9.13.sql script (because 0.9.10 is also older than 0.9.13):

$ psql -d guacamole_db -f schema/upgrade/upgrade-pre-0.9.11.sql
ALTER TABLE
CREATE TABLE
CREATE INDEX
$ psql -d guacamole_db -f schema/upgrade/upgrade-pre-0.9.13.sql
CREATE TYPE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
$

Because the permissions granted to the Guacamole-specific PostgreSQL user when the database was first created will not automatically be granted for any new tables and sequences, you will also need to re-grant those permissions after applying any upgrade relevant scripts:

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

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
$

If there are no upgrade-pre-VERSION.sql scripts present in the schema/upgrade/ directory which apply to your existing Guacamole database, then the schema has not changed between your version and the version your are installing, and there is no need to run any database upgrade scripts.

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:

Create the GUACAMOLE_HOME/extensions directory, if it does not already exist.
Copy guacamole-auth-jdbc-mysql-0.9.13-incubating.jar or guacamole-auth-jdbc-postgresql-0.9.13-incubating.jar within GUACAMOLE_HOME/extensions, depending on whether you are using MySQL/MariaDB or PostgreSQL.
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.
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 Property	PostgreSQL Property	Description
mysql-hostname	postgresql-hostname	The hostname or IP address of the server hosting your database.
mysql-port	postgresql-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-database	postgresql-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-username	postgresql-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-password	postgresql-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.

Enforcing password policies

Configuration options are available for enforcing rules intended to encourage password complexity and regular changing of passwords. None of these options are enabled by default, but can be selectively enabled through additional properties in guacamole.properties.

Password complexity

Administrators can require that passwords have a certain level of complexity, such as having both uppercase and lowercase letters ("multiple case"), at least one digit, or at least one symbol, and can prohibit passwords from containing the user's own username.

For the sake of password content, the database authentication defines a "digit" as any numeric character. This takes non-English languages into account, and is not be simply "0" thorough "9". There are quite a few numeric characters defined by Unicode. A "symbol" is defined as any non-alphanumeric character - any character which Unicode does not define as alphabetic or numeric.

The check for whether a password contains the user's own username is performed in a case-insensitive manner. For example, if the user's username is "phil", the passwords "ch!0roPhil" and "PHIL-o-dendr0n" would still be prohibited.

# MySQL
mysql-user-password-min-length: 8
mysql-user-password-require-multiple-case: true
mysql-user-password-require-symbol: true
mysql-user-password-require-digit: true
mysql-user-password-prohibit-username: true

# PostgreSQL
postgresql-user-password-min-length: 8
postgresql-user-password-require-multiple-case: true
postgresql-user-password-require-symbol: true
postgresql-user-password-require-digit: true
postgresql-user-password-prohibit-username: true

Password age / expiration

"Password age" refers to two separate concepts:

Requiring users to change their password after a certain amount of time has elapsed since the last password change (maximum password age).
Preventing users from changing their password too frequently (minimum password age).

In both cases, these values are specified in units of days, and are both disabled by default.

While it may seem strange to prevent users from changing their password too frequently, it does make sense if you are concerned that rapid password changes may defeat password expiration (users could immediately change the password back) or tracking of password history (users could cycle through passwords until the history is exhausted and their old password is back).

So that administrators can always intervene in the case that a password needs to be reset despite restrictions, the minimum age restriction does not apply to any user with permission to administer the system.

# MySQL
mysql-user-password-min-age: 7
mysql-user-password-max-age: 90

# PostgreSQL
postgresql-user-password-min-age: 7
postgresql-user-password-max-age: 90

Preventing password reuse

If desired, Guacamole can keep track of each user's most recently used passwords, and will prohibit reuse of those passwords until the password has been changed sufficiently many times. By default, Guacamole will not keep track of old passwords.

Note that these passwords are hashed in the same manner as each user's current password. When a user's password is changed, the hash, salt, etc. currently stored for that user is actually just copied verbatim (along with a timestamp) into a list of historical passwords, with older entries from this list being automatically deleted.

# MySQL
mysql-user-password-history-size: 6

# PostgreSQL
postgresql-user-password-history-size: 6

Concurrent use of Guacamole connections

The database authentication module provides configuration options to restrict concurrent use of connections or connection groups. These options are set through guacamole.properties and specify the default concurrency policies for connections and connection groups. The values set through the properties can be overridden later on a per-connection basis using the administrative interface:

# MySQL
mysql-default-max-connections: 1
mysql-default-max-group-connections: 1

# PostgreSQL
postgresql-default-max-connections: 1
postgresql-default-max-group-connections: 1

These properties are not required, but with the above properties in place, users attempting to use a connection or group that is already in use will be denied access. By default, concurrent access is allowed.

Concurrent access can also be restricted such that a particular user may only use a connection or group a certain number of times. By default, per-user concurrent use is limited for connection groups (to avoid allowing a single user to exhaust the contents of the group) but otherwise unrestricted. This default behavior can be modified through guacamole.properties or the per-connection settings exposed in the administrative interface:

# MySQL
mysql-default-max-connections-per-user: 0
mysql-default-max-group-connections-per-user: 0

# PostgreSQL
postgresql-default-max-connections-per-user: 0
postgresql-default-max-group-connections-per-user: 0

The above properties replace the "simultaneous" and "duplicate" properties used by prior Guacamole releases. The older properties will still work, but are now deprecated. If you continue to use those properties, you will receive warnings in the logs advising you of their deprecated status and including examples for providing the same behavior with the new properties described above:

WARN  o.g.g.a.p.PostgreSQLAuthenticationProvider - The
"postgresql-disallow-simultaneous-connections" property is deprecated.
Use "postgresql-default-max-connections" and
"postgresql-default-max-group-connections" instead.
INFO  o.g.g.a.p.PostgreSQLAuthenticationProvider - To achieve the same
result of setting "postgresql-disallow-simultaneous-connections" to
"false", set "postgresql-default-max-connections" to "0" and
"postgresql-default-max-group-connections" to "0".

If you wish to impose an absolute limit on the number of connections that can be established through Guacamole, ignoring which users or connections are involved, this can be done as well. By default, Guacamole will impose no such limit:

# MySQL
mysql-absolute-max-connections: 0

# PostgreSQL
postgresql-absolute-max-connections: 0

Restricting authentication to database users only

By default, users will be allowed access to Guacamole as long as they are authenticated by at least one extension. If database authentication is in use, and a user is not associated with the database, then that user will be allowed access to Guacamole if another extension grants this access, and will be provided with a view of the data exposed by other extensions for that user account.

In some situations, such as when combining LDAP with a database, it would be preferable to let the database have the last word regarding whether a user should be allowed into the system: restricting access to only those users which exist in the database, and explicitly denying authentication through all other means unless that user has been associated with the database as well. This behavior can be forced by setting properties which declare that database user accounts are required:

# MySQL
mysql-user-required: true

# PostgreSQL
postgresql-user-required: true

With the above properties set, successful authentication attempts for users which are not associated with the database will be vetoed by the database authentication. Guacamole will report that the login is invalid, as if the user does not exist at all.

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 12, 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.

password_date

The date (and time) that the password was last changed. When a password is changed via the Guacamole interface, this value is updated. This, along with the contents of the guacamole_user_password_history table, is used to enforce password policies.

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.

access_window_start

The time of day (not date) after which this user account may be used. If NULL, this restriction does not apply. If set to non-NULL, attempts to log in after the specified time will be allowed, while attempts to log in before the specified time will be denied.

access_window_end

The time of day (not date) after which this user account may not be used. If NULL, this restriction does not apply. If set to non-NULL, attempts to log in after the specified time will be denied, while attempts to log in before the specified time will be allowed.

valid_from

The date (not time of day) after which this user account may be used. If NULL, this restriction does not apply. If set to non-NULL, attempts to log in after the specified date will be allowed, while attempts to log in before the specified date will be denied.

valid_until

The date (not time of day) after which this user account may not be used. If NULL, this restriction does not apply. If set to non-NULL, attempts to log in after the specified date will be denied, while attempts to log in before the specified date will be allowed.

timezone

The time zone to use when interpreting the access_window_start, access_window_end, valid_from, and valid_until values. This value may be any Java TimeZone ID, as defined by getAvailableIDs(), though the Guacamole management interface will only present a subset of these time zones.

full_name

The user's full name. Unlike the username, this name need not be unique; it is optional and is meant for display purposes only. Defining this value has no bearing on user identity, which is dictated purely by the username. User accounts with no associated full name should have this column set to NULL.

email_address

The user's email address, if any. This value is optional, need not be unique relative to other defined users, and is meant for display purposes only. Defining this value has no bearing on user identity, which is dictated purely by the username. If the user has no associated email address, this column should be set to NULL.

organization

The name of the organization, company, etc. that the user is affiliated with. This value is optional and is meant for display purposes only. Defining this value has no bearing on user identity, which is dictated purely by the username. Users with no associated organization should have this column set to NULL.

organizational_role

The role or title of the user at the organization described by the organization column. This value is optional and is used for display purposes only. Defining this value has no bearing on user identity, which is dictated purely by the username. Users with no associated organization (or specific role/title at that organization) should have this column set to NULL.

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

Password history

When a user's password is changed, a copy of the previous password's hash and salt is made within the guacamole_user_password_history. Each entry in this table is associated with the user whose password changed, along with the date that password first applied.

Old entries within this table are automatically deleted on a per-user basis depending on the requirements of the password policy. For example, if the password policy has been configured to require that users not reuse any of their previous six passwords, then there will be no more than six entries in this table for each user.

password_history_id

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

user_id

The value of the user_id column from the entry in guacamole_user associated with the user who previously had this password.

password_hash

The hashed password specified within the password_hash column of guacamole_user prior to the password being changed.

In most cases, this will be a salted hash, though it is possible to force the use of unsalted hashes when making changes to the database manually or through an external system.

password_salt

The salt value specified within the password_salt column of guacamole_user prior to the password being changed.

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, in which case this may be NULL.

password_date

The date (and time) that the password was set. The time that the password ceased being used is recorded either by the password_date of the next related entry in guacamole_user_password_history or password_date of guacamole_user (if there is no such history entry).

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.
max_connections: The maximum number of concurrent connections to allow to this connection at any one time regardless of user. NULL will use the default value specified in guacamole.properties with the mysql-default-max-connections or postgresql-default-max-connections properties, and a value of 0 denotes unlimited.
max_connections_per_user: The maximum number of concurrent connections to allow to this connection at any one time from a single user. NULL will use the default value specified in guacamole.properties with the mysql-default-max-connections or postgresql-default-max-connections properties, and a value of 0 denotes unlimited.
proxy_hostname: The hostname or IP address of the Guacamole proxy daemon (guacd) which should be used for this connection. If NULL, the value defined with the guacd-hostname property in guacamole.properties will be used.
proxy_port: The TCP port number of the Guacamole proxy daemon (guacd) which should be used for this connection. If NULL, the value defined with the guacd-port property in guacamole.properties will be used.
proxy_encryption_method: The encryption method which should be used when communicating with the Guacamole proxy daemon (guacd) for this connection. This can be either NONE, for no encryption, or SSL, for SSL/TLS. If NULL, the encryption method will be dictated by the guacd-ssl property in guacamole.properties.

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, the sharing profile in use (if the connection is being shared), 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. If the user no longer exists, this will be NULL.
username: The username associated with the user at the time that they used the connection. This username value is not guaranteed to uniquely identify a user, as the original user may be subsequently renamed or deleted.
connection_id: The value of the connection_id from the entry in guacamole_connection associated the connection being used. If the connection associated with the history record no longer exists, this will be NULL.
connection_name: The name associated with the connection at the time that it was used.
sharing_profile_id: The value of the sharing_profile_id from the entry in guacamole_sharing_profile associated the sharing profile being used to access the connection. If the connection is not being shared (no sharing profile is being used), or if the sharing profile associated with the history record no longer exists, this will be NULL.
sharing_profile_name: The name associated with the sharing profile being used to access the connection at the time this history entry was recorded. If the connection is not being shared, this will be NULL.
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.

Sharing profiles and parameters

Each sharing profile has an entry in the guacamole_sharing_profile table, with a one-to-many relationship to parameters, stored as name/value pairs in the guacamole_sharing_profile_parameter table.

The guacamole_sharing_profile table is simply a pairing of a unique and descriptive name with the connection that can be shared using the sharing profile, also known as the "primary connection". It contains the following columns:

sharing_profile_id: The unique integer associated with each sharing profile. This value is generated automatically when a new entry is inserted into the guacamole_sharing_profile table.
sharing_profile_name: The unique name associated with each sharing profile. This value must be specified manually, and must be different from any existing sharing profile name associated with the same primary connection. References to sharing profiles in other tables use the value from sharing_profile_id, not sharing_profile_name.
primary_connection_id: The unique integer associated with the primary connection. The "primary connection" is the connection which can be shared using this sharing profile.

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

sharing_profile_id: The sharing_profile_id value from the entry in the guacamole_sharing_profile table for the sharing profile this parameter applies to.
parameter_name: The name of the parameter to set. This is the name listed in the documentation for the protocol of the primary connection of the associated sharing profile.
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.

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.
max_connections: The maximum number of concurrent connections to allow to this connection group at any one time regardless of user. NULL will use the default value specified in guacamole.properties with the mysql-default-max-connections or postgresql-default-max-connections properties, and a value of 0 denotes unlimited. This only has an effect on BALANCING groups.
max_connections_per_user: The maximum number of concurrent connections to allow to this connection group at any one time from a single user. NULL will use the default value specified in guacamole.properties with the mysql-default-max-connections or postgresql-default-max-connections properties, and a value of 0 denotes unlimited. This only has an effect on BALANCING groups.
enable_session_affinity: Whether session affinity should apply to this connection group. If this column is set to TRUE or 1, users will be consistently routed to the same underlying connection until they log out. The normal balancing behavior will only apply for each user's first connection attempt during any one Guacamole session. By default, session affinity is not enabled, and connections will always be balanced across the entire connection group. This only has an effect on BALANCING groups.

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.

Sharing profile permissions

Sharing profile permissions are defined by entries in the guacamole_sharing_profile_permission table. Each entry grants permission for a specific user to perform a specific operation on an existing sharing profile.

The guacamole_sharing_profile_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.
sharing_profile_id: The value of the sharing_profile_id column of the entry associated with the sharing profile affected by this permission. This is the sharing profile 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 sharing profile, READ, which grants the ability to read data associated with the sharing profile (a prerequisite for using the sharing profile to share an active connection), UPDATE, which grants the ability to update data associated with the sharing profile, or DELETE, which grants the ability to delete the sharing profile.

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

Prev	Up	Next
Chapter 5. Configuring Guacamole	Home	Chapter 7. LDAP authentication