Postfix and MySQL (Debian)

Integration of Databases in the Postfix SMTP server in Debian GNU/Linux

Why would somebody want to let postfix connect to a SQL-database?

  • There’s no need to create a real local user for each e-mail account
  • SQL-databases can be kept in RAM, so if you have excessive mailing
    on your server, there will be reduced harddisk access
  • Management of mailinglists becomes real easy
  • /etc/aliases is kept small and simple

Step 1

Install the package “mysql-server” and “mysql-client” if not yet installed.
Log on to your sql-server using the root account:

mysql --user root
mysql> create database postfix_database;
mysql> GRANT ALL PRIVILEGES ON postfix_database \
TO 'postfix'-AT-'localhost' IDENTIFIED BY 'postfix_password' \
WITH GRANT OPTION;
mysql> flush privileges;
mysql> create table postfix.postfix_alias (destination VARCHAR(50), \
alias VARCHAR(50));
mysql> exit;

Now we have created a database called “postfix_database” and a user called
“postfix” who has access to it using his unique password “postfix_password”.
With “flush privileges” we bring the sql-server up to date concerning user rights.
Then we create a table called “postfix_alias” in the database “postfix” with two rows:
“destination” is a text variable where the mail will be relayed to and “alias” is the name
of the mailinglist in my example.

Step 2

Install the package “postfix-mysql”. Besides the needed
library this will bring you the config file “/etc/postfix/mysql-aliases.cf” which we
will modify like this

user = postfix
password = postfix_password
table = postfix_alias
query =  SELECT destination FROM postfix_alias WHERE alias = '%s'
hosts = unix:/var/run/mysqld/mysqld.sock
select_field = destination
where_field = alias

Since postfix runs in a chroot it lacks several information it needs to have;
for example the socket to the mysql daemon. That’s why we provide it
with some bind mounts, which can be done by inserting these lines into
“/etc/fstab”.

/etc/passwd     /var/spool/postfix/etc/passwd           none bind 0 0
/etc/shadow     /var/spool/postfix/etc/shadow           none bind 0 0
/etc/group      /var/spool/postfix/etc/group            none bind 0 0
/var/run/mysqld /var/spool/postfix/var/run/mysqld       none bind 0 0

To update this information the root user has to remount all filesystems
using “mount -a”.

Step 3

We’re done already(almost). All that is still needed is some information in the database.
Single entries can be made with the mysql client like this:

mysql> insert into postfix_alias values \
('someone-AT-somewhere-DOT-de', 'mailinglistname');

Now if you send a mail to “mailinglistname-AT-yourhost-DOT-com” the mail will be relayed to
“someone@somewhere-DOT-de”. That’s it.

Step 4

Note that installing the package postfix-mysql updated a line in your “/etc/postfix/main.cf”:

alias_maps = hash:/etc/aliases
...
alias_maps = mysql:/etc/postfix/mysql-aliases.cf

There are most likely many more lines in this file, but the important factor is
that the first line mapping to “/etc/aliases” is made obsolete by the second entry.
So if you were using some important relaying in this file you should migrate it.
For this reason I wrote a small
shellscript that was capable to do the job for my setup.

 

Update

I received the following information via mail from a reader:

Since 1995 I am running my own mail servers and probably since 2005
postfix with mysql. So I did not follow your article
word by word, but it helped a lot, because I was not
aware that Debian 6.0 (Squeeze) postfix uses a chroot and the error
message “Can’t connect to local MySQL server through socket”
did not make sense to me until I read your article.

Here in full:
Sep 11 15:12:23 m postfix/trivial-rewrite[16044]: warning: connect
to mysql server unix:/var/run/mysqld/mysqld.sock: Can’t connect to
local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

So you article helped a lot!

Some suggestions I found while I was reading your article:

* For me only one mount was needed:

mkdir -p /var/spool/postfix/var/run/mysqld
echo “/var/run/mysqld /var/spool/postfix/var/run/mysqld none bind 0
0″ >> /etc/fstab
mount -a

* For me also the mkdir was essential (not mentioned)

* In your  mysql-aliases.cf the key “dbname” and value
“postfix_database” for setting the database name is missing.
at least my postfix is complaining about this, if I
leave it out.

dbname=postfix_database

N.B.: In my case the files was already there and the
package did not overwrite it. I think it is not
in the package, as you say.

dpkg -L postfix-mysql
/.
/usr
/usr/share
/usr/share/doc
/usr/share/doc/postfix-mysql
/usr/share/doc/postfix-mysql/changelog.Debian.gz
/usr/share/doc/postfix-mysql/copyright
/usr/share/doc/postfix-mysql/README.Debian
/usr/lib
/usr/lib/postfix
/usr/lib/postfix/dict_mysql.so

* You might consider mention you Debian version? Would help
to limit expectations ;)

* “Note that installing the package postfix-mysql updated a line in
your “/etc/postfix/main.cf”: ” could be true. But is not against
Debian policy to mess around with user configuration? But if this
is true, than thanks for the warning!