Home » Plesk + Postfix-MySQL before qmail

Plesk + Postfix-MySQL before qmail

I’ve been working on a project to do something about the poor ACL capabilities with qmail + Plesk. This is a rough breakdown of what I did to make it work. I’ve since made some changes, added a few capabilities that I will document here when I have time.
I just took the first step this week by sheltering qmail from any direct interaction with the Internet or users, here is stage one of Plesk + Postfix + MySQL – qmail. My overall goal is to forklift qmail out of the Plesk server, replacing it with Postfix entirely.

My Problem

 

From what I’ve been able to gather through much googling and searching on the Plesk message board, qmail is unable to restrict who sends mail directly to the qmail process on the server. It does prevent relaying, but it can’t just allow authorized users via poplock or SMTP auth. It can use those methods to determine if relaying is allowed, but anyone can send mail directly to the qmail server if it hosts mail for a particular domain.

Normally, this is a good thing, however when you have a mail gateway that acts as your e-mail security platform you don’t want just anyone to connect directly to the server which hosts your mailboxes. In an enterprise or a closed environment, you would typically wall off the particular server by putting it behind a firewall and forcing your users to access through a VPN, DUN, or locally. Webhosting companies, however, can not typically do this so they are left with a hole in their security wall.

My Solution – Postfix + MySQL

My main problem is, allowing only the authorized users access to relay mail so I need to put something between qmail and the world that will do this for me. Having worked with Postfix for several years before, I figured this would be a good choice since I know it can be easily configured to read from a MySQL database to provide this functionality. I am also very confident in the security and community support Postfix has behind it.

Requirements

  1. A testing environment, separate from your Plesk installation. This environment should match production as much as possible as far as the distribution and packages installed. You do not need plesk running in this environment, though a copy of your plesk database or access to it will make testing easier.
  2. A copy of the most recent version of Postfix w/ MySQL support that is available from your distribution (as of this writing, postfix-2.2.10-1 was the most recent from CentOS available in the centosplus repository).
  3. A couple of free hours to test, test, test… It’s all about testing, this is a pretty simple program to setup but it’s easy to make a typo and bring down access to your mail server, no one wants that…

Instructions (simple method)

As most people are using Plesk to keep things simple I figure using supported packages (RPMS) from your distribution of choice is the easiest and safest route. If you have an issue, there are more people available to help or the issue may already be documented. If you’re feeling spiffy, there’s a more involved method that follows.

Install postfix-2.2.10-1.RHEL4.2.mysql_pgsql.c4.i386.rpm from centosplus:

[root@plesk:~]# yum –enablerepo=centosplus install postfix
Setting up Install Process
Setting up repositories
Reading repository metadata in from local files
…
Dependencies Resolved
============================================
Package Arch Version Repository Size
============================================
Installing:
postfix i386 2:2.2.10-1.RHEL4.2.mysql_pgsql.c4 centosplus 3.1 M
Installing for dependencies:
postgresql i386 7.4.13-2.RHEL4.1 base 2.0 M

Transaction Summary
============================================
Install 2 Package(s)
Update 0 Package(s)
Remove 0 Package(s)
Total download size: 5.1 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): postfix-2.2.10-1.R 100% |=========================| 3.1 MB 00:06
(2/2): postgresql-7.4.13- 100% |=========================| 2.0 MB 00:03
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: postgresql ######################### [1/2]
Installing: postfix ######################### [2/2]

Installed: postfix.i386 2:2.2.10-1.RHEL4.2.mysql_pgsql.c4
Dependency Installed: postgresql.i386 0:7.4.13-2.RHEL4.1
Complete!
[root@plesk:~]#

Configure postfix to read pop-before-smtp auth from mysql

Create a MySQL user for postfix that only has SELECT access to the psa.smtp_poplocks table. One way to do this is:

[root@plesk:~]# mysql -u admin -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43 to server version: 4.1.20

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> grant SELECT on psa.smtp_poplocks to postfix@localhost;
Query OK, 0 rows affected (0.02 sec)

mysql> grant SELECT on psa.misc to postfix@localhost;
Query OK, 0 rows affected (0.02 sec)

mysql> set password for postfix@localhost = password (‘secret’);
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit;

I shouldn’t have to say this but, please be sure to replace secret above with a real and secure password. It’s only the smtp_poplocks and misc tables but it’s no excuse to be insecure.

Now we create the actual configuration for Postfix to read from the psa.smtp_poplocks table. We need to modify /etc/postfix/main.cf and create a config file in /etc/postfix which contains the specifics of what we want out of the database. Let’s create the file first. Using your favorite editor, create /etc/postfix/mysql-psa-smtp_poplocks.cf (notice how I use the datbasetype_datbase-table syntax when creating this file; this is entirely optional, but makes troubleshooting and documentation easier):

user = postfix
password = secret
dbname = psa
query = SELECT CASE WHEN ‘%s’ = (SELECT ip_address FROM smtp_poplocks WHERE ip_address=’%s’ AND(lock_time > DATE_SUB(NOW(), INTERVAL(SELECT val FROM misc WHERE param=’poplock_time’ LIMIT 1)MINUTE))LIMIT 1)THEN ‘OK’ END AS response

