mail:postfix:cidr_blacklists_in

Postfix knows a very nice feature called cidr-maps. This helps to block larger ip-ranges in a very comfortable way. You can read more about it in the postfix manual

You might want to read this as well. I found a neat way to add a timestamp which updates on every sucessful SELECT

For example:

192.168.205.0/27 reject

which then could be integrated into postfix config like the following

smtpd_client_restrictions = ... cidr:/etc/postfix/blacklist ...

note: cidr-maps are no hash maps, therefore postmap command is NOT needed

Now it's not so an easy job to „bring“ cidr-maps into a MySql database. That's because IP/networks in the form of 192.168.205.0/27 would be saved as strings in the tables. And that would be quite a bad idea in the view of performance. Database operations (e.g. search) are not really fast on TEXT or VARCHAR datatypes. Especially if used on a higher frequented mailserver, string searches do everything but scale well

Luckily every ip address can also be converted into a INT datatype. Databases work very fast on non-string (e.g. numerical) values. Furthermore it's possible to add primary keys and/or unique keys on numeric columns. UNIQUE helps avoiding double entries in the table

First of all a suitable MySql table has to be created

CREATE TABLE `blacklist` (
  `network` int(10) unsigned NOT NULL,
  `broadcast` int(10) unsigned NOT NULL,
  UNIQUE KEY `network` (`network`),
  UNIQUE KEY `broadcast` (`broadcast`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

very important: it is a must to set the columns to unsigned integer values. The default value of MySql is signed. If you use a signed integer the ip addresses where the first octet is higher than 127 could NOT be saved

To fill this table with the content of an existing cidr-map one need a tool which can determine the first (network) and the last ip (broadcast) of a cidr-notation. For my little script I used ipcalc utility and some lines of bash.
The following script parses a cidr-map file and get's the network and broadcast portion from which the MySQL INSERT statements are generated.

#!/bin/bash
 
for i in $(cat /etc/postfix/block.cidr | awk '{print $1}') ; do
 IP=$(ipcalc $i | grep 'Address' | awk '{print $2}')
 SUB=$(ipcalc $i | grep 'Broadcast' | awk '{print $2}')
# this check is necessary because a host in notation
# 192.168.205.16/32 does NOT have a 
# in this case network and broadcast (mysql rows) will be set to identical values
 [ "x$SUB" = 'x' ] && SUB=$IP
 echo "INSERT INTO \`blacklist\` (\`network\`,\`broadcast\` ) VALUES (INET_ATON('$IP'),INET_ATON('$SUB')) ON DUPLICATE KEY UPDATE \`active\`=1;" >> /root/cidr2mysql.sql
done

in /root/cidr2mysql there will be the MySql INSERT statements (in the file each statement is on on line)

INSERT INTO `blacklist` (`network`,`broadcast` ) 
  VALUES
   (
   INET_ATON('192.168.205.0'),
   INET_ATON('192.168.205.31')
   ) 
   ON DUPLICATE KEY UPDATE 
    active`=1;

INET_ATON function from MySql is used to convert network and broadcast into integers.
You can check this directly on the MySql console

mysql> select inet_aton('192.168.205.0'),inet_aton('192.168.205.31');
+----------------------------+-----------------------------+
| inet_aton('192.168.205.0') | inet_aton('192.168.205.31') |
+----------------------------+-----------------------------+
|                 3232288000 |                  3232288031 |
+----------------------------+-----------------------------+
1 row in set (0.01 sec)

INET_ATON computes the values with the following function:
192×256^3 + 168×256^2 + 205×256 + 0
192×256^3 + 168×256^2 + 205×256 + 31

Next a small postfix configfile is needed to tell postfix about the database details (e.g. host, user, password, table …). The name of the file is not important but it should be placed in the default postfix config-directory /etc/postfix on most Linux. In my case /etc/postfix/mysql-blacklist.cf

hosts = MYSQL_HOST
user = MYSQL_USER
password = MYSQL_PASSWORT
dbname = MYSQL_DATENBANK
query = SELECT 'reject' FROM `blacklist` WHERE inet_aton('%s') BETWEEN `network` AND `broadcast`

Change the values to fit your enviroment Before you insert the new restriction it's advisable to check that postfix can connect to the database and gets the expected data. To do this postmap command can be helpful

postmap -q 'IP.ADDR.IN.BLOCK' mysql:/etc/postfix/mysql.blacklist.cf

change the IP to one that is for sure in your table.

Then add the „map“ to the postfix config in /etc/postfix/main.cf

smtpd_client_restrictions = ... mysql:/etc/postfix/mysql-blacklist.cf ...

and service postfix restart

Last word: Postfix config/maps in MySql is nice but also has a huge disadvantage. If the database goes south (for whatever reason ever) then postfix would reject EVERY mail with a temporary (450) error. For a testing enviroment this might be acceptable but for production that is a no-go Single Point of Failure.
So I suggest eighter to use as MySql Master/Slave replication or even better a MySql-cluster (ndb cluster enviroment

The main difference between replication and the cluster is that a replication is ALWAYS asynchronus whereas a cluster is truly synchron. With replication there is a small (depending on the enviroment also bigger) timewindow where the Master already commited the change but the Slave has not comitted yet. For example due to high loads on the Slave which delay the local commit. So in that timeframe the data ARE NOT constistent between Master and Slave, which can be potentialy bad.

To be honest in most environments where Master and Slave are in the same subnet the timeframe is very very very small. So with local master/slave a replication is surely a good thing.

There is one disadvantage more, which imho is „worse“ than the tiny little timeframe: A slave can never write to the master. That means all write operations depend on the availability of the master. If the master goes south nothing can be written to the database anymore until the master is up again or an administrator manually intervened.
Here comes the beauty of a ndb-cluster. It's like a RAID1 for MySql via TCP. NDB consists of several nodes. We have management-nodes which do what the name say, they manage the cluster. Then there are the storage-nodes which hold the database data. And finally we have the api-nodes which interact with user applications (e.g. a webpage or a postfix). Ideally a setup consists of at least two managment-nodes and two storage nodes. So even if one management-node and one storage-node goes down the cluster is still fully operational.
api-nodes can be connected as many as you want and in contrast to replication api-nodes (slave in replication) can write to the storage-nodes. So a cluster is truly bi-directional. In the moment a client (e.g. api) commits to the database, the commit is instantly visable for all other members of the cluster. There is not the slightest timeframe for inconsistent data