Re: userbase question

2018-09-01 Thread Matt Schwartz
Hi Gilles,

Thank you for your advice about using wrappers. I decided to implement
an mda wrapper as per your suggestion. It is interesting that I still
needed to specify either an mbox or maildir in the syntax when I
specify a wrapper. In this case, it doesn't seem to matter if I use
mbox or maildir because dovecot's LDA is doing the final delivery.
This works but I might be doing it wrong.

action "local" mbox wrapper "deliver" alias 
action "domain" mbox wrapper "deliver" virtual 
match for local action "local"
match from any for domain  action "domain"

I have to agree that using the mda wrapper feature is a heck of a lot
cleaner. I am even going to do some testing using OpenSMTPD for final
delivery now that there is explicit support for junk mail delivery. I
think the reason that the userbase didn't work is that I am using
dovecot for final delivery of the email. Below is a patch for the
smtpd.conf(8) man page to reflect where to use the wrapper specified
by mda wrapper.

--- smtpd.conf.5Sat Sep  1 08:52:32 2018
+++ smtpd.conf.5 Sat Sep  1 08:55:23 2018
@@ -156,6 +156,9 @@
 .Pq see Sx FORMAT SPECIFIERS .
 .It Cm relay
 Relay the message to another SMTP server.
+.It Cm wrapper Ar name
+Use a wrapper specified by
+.Cm mda wrapper .It command.
 .El
 .Pp
 The local delivery methods support additional options:
On Sat, Sep 1, 2018 at 8:01 AM Gilles Chehade  wrote:
>
> On Mon, Aug 27, 2018 at 09:54:05AM -0400, Matt Schwartz wrote:
> > I am hoping not to have to use sqlite tables. I like the simplicity of
> > file-based configuration.
>
> just for the record:
>
> besides table-specific features, all smtpd features are usable from file
> configurations since I write the features for the file backend _then_ we
> adapt the other backends.
>
>
>
> > On Mon, Aug 27, 2018 at 9:47 AM Reio Remma  wrote:
> > >
> > > Iirc I got the .forward file working with sqlite tables, where the user 
> > > query also returned the virtual user???s maildir as an extra parameter.
> > >
> > > Good luck,
> > > Reio
> > >
> > > > On 27 Aug 2018, at 16:11, Matt Schwartz  
> > > > wrote:
> > > >
> > > > Hello misc@,
> > > >
> > > > Below is my configuration file. I am trying to use the userbase
> > > > parameter and when I try to send an email to myself, I get the 550
> > > > Invalid Recipient error. I am trying to get the usrbase parameter
> > > > working so that I can add a .forward file for virtual users as per the
> > > > table(5) man page. If I don't use the userbase parameter, mail
> > > > delivery works just fine. I am not certain what I am doing wrong here.
> > > >
> > > > #smtpd.conf
> > > > pki mail cert "/etc/ssl/smtpd.crt"
> > > > pki mail key "/etc/ssl/private/smtpd.key"
> > > >
> > > > table aliases file:/etc/mail/aliases
> > > > table addrnames file:/etc/mail/addrnames
> > > > table credentials file:/etc/mail/credentials
> > > > table domains file:/etc/mail/domains
> > > > table virtuals file:/etc/mail/virtuals
> > > > table usrbase file:/etc/mail/usrbase
> > > > table rejects file:/etc/mail/rejects
> > > >
> > > > # Listeners
> > > > #
> > > > listen on lo0
> > > > listen on lo0 port 10028 tag DKIM
> > > > listen on vio0 tls pki mail hostnames 
> > > > listen on vio0 port 587 tls-require pki mail auth  \
> > > >hostnames 
> > > >
> > > > # Actions
> > > > #
> > > > action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec
> > > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a
> > > > %{rcpt}'" alias 
> > > > action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec
> > > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a
> > > > %{rcpt}'" userbase  virtual 
> > > > action "dkim" relay host smtp://127.0.0.1:10027
> > > > action "relay" relay
> > > >
> > > > # Incoming
> > > > #
> > > > match from any mail-from  for any reject
> > > > match from local for local action "local"
> > > > match from any for domain  action "domain"
> > > >
> > > > # Outgoing
> > > > #
> > > > match tag DKIM for any action "relay"
> > > > match from local for any action "dkim"
> > > > match auth from any for any action "dkim"
> > > >
> > > > #usrbase
> > > > m...@example.org 2000:2000:/var/vmail/example.org/matt
> > > >
> > > > #virtuals
> > > > m...@example.org vmail
> > > >
> > > > Thanks in advance,
> > > > Matt
> > > >
> > > > --
> > > > You received this mail because you are subscribed to misc@opensmtpd.org
> > > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
> > > >
> > >
> > >
> > > --
> > > You received this mail because you are subscribed to misc@opensmtpd.org
> > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
> > >
> >
> > --
> > You received this mail because you are subscribed to misc@opensmtpd.org
> > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
> >
>
> --
> Gilles Chehade
>
> https://www.poolp.org  @poolpOrg

