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')
('[email protected]', 'my.domain', 'my.aliass');
Olivier.
De : Reio Remma [mailto:[email protected]]
Envoyé : Sunday, February 4, 2018 9:22 PM
À : [email protected]
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:[email protected]]
Envoyé : Sunday, February 4, 2018 3:02 PM
À : [email protected]
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 [email protected])
Mail sent to [email protected] will end up at [email protected] mailbox.
Am I missing something about using virtuals in general? I'm starting to feel a
little stupid here. :)
Thanks,
Reio
