1 Topic by edgeroute

  • edgeroute
  • Member
  • Offline
  • Registered: 2016-01-10
  • Posts: 2

Topic: Greylisting from major mail providers is BROKEN

==== Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.3
- Linux/BSD distribution name and version: Ubuntu LTS 14.04
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro? Nope
- Related log if you're reporting an issue:
====

Greylisting with iredapd and the greylisting plugin is busted for mail from major mail hosting providers. The whitelist in the MySql Database (greylisting_whitelist) is grossly out of date.

Any way to update it efficiently? I do not want to disable greylisting, but I'm going to be forced to disable or move to postgrey. Postgrey has a nice whitelist for major mail providers based on dns regex.

This is the log I get:

postfix/smtpd[4415]: NOQUEUE: reject: RCPT from mail-by2on0056.outbound.protection.outlook.com[207.46.100.56]: 451 4.7.1

Each time the mail is resent, it comes from a different IP, so the greylisting has been going on for many hours / days.

Please assist.

Chris

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: Greylisting from major mail providers is BROKEN

You may want to try one of these tools:
https://github.com/mohshami/whitelister
https://github.com/0x9900/whitelist
https://github.com/stevejenkins/postwhite/

I will try to improve it in the future.

3 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: Greylisting from major mail providers is BROKEN

OK, forget about all tools mentioned in my previous post, i wrote a shell script to do this, it's much cleaner and easier to use. You can download it from iRedAPD repository:
https://bitbucket.org/zhb/iredapd/raw/d … telists.sh

How to use it:

#
# USAGE
#
#   Run command with the mail domain names which you want to avoid gryelisting:
#
#       $ bash spf_to_greylist_whitelists.sh <domain> [domain ...]
#
#   For example:
#
#       $ bash spf_to_greylist_whitelists.sh google.com aol.com hotmail.com
#
#   it will create file:
#
#       /tmp/iredapd-greylisting-whitelist-auto-update.sql
#
#   You'd better review it before importing it to iRedAPD SQL database.

Notes: with current version,

*) it doesn't support 'ptr' and 'ptr:<domain>' syntax (yet).
*) new sql records will have comment like this: 'AUTO-UPDATE: <domain>'.
*) if the dns query returns new IP addresses, it will delete old records.

4 Reply by cegner

  • cegner
  • Member
  • Offline
  • Registered: 2015-04-17
  • Posts: 10

Re: Greylisting from major mail providers is BROKEN

Thanks for the script. I noticed that it can't handle already existing entries:

ERROR 1062 (23000) at line 2: Duplicate entry '@.-66.102.0.0/20' for key 'account'

5 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: Greylisting from major mail providers is BROKEN

cegner wrote:

ERROR 1062 (23000) at line 2: Duplicate entry '@.-66.102.0.0/20' for key 'account'

This error is safe to ignore. It's caused by duplicate entry.

6 Reply by edgeroute

  • edgeroute
  • Member
  • Offline
  • Registered: 2016-01-10
  • Posts: 2

Re: Greylisting from major mail providers is BROKEN

Thank you for turning around a solution so quickly!

-----

ZhangHuangbin wrote:

OK, forget about all tools mentioned in my previous post, i wrote a shell script to do this, it's much cleaner and easier to use. You can download it from iRedAPD repository:
https://bitbucket.org/zhb/iredapd/raw/d … telists.sh

How to use it:

#
# USAGE
#
#   Run command with the mail domain names which you want to avoid gryelisting:
#
#       $ bash spf_to_greylist_whitelists.sh <domain> [domain ...]
#
#   For example:
#
#       $ bash spf_to_greylist_whitelists.sh google.com aol.com hotmail.com
#
#   it will create file:
#
#       /tmp/iredapd-greylisting-whitelist-auto-update.sql
#
#   You'd better review it before importing it to iRedAPD SQL database.

Notes: with current version,

*) it doesn't support 'ptr' and 'ptr:<domain>' syntax (yet).
*) new sql records will have comment like this: 'AUTO-UPDATE: <domain>'.
*) if the dns query returns new IP addresses, it will delete old records.

7 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: Greylisting from major mail providers is BROKEN

edgeroute wrote:

Thank you for turning around a solution so quickly!

Let me know whether or not it works great for you. I will keep improving it in the future.

How about importing generated SQL file directly?

8 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: Greylisting from major mail providers is BROKEN

UPDATE:

Improved tools/spf_to_greylist_whitelists.sh a little bit: it's now able to import generated SQL file directly with argument '--import'. It will read iRedAPD config file (defaults to /opt/iredapd/settings.py) to extract SQL credential and import generated SQL file.

