2FA no longer valid

[q20]

Hey there

After following the guide to upgrade PostgreSQL to v14, my docker instance now successfully starts and I can log in with my stored username and password, only the 2FA fails:

My clocks are all synced, so I have no idea what next to try. Is there a way to reinitiate the setup of 2FA for a given user from the CLI?

[abesnier]

Indeed, I believe schemas are not compatible between PG versions. I should mention it, and add a paragraph in the updated method about that.

That being said, here's what should work (provided you have access to the machine without Guacamole!)

Enter the docker conainer:
docker exec -it guacamole bash

Connect to the database:
psql -U guacamole guacamole_db

Find the user id of the user:
SELECT user_id FROM guacamole_user INNER JOIN guacamole_entity ON guacamole_entity.entity_id = guacamole_user.entity_id WHERE guacamole_entity.name = 'your user name';

This should return something like:

 user_id 
---------
       2
(1 row)

Run the following command:

UPDATE guacamole_user_attribute SET attribute_value='false' WHERE attribute_name = 'guac-totp-key-confirmed' and user_id = 'the id you found in the previous step';
quit;

Exit the container, and try to login again to guacamole. You should be prompted with the MFA registration again (qr code, etc...)

EDIT: one-liner should be something like: docker exec -it guacamole bash -c "psql -U guacamole guacamole_db -c \"UPDATE guacamole_user_attribute SET attribute_value='false' WHERE attribute_name = 'guac-totp-key-confirmed' and user_id = (SELECT user_id FROM guacamole_user INNER JOIN guacamole_entity ON guacamole_entity.entity_id = guacamole_user.entity_id WHERE guacamole_entity.name = 'your username');\""

[q20]

Thanks for the excellent reply, including the one-liner. 😉
Unfortunately, although prompted to create a new OTP, the number generated by the QR code is not accepted:

Any other ideas? I do not mind creating a new admin user...

[abesnier]

I think I saw a similar issue in the Guacamole mailing list a while ago, I'll have a look.

If you have a work around, that's OK, but I'll still try to find the correct solution.

Can you also post your logs? (docker logs Guacamole)

Another question comes to mind: can you check that you don't have multiple versions of the extensions in the config/guacamole/extensions and extensions-enabled directories?

[q20]

Sure thing:

Starting guacamole guacd...
Starting postgres...
/var/run/postgresql:5432 - no response
Waiting for postgres to come up...
Starting postgres...
/var/run/postgresql:5432 - no response
Waiting for postgres to come up...
Starting postgres...
/var/run/postgresql:5432 - no response
Waiting for postgres to come up...
Starting postgres...
/var/run/postgresql:5432 - no response
Waiting for postgres to come up...
Starting postgres...
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /config/postgres ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

    /usr/lib/postgresql/14/bin/pg_ctl -D /config/postgres -l logfile start