-- 
You received this mail because you 

Re: userbase question

2018-09-01 Thread Gilles Chehade
On Mon, Aug 27, 2018 at 09:54:05AM -0400, Matt Schwartz wrote:
> I am hoping not to have to use sqlite tables. I like the simplicity of
> file-based configuration.

just for the record:

besides table-specific features, all smtpd features are usable from file
configurations since I write the features for the file backend _then_ we
adapt the other backends.



> On Mon, Aug 27, 2018 at 9:47 AM Reio Remma  wrote:
> >
> > Iirc I got the .forward file working with sqlite tables, where the user 
> > query also returned the virtual user???s maildir as an extra parameter.
> >
> > Good luck,
> > Reio
> >
> > > On 27 Aug 2018, at 16:11, Matt Schwartz  wrote:
> > >
> > > Hello misc@,
> > >
> > > Below is my configuration file. I am trying to use the userbase
> > > parameter and when I try to send an email to myself, I get the 550
> > > Invalid Recipient error. I am trying to get the usrbase parameter
> > > working so that I can add a .forward file for virtual users as per the
> > > table(5) man page. If I don't use the userbase parameter, mail
> > > delivery works just fine. I am not certain what I am doing wrong here.
> > >
> > > #smtpd.conf
> > > pki mail cert "/etc/ssl/smtpd.crt"
> > > pki mail key "/etc/ssl/private/smtpd.key"
> > >
> > > table aliases file:/etc/mail/aliases
> > > table addrnames file:/etc/mail/addrnames
> > > table credentials file:/etc/mail/credentials
> > > table domains file:/etc/mail/domains
> > > table virtuals file:/etc/mail/virtuals
> > > table usrbase file:/etc/mail/usrbase
> > > table rejects file:/etc/mail/rejects
> > >
> > > # Listeners
> > > #
> > > listen on lo0
> > > listen on lo0 port 10028 tag DKIM
> > > listen on vio0 tls pki mail hostnames 
> > > listen on vio0 port 587 tls-require pki mail auth  \
> > >hostnames 
> > >
> > > # Actions
> > > #
> > > action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec
> > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a
> > > %{rcpt}'" alias 
> > > action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec
> > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a
> > > %{rcpt}'" userbase  virtual 
> > > action "dkim" relay host smtp://127.0.0.1:10027
> > > action "relay" relay
> > >
> > > # Incoming
> > > #
> > > match from any mail-from  for any reject
> > > match from local for local action "local"
> > > match from any for domain  action "domain"
> > >
> > > # Outgoing
> > > #
> > > match tag DKIM for any action "relay"
> > > match from local for any action "dkim"
> > > match auth from any for any action "dkim"
> > >
> > > #usrbase
> > > m...@example.org 2000:2000:/var/vmail/example.org/matt
> > >
> > > #virtuals
> > > m...@example.org vmail
> > >
> > > Thanks in advance,
> > > Matt
> > >
> > > --
> > > You received this mail because you are subscribed to misc@opensmtpd.org
> > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
> > >
> >
> >
> > --
> > You received this mail because you are subscribed to misc@opensmtpd.org
> > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
> >
> 
> --
> You received this mail because you are subscribed to misc@opensmtpd.org
> To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
> 

-- 
Gilles Chehade

https://www.poolp.org  @poolpOrg

-- 
You received this mail because you are subscribed to misc@opensmtpd.org
To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org



Re: userbase question

2018-09-01 Thread Gilles Chehade
On Mon, Aug 27, 2018 at 09:11:02AM -0400, Matt Schwartz wrote:
> Hello misc@,
> 
> Below is my configuration file. I am trying to use the userbase
> parameter and when I try to send an email to myself, I get the 550
> Invalid Recipient error. I am trying to get the usrbase parameter
> working so that I can add a .forward file for virtual users as per the
> table(5) man page. If I don't use the userbase parameter, mail
> delivery works just fine. I am not certain what I am doing wrong here.
> 
> #smtpd.conf
> pki mail cert "/etc/ssl/smtpd.crt"
> pki mail key "/etc/ssl/private/smtpd.key"
> 
> table aliases file:/etc/mail/aliases
> table addrnames file:/etc/mail/addrnames
> table credentials file:/etc/mail/credentials
> table domains file:/etc/mail/domains
> table virtuals file:/etc/mail/virtuals
> table usrbase file:/etc/mail/usrbase
> table rejects file:/etc/mail/rejects
> 
> # Listeners
> #
> listen on lo0
> listen on lo0 port 10028 tag DKIM
> listen on vio0 tls pki mail hostnames 
> listen on vio0 port 587 tls-require pki mail auth  \
> hostnames 
> 
> # Actions
> #
> action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec
> '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a
> %{rcpt}'" alias 
> action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec
> '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a
> %{rcpt}'" userbase  virtual 
> action "dkim" relay host smtp://127.0.0.1:10027
> action "relay" relay
> 

