Hello, @Mischa, thanks a lot for your explanations. Following your suggestions :
I designed new database. I will see later to alter some table to add some details (name, location or anything needed) I keeped vmail as destination for virtual users. I designed it (with constraint ON DELETE CASCADE) ; GOAL is, if we delete : _ One domain, all virtual users will be deleted _ One virtual user, credential will be deleted (and in cascade if one domain is deleted) (I have to think also about the deletion of one virtual destination (alias) (ex ; [email protected] --> [email protected])) --> I have to study what is the comportment of the database with 2 foreign key. I modified pgsql.conf to parse only activated mailboxes. F.Y.I. PGSQL schemas: CREATE SEQUENCE seq_vmail_vdomains_id START 1; CREATE SEQUENCE seq_vmail_vusers_id START 1; CREATE SEQUENCE seq_vmail_credentials_id START 1; CREATE TABLE vdomains ( id INT NOT NULL DEFAULT nextval('seq_vmail_vdomains_id'), domain varchar(40) NOT NULL UNIQUE, PRIMARY KEY (id) ); CREATE TABLE vusers ( id INT NOT NULL DEFAULT nextval('seq_vmail_vusers_id'), email VARCHAR(60) NOT NULL UNIQUE, domain VARCHAR(40) NOT NULL, destination VARCHAR(60) NOT NULL DEFAULT 'vmail', PRIMARY KEY (id), FOREIGN KEY (domain) REFERENCES vdomains(domain) ON DELETE CASCADE ); CREATE TABLE credentials ( id INT NOT NULL DEFAULT nextval('seq_vmail_credentials_id'), email VARCHAR(60) NOT NULL, password VARCHAR(60), active BOOLEAN DEFAULT 'TRUE' NOT NULL, PRIMARY KEY (id), FOREIGN KEY (email) REFERENCES vusers(email) ON DELETE CASCADE ); ### /etc/mail/pgsql.conf # smtpd.conf: table users pgsql:/etc/mail/pgsql.conf conninfo host='127.0.0.1' user='DBUSER' password='DBPASSWORD' dbname='DBNAME' # Alias lookup query query_alias select destination from vusers where email=$1; # Domain lookup query query_domain select domain from vdomains where domain=$1; # Credentials lookup query query_credentials select email, password from credentials where email=$1 and active = 'Y'; ### /etc/mail/smtpd.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 accept from local for local alias <aliases> deliver to mbox ## Relay # Tagged mail returned from DKIM accept tagged DKIM_OUT for any relay # Tagged mail returned from clamsmtpd to ... accept tagged CLAM_IN for domain <vdomains> virtual <valiases> deliver to lmtp "/var/dovecot/lmtp" rcpt-to # deliver via lmtp (spamassassin filter is used) accept tagged CLAM_OUT for any relay via smtp://127.0.0.1:10029 # send to dkimproxy_out # Start here (inbound) accept from any for domain <vdomains> relay via smtp://127.0.0.1:10035 # to clamd via clamsmtpd_in accept from local for any relay #via smtp://127.0.0.1:10037 # to clamd via clamsmtpd_out -----Message d'origine----- De : Mischa [mailto:[email protected]] Envoyé : lundi 11 juillet 2016 16:58 À : Olivier Cc : [email protected] Objet : Re: [OpenSMTPD-5.9.1] Postgresql Backend > On 11 Jul 2016, at 16:37, Olivier <[email protected]> wrote: > > 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) I would suggest to have 3 separate tables. - domains - aliases - credentials This will give you a lot more flexibility later. > è 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 ? Yes > _ Seems maybe I not understood the delivery side, made i a mistake ? If you don't have the vmail user OpenSMTPD doesn't know which user to hand this off to. The "query_userinfo" configuration is not mandatory and you can leave that out if you want. I use the following smtpd.conf for my delivery, modified to your config: accept from any for domain <vdomains> virtual <valiases> deliver to maildir "/var/vmail/%d/%{dest:lowercase}/" And I am using this with sqlite: query_alias select destination from virtuals where email=?; query_credentials select email, password from credentials where email=?; query_domain select domain from domains where domain=?; I am using the following SQL schema: CREATE TABLE domains ( id INTEGER PRIMARY KEY AUTOINCREMENT, domain VARCHAR(255) NOT NULL ); CREATE TABLE virtuals ( id INTEGER PRIMARY KEY AUTOINCREMENT, email VARCHAR(255) NOT NULL, destination VARCHAR(255) NOT NULL ); CREATE TABLE credentials ( id INTEGER PRIMARY KEY AUTOINCREMENT, email VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL ); > If not, what is the role & functions of the query_userinfo ? I have been trying to figure the use of query_userinfo out as well, haven't been able to figure it out yet. > è Do I have to fill specific home : /var/vmail/%d/%u to the db / > query_userinfo ? No > è Does query_userinfo fill a proc:<table> ? No idea. > 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 Giovanni’s presentation : > opensmtpd-linuxcon2015) The syntax used is working fine on my side. > è Where can I find all last informations or correct syntax for it ? I think it depends on the backend you are using. Mischa -- You received this mail because you are subscribed to [email protected] To unsubscribe, send a mail to: [email protected]
