OpenSMTPD with Dovecot, virtual users and domains, SQLite3, RSpamd antispam

$ pkg_add sqlite3
$ pkg_add opensmtpd-extras
$ pkg_add rspamd
$ pkg_add dovecot

$ apt install sqlite3
$ apt install opensmtpd
$ apt install opensmtpd-extras
$ apt install rspamd
$ apt install dovecot-imapd
$ apt install dovecot-lmtpd
$ apt install dovecot-sqlite

$ cd ~
$ openssl req -new -x509 -nodes -newkey rsa:4096 -keyout mail.key -out mail.crt -days 365
$ mkdir /etc/ssl/mail-servers
$ mv mail.key /etc/ssl/mail-servers/
$ mv mail.crt /etc/ssl/mail-servers/

### OpenSMTPD config file: smtpd.conf

## enable TLS high only ciphers
smtp ciphers HIGH

## setup of TLS certificates
#ca  my-email.domain  cert "/etc/ssl/mail-servers/ca.crt"
pki  my-email.domain  key  "/etc/ssl/mail-servers/mail.key"
pki  my-email.domain  cert "/etc/ssl/mail-servers/mail.crt"
#pki my-email.domain  dhe  auto

## OpenSMTPD Auth against SQlite3
table credentials  sqlite:/etc/vmail-auth/opensmtpd-auth-sqlite.conf
table vdomains     sqlite:/etc/vmail-auth/opensmtpd-auth-sqlite.conf
table vusers       sqlite:/etc/vmail-auth/opensmtpd-auth-sqlite.conf
table vuserinf     sqlite:/etc/vmail-auth/opensmtpd-auth-sqlite.conf

# RSpam filter for OpenSMTPD ; will use port 25 standard and port 587 for authenticate virtual users for SMTP send messages
filter "rspamd" proc-exec "filter-rspamd"
listen on lo0    port 25                                              filter "rspamd"
listen on egress port 25  tls         pki unix.uxm                    filter "rspamd"
listen on lo     port 587 tls-require pki unix.uxm auth <credentials> filter "rspamd"
listen on egress port 587 tls-require pki unix.uxm auth <credentials> filter "rspamd"

# Deliver the email messages directly to Dovecot IMAP4 via Dovecot LMTP (use LMTP IP:PORT not socket, which may require aditional setup of privileges ... !!!)
#action "deliver-virtual" lmtp "/var/dovecot/lmtp" rcpt-to virtual <vusers>
action "deliver-virtual" lmtp "127.0.0.1:10024" rcpt-to virtual <vusers> userbase <vuserinf>
match from any for domain <vdomains> action "deliver-virtual"

# Deliver local system messages for the existing real system accounts
action "deliver-relay" relay
match      from local for any action "deliver-relay"
match auth from any   for any action "deliver-relay"

### Dovecot config file: dovecot.conf

ssl = required
ssl_min_protocol = TLSv1.2
ssl_cipher_list = HIGH
ssl_key = </etc/ssl/mail-servers/mail.key
## without CA (simple certificate)
ssl_cert = </etc/ssl/mail-servers/mail.crt
## with CA (full chain certificate)
#ssl_cert = </etc/ssl/mail-servers/full-chain.crt

disable_plaintext_auth = no
auth_mechanisms = plain login

## If something does not work after all the setup is completed uncomment the following two lines and look at the messages in Dovecot's logs to have a clue what is wrong ...
#auth_debug = yes
#auth_debug_passwords = yes

### vmail: uid=5200 ; gid=5200 ###
mail_uid = vmail
mail_gid = vmail
mail_location = maildir:/var/vmail/%u
mail_plugins = quota

# use the SQLite auth (will be setup below)
passdb {
    driver = sql
    args = /etc/vmail-auth/dovecot-auth-sqlite.conf.ext
}
userdb {
    driver = sql
    args = /etc/vmail-auth/dovecot-auth-sqlite.conf.ext
}

# lmtp is required because OpenSMPTD will use it to deliver incoming email messages to Dovecot directly
protocols = lmtp imap

base_dir = /var/dovecot/

postmaster_address = postmaster@my-email.domain
mail_max_userip_connections = 10

service lmtp {
    inet_listener lmtp {
      address = 127.0.0.1
      port = 10024
    }
    unix_listener lmtp {
      group = vmail
      user = vmail
      mode = 0660
    }
}