you might want to have a look at mda wrappers, it will simplify your
actions considerably ;-)


> # Incoming
> #
> match from any mail-from  for any reject
> match from local for local action "local"
> match from any for domain  action "domain"
> 
> # Outgoing
> #
> match tag DKIM for any action "relay"
> match from local for any action "dkim"
> match auth from any for any action "dkim"
> 
> #usrbase
> m...@example.org 2000:2000:/var/vmail/example.org/matt
> 

userbase maps a user to an account, so you shouldn't use an email address
here, it should be 'vmail' since that's what you use as the delivery user
in your virtuals table below:

> #virtuals
> m...@example.org vmail
> 


-- 
Gilles Chehade

https://www.poolp.org  @poolpOrg

-- 
You received this mail because you are subscribed to misc@opensmtpd.org
To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org



Re: userbase question

2018-08-27 Thread Edgar Pettijohn
Sent from my Verizon SmartphoneOn Aug 27, 2018 8:54 AM, Matt Schwartz  wrote:>> I am hoping not to have to use sqlite tables. I like the simplicity of > file-based configuration. > On Mon, Aug 27, 2018 at 9:47 AM Reio Remma  wrote: > > > > Iirc I got the .forward file working with sqlite tables, where the user query also returned the virtual user’s maildir as an extra parameter. > > > > Good luck, > > Reio > > > > > On 27 Aug 2018, at 16:11, Matt Schwartz  wrote: > > > > > > Hello misc@, > > > > > > Below is my configuration file. I am trying to use the userbase > > > parameter and when I try to send an email to myself, I get the 550 > > > Invalid Recipient error. I am trying to get the usrbase parameter > > > working so that I can add a .forward file for virtual users as per the > > > table(5) man page. If I don't use the userbase parameter, mail > > > delivery works just fine. I am not certain what I am doing wrong here. > > > > > > #smtpd.conf > > > pki mail cert "/etc/ssl/smtpd.crt" > > > pki mail key "/etc/ssl/private/smtpd.key" > > > > > > table aliases file:/etc/mail/aliases > > > table addrnames file:/etc/mail/addrnames > > > table credentials file:/etc/mail/credentials > > > table domains file:/etc/mail/domains > > > table virtuals file:/etc/mail/virtuals > > > table usrbase file:/etc/mail/usrbase > > > table rejects file:/etc/mail/rejects > > > > > > # Listeners > > > # > > > listen on lo0 > > > listen on lo0 port 10028 tag DKIM > > > listen on vio0 tls pki mail hostnames  > > > listen on vio0 port 587 tls-require pki mail auth  \ > > >    hostnames  > > > > > > # Actions > > > # > > > action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > > > %{rcpt}'" alias  > > > action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > > > %{rcpt}'" userbase  virtual  > > > action "dkim" relay host smtp://127.0.0.1:10027 > > > action "relay" relay > > > > > > # Incoming > > > # > > > match from any mail-from  for any reject > > > match from local for local action "local" > > > match from any for domain  action "domain" > > > > > > # Outgoing > > > # > > > match tag DKIM for any action "relay" > > > match from local for any action "dkim" > > > match auth from any for any action "dkim" > > > > > > #usrbase > > > m...@example.org 2000:2000:/var/vmail/example.org/matt > > > > > > #virtuals > > > m...@example.org vmail > > > > > > Thanks in advance, > > > Matt > > > > > > -- > > > You received this mail because you are subscribed to misc@opensmtpd.org > > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > > > > > > > > > -- > > You received this mail because you are subscribed to misc@opensmtpd.org > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > > >> -- > You received this mail because you are subscribed to misc@opensmtpd.org > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org >It seems to be a bug. Look at the thread about forwarding a single email. He has the same issue. I switched to MySQL tables about a year ago and it is so much easier. 

Re: userbase question

