Improve Spam abuse protection in dovecot by restricting access to mail accounts by IP address (e. g. with ISPConfig 3)

What we'll do

This howto will show you how to add ip restrictions to single mail accounts when using dovecot with MySQL.

This is especially useful if you need to access a mail account from only one single ip or a few ips or if you want to block specific ip addresses from accessing the mail account (e. g. due to spam abuse).

Prerequisites

Changes to your system

First we need to create a new database table on your mail server. In this howto we will add it to the ISPConfig database dbispconfig.

Open MySQL console:
mysql -u root -D dbispconfig -p

When logged in, create the new ip restriction table with this query:

CREATE TABLE mail_user_ip_restriction (
    restriction_id INT(11) UNSIGNED NOT NULL auto_increment,
    mailuser_id INT(11) UNSIGNED NOT NULL DEFAULT '0',
    ip VARCHAR(50) NOT NULL DEFAULT '',
    mode ENUM('w','b') NOT NULL DEFAULT 'w',
    service VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY (`restriction_id`),
    KEY `ident` (`mailuser_id`, `mode`, `service`, `ip`)
) ENGINE=MyISAM;

Now you have to alter the dovecot sql config. On ISPConfig 3 installations (e. g. Debian/Ubuntu) it should reside in /etc/dovecot/dovecot-sql.conf

Change these lines:

password_query = SELECT password FROM mail_user WHERE (login = '%u' OR email = '%u') AND disable%Ls = 'n'

user_query = SELECT email as user, maildir as home, CONCAT('maildir:', maildir, '/Maildir') as mail, uid, gid, CONCAT('*:storage=', quota, 'B') AS quota_rule, CONCAT(maildir, '/.sieve') as sieve FROM mail_user WHERE (login = '%u' OR email = '%u') AND `disable%Ls` = 'n'

(if you are not using the latest version of ISPConfig 3 the lines in your config might slightly differ)

to:

password_query = SELECT m.password FROM mail_user as m LEFT JOIN mail_user_ip_restriction as r ON (r.mailuser_id = m.mailuser_id AND r.mode = 'w' AND r.service IN ('', '%Ls')) LEFT JOIN mail_user_ip_restriction as rb ON (rb.mailuser_id = m.mailuser_id AND rb.ip = '%r' AND rb.mode = 'b' AND rb.service IN ('', '%Ls')) WHERE (m.login = '%u' OR m.email = '%u') AND m.disable%Ls = 'n' AND (r.ip IS NULL OR r.ip = '%r') AND rb.ip IS NULL

user_query = SELECT m.email as user, m.maildir as home, CONCAT('maildir:', m.maildir, '/Maildir') as mail, m.uid, m.gid, CONCAT('*:storage=', m.quota, 'B') AS quota_rule, CONCAT(m.maildir, '/.sieve') as sieve FROM mail_user as m LEFT JOIN mail_user_ip_restriction as r ON (r.mailuser_id = m.mailuser_id AND r.mode = 'w' AND r.service IN ('', '%Ls')) LEFT JOIN mail_user_ip_restriction as rb ON (rb.mailuser_id = m.mailuser_id AND rb.ip = '%r' AND rb.mode = 'b' AND rb.service IN ('', '%Ls')) WHERE (m.login = '%u' OR m.email = '%u') AND m.disable%Ls = 'n' AND (r.ip IS NULL OR r.ip = '%r') AND rb.ip IS NULL

Now restart dovecot:
service dovecot restart (or /etc/init.d/dovecot restart, depending on your system)

Keep an eye on the mail and/or system log now! If you see errors from your dovecot, then something went wrong and you should check your changes or revert them.
If everything runs as it did before, then you are ready to go.

How to restrict access

You have different possibilities to restrict access to a mailbox now. I will show you the way doing it with SQL queries on mysql console but you can use phpMyAdmin of course.

Block ip

To block a single ip from accessing a mail account you have to add it to the restriction table with mode 'b':

First you need the mailuser_id for your mail account (e.g. [email protected]). This is a simple SQL query:

SELECT `mailuser_id` FROM `mail_user` WHERE `email` = '[email protected]';

This will return a number (id) like 12345. Now add the restriction entry for this id:

INSERT INTO `mail_user_ip_restriction` VALUES (NULL, 12345, '123.234.123.234', 'b', '');

This entry will block ip 123.234.123.234 from accessing all services (pop3, imap, smtp) of mail account 12345.

If you want the ip to be able to access pop and imap but prevent it from using smtp (sending mails) you can add this entry instead:

INSERT INTO `mail_user_ip_restriction` VALUES (NULL, 12345, '123.234.123.234', 'b', 'smtp');

You can add multiple services like this, one entry for each. But keep in mind that the entry '' blocks all services.

Restrict to specific ips

Like you can block you can even restrict usage of mail accounts to single ips. This means NO other ip than the specified ones can access the account. You simply have to create an entry with mode 'w' instead of 'b':

INSERT INTO `mail_user_ip_restriction` VALUES (NULL, 12345, '123.234.123.234', 'w', 'pop3');
INSERT INTO `mail_user_ip_restriction` VALUES (NULL, 12345, '123.234.123.235', 'w', 'smtp');

This entry does the following:
- all ips can use imap of account 12345 ([email protected])
- only ip 123.234.123.234 can use pop3 for this account - all other ips don't!
- only ip 123.234.123.235 can use smtp for this account - all other ips don't!

Of course you can combine all the features and create multiple ip entries for differend modes and services.

Use case

Ok, some real-life stuff now. Imagine you have a mail address that is used by your online shop for sending mails. No one else should send mails with this account but you would like to check if someone replies to your shop emails. This, again, only shall be done by imap, not by pop3.

These are the entries you would create in this case, where 10.0.0.101 is the ip of the web server with your shop and 12345 is the mailuser_id of your mail account.

INSERT INTO `mail_user_ip_restriction` VALUES (NULL, 12345, '10.0.0.101', 'w', 'smtp');
INSERT INTO `mail_user_ip_restriction` VALUES (NULL, 12345, 'none', 'w', 'pop3');

No other ip than 10.0.0.101 can access smtp of this specific mail account and no one can access pop3 for it. Imap is available to all ips.

Don't forget IPv6! If your server or the client you want to specify has an ipv4 and an ipv6 address you have to add both!