There are major account take-over problems happening right now on mastodon instances – all appearances are that the actor is using stolen passwords, probably from malware running on desktops. I've suspended hundreds of compromised accounts over the past 48 hours, but only two on mine.

If you run a mastodon instance, here is a postgres query I use to randomize the passwords of accounts that have been inactive for 6 months or more. It also removes logged in sessions. This doesn't lock people out, just makes them perform a password reset. Yes, I wish I didn't have to do this, but here we are. I hope it helps someone else.

BEGIN;

CREATE TEMP TABLE dormant_batch ON COMMIT DROP AS
SELECT u.id AS user_id
FROM users u
WHERE COALESCE(u.current_sign_in_at, u.created_at) < now() – interval '6 months'
AND NOT u.otp_required_for_login
AND NOT EXISTS (SELECT 1 FROM webauthn_credentials w WHERE w.user_id = u.id)
AND NOT EXISTS (SELECT 1 FROM oauth_access_tokens t
WHERE t.resource_owner_id = u.id
AND t.revoked_at IS NULL
AND t.last_used_at > now() – interval '6 months');
UPDATE users u
SET encrypted_password = '$2a$10$' || left(replace(gen_random_uuid()::text || gen_random_uuid()::text, '-', ''), 53),
updated_at = now()
FROM dormant_batch b
WHERE u.id = b.user_id;

UPDATE oauth_access_tokens t SET revoked_at = now()
FROM dormant_batch b
WHERE t.resource_owner_id = b.user_id AND t.revoked_at IS NULL;

DELETE FROM session_activations s
USING dormant_batch b
WHERE s.user_id = b.user_id;

SELECT COALESCE(y::text, 'total') AS last_seen_year, count(*) AS locked
FROM (SELECT date_part('year', COALESCE(u.current_sign_in_at, u.created_at))::int AS y
FROM dormant_batch b JOIN users u ON u.id = b.user_id) s
GROUP BY ROLLUP (y) ORDER BY y NULLS LAST;

COMMIT;

#mastoadmin #fediadmin