2018-08-27 Thread Matt Schwartz
I am hoping not to have to use sqlite tables. I like the simplicity of
file-based configuration.
On Mon, Aug 27, 2018 at 9:47 AM Reio Remma  wrote:
>
> Iirc I got the .forward file working with sqlite tables, where the user query 
> also returned the virtual user’s maildir as an extra parameter.
>
> Good luck,
> Reio
>
> > On 27 Aug 2018, at 16:11, Matt Schwartz  wrote:
> >
> > Hello misc@,
> >
> > Below is my configuration file. I am trying to use the userbase
> > parameter and when I try to send an email to myself, I get the 550
> > Invalid Recipient error. I am trying to get the usrbase parameter
> > working so that I can add a .forward file for virtual users as per the
> > table(5) man page. If I don't use the userbase parameter, mail
> > delivery works just fine. I am not certain what I am doing wrong here.
> >
> > #smtpd.conf
> > pki mail cert "/etc/ssl/smtpd.crt"
> > pki mail key "/etc/ssl/private/smtpd.key"
> >
> > table aliases file:/etc/mail/aliases
> > table addrnames file:/etc/mail/addrnames
> > table credentials file:/etc/mail/credentials
> > table domains file:/etc/mail/domains
> > table virtuals file:/etc/mail/virtuals
> > table usrbase file:/etc/mail/usrbase
> > table rejects file:/etc/mail/rejects
> >
> > # Listeners
> > #
> > listen on lo0
> > listen on lo0 port 10028 tag DKIM
> > listen on vio0 tls pki mail hostnames 
> > listen on vio0 port 587 tls-require pki mail auth  \
> >hostnames 
> >
> > # Actions
> > #
> > action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec
> > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a
> > %{rcpt}'" alias 
> > action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec
> > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a
> > %{rcpt}'" userbase  virtual 
> > action "dkim" relay host smtp://127.0.0.1:10027
> > action "relay" relay
> >
> > # Incoming
> > #
> > match from any mail-from  for any reject
> > match from local for local action "local"
> > match from any for domain  action "domain"
> >
> > # Outgoing
> > #
> > match tag DKIM for any action "relay"
> > match from local for any action "dkim"
> > match auth from any for any action "dkim"
> >
> > #usrbase
> > m...@example.org 2000:2000:/var/vmail/example.org/matt
> >
> > #virtuals
> > m...@example.org vmail
> >
> > Thanks in advance,
> > Matt
> >
> > --
> > You received this mail because you are subscribed to misc@opensmtpd.org
> > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
> >
>
>
> --
> You received this mail because you are subscribed to misc@opensmtpd.org
> To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
>

--
You received this mail because you are subscribed to misc@opensmtpd.org
To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org



Re: userbase question

2018-08-27 Thread Reio Remma
Iirc I got the .forward file working with sqlite tables, where the user query 
also returned the virtual user’s maildir as an extra parameter.

Good luck,
Reio

> On 27 Aug 2018, at 16:11, Matt Schwartz  wrote:
> 
> Hello misc@,
> 
> Below is my configuration file. I am trying to use the userbase
> parameter and when I try to send an email to myself, I get the 550
> Invalid Recipient error. I am trying to get the usrbase parameter
> working so that I can add a .forward file for virtual users as per the
> table(5) man page. If I don't use the userbase parameter, mail
> delivery works just fine. I am not certain what I am doing wrong here.
> 
> #smtpd.conf
> pki mail cert "/etc/ssl/smtpd.crt"
> pki mail key "/etc/ssl/private/smtpd.key"
> 
> table aliases file:/etc/mail/aliases
> table addrnames file:/etc/mail/addrnames
> table credentials file:/etc/mail/credentials
> table domains file:/etc/mail/domains
> table virtuals file:/etc/mail/virtuals
> table usrbase file:/etc/mail/usrbase
> table rejects file:/etc/mail/rejects
> 
> # Listeners
> #
> listen on lo0
> listen on lo0 port 10028 tag DKIM
> listen on vio0 tls pki mail hostnames 
> listen on vio0 port 587 tls-require pki mail auth  \
>hostnames 
> 
> # Actions
> #
> action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec
> '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a
> %{rcpt}'" alias 
> action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec
> '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a
> %{rcpt}'" userbase  virtual 
> action "dkim" relay host smtp://127.0.0.1:10027
> action "relay" relay
> 
> # Incoming
> #
> match from any mail-from  for any reject
> match from local for local action "local"
> match from any for domain  action "domain"
> 
> # Outgoing
> #
> match tag DKIM for any action "relay"
> match from local for any action "dkim"
> match auth from any for any action "dkim"
> 
> #usrbase
> m...@example.org 2000:2000:/var/vmail/example.org/matt
> 
> #virtuals
> m...@example.org vmail
> 
> Thanks in advance,
> Matt
> 
> -- 
> You received this mail because you are subscribed to misc@opensmtpd.org
> To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
> 


--
You received this mail because you are subscribed to misc@opensmtpd.org
To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org



Re: Userbase question.

2018-02-04 Thread Reio Remma

On 04.02.2018 22:21, Reio Remma wrote:

The only reference I've found that tackles a similar problem:

https://hugo.barrera.io/journal/2015/02/15/opensmtpd-dovecot-shared-sql-db/

Rather convoluted for a simple thing though. :)

Reio


On 04.02.2018 22:01, Reio Remma wrote:

On 04.02.2018 21:56, Reio Remma wrote:

Hello!

query_userinfo select 1001,1001,'/var/vmail/' from vusers where 
email=$1;


I now realize you version doesn't return the actual user's virtual 
mail directory. But maybe it doesn't need to. I suspect Dovecot can 
handle .forward files as well, though it would be nice if they were 
checked without turning to Dovecot.