The secret sauce here is the query line. The %s variable is replaced by the connecting IP Address. We have to use the CASE control flow function because the check_client_access in smtpd_recipient_restrictions expects OK as the only valid response for a match, anything else (i.e. NULL) is a miss. There is some redundancy here as this function checks to ensure that the entry in the smtp_poplocks table is no older than the value set in the Plesk GUI for the poplock time (usually 20 minutes). I say it’s redundant because the poplock auth process courier uses checks and clears out this table upon each connect from a pop/imap client. It is possible that a client would not connect for over 20 minutes, but on a high traffic server this is rarely the case, we do it here to say consistent with Plesk. People familar with SQL may also notice the “AS response” alias at the end of the query. While we will never see the actual result of this query, it would look like this to Postfix:

CASE WHEN ’96.640.72.378′ = ( SELECT ip_address FROM smtp_poplocks WHERE ip_address=’96.640.72.378′ AND ( lock_time > DATE_SUB ( NOW(), INTERVAL ( SELECT val FROM misc WHERE param=’poplock_time’ LIMIT 1 ) MINUTE ) ) LIMIT 1 ) THEN ‘OK’ END AS response
————————————————————…
OK

Which shouldn’t matter, but that’s a huge field name for such a small value. I could see where this could potentially be a problem somewhere, so we just alias all that nonsense to “response” to make it somewhat managable.

Anyway, this is enough to test our access to the psa.stmp_poplocks database through Postfix. As long as the permissions are correct in MySQL, and the psa database exists and has data in the smtp_poplocks and a poplock_time entry exists in the misc table we can have some fun. First, lets get a list of IPs in the smtp_poplocks table we can use for testing:

[root@plesk:~]# mysql -u postfix -p (be sure to use your newly created postfix user to confirm permissions)
…
mysql> SELECT DISTINCT ip_address from smtp_poplocks;
+—————+
| ip_address |
+—————+
| 127.0.0.0 |
| 96.640.72.378 |
| 37.99.119.414 |
| 27.669.125.324 |
| 40.346.54.99 |
| 91.99.26.452 |
| 62.385.51.356 |
+—————+

in case you didn’t get it, those IPs are fake

Okay, now to test. First we’ll test for a matching IP:

[root@plesk:~]# postmap -q 96.640.72.378 mysql:/etc/postfix/mysql-psa-smtp_poplocks.cf
OK
[root@plesk:~]#

Awesome, now let’s test an invalid IP:

[root@plesk:~]# postmap -q 1.1.1.1 mysql:/etc/postfix/mysql-psa-smtp_poplocks.cf
[root@plesk:~]#

As you can see, we got nothing back so we’re good to go on now that postfix can talk inteligently with MySQL. We can move on…

Since we’re making a fairly substantial change to our environment, it’s a good idea to make some changes to Postfix to provide a somewhat meaningful error if someone attempts to send mail through with out being authenticated. The best way I know to do that, is to cheat a little bit by using a regexp lookup table that will match everything thrown at it. Since the smtpd_recipient_restrictions rules are processed in order we can catch any IP that doesn’t match a client in the smtp_poplocks table and spit out a meaningful error message to them. For this, we need to first create a simple regexp lookup table, we’ll call it /etc/postfix/regexp-authentication_required.cf:

[root@plesk:~]# vi /etc/postfix/regexp-authentication_required.cf:
/./ 530 Authentication required
[root@plesk:~]#

What this will do is simply respond with a 530 Authentication Required error if a connecting IP isn’t matched in the smtp_poplocks table.

With that out of the way, we must now configure Postfix to accept connections from users in the smtp_poplocks table. We can use the postconf command to easily add this to /etc/postfix/main.cf

[root@plesk:~]# postconf -e “smtpd_recipient_restrictions = check_client_access mysql:/etc/postfix/mysql-psa-smtp_poplocks.cf, check_client_access regexp:/etc/postfix/regexp-authentication_required.cf, reject”

Next we need to tell postfix where to forward all the mail it gets

[root@plesk:~]# postconf -e “relayhost = 10.0.0.1″

And what are the external IPs postfix is going to listen on:

[root@plesk:~]# postconf -e “inet_interfaces = 10.0.0.5, 10.0.0.6, 10.0.0.7″

Postfix should now be configured for BASIC reception of authorized e-mail. This means that if a user who is authorized via the courier poplock from the plesk server connects to Postfix their e-mail will be accepted and forwarded on to the gateway server.

Now we have to tell qmail to listen to localhost and another IP that is protected from the outside world, but the external gateway can get to. We’ll pretend that IP is 192.168.0.5, so we’ll add a bind line to the the existing xinetd config for qmail and create a new one for the localhost side.

[root@plesk:~]# cp /etc/xinetd.d/smtp_psa /root (making a backup just in case)
[root@plesk:~]# vi /etc/xinetd.d/smtp_psa

