Hi,

 

In fact Dovecot handles the delivery via LMTP.

à The relation <vdomains> & <valiases>  in the extract below permit the 
delivery via the SQL query (depending the case for local / vdomains).

 

Extract from /etc/mail/smtpd.conf

-------------------------------------------------------------------------------------------

(…)

###################################################

#

## Allow to deliver

#

accept for local alias <aliases> deliver to mbox

 

###################################################

#

## Relay

#

# Tagged mail returned from DKIM

accept tagged DKIM_OUT for any relay

 

# tagged mail returned from spampd deliver to maildir

accept tagged SPAM_IN for domain <vdomains> virtual <valiases> deliver to lmtp 
"/var/dovecot/lmtp" rcpt-to # deliver via lmtp

 

# tagged mail returned from clamsmtpd either send to spampd or dkimproxy_out

accept tagged CLAM_IN   for any relay via smtp://127.0.0.1:10035 # send to 
spampd

accept tagged CLAM_OUT  for any relay via smtp://127.0.0.1:10030 # send to 
dkimproxy_out

 

#

# Start here (inbound)

accept from any for domain <vdomains> relay via smtp://127.0.0.1:10025  # to 
clamd via clamsmtpd_in

accept from local for any relay via smtp://127.0.0.1:10027              # to 
clamd via clamsmtpd_out

 

In parallel dovecot has to be configured :

 

# Authentication for SQL users. Included from 10-auth.conf.

#

# <doc/wiki/AuthDatabase.SQL.txt>

 

passdb {

  driver = sql

 

  # Path for SQL configuration file, see example-config/dovecot-sql.conf.ext

  args = /etc/dovecot/dovecot-sql.conf.ext

}

 

userdb {

  driver = static

  args = uid=vmail gid=vmail home=/var/vmail/%d/%n

}

 

/etc/mail/dovecot-sql.conf.ext

 

+---> doas cat /etc/dovecot/dovecot-sql.conf.ext

# This file is commonly accessed via passdb {} or userdb {} section in

# conf.d/auth-sql.conf.ext

 

# Database driver: mysql, pgsql, sqlite

driver = pgsql

 

connect = host=127.0.0.1 dbname=vmail user=vmail password=myPassword

 

default_pass_scheme = BLF-CRYPT     # BSD authentication

 

password_query = \

  SELECT email, password \

  FROM credentials WHERE email = '%u' AND active = 'Y' # the active fiels 
permit here to unactive temporary a user. 

 

 

You have also to configure another part of the dovecot side :  
<http://wiki2.dovecot.org/> http://wiki2.dovecot.org/

 

Postgresql instructions for OpenBSD:

 

doas pkg_add postqgresql-server

doas su - _postgresql

mkdir /var/postgresql/data

initdb -D /var/postgresql/data -U postgres -A md5 -E UTF8 -W

exit

rcctl enable postgresql

à to modify /var/postgresql/data/postgresql.conf expecting your preconisations

à to modify /var/postgresql/data/pg_hba.conf expecting your preconisations

doas rcctl start postgresql

psql -U postgres

CREATE USER myUser;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

REVOKE USAGE ON SCHEMA public FROM PUBLIC;

GRANT CREATE ON SCHEMA public TO postgres;

GRANT USAGE ON SCHEMA public TO postgres;

CREATE DATABASE myDatabase WITH OWNER myUser;

ALTER ROLE myUser WITH PASSWORD 'myPassword';

\q

psql -U myUser

CREATE SEQUENCE seq_myDatabase_vDomains_id START 1;

CREATE SEQUENCE seq_myDatabase_vUsers_id START 1;

CREATE SEQUENCE seq_myDatabase_credentials_id START 1;

CREATE TABLE vDomains (

id INT NOT NULL DEFAULT nextval('seq_myDatabase_vdomains_id'),

domain varchar(40) NOT NULL UNIQUE,

PRIMARY KEY (id)

);

CREATE TABLE vUsers (

id INT NOT NULL DEFAULT nextval('seq_myDatabase_vusers_id'),

email VARCHAR(60) NOT NULL UNIQUE,

domain VARCHAR(40) NOT NULL,

destination VARCHAR(60) NOT NULL DEFAULT 'myUser',

PRIMARY KEY (id),

FOREIGN KEY (domain) REFERENCES vDomains(domain) ON DELETE CASCADE

);

CREATE TABLE credentials (

id INT NOT NULL DEFAULT nextval('seq_myDatabase_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

);

INSERT INTO vdomains (domain) VALUES ('first.domain');

INSERT INTO credentials (email, password) VALUES ('one.user', 
'myEncryptedPassword')  #using smtpctl encrypt

INSERT INTO vusers (email, alias)

VALUES

('one.user', 'first.domain','vmail')

('postmaster@my.domain', 'my.domain', 'my.aliass');

 

 

Olivier.

 

 

 

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

 

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

 

 

 

Reply via email to