In my setup currently OpenSMTPD can use .forward files by itself, 
which is nice.




Does it match against an actual (whole) e-mail addess or username 
for you?


What does your "accept for domain ..." line in smtpd.conf look like?

All the best,
Reio

On 04.02.2018 21:11, Olivier wrote:


Hello

I am using my own server with a postgresql database to store 
domains, users & password:


vmail=> \d

List of relations

Schema |   Name   |   Type   | Owner

+--+--+---

public | credentials  | table    | vmail

public | seq_vmail_credentials_id | sequence | vmail

public | seq_vmail_vdomains_id    | sequence | vmail

public | seq_vmail_vusers_id  | sequence | vmail

public | vdomains | table    | vmail

public | vusers   | table    | vmail

This database is used by dovecot & opensmtpd for authentication 
(for encoding: use smtpctl encrypt).


All email are stored in the Maildir format, and store in the disk:

/var/vmail/some.domain.tld/SomeUsers/Maildir

For this, following packages have to be installed

_opensmtpd-extras-201703132115p1 extras

_opensmtpd-extras-pgsql-201703132115p1

Extract from 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

(…)

Below, the database interface  (/etc/mail/pgsql.conf)

# smtpd.conf: table users pgsql:/etc/mail/pgsql.conf

conninfo host='myHost' user=’myUser' password='myPassword' 
dbname='myDBName'


# Alias lookup query

#

query_alias select destination from myRelation where email=$1;

#

# Domain lookup query

#

query_domain select domain from myRelation where domain=$1;

#

# User lookup query

#

#query_userinfo select 1001,1001,'/var/vmail/' from vusers where 
email=$1;


#

# Credentials lookup query

#

query_credentials select email, password from credentials where 
email=$1 and active = 'Y';  # <-- here your SQL request


Sorry for my bad english. I hope that it will be helping.

Olivier.

*De :*Reio Remma [mailto:r...@mrstuudio.ee]
*Envoyé :* Sunday, February 4, 2018 3:02 PM
*À :* misc@opensmtpd.org
*Objet :* Userbase question.

Hello!

I'm trying to figure out how I can have virtual domains/users 
working completely decoupled from system users.


Every virtual/alias path seem to want to end up at a system account 
so I'm trying to use userbase, but userbase seems to take username 
without the domain part as key.


query_userinfo  SELECT 5000, 5000, 
CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir 
FROM users WHERE username = ?;


domain-one.com
- bob
- emily

domain-two.com
- john
- albert
- bob (not the same bob as b...@domain-one.com 
)


Mail sent to b...@domain-two.com  will 
end up at b...@domain-one.com  mailbox.


Am I missing something about using virtuals in general? I'm 
starting to feel a little stupid here. :)


Thanks,
Reio



I think I may have solved it (with a similar approach to Hugo Barrera's).

OpenSMTPD now sees all virtual maildirs' .forward files etc.

query_alias SELECT CONCAT( username, '_', domain ) FROM 
users WHERE email = ?;

query_domain    SELECT domain FROM users WHERE domain = ? LIMIT 1;
query_userinfo  SELECT 5000, 5000, 
CONCAT('/home/dovecot/domains/', domain, '/', username ) as maildir FROM 
users WHERE
                                        domain = SUBSTRING_INDEX( @u := 
?, "_", -1 ) AND
                                        username = TRIM( TRAILING 
CONCAT('_', SUBSTRING_INDEX( @u, "_", -1 ) ) FROM @u );


I do hope query_userinfo will one day accept a second parameter (domain).

Good night!
Reio


Re: Userbase question.

2018-02-04 Thread Reio Remma

The only reference I've found that tackles a similar problem:

https://hugo.barrera.io/journal/2015/02/15/opensmtpd-dovecot-shared-sql-db/

Rather convoluted for a simple thing though. :)

Reio


On 04.02.2018 22:01, Reio Remma wrote:

On 04.02.2018 21:56, Reio Remma wrote:

Hello!

query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1;


I now realize you version doesn't return the actual user's virtual 
mail directory. But maybe it doesn't need to. I suspect Dovecot can 
handle .forward files as well, though it would be nice if they were 
checked without turning to Dovecot.


In my setup currently OpenSMTPD can use .forward files by itself, 
which is nice.




Does it match against an actual (whole) e-mail addess or username for 
you?


What does your "accept for domain ..." line in smtpd.conf look like?

All the best,
Reio

On 04.02.2018 21:11, Olivier wrote:


Hello

I am using my own server with a postgresql database to store 
domains, users & password:


vmail=> \d

List of relations

Schema |   Name   |   Type   | Owner

+--+--+---

public | credentials  | table    | vmail

public | seq_vmail_credentials_id | sequence | vmail

public | seq_vmail_vdomains_id    | sequence | vmail

