When you have a shared IP for multiple domains, it’s important to avoid the blacklisting of the IP for spamming.
Outgoing Spam can be controlled with Plesk 12 by enabling Outgoing Mail Limits but an important point is about mail addresses with forwarding to an external mailbox, especially when you have SRS (will be explained in a future post) activated on your server as SRS will rewrite the Return-Path to your own domain and if Spam is forwarded it’s the same thing as if you really send this Spam from your server.
You may have many mailboxes on the server and setting the spam filter could be a hard work to do it manually, here come the Plesk CLI and SQL queries to the PSA database.
This query list mail addresses that have a forward option enabled to an external mailbox (not an internal domain) and where the spam filter is not enabled. Another condition is to filter it this address has or not a mailbox. If there is a mailbox the Spam filter action should be “Move to the spam folder” if not the Spam should be deleted. This first query extract the mail addresses without mailbox and show the CLI command to enable Spam filtering with the specific action.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT DISTINCT CONCAT(mail.mail_name, '@', domains.name) AS mailbox, mail.postbox, mail.spamfilter, CONCAT('/opt/psa/bin/spamassassin -u ', mail.mail_name, '@', domains.name, ' -status true -action del') AS command FROM mail LEFT JOIN mail_redir ON mail_redir.mn_id = mail.id JOIN domains ON domains.id = mail.dom_id WHERE SUBSTRING_INDEX(mail_redir.address, '@', -1) NOT IN (SELECT name FROM domains) AND spamfilter = 'false' AND postbox = 'false' ORDER BY mail.postbox ASC, domains.name ASC |
Result is
| mailbox | postbox | spamfilter | command |
|---|---|---|---|
| test@domain.com | false | false | /opt/psa/bin/spamassassin -u test@domain.com -status true -action del |
And you just have to past this to a elevated privilege shell on your Plesk server to enable Spam filter to this mail address
Now here is the same SQL query but with the action of moving the Spam to the Spam folder for mail addresses that have a mailbox.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT DISTINCT CONCAT(mail.mail_name, '@', domains.name) AS mailbox, mail.postbox, mail.spamfilter, CONCAT('/opt/psa/bin/spamassassin -u ', mail.mail_name, '@', domains.name, ' -status true -action move') AS command FROM mail LEFT JOIN mail_redir ON mail_redir.mn_id = mail.id JOIN domains ON domains.id = mail.dom_id WHERE SUBSTRING_INDEX(mail_redir.address, '@', -1) NOT IN (SELECT name FROM domains) AND spamfilter = 'false' AND postbox = 'true' ORDER BY mail.postbox ASC, domains.name ASC |
If you let your users change their Spam filter settings, you may have to check multiples time if they didn’t deactivate it.
There is a third action to the Spam filter that add a prefix on the mail subject. For forwarded addresses to external this action should be removed as there is still Spam outgoing from your mail server. Here is the query that identify mail addresses with a forward and the action to tag the mail subject. The command change the action to “Delete”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT DISTINCT CONCAT(mail.mail_name, '@', domains.name) AS mailbox, mail.postbox, mail.spamfilter, spamfilter_preferences.value, CONCAT('/opt/psa/bin/spamassassin -u ', mail.mail_name, '@', domains.name, ' -status true -action del') as command FROM mail LEFT JOIN mail_redir ON mail_redir.mn_id = mail.id JOIN domains ON domains.id = mail.dom_id LEFT JOIN spamfilter ON spamfilter.username = CONCAT(mail.mail_name, '@', domains.name) LEFT JOIN spamfilter_preferences ON spamfilter_preferences.spamfilter_id = spamfilter.id WHERE SUBSTRING_INDEX(mail_redir.address, '@', -1) NOT IN (SELECT name FROM domains) AND spamfilter_preferences.preference = 'action' AND (spamfilter_preferences.value = 'text' AND mail.postbox = 'false') ORDER BY mail.postbox ASC, domains.name ASC |
And that’s all for this post !