Starting guacamole guacd...
Starting postgres...
/var/run/postgresql:5432 - no response
Waiting for postgres to come up...
/var/run/postgresql:5432 - accepting connections
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX
INSERT 0 1
INSERT 0 1
INSERT 0 6
INSERT 0 3
Starting guacamole client...
06:27:02.507 [main] INFO  o.a.g.environment.LocalEnvironment - GUACAMOLE_HOME is "/config/guacamole".
06:27:02.618 [main] INFO  o.a.g.GuacamoleServletContextListener - Read configuration parameters from "/config/guacamole/guacamole.properties".
06:27:02.620 [main] INFO  o.a.g.rest.auth.HashTokenSessionMap - Sessions will expire after 60 minutes of inactivity.
06:27:03.177 [main] INFO  o.a.g.extension.ExtensionModule - Multiple extensions are installed and will be loaded in order of decreasing priority:
06:27:03.177 [main] INFO  o.a.g.extension.ExtensionModule -  - [postgresql] "PostgreSQL Authentication" (/config/guacamole/extensions/guacamole-auth-jdbc-postgresql-1.5.1.jar)
06:27:03.178 [main] INFO  o.a.g.extension.ExtensionModule -  - [postgresql] "PostgreSQL Authentication" (/config/guacamole/extensions/guacamole-auth-jdbc-postgresql-1.5.2.jar)
06:27:03.178 [main] INFO  o.a.g.extension.ExtensionModule -  - [ldap] "LDAP Authentication" (/config/guacamole/extensions/guacamole-auth-ldap-1.5.2.jar)
06:27:03.178 [main] INFO  o.a.g.extension.ExtensionModule -  - [totp] "TOTP TFA Authentication Backend" (/config/guacamole/extensions/guacamole-auth-totp-1.5.2.jar)
06:27:03.178 [main] INFO  o.a.g.extension.ExtensionModule - To change this order, set the "extension-priority" property or rename the extension files. The default priority of extensions is dictated by the sort order of their filenames.
06:27:04.281 [main] INFO  o.a.g.extension.ExtensionModule - Extension "PostgreSQL Authentication" (postgresql) loaded.
06:27:05.089 [main] INFO  o.a.g.extension.ExtensionModule - Extension "PostgreSQL Authentication" (postgresql) loaded.
06:27:05.267 [main] WARN  o.a.g.e.LanguageResourceService - Overlay language resource "de" does not exist.
06:27:05.270 [main] INFO  o.a.g.extension.ExtensionModule - Extension "LDAP Authentication" (ldap) loaded.
06:27:05.479 [main] INFO  o.a.g.extension.ExtensionModule - Extension "TOTP TFA Authentication Backend" (totp) loaded.
06:27:05.608 [main] INFO  o.a.g.t.w.WebSocketTunnelModule - Loading JSR-356 WebSocket support...
06:27:06.133 [main] WARN  o.g.jersey.server.wadl.WadlFeature - JAXBContext implementation could not be found. WADL feature is disabled.
Database already configured
Starting postgres...
Starting guacamole guacd...
/var/run/postgresql:5432 - no response
Waiting for postgres to come up...
/var/run/postgresql:5432 - accepting connections
Starting guacamole client...
06:27:33.549 [main] INFO  o.a.g.environment.LocalEnvironment - GUACAMOLE_HOME is "/config/guacamole".
06:27:33.694 [main] INFO  o.a.g.GuacamoleServletContextListener - Read configuration parameters from "/config/guacamole/guacamole.properties".
06:27:33.696 [main] INFO  o.a.g.rest.auth.HashTokenSessionMap - Sessions will expire after 60 minutes of inactivity.
06:27:34.211 [main] INFO  o.a.g.extension.ExtensionModule - Multiple extensions are installed and will be loaded in order of decreasing priority:
06:27:34.212 [main] INFO  o.a.g.extension.ExtensionModule -  - [postgresql] "PostgreSQL Authentication" (/config/guacamole/extensions/guacamole-auth-jdbc-postgresql-1.5.1.jar)
06:27:34.212 [main] INFO  o.a.g.extension.ExtensionModule -  - [postgresql] "PostgreSQL Authentication" (/config/guacamole/extensions/guacamole-auth-jdbc-postgresql-1.5.2.jar)
06:27:34.212 [main] INFO  o.a.g.extension.ExtensionModule -  - [ldap] "LDAP Authentication" (/config/guacamole/extensions/guacamole-auth-ldap-1.5.2.jar)
06:27:34.212 [main] INFO  o.a.g.extension.ExtensionModule -  - [totp] "TOTP TFA Authentication Backend" (/config/guacamole/extensions/guacamole-auth-totp-1.5.2.jar)
06:27:34.212 [main] INFO  o.a.g.extension.ExtensionModule - To change this order, set the "extension-priority" property or rename the extension files. The default priority of extensions is dictated by the sort order of their filenames.
06:27:35.251 [main] INFO  o.a.g.extension.ExtensionModule - Extension "PostgreSQL Authentication" (postgresql) loaded.
06:27:36.158 [main] INFO  o.a.g.extension.ExtensionModule - Extension "PostgreSQL Authentication" (postgresql) loaded.
06:27:36.376 [main] WARN  o.a.g.e.LanguageResourceService - Overlay language resource "de" does not exist.
06:27:36.378 [main] INFO  o.a.g.extension.ExtensionModule - Extension "LDAP Authentication" (ldap) loaded.
06:27:36.560 [main] INFO  o.a.g.extension.ExtensionModule - Extension "TOTP TFA Authentication Backend" (totp) loaded.
06:27:36.662 [main] INFO  o.a.g.t.w.WebSocketTunnelModule - Loading JSR-356 WebSocket support...
06:27:37.262 [main] WARN  o.g.jersey.server.wadl.WadlFeature - JAXBContext implementation could not be found. WADL feature is disabled.
06:29:23.486 [http-nio-8080-exec-7] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
06:29:25.771 [http-nio-8080-exec-6] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
06:29:28.473 [http-nio-8080-exec-5] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
14:41:58.396 [http-nio-8080-exec-3] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
14:42:00.124 [http-nio-8080-exec-6] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:48:11.899 [http-nio-8080-exec-1] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:48:37.977 [http-nio-8080-exec-3] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:48:45.126 [http-nio-8080-exec-2] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:49:28.021 [http-nio-8080-exec-7] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:50:38.114 [http-nio-8080-exec-7] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:50:43.123 [http-nio-8080-exec-8] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:50:44.490 [http-nio-8080-exec-5] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
Database already configured
Starting postgres...
Starting guacamole guacd...
/var/run/postgresql:5432 - no response
Waiting for postgres to come up...
/var/run/postgresql:5432 - accepting connections
Starting guacamole client...
15:51:40.770 [main] INFO  o.a.g.environment.LocalEnvironment - GUACAMOLE_HOME is "/config/guacamole".
15:51:40.871 [main] INFO  o.a.g.GuacamoleServletContextListener - Read configuration parameters from "/config/guacamole/guacamole.properties".
15:51:40.873 [main] INFO  o.a.g.rest.auth.HashTokenSessionMap - Sessions will expire after 60 minutes of inactivity.
15:51:41.472 [main] INFO  o.a.g.extension.ExtensionModule - Multiple extensions are installed and will be loaded in order of decreasing priority:
15:51:41.472 [main] INFO  o.a.g.extension.ExtensionModule -  - [postgresql] "PostgreSQL Authentication" (/config/guacamole/extensions/guacamole-auth-jdbc-postgresql-1.5.1.jar)
15:51:41.473 [main] INFO  o.a.g.extension.ExtensionModule -  - [postgresql] "PostgreSQL Authentication" (/config/guacamole/extensions/guacamole-auth-jdbc-postgresql-1.5.2.jar)
15:51:41.473 [main] INFO  o.a.g.extension.ExtensionModule -  - [ldap] "LDAP Authentication" (/config/guacamole/extensions/guacamole-auth-ldap-1.5.2.jar)
15:51:41.473 [main] INFO  o.a.g.extension.ExtensionModule -  - [totp] "TOTP TFA Authentication Backend" (/config/guacamole/extensions/guacamole-auth-totp-1.5.2.jar)
15:51:41.473 [main] INFO  o.a.g.extension.ExtensionModule - To change this order, set the "extension-priority" property or rename the extension files. The default priority of extensions is dictated by the sort order of their filenames.
15:51:42.634 [main] INFO  o.a.g.extension.ExtensionModule - Extension "PostgreSQL Authentication" (postgresql) loaded.
15:51:43.484 [main] INFO  o.a.g.extension.ExtensionModule - Extension "PostgreSQL Authentication" (postgresql) loaded.
15:51:43.678 [main] WARN  o.a.g.e.LanguageResourceService - Overlay language resource "de" does not exist.
15:51:43.681 [main] INFO  o.a.g.extension.ExtensionModule - Extension "LDAP Authentication" (ldap) loaded.
15:51:43.909 [main] INFO  o.a.g.extension.ExtensionModule - Extension "TOTP TFA Authentication Backend" (totp) loaded.
15:51:44.031 [main] INFO  o.a.g.t.w.WebSocketTunnelModule - Loading JSR-356 WebSocket support...
15:51:44.623 [main] WARN  o.g.jersey.server.wadl.WadlFeature - JAXBContext implementation could not be found. WADL feature is disabled.
15:51:57.803 [http-nio-8080-exec-9] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:52:29.290 [http-nio-8080-exec-1] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:52:45.694 [http-nio-8080-exec-4] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:52:50.977 [http-nio-8080-exec-2] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:52:56.183 [http-nio-8080-exec-3] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:53:38.197 [http-nio-8080-exec-6] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].
15:54:09.750 [http-nio-8080-exec-7] INFO  o.a.g.r.auth.AuthenticationService - User "admin" successfully authenticated from [x.x.x.x, 172.17.0.1].

[q20]

I just saw that the time zone logged was Etc/UTC. I have now set the -e TZ="Europe/Berlin" variable to match the host and my desktop, thinking it was indeed a symptom of time being out of sync, but the issue remains.

[abesnier]

I was able to reproduce: the issue is conflicting extensions (guacamole-auth-jdbc-postgresql-1.5.1.jar and guacamole-auth-jdbc-postgresql-1.5.2.jar). Come to think of it, maybe it was not required to reset the TOTP secret (I'll have to try some more)...

Remove the 1.5.1 one, restart the container, and you should be good.

TODO:

• make sure extensions are properly cleaned between guacamole updates.
• update README.MD and/or UPDATE.MD to add the TOTP reset method.

[q20]

Hey, champ! That worked. Thanks a million for your expert support. 👍