Disabiling e-mail Forwarding

[Eric Pretorious]

Like most hosters - I'm sure - many of our clients create e-mail accounts that forward incoming e-mail to mailboxes hosted on other e-mail services. e.g., Gmail.

Lately, though, a handful of these accounts have been receiving a lot of spam (i.e., UCE) and that's been causing a lot of damage to our reputation with these other e-mail services because the message that's being forwarded appears to have originated from our servers.

<someuser@gmail.com>: host gmail-smtp-in.l.google.com[64.233.183.27]
    said: 552-5.7.0 This message was blocked because its content presents a
    potential 552-5.7.0 security issue. Please visit 552-5.7.0
    http://support.google.com/mail/bin/answer.py?answer=6590 to review our 552
    5.7.0 message content and attachment content guidelines.
    g20si28780491ici.46 - gsmtp (in reply to end of DATA command)

Is there some way to...

1. find accounts that are configured to forward to gmail?
2. disable forwarding in accounts that are configured to forward to gmail?

 

[Eric Pretorious]

It's been four days without a single response!

Bumping this thread.

 

[Eric Pretorious]

After reading Shall's code samples in the forum post 'Extract "all valid emails"?' I've been able to...

• Identify virtual mailboxes that have redirects configured:
  

  mysql> SELECT mail.id, CONCAT(mail.mail_name,'@',domains.name) FROM mail LEFT JOIN domains ON domains.id = mail.dom_id WHERE mail.redirect = true;

• Identify e-mail redirects addresses:
  

  mysql> SELECT mail_redir.mn_id,mail_redir.address FROM mail_redir ;

...but I can't quite figure out how to join the three tables: mail, domains, and mail_redir to correlate e-mail redirects with virtual mailboxes.

Is that even possible in SQL?

TIA,

 

[Eric Pretorious]

After reading Shall's code samples in the forum post 'Extract "all valid emails"?' I've been able to...

• Identify virtual mailboxes that have redirects configured:
  

  mysql> SELECT mail.id, CONCAT(mail.mail_name,'@',domains.name) FROM mail LEFT JOIN domains ON domains.id = mail.dom_id WHERE mail.redirect = true;

• Identify e-mail redirects addresses:
  

  mysql> SELECT mail_redir.mn_id,mail_redir.address FROM mail_redir ;

...but I can't quite figure out how to join the three tables: mail, domains, and mail_redir to correlate e-mail redirects with virtual mailboxes.

Is that even possible in SQL?

I was able to leverage more of Shall's code to find the answer:

mysql> SELECT CONCAT(mail.mail_name,'@',domains.name) AS 'virtual mailbox', mail_redir.address AS 'redirects to' FROM mail_redir,mail,domains WHERE mail_redir.mn_id = mail.id AND mail.dom_id = domains.id AND mail.redirect = true ;
+---------------------------------+-----------------------------------+
| virtual mailbox                 | redirects to                      |
+---------------------------------+-----------------------------------+
| eric@example.com                | eric@gmail.com                    |
| justine@example.net             | justine@gmail.com                 |
+---------------------------------+-----------------------------------+
2 rows in set (0.00 sec)

 

[Eric Pretorious]

I was able to leverage more of Shall's code to find the answer:

mysql> SELECT CONCAT(mail.mail_name,'@',domains.name) AS 'virtual mailbox', mail_redir.address AS 'redirects to' FROM mail_redir,mail,domains WHERE mail_redir.mn_id = mail.id AND mail.dom_id = domains.id AND mail.redirect = true ;
+---------------------------------+-----------------------------------+
| virtual mailbox                 | redirects to                      |
+---------------------------------+-----------------------------------+
| eric@example.com                | eric@gmail.com                    |
| justine@example.net             | justine@gmail.com                 |
+---------------------------------+-----------------------------------+
2 rows in set (0.00 sec)

FYI: I discovered that the last condition (i.e., "AND mail.redirect = true ;") didn't do anything for paring down the results so I modified the search term by enclosing the word true in quotes and got very different results:

mysql> SELECT mail.mail_name FROM mail WHERE mail.redirect =  'true';
Empty set (0.00 sec)

mysql> SELECT CONCAT(mail.mail_name,'@',domains.name) AS 'virtual mailbox' , mail.redirect AS 'enabled?' FROM mail_redir,mail,domains WHERE mail.redirect = 'true' ;
Empty set (0.00 sec)