Messages from the list in the moderation queue are spam

[Curtis Hovey]

The openjdk list has a more than a year's backlog of held messages that they could not moderate because of timeouts. The new batching feature does allow them to moderate message, but it is clear that most are spam, and that most have an impossible email address that mailman could have used to automatically discard the message.

Mailman accepts messages claiming to be from the list. Launchpad monkey-patch rules place non-subscriber emails with Lp registered address in the moderation queue. However, the list email address is not a user, and it is controled by Launchpad--no user should ever use this address to send a message to the list. Thus any message that enters lpmoderation.hold() that has the same email address as the list (eg mlist.address in msg['From'] ) should raise Errors.DiscardMessage.

There 31249 messages in stagings MessageApproval table claim to be from the list; or 15972 that are not. 2/3 of all messages in moderations queues are spam that could be avoided with a simple sanity check in the code

This is the SQL that can be used it investigate
SELECT count(MessageApproval.id)
FROM
    Person Team
    JOIN MailingList ON Team.id = MailingList.team
    JOIN MessageApproval ON MailingList.id = MessageApproval.mailing_list
WHERE
    MessageApproval.posted_by = Team.id

I think we can write a SQL script to force these messages into the DISCARD_PENDING status so that users do not need to clear the backlog.

[Curtis Hovey (sinzui)]

I set this as high because the amount of work required to manage moderation like openjdk is an indirect cause of timeouts.

[Barry Warsaw (barry)]

Seems reasonable. I think the test though should probably be

mlist.address == msg['from'].lower()

[Curtis Hovey (sinzui)]

I think this will work as a cleanup script.
-- Discard list messages that claim to have been sent from the list.
-- status = DISCARD_PENDING = 60
-- disposed_by = janitor = 931129
UPDATE
    MessageApproval
SET
    status = 60,
    disposed_by = 931129,
    disposal_date = CURRENT_TIMESTAMP
FROM
    Person Team
    JOIN MailingList ON Team.id = MailingList.team
WHERE
    MailingList.id = MessageApproval.mailing_list
    AND MessageApproval.posted_by = Team.id
    AND MessageApproval.status = 0
;

[Launchpad QA Bot (lpqabot)]

Fixed in stable r11584 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11584>.

[Curtis Hovey (sinzui)]

updates to lists.staging.launchpad.net have failed because staging server (marambio) was missed in the upgrade to Lucid.