Creating and initializing the Guacamole database

If you haven’t already done so, a database specific to Guacamole needs to be created within PostgreSQL. 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:

CREATE DATABASE guacamole_db;

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-postgresql package, which are located within the /opt/keeper/share/guacamole-auth-jdbc-postgresql/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-postgresql/schema/*.sql | psql -d guacamole_db -f -

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

$ psql -d guacamole_db -f /opt/keeper/share/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql
$ psql -d guacamole_db -f /opt/keeper/share/guacamole-auth-jdbc-postgresql/schema/002-create-admin-user.sql

Connecting Guacamole to PostgreSQL

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 WITH PASSWORD 'some_password';
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO guacamole_user;
GRANT SELECT,USAGE ON ALL SEQUENCES IN SCHEMA public TO guacamole_user;

Advanced Linux Install Method

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

$ sudo yum install kcm-guacamole-auth-jdbc-postgresql

Guacamole’s main configuration file, /etc/guacamole/guacamole.properties, must now be modified to specify the credentials of the PostgreSQL user and to point the PostgreSQL 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 postgresql-hostname and postgresql-port properties, modifying their values to point to your PostgreSQL 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

postgresql-hostname: localhost
postgresql-port:     5432

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 postgresql-database, postgresql-username, and postgresql-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

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

Guacamole will generally only load new extensions and reread guacamole.properties during the startup process.

Advanced Linux Install Method

$ 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 PostgreSQL database server called "postgresql-server". Installing this package will install a version of PostgreSQL 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 PostgreSQL for use by Guacamole will work nicely:

$ sudo yum install postgresql-server

As with other standard CentOS / RHEL packages providing a service, the PostgreSQL service will not be started by default after the "postgresql-server" package is installed. However, if you attempt to start the PostgreSQL service now, the service will fail to start as PostgreSQL's database has not yet been created and initialized. This must be done manually with the "postgresql-setup" command:

$ sudo postgresql-setup initdb

Once the database has been initialized, the service can be safely started and configured to start automatically if the system is rebooted:

$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql

Configuring PostgreSQL to accept password authentication locally

If PostgreSQL is installed locally (on the same server as Apache Guacamole), its default configuration will prevent Guacamole from authenticating. This is because PostgreSQL can be configured to use different authentication mechanisms for connections coming from different networks or addresses, and the default configuration uses "ident" authentication for connections from the local machine. The "ident" method is incompatible with providing a database username and password via TCP, which will result in Guacamole being unable to connect to PostgreSQL.

Edit PostgreSQL's main configuration file, /var/lib/pgsql/data/pg_hba.conf, looking for the lines which associate IPv4 or IPv6 loopback addresses with "ident":

host    all     all     127.0.0.1/32    ident
host    all     all     ::1/128         ident

The keyword ident should be changed to md5 to allow username/password authentication for local connections:

host    all     all     127.0.0.1/32    md5
host    all     all     ::1/128         md5

PostgreSQL will then need to be restarted to apply these changes:

$ sudo systemctl restart postgresql

Pointing Guacamole at the new PostgreSQL instance

Once PostgreSQL has been deployed, you should move forward with configuring Guacamole to use your new PostgreSQL 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 PostgreSQL support (kcm-guacamole-auth-jdbc-postgresql).

• Creating a new database within your PostgreSQL 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).