plugin {
    quota = maildir:User quota
    quota_rule = *:storage=50MB
    quota_warning = storage=95%% quota-warning 95 %u
    quota_exceeded_message = Fail: User Quota Exceeded
}

# IMAP folders definition

namespace inbox {

    inbox = yes
    separator = .

    mailbox "Drafts" {
    auto = subscribe
    special_use = \Drafts
    }

    mailbox "Sent" {
    auto = subscribe
    special_use = \Sent
    }
    mailbox "Sent Mail" {
    auto = no
    special_use = \Sent
    }

    mailbox "Trash" {
    auto = subscribe
    special_use = \Trash
    }
    mailbox "TRASH" {
    auto = no
    special_use = \Trash
    }

    mailbox "Junk" {
    auto = subscribe
    special_use = \Junk
    }
    mailbox "Spam" {
    auto = no
    special_use = \Junk
    }

    mailbox "Archive" {
    auto = create
    special_use = \Archive
    }
    mailbox "Archives" {
    auto = no
    special_use = \Archive
    }

    mailbox "Notes" {
    auto = create
    }

}

$ mkdir /var/vmail
$ groupadd -g 5200 vmail
$ useradd -g vmail -u 5200 -s /sbin/nologin -d /var/vmail -m vmail
$ chown /var/vmail vmail:vmail

--- SQlite dump file: vmail-db-sqlite.dump.sql

BEGIN TRANSACTION;

---
--- Schema
---


---- Table: vdomains (Virtual Domains)


CREATE TABLE vdomains (
    'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    'domain' VARCHAR(63) NOT NULL
);

CREATE UNIQUE INDEX 'vdomains_uidx_id' ON `vdomains` (`id` ASC);
CREATE UNIQUE INDEX 'vdomains_idx_domain' ON `vdomains` (`domain` ASC);


--- Table: vmail (Virtual Email Addresses)


CREATE TABLE 'vmail' (
    'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    'account' VARCHAR(96) NOT NULL,
    'pass' VARCHAR(255) NOT NULL DEFAULT '',
    'quota_mb' INTEGER NOT NULL DEFAULT 100,
    'quota_msg' INTEGER NOT NULL DEFAULT 1000
);

CREATE UNIQUE INDEX 'vmail_uidx_id' ON `vmail` (`id` ASC);
CREATE UNIQUE INDEX 'vmail_uidx_account' ON `vmail` (`account` ASC);
CREATE INDEX 'vmail_idx_pass' ON `vmail` (`pass`);


--- Table: valias (Virtual Email Aliases)


CREATE TABLE valias (
    'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    'recipient' VARCHAR(96) NOT NULL,
    'destination' VARCHAR(96) NOT NULL,
    'virtualid' INTEGER NOT NULL DEFAULT 0
);

CREATE UNIQUE INDEX 'valias_uidx_id' ON `valias` (`id` ASC);
CREATE INDEX 'valias_idx_recipient' ON `valias` (`recipient` ASC);
CREATE INDEX 'valias_idx_destination' ON `valias` (`destination` ASC);
CREATE INDEX 'valias_idx_virtualid' ON `valias` (`virtualid` ASC);


--- Table: _info (MetaInfo)


CREATE TABLE '_info' (
    'key' VARCHAR(100) PRIMARY KEY NOT NULL,
    'value' TEXT NOT NULL
);

CREATE UNIQUE INDEX '_info_uidx_key' ON `_info` (`key` ASC);


---
--- Data (edit this data to follow your particular needs) ; This is only a sample !!!
---

--- create 2 sample email domains
INSERT INTO `vdomains` (`id`,`domain`) VALUES ('1','my-email.domain');
INSERT INTO `vdomains` (`id`,`domain`) VALUES ('2','my-email.domain2');

--- create 3 sample email accounts
--- the password for all 3 accounts below is just a sample: 'sample-password'
--- the encrypted password password has been created using the dovecot admin utility as (run in a shell):
--- doveadm pw -s BLF-CRYPT
--- all three accounts below have a sample quota of max 100MB and max 7500 messages
INSERT INTO `vmail` (`id`,`account`,`pass`,`quota_mb`,`quota_msg`) VALUES ('1','account1@my-email.domain','{BLF-CRYPT}$2y$05$S0d1Dif4B.F2.Rhw6R7.JuYvsoDwv6CmkHcrZ8JbP19eC.NyXmxjC','100','7500');
INSERT INTO `vmail` (`id`,`account`,`pass`,`quota_mb`,`quota_msg`) VALUES ('2','account2@my-email.domain','{BLF-CRYPT}$2y$05$W2OnqAK82wpZCs4E6/G21OJnxXVVb99no/EMqDrL8ZCVEox6ifi/y','100','7500');
INSERT INTO `vmail` (`id`,`account`,`pass`,`quota_mb`,`quota_msg`) VALUES ('3','account@my-email.domain2','{BLF-CRYPT}$2y$05$6d20muKiYPjZeUjvFvQYUODeR6C4YbAEmVO.xGVfZcYgd5hjdBJZm','100','7500');

