Noting this here for my own reference, and any other lemmy server admins that don't happen to be database administrators by day 🙂
I am not a DBA, if I'm doing something bad/incorrect here... please post! Yes, I've reset the password and TOTP token on the example account below.
2FA flags are stored in the local_user
table, however that does not show usernames. To find the person_id
for the user account you want to disable 2FA for, you'll need to check the person
table. I'll use my test account here as an example:
SELECT * from person where name = 'guineapig' and local = 't';
Giving:
*removed externally hosted image*
Note the number 781227, this is the person_id
for this account on my instance. To confirm:
SELECT * from local_user where person_id = '781227';
*removed externally hosted image*
Yep, the 2FA string has the expected username in it. Now to disable 2FA on the account we need to NULL out both totp_2fa_url
and totp_2fa_secret
rows:
UPDATE local_user
SET totp_2fa_url = NULL
WHERE person_id = 781227;
UPDATE local_user
SET totp_2fa_secret = NULL
WHERE person_id = 781227;
Should give output like this:
*removed externally hosted image*
And checking the local_user
table again, both TOTP fields should be empty:
*removed externally hosted image*