public | seq_vmail_vusers_id  | sequence | vmail

public | vdomains | table    | vmail

public | vusers   | table    | vmail

This database is used by dovecot & opensmtpd for authentication (for 
encoding: use smtpctl encrypt).


All email are stored in the Maildir format, and store in the disk:

/var/vmail/some.domain.tld/SomeUsers/Maildir

For this, following packages have to be installed

_opensmtpd-extras-201703132115p1 extras

_opensmtpd-extras-pgsql-201703132115p1

Extract from 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

(…)

Below, the database interface  (/etc/mail/pgsql.conf)

# smtpd.conf: table users pgsql:/etc/mail/pgsql.conf

conninfo host='myHost' user=’myUser' password='myPassword' 
dbname='myDBName'


# Alias lookup query

#

query_alias select destination from myRelation where email=$1;

#

# Domain lookup query

#

query_domain select domain from myRelation where domain=$1;

#

# User lookup query

#

#query_userinfo select 1001,1001,'/var/vmail/' from vusers where 
email=$1;


#

# Credentials lookup query

#

query_credentials select email, password from credentials where 
email=$1 and active = 'Y';  # <-- here your SQL request


Sorry for my bad english. I hope that it will be helping.

Olivier.

*De :*Reio Remma [mailto:r...@mrstuudio.ee]
*Envoyé :* Sunday, February 4, 2018 3:02 PM
*À :* misc@opensmtpd.org
*Objet :* Userbase question.

Hello!

I'm trying to figure out how I can have virtual domains/users 
working completely decoupled from system users.


Every virtual/alias path seem to want to end up at a system account 
so I'm trying to use userbase, but userbase seems to take username 
without the domain part as key.


query_userinfo  SELECT 5000, 5000, 
CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir 
FROM users WHERE username = ?;


domain-one.com
- bob
- emily

domain-two.com
- john
- albert
- bob (not the same bob as b...@domain-one.com 
)


Mail sent to b...@domain-two.com  will end 
up at b...@domain-one.com  mailbox.


Am I missing something about using virtuals in general? I'm starting 
to feel a little stupid here. :)


Thanks,
Reio









Re: Userbase question.

2018-02-04 Thread Reio Remma

On 04.02.2018 21:56, Reio Remma wrote:

Hello!

query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1;


I now realize you version doesn't return the actual user's virtual mail 
directory. But maybe it doesn't need to. I suspect Dovecot can handle 
.forward files as well, though it would be nice if they were checked 
without turning to Dovecot.


In my setup currently OpenSMTPD can use .forward files by itself, which 
is nice.




Does it match against an actual (whole) e-mail addess or username for you?

What does your "accept for domain ..." line in smtpd.conf look like?

All the best,
Reio

On 04.02.2018 21:11, Olivier wrote:


Hello

I am using my own server with a postgresql database to store domains, 
users & password:


vmail=> \d

List of relations

Schema |   Name   |   Type   | Owner

+--+--+---

public | credentials  | table    | vmail

public | seq_vmail_credentials_id | sequence | vmail

public | seq_vmail_vdomains_id    | sequence | vmail

public | seq_vmail_vusers_id  | sequence | vmail

public | vdomains | table    | vmail

public | vusers   | table    | vmail

This database is used by dovecot & opensmtpd for authentication (for 
encoding: use smtpctl encrypt).


All email are stored in the Maildir format, and store in the disk:

/var/vmail/some.domain.tld/SomeUsers/Maildir

For this, following packages have to be installed

_opensmtpd-extras-201703132115p1 extras

_opensmtpd-extras-pgsql-201703132115p1

Extract from 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

(…)

Below, the database interface  (/etc/mail/pgsql.conf)

# smtpd.conf: table users pgsql:/etc/mail/pgsql.conf

conninfo host='myHost' user=’myUser' password='myPassword' 
dbname='myDBName'


# Alias lookup query

#

query_alias select destination from myRelation where email=$1;

#

# Domain lookup query

#

query_domain select domain from myRelation where domain=$1;

#

# User lookup query

#

#query_userinfo select 1001,1001,'/var/vmail/' from vusers where 
email=$1;


#

# Credentials lookup query

#

query_credentials select email, password from credentials where 
email=$1 and active = 'Y';  # <-- here your SQL request


Sorry for my bad english. I hope that it will be helping.

Olivier.

*De :*Reio Remma [mailto:r...@mrstuudio.ee]
*Envoyé :* Sunday, February 4, 2018 3:02 PM
*À :* misc@opensmtpd.org
*Objet :* Userbase question.

Hello!

I'm trying to figure out how I can have virtual domains/users working 
completely decoupled from system users.


Every virtual/alias path seem to want to end up at a system account 
so I'm trying to use userbase, but userbase seems to take username 
without the domain part as key.


query_userinfo  SELECT 5000, 5000, 
CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir 
FROM users WHERE username = ?;


