> On 11 Jul 2016, at 16:37, Olivier <oliv...@burelli.fr> 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 u...@hostname.domain.tld
> _ deliver email to authorized virtual domains. All email to 
> r...@hostname.domain.tld has to be forwarded to u...@domain.tld
> For example In /etc/mail/aliases à root: ad...@burelli.fr
>  
> /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. webmas...@domain.tld, 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 "webmas...@burelli.fr" as ALIAS in table proc:valiases -> 
> "u...@burelli.fr"
> debug: aliases_virtual_get: 'webmas...@burelli.fr' 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 "u...@burelli.fr" as ALIAS in table proc:valiases -> "vmail"
> debug: aliases_virtual_get: 'u...@burelli.fr' 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 misc@opensmtpd.org
To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org

Reply via email to