Resetting Guacamole TOTP

At a glance#

• Purpose: Restore access for an Apache Guacamole user who has lost their two-factor authentication device.
• Applies to: Apache Guacamole with the TOTP extension and a MySQL/MariaDB backend.
• Risk: Medium — edits the authentication database directly and removes a security control.
• Time: About 10 minutes.

Overview#

Guacamole's TOTP extension adds a second authentication factor to logins. Unlike Nextcloud, Guacamole has no administrative command for clearing it — the state lives in the guacamole_user_attribute table and has to be changed with SQL.

The reset works by flipping the guac-totp-key-confirmed attribute to false. On their next login the user is shown a fresh QR code and enrols a new device.

Warning: You are editing the authentication database of a remote-access gateway by hand. Verify the requester's identity through a channel other than the one the request arrived on. Anyone who can reset TOTP on a Guacamole account can reach every system that account has connections to.

Before you start#

You will need:

• Shell access to the Guacamole database server.
• MySQL credentials with SELECT and UPDATE on the Guacamole schema.
• The user's exact Guacamole username.
• The database name. It is guacamole_db by default, but confirm with SHOW DATABASES; before assuming.

Tip: Take a backup of the two tables you are about to touch before making changes. It takes seconds and gives you a way back: ``bash mysqldump -u root -p guacamole_db guacamole_user guacamole_user_attribute > /root/guac-2fa-backup-$(date +%F).sql ``

Procedure#

1. Connect to MySQL and select the database#

mysql -u root -p

USE guacamole_db;

2. Find the user's internal user ID#

Guacamole splits identity across two tables. guacamole_entity holds the username; guacamole_user holds the account record. You need the numeric user_id from the second, looked up by name from the first.

Replace Kiran with the username you are resetting.

SELECT user_id
FROM guacamole_user
INNER JOIN guacamole_entity
        ON guacamole_entity.entity_id = guacamole_user.entity_id
WHERE guacamole_entity.name = 'Kiran';

The result is a single number, for example:

+---------+
| user_id |
+---------+
|      12 |
+---------+

If the query returns no rows, the username is wrong. List the accounts to find the correct spelling:

SELECT name FROM guacamole_entity WHERE type = 'USER';

3. Confirm the current TOTP state#

Check what is actually set before changing it, so you know the update did something:

SELECT attribute_name, attribute_value
FROM guacamole_user_attribute
WHERE user_id = 12;

You should see guac-totp-key-confirmed with a value of true.

4. Reset the TOTP enrolment#

Substitute the user_id from step 2 for 12.

UPDATE guacamole_user_attribute
SET attribute_value = 'false'
WHERE attribute_name = 'guac-totp-key-confirmed'
  AND user_id = 12;

MySQL reports how many rows changed:

Query OK, 1 row affected (0.01 sec)

Warning: 1 row affected is the only correct result. If it says 0 rows affected, the attribute row does not exist and nothing changed — see Troubleshooting. If it affects more than one row, you have omitted the user_id condition and just reset 2FA for multiple accounts.

5. Exit#

quit;

6. Have the user log in and re-enrol#

The user signs in with their username and password. Guacamole presents a new QR code, which they scan with their authenticator app. They then enter the generated code once to confirm enrolment.

Verification#

Re-run the query from step 3. The value should now read false before the user re-enrols, and return to true once they have scanned the new code and confirmed it.

That second check matters. A user who is shown a QR code but closes the tab without confirming is left with 2FA effectively off.

Troubleshooting#

Related#

• Resetting Nextcloud TOTP — the same task in Nextcloud, which provides a proper CLI and needs no SQL.