OpenSMTPD query issue with MariaDB

Dnz · Dec 7, 2018

Hi all,

It is a while now that I am struggling in configuring OpenSMTPD to query a MariaDB database as a backend after having a working environment using file tables. The OS is FreeBSD 11.2-RELEASE-p5 and I am using the following packages:

opensmtpd-5.9.2p1_6,1
opensmtpd-extras-table-mysql-201606230001_2
opensmtpd-extras-table-passwd-201606230001_2
dovecot-2.3.4_3
mariadb103-server-10.3.8_2 (installed on another server)

My smtpd.conf is:

Code:

#Dnz v1
#OpenSMTPD conf file

#If using the self-signed certificates
pki my.domain.tld certificate "/var/srv/certs/cert.pem"
pki my.domain.tld key "/var/srv/certs/pkey.pem"

table aliases mysql:/usr/local/etc/mail/smtpd-mysql.conf
table domains mysql:/usr/local/etc/mail/smtpd-mysql.conf
table passwd mysql:/usr/local/etc/mail/smtpd-mysql.conf
table virtuals mysql:/usr/local/etc/mail/smtpd-mysql.conf

#Ports to listen on.
listen on lo0
listen on lo0 port 10028 tag DKIM_OUT
listen on vmx0 port 25 tls pki my.domain.tld

#Mask the source on port 587 for more privacy
listen on vmx0 mask-source port 587 tls-require pki my.domain.tld auth <passwd>

#Allow local delivery
accept from local for local alias <aliases> deliver to lmtp "/var/run/dovecot/lmtp" rcpt-to

#Allow virtual domains
accept from any for domain <domains> virtual <virtuals> deliver to lmtp "/var/run/dovecot/lmtp" rcpt-to

#Allow outgoing mails to pass to DKIMproxy.
accept tagged DKIM_OUT for any relay
accept from local for any relay via smtp://127.0.0.1:10027

My smtpd-mysql.conf is:

Code:

#Dnz v1
#OpenSMTPD MySQL config file

host remotehost.com
username user
password password
database database

query_credentials SELECT username AS user, password FROM accounts WHERE username=? AND enabled = true;

query_domain SELECT domain FROM domains WHERE domain=?;

query_userinfo SELECT username AS user, password FROM accounts WHERE username=? AND enabled = true;

query_alias SELECT destination_username as destinations from aliases where source_username=? and enabled = true;

The issue that I am getting from the log is:

Code:

domains[2097]: debug: table-mysql: (re)connecting
domains[2097]: warn: table-mysql: wrong number of columns in resultset
lookup: check "mydomain.tld" as DOMAIN in table proc:domains -> -1
smtp-in: Failed command on session 630b215130abf33e: "RCPT TO:<user@mydomain.tld>" => 451 Temporary failure

And the result of the same query in the smtpd-mysql.conf ran on the DB:

Code:

MariaDB [database]> SELECT domain FROM domains WHERE domain="mydomain.tld";
+-------------+
| domain      |
+-------------+
| mydomain.tld |
+-------------+
1 row in set (0.000 sec)

Do you guys have any idea on what is preventing opensmtpd to get the data in the database? Even if I use a random non-existent domain the result is the same. On the other side Dovecot is working fine with no issues.
Thank you!