Tip: finding and cleaning bad user email addresses so Redmine notifications stop bouncing

Hi all,

On larger Redmine instances with self-registration enabled, the user table slowly fills up with addresses that no longer work: people who left the company, typos made at sign-up ("gmial.com", "outlok.com"), and throwaway addresses. Every issue update then sends notifications to dead mailboxes. The bounces come back to your SMTP account, and if there are enough of them your provider may start throttling or flagging your sending domain. Then the notifications that matter land in spam too.

Here is the routine I use to keep this under control.

1. Export the addresses

Since Redmine 4.0 the addresses are stored in the email_addresses table (one user can have several). Pull the active users from a Rails console:

# RAILS_ENV=production bin/rails console
require 'csv'
CSV.open('/tmp/redmine_emails.csv', 'w') do |csv|
  csv << %w[user_id login address is_default notify]
  EmailAddress.joins(:user).where(users: { status: User::STATUS_ACTIVE }).find_each do |e|
    csv << [e.user_id, e.user.login, e.address, e.is_default, e.notify]
  end
end

2. Check them

Syntax checks alone won't catch most of the problems, because john@oldcompany.com is perfectly valid syntax for a mailbox that was deleted years ago. You need an MX lookup plus an SMTP-level mailbox check. You can script this yourself, but greylisting, catch-all domains and rate limits make it fiddly. I upload the CSV to an email verification API and get each address back as valid, invalid, catch-all or disposable. The free tier gives 200 checks a month, which is enough for many smaller instances.

3. Act on the results

Invalid: lock the user, or turn off their notifications so you keep the history:

bad = File.readlines('/tmp/invalid.txt', chomp: true)
User.joins(:email_addresses).where(email_addresses: { address: bad }).find_each do |u|
  u.update_column(:mail_notification, 'none')   # or: u.lock!
end

Disposable: if you allow self-registration, consider switching Administration > Settings > Authentication > Self-registration to "manual account activation", so an admin approves new accounts.
Catch-all: keep these, but watch for bounces.

4. Watch bounces going forward

Set a real Return-Path/bounce mailbox in configuration.yml (not a black-hole noreply address) and check it every month. A couple of hard bounces from the same user usually means the address is dead.

Does anyone handle this with a plugin instead of console scripts? I'd be curious what others use.

Replies (1)

RE: Tip: finding and cleaning bad user email addresses so Redmine notifications stop bouncing - Added by Peter Braun 5 days ago

A small addition to the steps above: an MX lookup is the cheapest check and catches most typos in the domain part (gmial.com, outlok.com and similar), so it is worth running before anything else, for example straight from the Rails console, before the export in step 1:

# RAILS_ENV=production bin/rails console
require 'resolv'
cache = {}
Resolv::DNS.open do |dns|
  EmailAddress.joins(:user).where(users: { status: User::STATUS_ACTIVE }).find_each do |e|
    domain = e.address.to_s.split('@').last.to_s.downcase
    cache[domain] = dns.getresources(domain, Resolv::DNS::Resource::IN::MX).any? unless cache.key?(domain)
    puts "#{e.user.login}\t#{e.address}" unless cache[domain]
  end
end

Two caveats: a domain without an MX record can still accept mail via its A record (rare, but it happens), so I would review the list rather than lock accounts automatically. And MX only tells you the domain exists, not that the mailbox does, so addresses of people who left a company will still bounce. For those the bounce messages Redmine already receives are the best source.

Disclosure: I work for SendBridge (the service linked in my first post).

Peter Braun (SendBridge)

(1-1/1)

Project

General

Profile

Redmine