domain-one.com
- bob
- emily

domain-two.com
- john
- albert
- bob (not the same bob as b...@domain-one.com 
)


Mail sent to b...@domain-two.com  will end 
up at b...@domain-one.com  mailbox.


Am I missing something about using virtuals in general? I'm starting 
to feel a little stupid here. :)


Thanks,
Reio







Re: Userbase question.

2018-02-04 Thread Reio Remma

Hello!

query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1;

Does it match against an actual (whole) e-mail addess or username for you?

What does your "accept for domain ..." line in smtpd.conf look like?

All the best,
Reio

On 04.02.2018 21:11, Olivier wrote:


Hello

I am using my own server with a postgresql database to store domains, 
users & password:


vmail=> \d

List of relations

Schema |   Name   |   Type   | Owner

+--+--+---

public | credentials  | table    | vmail

public | seq_vmail_credentials_id | sequence | vmail

public | seq_vmail_vdomains_id    | sequence | vmail

public | seq_vmail_vusers_id  | sequence | vmail

public | vdomains | table    | vmail

public | vusers   | table    | vmail

This database is used by dovecot & opensmtpd for authentication (for 
encoding: use smtpctl encrypt).


All email are stored in the Maildir format, and store in the disk:

/var/vmail/some.domain.tld/SomeUsers/Maildir

For this, following packages have to be installed

_opensmtpd-extras-201703132115p1 extras

_opensmtpd-extras-pgsql-201703132115p1

Extract from 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

(…)

Below, the database interface  (/etc/mail/pgsql.conf)

# smtpd.conf: table users pgsql:/etc/mail/pgsql.conf

conninfo host='myHost' user=’myUser' password='myPassword' 
dbname='myDBName'


# Alias lookup query

#

query_alias select destination from myRelation where email=$1;

#

# Domain lookup query

#

query_domain select domain from myRelation where domain=$1;

#

# User lookup query

#

#query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1;

#

# Credentials lookup query

#

query_credentials select email, password from credentials where 
email=$1 and active = 'Y';  # <-- here your SQL request


Sorry for my bad english. I hope that it will be helping.

Olivier.

*De :*Reio Remma [mailto:r...@mrstuudio.ee]
*Envoyé :* Sunday, February 4, 2018 3:02 PM
*À :* misc@opensmtpd.org
*Objet :* Userbase question.

Hello!

I'm trying to figure out how I can have virtual domains/users working 
completely decoupled from system users.


Every virtual/alias path seem to want to end up at a system account so 
I'm trying to use userbase, but userbase seems to take username 
without the domain part as key.


query_userinfo  SELECT 5000, 5000, 
CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir 
FROM users WHERE username = ?;


domain-one.com
- bob
- emily

domain-two.com
- john
- albert
- bob (not the same bob as b...@domain-one.com )

Mail sent to b...@domain-two.com  will end 
up at b...@domain-one.com  mailbox.


Am I missing something about using virtuals in general? I'm starting 
to feel a little stupid here. :)


Thanks,
Reio





RE: Userbase question.

2018-02-04 Thread Olivier
Hello

 

I am using my own server with a postgresql database to store domains, users & 
password:

 

vmail=> \d

  List of relations

Schema |   Name   |   Type   | Owner

+--+--+---

public | credentials  | table| vmail

public | seq_vmail_credentials_id | sequence | vmail

public | seq_vmail_vdomains_id| sequence | vmail

public | seq_vmail_vusers_id  | sequence | vmail

public | vdomains | table| vmail

public | vusers   | table| vmail

 

 

This database is used by dovecot & opensmtpd for authentication (for encoding: 
use smtpctl encrypt).

 

All email are stored in the Maildir format, and store in the disk:

/var/vmail/some.domain.tld/SomeUsers/Maildir

 

For this, following packages have to be installed

 

_opensmtpd-extras-201703132115p1 extras

_opensmtpd-extras-pgsql-201703132115p1

 

Extract from smtpd.conf:

(…)

###

#

## Define Table

#

table aliases   db:/etc/mail/aliases.db

table vdomains  postgres:/etc/mail/pgsql.conf

table passwdpostgres:/etc/mail/pgsql.conf

table valiases  postgres:/etc/mail/pgsql.conf

(…)

 

 

Below, the database interface  (/etc/mail/pgsql.conf)

 

# smtpd.conf: table users pgsql:/etc/mail/pgsql.conf

conninfo host='myHost' user=’myUser' password='myPassword' dbname='myDBName'

 

# Alias lookup query

#

query_alias select destination from myRelation where email=$1;

#

# Domain lookup query

#

query_domain select domain from myRelation where domain=$1;

#

# User lookup query

#

#query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1;

#

# Credentials lookup query

#

query_credentials select email, password from credentials where email=$1 and 
active = 'Y';  # <-- here your SQL request

 

Sorry for my bad english. I hope that it will be helping.

 

 

