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:
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';
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:
And checking the local_user table again, both TOTP fields should be empty:
Aww no gifs in jerboa
deleted by creator
If you wanted to do it in one query I think you could do something like
UPDATE local_user AS u SET u.totp_2fa_url = null, u.totp_2fa_secret = null FROM person AS p WHERE p.id = u.person_id AND p.local AND p.name = 'guineapig';I assume the
p.localis optional, too, because the id match against thelocal_usertable will presumably limit it to only local users. 🤷Thanks, good to know.
