Alain M. Lafon / Felix Baumann: Campus Ambassador
University of Applied Sciences Stuttgart / University of Stuttgart

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.
I wrote a JSP/Servlet combination in JavaEE to create a webpage where users can put themselves on or off a mailinglist; you can find it here or in the projects folder if you're interested.

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.