Re: Userbase question.

[Reio Remma]

On 05.02.18 2:18, Reio Remma wrote:
On 04.02.2018 22:21, Reio Remma wrote:
On 04.02.2018 22:01, Reio Remma wrote:
On 04.02.2018 21:56, Reio Remma wrote:

*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 
<mailto:b...@domain-one.com>)

Mail sent to b...@domain-two.com <mailto:b...@domain-two.com> will 
end up at b...@domain-one.com <mailto: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

A little refinement to query_userinfo:

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

--
Tervitades,

Reio Remma
spetsialist
MR Stuudio O†Ü
Tondi 17BŠ, 11316, Tallinn
Tel: (+372) 6 504 808
Mob: (+372) 56 22 00 33
E-Mail: r...@mrstuudio.ee
www.mrstuudio.ee