Re: [exim] Blacklisting IP Ranges Using MySQL

Kezdőlap
Üzenet törlése
Válasz az üzenetre
Szerző: Mike Cardwell
Dátum:  
Címzett: exim-users
Tárgy: Re: [exim] Blacklisting IP Ranges Using MySQL
On 07/10/2010 20:57, Brian Spraker wrote:

> Is there a way to blacklist IP ranges using MySQL?
>
> I originally setup one file with all of the ranges in them such as:
>
> 41.0.0.0/8
> 58.0.0/8
>
> Each IP range on a different line in the "hosts_blacklist" file that was in the
> same directory as the Exim configuration file.
>
> This has worked well for a few years without any kind of problem.
>
> However, I am wanting to move this to a MySQL table. I have moved the domain
> blacklist/whitelist, sender blacklist/whitelist, and sender domains
> blacklist/whitelist to MySQL and those seem to be working OK.
>
> However, the hosts_blacklist does not work now. I'm not too sure why it
> wouldn't work; it is just a different method for storing the data.
>
> The reason I'm moving to database storage is because I am making a series of
> sites that I can use to quickly update the lists instead of logging into the
> server and updating the file. In addition, because I have two servers that are
> redundant, I have to update the file on each server. The MySQL database is
> setup with master-master replication so if I make the change on one server, it
> updates on the other at the same time - much more convenient.
>
> This is what works when using the flat file:
>
> hosts = ${if exists{CONFDIR/host_blacklist}{CONFDIR/host_blacklist}{}}
>
> Now I have changed it to this:
>
> hosts = ${lookup mysql{SELECT host FROM exim_host_blacklist }}
>
> No longer works now.


Rather than storing 41.0.0.0/8 in mysql, store two "ip numbers" covering
the start and end of the range. Notice I said "ip numbers" and not "ip
addresses" Eg, for 41.0.0.0/8:

Figure out the start and end ip addresses:

mike@snail:~$ netmask -r 41.0.0.0/8
       41.0.0.0-41.255.255.255  (16777216)
mike@snail:~$


So 41.0.0.0 and 41.255.255.255

Now convert those to ip numbers:

mysql> SELECT INET_ATON('41.0.0.0');
+-----------------------+
| INET_ATON('41.0.0.0') |

+-----------------------+
|             687865856 |

+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT INET_ATON('41.255.255.255');
+-----------------------------+
| INET_ATON('41.255.255.255') |

+-----------------------------+
|                   704643071 |

+-----------------------------+
1 row in set (0.00 sec)

mysql>

So I'd have two int columns in the mysql db called start_ip and end_ip,
and then I'd do my mysql select like this:

hosts = ${lookup mysql{SELECT '${quote_mysql:$sender_host_address}' FROM
table WHERE INET_ATON('${quote_mysql:$sender_host_address}')>=start_ip
AND INET_ATON('${quote_mysql:$sender_host_address}')<=end_ip LIMIT 1}}

You can convert ip numbers back to ip addresses in MySQL using INET_NTOA:

mysql> SELECT INET_NTOA(687865856);
+----------------------+
| INET_NTOA(687865856) |

+----------------------+
| 41.0.0.0             |

+----------------------+
1 row in set (0.00 sec)

mysql>

If you're using IPv6, you're on your own ;)

-- 
Mike Cardwell - Perl/Java/Web developer, Linux admin, Email admin
Read my tech Blog -              https://secure.grepular.com/
Follow me on Twitter -           http://twitter.com/mickeyc
Hire me - http://cardwellit.com/ http://uk.linkedin.com/in/mikecardwell