> 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]
