Hi Edgar
thanks for your reply! Von: Edgar Pettijohn <[email protected]> Datum: Sonntag, 16. August 2020 um 01:00 An: Fabian M??ller <[email protected]> Cc: <[email protected]> Betreff: Re: warn: table-proc: pipe closed (Probably mySQL-hassle and a newbie-question) On Sun, Aug 16, 2020 at 12:13:41AM +0200, Fabian M??ller wrote: Hi! I am hopefully a new opensmtpd user and before I???ll start off with my first newbie question I???d be happy to briefly introduce myself: I???m Fabian from Germany. Actually I am studying german law, but as ??? in opposite to legal work ??? anyone who wants to can ???do??? IT-stuff I???ve also been in IT since I left school. Together with some friends I own a small IT-company which makes me here and there a few bucks but is actually there more for the fun rather than the profit. During shool-time I???ve already run a mailserver (postfix + dovecot, but that actually doesn???t mean I???ve known anything about mail ;)) but after we started offering services to businesses we somehow switched over to an all in one solution (plesk). As those AiO-solutions sucks because they are a blackbox and debugging is a nightmare we've decided to do hosting ourself again. And as I am the only one of us who is motivated to dive into mail, it became my part. So after some days googeling around and spending a serious amount of time on youtube watching mail-server-congress-talks I decided to go with a setup including opensmtpd rather than postfix. As the best way to start with something is to start trying I span up a few cloud-servers and started trying. As expected I ran into problems but ??? not expected ??? reading the man and googeling around couldn't solve them. So that's how I ended up here, hoping for your help! # General Setup 1. OpenSMTPD (tables via mysql, delivering via lmtp) 2. Dovecot (not yet set up) 3. MariaDB Galera Cluster as Backend-Database # The Problem I'm getting the following error and can't connect to port 25 from outside world (telnet port 25). Are you sure your ISP isn't blocking you? Can you connect to a non standard port or the submission port from outside? listen on egress port 5000 telnet yourhost.com 5000 ** It turned out that opensmtd is exiting with status=1/FAILURE after generating the already mentioned error (I only looked at the mail-log and not at the syslog as I thought opensmtpd might be at least starting up successfully as the start command did not return an error (as it would if eg I had a syntax error in my config). So no ISP-Block. > Aug 15 23:17:25 mx01 smtpd[32458]: info: OpenSMTPD 6.0.3-portable starting > Aug 15 23:17:25 mx01 smtpd[32462]: warn: table-proc: pipe closed > Aug 15 23:17:25 mx01 smtpd[32462]: lookup: table-proc: exiting > Aug 15 23:17:25 mx01 smtpd[32459]: smtpd: process lka socket closed Is mysqld up and running? Have you verified from the command line that your username and password are correct? mysql --user=username --password=password dbase ** Yes! I even tried the command used in the mysql.conf (SELECT name FROM virtual_domains WHERE name=[mailhost];) which returned the expected hostname. # Host-System OS: Debian 10 OpenSMTPd: 6.0.3p1-5+deb10u4 Openssmtpd-extras: 5.7.1-4+b2 # /etc/smtpd.conf > # Variablen setzen > ipv4addr = [removed for privacy] > hostn = mx01.[removed for privacy] > > # komprimiert die Warteschlange, verschl??sselt sie und l??scht nach 4 > Stunden (optional) > #queue compression > #queue encryption key "[removed for privacy]" > #expire 4h > > # Zertifikate hinzuf??gen > pki mx01.[removed for privacy] certificate "/etc/ssl/mx01.[removed for > privacy].crt" > pki mx01.[removed for privacy] key "/etc/ssl/private/mx01.[removed for > privacy].key" > > # Relevante Tabellen laden > table domains mysql:/etc/mail/mysql.conf > table credentials mysql:/etc/mail/mysql.conf > > # Zuh??ren > listen on $ipv4addr port smtp tls > listen on $ipv4addr smtps pki mx01.mx.itsmind.dev auth <credentials> > listen on $ipv4addr port submission tls-require pki mx01.mx.itsmind.dev auth > <credentials> > > # E-Mails annehmen und weitergeben > accept from any for domain <domains> deliver to lmtp "mda1:24" > accept for any relay # /etc/mail/mysql.conf > host db.[removed for privacy] is your user allowed to connect to the host above? ** Which host do you mean? mx01 is allowed to connect to db (ha-proxy) and even db1, db2, db3 directly (which I also tried, but did not change anything). And the internet is allowed to connect to mx1. Or did you mean the mda1? mda1 is not yet set up. > username [removed for privacy] > password [removed for privacy] > database [removed for privacy] > > query_credentials SELECT email, password FROM virtual_users WHERE email=?; > query_domain SELECT name FROM virtual_domains WHERE name=?; > query_userinfo SELECT uid,gid,maildir FROM virtual_users WHERE email=?; > query_alias SELECT destination FROM virtual_aliases WHERE source=?; # Further explinations: What I've tried First I guess the error has something to do with the mysql-stuff. But I am really really confused about the whole mysql-tables thing and can't find a place where actually somebody explained (or documented) how it works. Have you tried: man table-mysql Perhaps its missing if so you can find it on github. ** I found the source for a man that sheds light on what those config options are for. But that actually doesn’t help me with the error which occurs or if they are needed. By taking a look at table_proc.c from the opensmdpd source on github I guess > warn: table-proc: pipe closed means that opensmtpd got an emty response when trying to do something (?) with a table. I am unsure what opensmtpd is trying to do with the table. Strangely it isn’t even trying to connect to the db-server (tcpdump unrevals that). So what we know: It has something to do with the mysql-tables. What I don’t understand is, what opensmtpd is trying to do which leads to that error. To my understanding opensmtpd should only try to connect to the database if it needs to read from the tables, which – if just starting up – obviously is not the case. In the beginning I thought it works like if I write > table domains mysql:/etc/mail/mysql.conf to the smtpd.conf the value domains is retrieved from what's stated after query_domains (query_domains because the name of the table is domains. So from my guess table example would translate to query_example). Therefor I only had one line query_domain SELECT name FROM virtual_domains WHERE name=?; in my mysql.conf. I've also tried using $1 instead of ?. After every conf I found in the internet (about 3) had query_credentials, query_domain, query_userinfo and query_alias I thought those are fixed terms, so I included them all in the mysql-config. In conclusion I think what I am trying to achive is not too complex: opensmtpd should 1. take e-mails on port 25, check via mysql if it's for a domain it is responsible for and then forward via lmtp to dovecot which then takes care of everything else (including rejecting unknown users). 2. Authenticate users on port 465 and 587 against mysql and forward their mails if successful. Later on I'd like to add rspamd and DKIM??? but one step at a time. I would be glad if anyone could shed some light on the whole mysql-hassle and knows what prevents my opensmtpd from doing what I want it to do. Thank you in advance! Fabian Edgar That seems to be a bit strange from my perspective. Thanks! Fabian
