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.
.jpg)
