Not able to connect to MySQL from postfix

Question

opensource community

I am using Postfix, dovecot and Postfix Admin for my complete email setup

I am using virtual users so I have installed postfix-mysql and dovecot-mysql

Issue:

My postfix service is not able to connect to MySQL server. Throwing:

May 19 19:52:18 mail postfix/trivial-rewrite[1600937]: warning: connect to mysql server localhost: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

May 19 19:52:18 mail postfix/trivial-rewrite[1600937]: warning: virtual_alias_domains: mysql:/etc/postfix/sql/mysql_virtual_alias_maps.cf: table lookup problem

May 19 19:52:18 mail postfix/trivial-rewrite[1600937]: warning: virtual_alias_domains lookup failure

May 19 19:52:18 mail postfix/trivial-rewrite[1600937]: warning: virtual_alias_domains: mysql:/etc/postfix/sql/mysql_virtual_alias_maps.cf: table lookup problem

May 19 19:52:18 mail postfix/trivial-rewrite[1600937]: warning: virtual_alias_domains lookup failure

My /etc/postfix/sql/mysql_virtual_alias_maps.cf looks like:

user = root

password = "mypass"

hosts = localhost

dbname = postfixadmin

query = SELECT goto FROM alias WHERE address='%s' AND active = '1'

#expansion_limit = 100

Additional Info:

The MySQL setup is correct. The bind address is 0.0.0.0, and the root user has all hosts allowed (%)

I can connect to MySQL from MySQL CLI using host 127.0.0.1 as well as localhost

Also, dovecot-mysql and postfix-admin are both able to connect to MySQL using the same config param

Help is much appreciated!

edit: Output of ss:

u_str LISTEN 0      70                                                            /var/run/mysqld/mysqlx.sock 9448594            * 0    users:(("mysqld",pid=1599671,fd=22))                              
u_str LISTEN 0      151                                                           /var/run/mysqld/mysqld.sock 9448596            * 0    users:(("mysqld",pid=1599671,fd=25))                              

Even if I connect to 127.0.0.1 from postfix I am getting:

May 20 14:57:09 mail postfix/trivial-rewrite[1609421]: warning: connect to mysql server 127.0.0.1: Access denied for user 'root'@'localhost' (using password: YES)
May 20 14:57:09 mail postfix/trivial-rewrite[1609421]: warning: virtual_alias_domains: mysql:/etc/postfix/sql/mysql_virtual_alias_maps.cf: table lookup problem

Answer 1

The specification

hosts = localhost

makes MySQL library to use unix socket by default and not TCP/IP (for efficiency reasons), but it seems your server isn't listening on an expected socket:

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

(you can confirm that with ss -lnpx | grep mysql — there'll be empty reply or it will be on the other path). It doesn't even try to connect over TCP/IP that way, so your checks (listens on 0.0.0.0:3306, you can mysql -H 127.0.0.1, etc) are irrelevant.

This also might be caused by running in an chrooted environment so Postfix's daemon sees a different view of the filesystem which has no database socket.

Change to

hosts = 127.0.0.1

to use TCP/IP to localhost IP address instead of a socket. Beware that's considerably less efficient!

Or, revert your MySQL/MariaDB configuration back to listen on domain sockets; it does no harm and I wonder why disabling it at all. If the problem was actually caused by chroot, you may use Postfix's proxymap service which is specifically designed to allow chrooted parts of Postfix to communicate to unix sockets not normally accessible to it.

P.S. is this root a mysql super user? Don't do that! Create an unprivileged user with only SELECT granted to only a few tables that actually are needed Postfix, and use that!

Answer 2

The original issue I was facing was that Postfix was not able to authenticate. And the reason for that was the postfix config parameter(.cf) file does not allow double quotes in the value so double quotes were being treated as a part of password

password = "mypass"

https://www.postfix.org/BASIC_CONFIGURATION_README.html#:~:text=Postfix%20does%20not%20know%20about%20quotes%20like%20the%20UNIX%20shell%20does

https://www.oreilly.com/library/view/postfix-the-definitive/0596002122/ch04s02.html#:~:text=Do%20not%20use%20quotation%20marks%20around%20values.%20They%20have%20no%20significance%20in%20the%20Postfix%20configuration%2C%20so%20they%20would%20be%20considered%20part%20of%20the%20value%2C%20which%20is%20probably%20not%20what%20you%20want.