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!