Olivier.

 

 

De : Reio Remma [mailto:r...@mrstuudio.ee] 
Envoyé : Sunday, February 4, 2018 3:02 PM
À : misc@opensmtpd.org
Objet : Userbase question.

 

Hello!

I'm trying to figure out how I can have virtual domains/users working 
completely decoupled from system users.

Every virtual/alias path seem to want to end up at a system account so I'm 
trying to use userbase, but userbase seems to take username without the domain 
part as key.

query_userinfo  SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', 
domain, '/', username ) AS homedir FROM users WHERE username = ?;

domain-one.com
- bob
- emily

domain-two.com
- john
- albert
- bob (not the same bob as b...@domain-one.com)

Mail sent to b...@domain-two.com will end up at b...@domain-one.com mailbox.

Am I missing something about using virtuals in general? I'm starting to feel a 
little stupid here. :)

Thanks,
Reio



Re: Userbase question.

2018-02-04 Thread Reio Remma

Hey!

uid/gid are for vmail (/home/dovecot directory). I've looked at the 
smtpd lookup trace and query_userinfo queries the database purely by 
user name (without domain part). That is essentially where all 
virtuality fails. :/ If the database was queried by the full e-mail 
address (not unlike the virtual alias query), I could extract the domain 
part easily and proceed from there.


In Dovecot I've specified the username + domain separately in MySQL 
lookups where clauses.


Thanks!
Reio


On 04.02.2018 19:18, Edgar Pettijohn wrote:


does the system have a uid and gid 5000? I'm using mysql myself, but i 
don't have a userinfo section.  I'm guessing it should still work the 
same as the userinfo table described in table(5) though. Unfortunantly 
I am no sql expert, so I would just recommend verifying that your 
query does what you expect it to do perhaps run it from the command 
line and see what you get.



On 02/04/18 10:32, Reio Remma wrote:

Current smtpd.conf below.

As I understand userbase is the only way to let OpenSMTPD know where 
to look for


table aliases  mysql:/etc/opensmtpd/mysql.conf
table domains mysql:/etc/opensmtpd/mysql.conf
table userinfo mysql:/etc/opensmtpd/mysql.conf
table credentials mysql:/etc/opensmtpd/mysql.conf

listen on 0.0.0.0 port 25 tls pki bwo.mrstuudio.ee
listen on 0.0.0.0 port 587 tls-require pki bwo.mrstuudio.ee auth 



listen on lo port 10025 tag Filtered
listen on lo port 10027 tag Signed

accept tagged Filtered for domain  virtual  
userbase  deliver to lmtp "/var/run/dovecot/lmtp" rcpt-to


accept from any for domain  relay via lmtp://127.0.0.1:10024

accept tagged Signed for any relay via tls://orc.mrstuudio.ee

accept from local for any relay via lmtp://127.0.0.1:10026

---

mysql.conf

query_alias SELECT username FROM users WHERE email = ?;
query_domain    SELECT domain FROM users WHERE domain = ? 
LIMIT 1;
query_userinfo  SELECT 5000, 5000, 
CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir 
FROM users WHERE username = ?;
query_credentials   SELECT username, password FROM users WHERE 
email = ?;


Thanks,
Reio

On 04.02.2018 18:09, Edgar Pettijohn wrote:


what does your smtpd.conf look like?


On 02/04/18 08:01, Reio Remma wrote:

Hello!

I'm trying to figure out how I can have virtual domains/users 
working completely decoupled from system users.


Every virtual/alias path seem to want to end up at a system account 
so I'm trying to use userbase, but userbase seems to take username 
without the domain part as key.


query_userinfo  SELECT 5000, 5000, 
CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir 
FROM users WHERE username = ?;


domain-one.com
- bob
- emily

domain-two.com
- john
- albert
- bob (not the same bob as b...@domain-one.com)

Mail sent to b...@domain-two.com will end up at b...@domain-one.com 
mailbox.


Am I missing something about using virtuals in general? I'm 
starting to feel a little stupid here. :)


Thanks,
Reio










Re: Userbase question.

2018-02-04 Thread Edgar Pettijohn

what does your smtpd.conf look like?


On 02/04/18 08:01, Reio Remma wrote:

Hello!

I'm trying to figure out how I can have virtual domains/users working 
completely decoupled from system users.


Every virtual/alias path seem to want to end up at a system account so 
I'm trying to use userbase, but userbase seems to take username 
without the domain part as key.


query_userinfo  SELECT 5000, 5000, 
CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir 
FROM users WHERE username = ?;


domain-one.com
- bob
- emily

domain-two.com
- john
- albert
- bob (not the same bob as b...@domain-one.com)

Mail sent to b...@domain-two.com will end up at b...@domain-one.com mailbox.

Am I missing something about using virtuals in general? I'm starting 
to feel a little stupid here. :)


Thanks,
Reio