Two new MySQL queries for Plesk:

1. See email addresses and their SpamAssassin spam score:

# mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` -e ’select concat(mail.mail_name,”@”,domains.name) as address,value from mail,domains,spamfilter_preferences where mail.dom_id=domains.id and mail.dom_id=spamfilter_preferences.spamfilter_id and spamfilter_preferences.preference=”required_score” order by domains.name;’

2. Reset all Plesk email passwords to a random string, output new password list to a file.

# for i in $(mysql -NB psa -uadmin -p`cat /etc/psa/.psa.shadow` -e ’select concat(mail.mail_name,”@”,domains.name) as address from mail,domains,accounts where mail.dom_id=domains.id and mail.account_id=accounts.id order by domains.name;’); do export PSA_PASSWD=”$(openssl rand 6 -base64)”; /usr/local/psa/bin/mail -u $i -passwd $PSA_PASSWD; echo “$i: $PSA_PASSWD” >> mail_passwords; done

The second one is a “nuclear option” for Plesk email account compromises, or if you just think it’s time to get a little more serious about security.