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.

$ 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
$

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

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

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
$

$ 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
$

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

# 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:

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.

# MySQL
mysql-disallow-simultaneous-connections: true

# PostgreSQL
postgresql-disallow-simultaneous-connections: true

# MySQL
mysql-disallow-duplicate-connections: false

# PostgreSQL
postgresql-disallow-duplicate-connections: false

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.

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.

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:

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

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:

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:

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');

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:

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');

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.