--- create an email alias as postmaster for the domain my-email.domain
INSERT INTO `valias` (`id`,`recipient`,`destination`,`virtualid`) VALUES ('1','postmaster@my-email.domain','account2@my-email.domain','1');
--- create an email alias as postmaster for the domain my-email.domain2
INSERT INTO `valias` (`id`,`recipient`,`destination`,`virtualid`) VALUES ('2','postmaster@my-email.domain2','account@my-email.domain2','3');

---

INSERT INTO `_info` (`key`,`value`) VALUES ('about','Virtual Mail System for OpenSMTPD + Dovecot');
INSERT INTO `_info` (`key`,`value`) VALUES ('copyright','(c) 2022 w3soft.org');
INSERT INTO `_info` (`key`,`value`) VALUES ('license','BSD');

---

COMMIT;

--- #END dump

$ mkdir /etc/vmail-auth
$ cd /etc/vmail-auth
$ sqlite3 vmail-auth.sqlite3 < /tmp/vmail-db-sqlite.dump.sql

$ doveadm pw -s BLF-CRYPT

UPDATE vmail SET pass = '{BLF-CRYPT}$2y$05$W2OnqAK82wpZCs4E6/G21OJnxXVVb99no/EMqDrL8ZCVEox6ifi/y' WHERE id = 1;

### OpenSMTPD config, auth SQLite3: opensmtpd-auth-sqlite.conf
## user/group: vmail/vmail as UID=5200 ; GID=5200

dbpath /etc/vmail-auth/vmail-auth.sqlite3

# Domain lookup query ; rows = 1 ; fields = 1 (domain VARCHAR)
query_domain SELECT domain FROM vdomains WHERE ((domain = ?) AND (domain != '')) LIMIT 1 OFFSET 0;

# Alias lookup query ; rows >= 0 ; fields = 1 (email VARCHAR)
query_alias SELECT 'vmail_' || virtualid AS alias FROM valias WHERE ((recipient = ?) AND (recipient != '')) LIMIT 250 OFFSET 0;

# UserInfo query ; rows = 1 ; fields = 3 (uid INTEGER, gid INTEGER, maildir VARCHAR, password VARCHAR)
query_userinfo SELECT 5200 AS uid, 5200 AS gid, 'var/vmail/deliver-virtual:lmtp/vmail_' || id AS maildir FROM vmail WHERE (('vmail_' || id = ?) AND (account != '') AND (pass != '')) LIMIT 1 OFFSET 0;

# Credentials lookup query ; rows = 1 ; fields = 2 (email VARCHAR, password VARCHAR)
query_credentials SELECT account AS user, pass AS password FROM vmail WHERE ((account = ?) AND (account != '') AND (pass != '')) LIMIT 1 OFFSET 0;

### Dovecot config, auth SQLite3: dovecot-auth-sqlite.conf.ext
## user/group: vmail/vmail as UID=5200 ; GID=5200

driver = sqlite
connect = /etc/vmail-auth/vmail-auth.sqlite3

# used by IMAP to check authentication on login
default_pass_scheme = BLF-CRYPT
password_query = SELECT account AS user, '{BLF-CRYPT}' || pass AS password FROM vmail WHERE ((account = '%u') AND (account != '') AND (pass != '')) LIMIT 1 OFFSET 0

# used by LMTP to know where to deliver (the OpenSMTPD will have to choose the real user for aliases ...)
user_query = SELECT '/var/vmail/' || vmail.account || '/' AS home, 'maildir:/var/vmail/' || vmail.account || '/' AS mail, 5200 AS uid, 5200 AS gid, '*:storage=' || vmail.quota_mb || 'MB:messages=' || vmail.quota_msg AS quota_rule FROM vmail WHERE ((account = (SELECT valias.destination FROM valias WHERE (valias.recipient = '%u') LIMIT 1 OFFSET 0)) AND (account != '') AND (pass != '')) LIMIT 1 OFFSET 0