How to Reset U2F for SOGo-User in Mailcow

Currently we are taking a closer look at Mailcow and we might even switch our mail server. During testing we found one particular case surprisingly hard to solve: what if a user locks herself out of the SOGo Web-UI by loosing her second factor (2FA)?

The second factor is a time-based one-time password (OTP) provided by the Google Authenticator or a similar App on your phone. It is a 6 digit code changing every 30 seconds. Without this code on your phone nobody can log in even if the password got stolen. This is good news… until your phones breaks.

This was our test scenario: how can an administrator disable the 2F without logging in as the user in trouble? This was surprisingly difficult.

No Button in the Admin UI

We did not find any button or whatsoever to disable the 2FA in the Mailcow administration UI. Maybe it is there or will be in future. Currently we did not find it.

No effect executing sogo-tool

After some internet research we came across the following solution.

# enter the SOGo container
cd /opt/mailcow-dockerized/
docker-compose exec sogo-mailcow /bin/bash
# disable 2FA for a given user
# (this is one command spreading several lines)
sogo-tool user-preferences \  set defaults \  needs.help@sandstorm.de \  SOGoGoogleAuthenticatorEnabled \  '{"SOGoGoogleAuthenticatorEnabled":0}'

In our case however the command had no effect. The value of SOGoGoogleAuthenticatorEnabled did not change in the database and the user was unable to log in. We did not investigate but started editing the database directly.

Direct update of the database

As last resort we disabled 2FA directly in the database. Note that the structure of the database might change in future. Please double-check before copy-pasting the following commands.

# enter mysql-container
cd /opt/mailcow-dockerized/
docker-compose exec mysql-mailcow /bin/bash
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD
-- select database
USE mailcow;
-- look at current settings
SELECT *
  FROM sogo_user_profile
  WHERE c_uid = "needs.help@sandstorm.de";
-- disable 2FA
UPDATE sogo_user_profile  SET c_defaults = REPLACE(    c_defaults,    "\"SOGoGoogleAuthenticatorEnabled\": 1",    "\"SOGoGoogleAuthenticatorEnabled\": 0")  WHERE c_uid = "needs.help@sandstorm.de";-- bye
QUIT;
# leave mysql-container
exit
# restart SOGo
# (also possible in Mailcow UI)
docker-compose restart sogo-mailcow

After some time (maybe due to some caching) the user was able to login without the 6-digit OTP. In her settings 2FA is disabled.

I hope you find this blog post helpful. If you have any comments, suggestions or question, feel free to contact us.