9 Reply by cegner

  • cegner
  • Member
  • Offline
  • Registered: 2015-04-17
  • Posts: 10

Re: Greylisting from major mail providers is BROKEN

Great idea.. but it seems to me that import exists on first error. Following records don't get processed:

* Importing SQL file ...
ERROR 1062 (23000) at line 2 in file: '/tmp/iredapd-greylisting-whitelists-auto-update.sql': Duplicate entry '@.-64.18.0.0/20' for key 'account'
* Importing failed.

Also I noticed that strip_quotes doesn't seem to work:

* Importing SQL file ...
ERROR 2005 (HY000): Unknown MySQL server host ''127.0.0.1'' (0)
* Importing failed.

What about 

tr -d '"' | tr -d "\'"

?

10 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: Greylisting from major mail providers is BROKEN

Fixed, you can try the latest version:
https://bitbucket.org/zhb/iredapd/raw/d … telists.sh

11 Reply by mir

  • mir
  • Member
  • Offline
  • Registered: 2014-05-30
  • Posts: 170

Re: Greylisting from major mail providers is BROKEN

cegner wrote:

Great idea.. but it seems to me that import exists on first error. Following records don't get processed:

* Importing SQL file ...
ERROR 1062 (23000) at line 2 in file: '/tmp/iredapd-greylisting-whitelists-auto-update.sql': Duplicate entry '@.-64.18.0.0/20' for key 'account'
* Importing failed.

Maybe a rewrite in Python is in order since inserts can be encapsulated in try..except to simply ignore the duplicate entry error. This is hard to do in bash.

12 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: Greylisting from major mail providers is BROKEN

Fixed. i didn't realize the whole importing process will be interrupt due to duplicate record sad
Download the latest version here:
https://bitbucket.org/zhb/iredapd/raw/d … telists.sh

Or check commit log:
https://bitbucket.org/zhb/iredapd/histo … at=default

13 Reply by cegner

  • cegner
  • Member
  • Offline
  • Registered: 2015-04-17
  • Posts: 10

Re: Greylisting from major mail providers is BROKEN

Thanks, works great!

14 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: Greylisting from major mail providers is BROKEN

*) I'm rewriting this script in Python programming language, it will be easier to handle SQL related operations.
*) Upcoming iRedAPD release will have a new SQL table "greylisting_whitelist_domains", you can insert all mail domain names you want to disable greylisting for into this table, and the new Python script will read domain names from this table. Of course we will offer web ui in iRedAdmin-Pro to allow you to manage them.

15 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: Greylisting from major mail providers is BROKEN

Dear all,

The script written in Python has been completed, you can try it now:
https://bitbucket.org/zhb/iredapd/src/default/tools/

16 Reply by tyllee (edited by tyllee 2016-01-14 18:19:53)

  • tyllee
  • Member
  • Offline
  • Registered: 2013-03-27
  • Posts: 98

Re: Greylisting from major mail providers is BROKEN

I need to create the table? Do you have a script for this?


_mysql_exceptions.ProgrammingError: (1146, "Table 'iredapd.greylisting_whitelist_domains' doesn't exist")

Or should I upgrade to iRedAPD-1.8.0?

17 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: Greylisting from major mail providers is BROKEN

If you run this script without any argument, it will try to get mail domain names from SQL table "iredapd.greylisting_whitelist_domains". This table will be introduced in upcoming iRedAPD release (v1.8.0).

It's better to run this script with some ISP mail domain names BEFORE iRedAPD-1.8.0. For example:

cd /opt/iredapd/tools/
python spf_to_greylist_whitelists.py outlook.com microsoft.com

18 Reply by tyllee (edited by tyllee 2016-01-17 02:00:18)

  • tyllee
  • Member
  • Offline
  • Registered: 2013-03-27
  • Posts: 98

Re: Greylisting from major mail providers is BROKEN

ZhangHuangbin wrote:

It's better to run this script with some ISP mail domain names BEFORE iRedAPD-1.8.0. For example:

cd /opt/iredapd/tools/
python spf_to_greylist_whitelists.py outlook.com microsoft.com

Is this saved to any file/database or do I need to do anything with the output?
Do I have to re run the script after a reboot?

19 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: Greylisting from major mail providers is BROKEN

*) Script `spf_to_greylist_whitelists.py` will store result in SQL table "iredapd.greylisting_whitelists".
*) upcoming iRedMail release will run it hourly.