Hello,
I back to you to share with you and request help on my misunderstood. OS : OpenBSD 5.9- stable SMTPD : OpenSMTPD 5.9.1 My goal : OpenSMTPD has to _ deliver local email (for [email protected] _ deliver email to authorized virtual domains. All email to [email protected] has to be forwarded to [email protected] For example In /etc/mail/aliases à root: [email protected] /etc/mail/mtpd.conf ## Define Table table aliases db:/etc/mail/aliases.db table vdomains postgres:/etc/mail/pgsql.conf table passwd postgres:/etc/mail/pgsql.conf table valiases postgres:/etc/mail/pgsql.conf According to my understanding about SQL backend for opensmtpd, we have to create a sql conf file for smtpd, where : _ query_credentials is used to accept or not, user for submission (AUTH LOGIN) _ query_domain is used to validate the domain delivery _ query_userinfo is used to check email delivery à if mailboxes exists or not ??? _ query alias provides alias for a virtual account ### /etc/mail/pgsql.conf conninfo host='127.0.0.1' user='DBUSER' password=PASSWORD' dbname='DBNAME' query_alias select alias from vusers where account=$1; query_domain select domain from vusers where domain=$1; query_userinfo select 1001,1001,'/var/vmail' from vusers where account=$1; query_credentials select account, password from vusers where account=$1; ### So I created PostgreSQL database (one table) : CREATE SEQUENCE seq_vmail_users_id START 1; CREATE TABLE vusers ( id INT2 NOT NULL DEFAULT nextval('seq_vmail_users_id'), account VARCHAR(60) NOT NULL UNIQUE, domain VARCHAR(40) NOT NULL, password VARCHAR(71) NOT NULL, active CHAR(1) DEFAULT 'Y' NOT NULL, à for dovecot PRIMARY KEY (id), ); In fact i had to add an another column : alias(VARCHAR(60)) with local vmail user for each virtual account (however with physical store in /var/vmail/%d/%u) (this column is also used to provide aliases, ex. [email protected], to smtpd) è Without this vmail alias I have an error : 550 Invalid recipient è All is ok to deliver : lookup: check "burelli.fr" as DOMAIN in table proc:vdomains -> found lookup: lookup "[email protected]" as ALIAS in table proc:valiases -> "[email protected]" debug: aliases_virtual_get: '[email protected]' resolved to 1 nodes lookup: check "local" as NETADDR in table static:<localhost> -> found lookup: check "burelli.fr" as DOMAIN in table static:<localnames> -> 0 lookup: check "local" as NETADDR in table static:<localhost> -> found lookup: check "burelli.fr" as DOMAIN in table proc:vdomains -> found lookup: lookup "[email protected]" as ALIAS in table proc:valiases -> "vmail" debug: aliases_virtual_get: '[email protected]' resolved to 1 nodes lookup: lookup "vmail" as ALIAS in table proc:valiases -> 0 lookup: lookup "vmail" as USERINFO in table getpwnam:<getpwnam> -> "vmail:1001:1001:/var/vmail" lookup: lookup "vmail" as USERINFO in table getpwnam:<getpwnam> -> "vmail:1001:1001:/var/vmail" QUESTION : _ Is it mandatory to provide the local user vmail as alias for all virtual email accounts ? _ Seems maybe I not understood the delivery side, made i a mistake ? If not, what is the role & functions of the query_userinfo ? è Do I have to fill specific home : /var/vmail/%d/%u to the db / query_userinfo ? è Does query_userinfo fill a proc:<table> ? In parallel I used the syntax : _ query_credentials select account, password from vusers where account=$1; instead _ query_credentials select account, password from vusers where account=?; (from the Giovannis presentation : opensmtpd-linuxcon2015) The syntax used is working fine on my side. è Where can I find all last informations or correct syntax for it ? In fact I would isolate (with constraints), aliases, domains, users (to authenticate) and currently is not possible. I would not perform an alias to vmail for each virtual users. Thanks in advance, have a nice day. Best regards, Olivier Burelli.