service smtp
{
socket_type = stream
protocol = tcp
wait = no
bind = 192.168.0.5
disable = no
user = root
instances = UNLIMITED
server = /var/qmail/bin/tcp-env
server_args = -Rt0 /var/qmail/bin/relaylock /var/qmail/bin/qmail-smtpd /var/qmail/bin/smtp_auth /var/qmail/bin/true /var/qmail/bin/cmd5checkpw /var/qmail/bin/true
}

[root@plesk:~]# vi /etc/xinetd.d/smtp_psa_localhost (this is the new file)

service smtp
{
socket_type = stream
protocol = tcp
wait = no
bind = 127.0.0.1
disable = no
user = root
instances = UNLIMITED
server = /var/qmail/bin/tcp-env
server_args = -Rt0 /var/qmail/bin/relaylock /var/qmail/bin/qmail-smtpd /var/qmail/bin/smtp_auth /var/qmail/bin/true /var/qmail/bin/cmd5checkpw /var/qmail/bin/true
}

Now, the last step is to actually throw the switch. This can get tricky because you don’t want to interrupt service for your users, and you don’t want to reject any mail so this is what I did.

I’m using an IronPort appliance, nice thing about their setup is you can make several configuration changes on the appliance and then COMMIT them. So, what I did was change any STMP routes I needed to change so all I need to do is COMMIT them. Before we pull the trigger, it would be a good idea to bring up some spare terminal windows to the Plesk server and mail gateway / appliance. I bring up two spare windows for the Plesk server and one spare window for the IronPort. On the Plesk server’s first window I run:

[root@plesk:~]# tail -f /usr/local/psa/var/log/maillog

On the second Plesk window I run:

[root@plesk:~]# tail -f /var/log/messages

On the IronPort I run:

ironport> tail mail_logs

Stop qmail:

[root@plesk:~]# service xinetd stop

Commit changes on your mail gateway / appliance

ironport> commit

Start postfix:

[root@plesk:~]# service postfix start

Start qmail

[root@plesk:~]# service xinetd start

Now, if all went well you should have a postfix process and an xinetd process running, if not check your spare terminal windows (tail files). If it’s not something you don’t think you can easily fix, stop postfix and restart your mail gateway and qmail with the old configuration. If you see no errors you’re almost home, it would be a good time to do a ps and make sure postfix and xinetd are truly runing:

[root@plesk:~]# ps -ef | grep postfix
root 31788 1 0 14:42 ? 00:00:00 /usr/libexec/postfix/master
postfix 31792 31788 0 14:42 ? 00:00:00 qmgr -l -t fifo -u
root 13928 13787 0 17:04 pts/0 00:00:00 grep postfix
[root@plesk:~]# ps -ef | grep xinetd
root 19950 1 0 Oct17 ? 00:00:03 xinetd -stayalive -pidfile /var/run/xinetd.pid
root 13998 13787 0 17:05 pts/0 00:00:00 grep xinetd
[root@plesk:~]#

Now, the next test is to fire up your mail client and try and send a message to yourself, if you were using pop-before-smtp authentication before everything should just work. You can test this manually as well by using telnet as such. It’s preferable to do this first from a site that is not in the smtp_poplocks table (another words, has not logged into the Plesk pop3/imap server).

[user@wokstation:~]$ telnet mail.mydomain.com 25
Trying 10.0.0.5…
Connected to mail.mydomain.com (10.0.0.5).
Escape character is ‘^]’.
220 mail.mydomain.com ESMTP Postfix
helo workstation.mydomain.com
250 mail.mydomain.com
mail from:me@mydomain.com
250 Ok
rcpt to:you@mydomain.com
530 : Client host rejected: Authentication required
quit
221 Bye
Connection closed by foreign host.
[user@wokstation:~]$

Okay, that seemed to work as expected, now let’s connect to the pop3 server and authenticate.

[user@wokstation:~]$ telnet mail.mydomain.com 110
Trying 10.0.0.5…
Connected to mail.mydomain.com (10.0.0.5).
Escape character is ‘^]’.
+OK Hello there. <38475.234553434@localhost.localdomain>
user test@mydomain.com
+OK Password required.
pass secret
+OK logged in.
quit
+OK Bye-bye.
Connection closed by foreign host.
[user@wokstation:~]$

Now try the SMTP test again:

[user@wokstation:~]$ telnet mail.mydomain.com 25
Trying 10.0.0.5…
Connected to mail.mydomain.com (10.0.0.5).
Escape character is ‘^]’.
220 mail.mydomain.com ESMTP Postfix
helo workstation.mydomain.com
250 mail.mydomain.com
mail from:me@mydomain.com
250 Ok
rcpt to:test@mydomain.com
250 Ok
data
354 End data with .
From: Me Test
To: Test User
Subject: Test message

Hello!!!
.
250 Ok: queued as A578A11380A3
quit
221 Bye
Connection closed by foreign host.
[user@wokstation:~]$

Okay, so it’s really using the smtp_poplocks table. Now would be a good time to go to your gmail account and send a test message from a host on the Internet to ensure everything is working from that end. If that works, you’re all set; if not you have more work to do!