RE: [OpenSMTPD-5.9.1] Postgresql Backend

[Olivier]

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 
; postmas...@domain.tld --> u...@domain.tld))
--> 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  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  virtual  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  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:open...@high5.nl] 
Envoyé : lundi 11 juillet 2016 16:58
À : Olivier
Cc : misc@opensmtpd.org
Objet : Re: [OpenSMTPD-5.9.1] Postgresql Backend


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

Re: [OpenSMTPD-5.9.1] Postgresql Backend

[Mischa]

> On 11 Jul 2016, at 16:37, Olivier  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: -> found
> lookup: check "burelli.fr" as DOMAIN in table static: -> 0
> lookup: check "local" as NETADDR in table static: -> 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: -> 
> "vmail:1001:1001:/var/vmail"
> lookup: lookup "vmail" as USERINFO in table 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  virtual  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: ?

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