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