New Zealand Linux

A lot of people use postfixadmin, it means email users are in the database, its easy to use, easy to admin. However the setup can be a little tricky for those not used to it.

If you want to have a go doing it yourself, there is a great tutorial here http://rimuhosting.com/knowledgebase/linux/mail/postfixadmin

Today however we had the most exasperating problem trying to get postfix to connect to the database however on one particular VPS. 2 of us sat for over an hour trying to figure out why all the credentials worked fine, but postfix still couldnt connect.

The logs had the following error messages

Nov 5 01:12:40 hostname postfix/trivial-rewrite[8740]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (40)
Nov 5 01:12:40 hostname postfix/trivial-rewrite[8740]: fatal: mysql:/etc/postfix/mysql_virtual_alias_maps.cf(0,lock|fold_fix): table lookup problem
Nov 5 01:12:40 hostname postfix/trivial-rewrite[8741]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (40)
Nov 5 01:12:40 hostname postfix/trivial-rewrite[8741]: fatal: mysql:/etc/postfix/mysql_virtual_alias_maps.cf(0,lock|fold_fix): table lookup problem
Nov 5 01:12:41 hostname postfix/smtpd[8657]: warning: problem talking to service rewrite: Success
Nov 5 01:12:41 hostname postfix/smtpd[8491]: warning: problem talking to service rewrite: Connection reset by peer
Nov 5 01:12:41 hostname postfix/master[8481]: warning: process /usr/lib/postfix/trivial-rewrite pid 8740 exit status 1
Nov 5 01:12:41 hostname postfix/master[8481]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup — throttling
Nov 5 01:12:41 hostname postfix/smtpd[8685]: warning: problem talking to service rewrite: Success
Nov 5 01:12:41 hostname postfix/master[8481]: warning: process /usr/lib/postfix/trivial-rewrite pid 8741 exit status 1

So clearly it was a case of not being able to connect to the MySQL server, but why? the user was fine, the host was fine, everything was checked, and rechecked. Finally I saw an email which touched on postfix being in a chroot, which meant it was unable to access the mysql.sock file (i had already checked permissions on this multiple times by now).

To make a long story short, this is a Debian based box, and the fix is to change the mysql socket to a place that the mail could access it.

/etc/init.d/mysql stop
cd /etc/mysql
mkdir backup # always
cp *.cnf backup/ # backup everything
sed -i s@/var/run/mysqld/@/var/spool/postfix/var/run/mysqld/@g my.cnf # this edits the file inline
sed -i s@/var/run/mysqld/@/var/spool/postfix/var/run/mysqld/@g debian.cnf #changing the socket location
mkdir /var/spool/postfix/var/run/mysqld/
chown mysql /var/spool/postfix/var/run/mysqld/
/etc/init.d/mysql start

This changed the mysql sock into a directory so that they postfix was able to access it, enabling all mail to run freely.