dovecot block login per country / ip2location / geoip

I’ll need to be honest and say that I’ve search for a long time an “easy” way to restrict IMAP logins / per user / per country.

During my search around this topic i’ve also found allow_nets which is a nice feature but it only allows you to restrict per user / per ip or net. Plus the wiki tutorial for this feature is not showing a nice way to integrate with dovecot-sql. My approach was to create another field in the mysql table(in my case vpopmail) and when password_query is formed to request also this field. In short words my current sql lookups are like:

user_query = SELECT pw_dir as home, 89 AS uid, 89 AS gid FROM vpopmail WHERE pw_name = '%n' AND pw_domain = '%d'

password_query = select pw_clear_passwd as password, allow_nets FROM vpopmail LEFT JOIN limits ON vpopmail.pw_domain=limits.domain WHERE pw_name='%n' and pw_domain='%d' and ( !(pw_gid & 8)
and ('%r'!='127.0.0.1' or !(pw_gid & 4)) and ( '%r'!='127.0.0.1' or COALESCE(disable_webmail,0)!=1) and COALESCE(disable_imap,0)!=1);

Why so big ? Simply because i wanted also the support for vmoduser features which are also a nice set to restrict behaviour per user/domain. For more info about dovecot and vpopmail sql auth you can read on dovecot wiki.

As i said in the beginning of the article i was also search for ways to block user(s)/country. Apparently it is possible via dovecot authentication policy but i’ve found it quite painful. You can read more about this method here.

My method is base on ip2location which has also also to possibility to download the geographical IP data in CSV. Starting from this data you can easily create a database and import the CVS into it.

CREATE DATABASE ip2location;
USE ip2location;
CREATE TABLE `ip2location_db1`(
`ip_from` INT(10) UNSIGNED,
`ip_to` INT(10) UNSIGNED,
`country_code` CHAR(2),
`country_name` VARCHAR(64),
PRIMARY KEY (`ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

LOAD DATA LOCAL
INFILE 'IPCountry.CSV'
INTO TABLE
`ip2location_db1`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

You can find more about how to manipulate data from ip2location on their site.

In this moment you will have a database with IPs and their location. Using the dovecot variable %r ( remote ip ) we can pass this to the sql query when searching for username/password. For more about dovecot variables i recommend reading their site.

I though that will be easy but i didn’t expect the query to became so long. To be honest i’ve tried also with sql variables and stored procedures but i didn’t found a way to properly set it into dovecot. If you find it please leave me a comment.

In this moment my username query is looking like this. Please have in mind that allow_country is another field in vpopmail database(or sql db used for authentication) and this will contain data in the fallowing format: RO,DE,NL (example for: Romania, Germany, Netherlands)

user_query = SELECT pw_dir as home, 89 AS uid, 89 AS gid FROM vpopmail WHERE pw_name = '%n' AND pw_domain = '%d' and allow_country like CONCAT('%%',(select country_code from ip2location.ip2location_db1 where ip_from <= INET_ATON('%r') and ip_to >= INET_ATON('%r')),'%%')

 

This is all, hope that it was instructive.