Getting the TOTP Key From the Guacamole Database

Guac’s great, but there’s no interface for changing the TOTP key once it’s set for a user.

I’m using docker-compose to build this, so your method might be different.

Connect to postgresql:

psql guacamole postgres

To make sure you’re in the right db, type \dt and hit enter, it should show you a bunch of tables starting with guacamole_.

This is the query, copypasta should do it.

select, uid.user_id, uattr.attribute_value 
from guacamole_user as uid, 
    guacamole_user_attribute as uattr, 
    guacamole_entity as entity 
where uattr.attribute_name='guac-totp-key-secret' 
    AND uid.entity_id=entity.entity_id
    AND uid.user_id=uattr.user_id;

And the result I get (I’ve only got one user)

  name   | user_id |         attribute_value
 yaleman |       2 | <redacted>AAAABBBBCCCCDDDD333311

This is more a note for myself for the future than useful documentation… I’m pretty sure you could change the user’s token by updating the value in the guacamole_user_attribute table.

There’s a PR on the repository adding functionality to reset user tokens, this might be an option for the future.

Update 2021-09-26: A helpful user pointed out that my SQL query was wrong in some cases (was AND uid=user_id=entity.entity_id, is now AND uid.entity_id=entity.entity_id. Thanks!

#database #postgresql #guacamole #tips #docker