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!

Alain M. Lafon

Postfix and MySQL (Debian)