Aug 5, 2024: iRedMail-1.7.1 has been released.

**edgeroute** · 2016-01-10 00:32:15

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.

**ZhangHuangbin** · 2016-01-10 18:21:39

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,165

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.

**ZhangHuangbin** · 2016-01-10 23:21:57

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,165

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.

**cegner** · 2016-01-11 03:52:30

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'

**ZhangHuangbin** · 2016-01-11 07:59:32

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,165

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.

**edgeroute** · 2016-01-11 08:30:24

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.

**ZhangHuangbin** · 2016-01-11 08:34:45

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,165

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?

**ZhangHuangbin** · 2016-01-11 09:45:26

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,165

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.

**cegner** · 2016-01-11 15:10:42

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 "\'"

?

**ZhangHuangbin** · 2016-01-11 16:56:22

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,165

Re: Greylisting from major mail providers is BROKEN

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

**mir** · 2016-01-11 17:00:33

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.

**ZhangHuangbin** · 2016-01-11 17:37:47

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,165

Re: Greylisting from major mail providers is BROKEN

Fixed. i didn't realize the whole importing process will be interrupt due to duplicate record
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

**cegner** · 2016-01-11 20:40:36

cegner
Member
Offline

Registered: 2015-04-17
Posts: 10

Re: Greylisting from major mail providers is BROKEN

Thanks, works great!

**ZhangHuangbin** · 2016-01-11 21:22:49

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,165

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.

**ZhangHuangbin** · 2016-01-14 08:45:02

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,165

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/

**tyllee** · 2016-01-14 18:13:20

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?

**ZhangHuangbin** · 2016-01-16 23:20:29

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,165

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

**tyllee** · 2016-01-17 01:59:59

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?

**ZhangHuangbin** · 2016-01-18 21:28:48

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,165

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.

Aug 5, 2024: iRedMail-1.7.1 has been released.

Greylisting from major mail providers is BROKEN

Posts: 19

1 Topic by edgeroute 2016-01-10 00:32:15

Topic: Greylisting from major mail providers is BROKEN

2 Reply by ZhangHuangbin 2016-01-10 18:21:39

Re: Greylisting from major mail providers is BROKEN

3 Reply by ZhangHuangbin 2016-01-10 23:21:57

Re: Greylisting from major mail providers is BROKEN

4 Reply by cegner 2016-01-11 03:52:30

Re: Greylisting from major mail providers is BROKEN

5 Reply by ZhangHuangbin 2016-01-11 07:59:32

Re: Greylisting from major mail providers is BROKEN

6 Reply by edgeroute 2016-01-11 08:30:24

Re: Greylisting from major mail providers is BROKEN

7 Reply by ZhangHuangbin 2016-01-11 08:34:45

Re: Greylisting from major mail providers is BROKEN

8 Reply by ZhangHuangbin 2016-01-11 09:45:26

Re: Greylisting from major mail providers is BROKEN

9 Reply by cegner 2016-01-11 15:10:42

Re: Greylisting from major mail providers is BROKEN

10 Reply by ZhangHuangbin 2016-01-11 16:56:22

Re: Greylisting from major mail providers is BROKEN

11 Reply by mir 2016-01-11 17:00:33

Re: Greylisting from major mail providers is BROKEN

12 Reply by ZhangHuangbin 2016-01-11 17:37:47

Re: Greylisting from major mail providers is BROKEN

13 Reply by cegner 2016-01-11 20:40:36

Re: Greylisting from major mail providers is BROKEN

14 Reply by ZhangHuangbin 2016-01-11 21:22:49

Re: Greylisting from major mail providers is BROKEN

15 Reply by ZhangHuangbin 2016-01-14 08:45:02

Re: Greylisting from major mail providers is BROKEN

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

Re: Greylisting from major mail providers is BROKEN

17 Reply by ZhangHuangbin 2016-01-16 23:20:29

Re: Greylisting from major mail providers is BROKEN

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

Re: Greylisting from major mail providers is BROKEN

19 Reply by ZhangHuangbin 2016-01-18 21:28:48

Re: Greylisting from major mail providers is BROKEN

Posts: 19