1

I have a production running installation of Postfix 3.1 and Dovecot. Recently upgraded to a new version of MySQL, passing from 5.6 to 8 hosted in a new server.

The problem now is that postfix and dovecot services can't connect to MySQL8 due to incompatible settings in mysql client (related with SSL).

If I try to connect with mysql command line to the new database server (mysql03) I get an error:

mysql -hmysql03 -uvimbadmin -p vimbadmin
ERROR 2026 (HY000): SSL connection error: unknown error number

I know that's a problem with SSL, so if I try

mysql --ssl-mode=disabled -hmysql03 -uvimbadmin -p vimbadmin

it connects well. So I put this config in the /etc/mysql/conf.d/mysql.cnf

[mysql]
ssl-mode=DISABLED

[client]
ssl-mode=DISABLED

Tested again with command line mysql (without the ssl-mode option) and connects well.

But postfix refuses to connect, even if I specify the option_file and option_group params in the config. So, for postfix I managed to implement a workaround, changing the database type for alias, and mailboxes, from mysql to texthash. And generate the texthash files with a script.

The problem is that dovecot does not connect to MySQL8 either, and I don't know any other way to do it. Contents of file /etc/dovecot/dovecot-sql.conf.ext

driver = mysql

connect = host=mysql02 user=vimbadmin password=*** dbname=vimbadmin
default_pass_scheme = MD5


password_query = SELECT username as user, password as password, 

        homedir AS userdb_home, maildir AS userdb_mail, 

        concat('*:bytes=', quota) AS userdb_quota_rule, uid AS userdb_uid, gid AS userdb_gid 

    FROM mailbox 

        WHERE username = '%Lu' AND active = '1' 

            AND ( access_restriction = 'ALL' OR LOCATE( '%Us', access_restriction ) > 0 )


user_query = SELECT homedir AS home, maildir AS mail, 

        concat('*:bytes=', quota) as quota_rule, uid, gid 

    FROM mailbox WHERE username = '%u'

Now I'm using mysql02 because is the old database server and keeps running to keep the email working. The goal is to change to mysql03.

And finally, my question is if there is a way to configure dovecot with files like postfix, so I don't need to connect from the application to the database. And if not, what can I change to be able to connect to the new MySQL server.

Sergi
  • 113

1 Answers1

0

If this is just temporary during upgrades* and your only trouble is transport security.. temporarily swap out your host line for something mysql knows it is not responsible for transport security for:

# connect = host=mysql02 user=vimbadmin password=*** dbname=vimbadmin
connect = host=/run/sergi-temp-maintenance/mysql03.sock user=vimbadmin password=*** dbname=vimbadmin

If that host is remote and you therefore do not have a local socket yet, let SSH do the secure forwarding:

ssh -f -N -T -o ExitOnForwardingFailure=yes -L /run/sergi-temp-maintenance/mysql03.sock:/var/run/mysqld/mysqld.sock unprivileged@mysql03

Be aware mysql will, for connections via sockets, automatically permit connecting as the local owner of the connection, so you want that socket only readable by the intended consumers on the forward host, and the SSH user on the destination not be associated with a privileged mysql user.

*) I hope this is just temporarily during upgrades, because Postfix 3.1 sound like something you want to upgrade as well

anx
  • 10,888