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

Reply via email to