Email Server

Notes on how to configure email server with domains and accounts data saved on postgresql database and web email client.

There are two setups described in this document, simple and vexim2?. They differ on database layout and exim configuration, simple is recommended to manage localhost or a internal email server. vExim2 adds more features such as email list integration, web administration panel to add domains, email accounts, alias, etc.


Install

Backup configuration and data of exim from core and dovecot from opt collection before removing and installing exim-postgresql and dovecot-postgresql from ports.

Remove default exim and dovecot package;

$ sudo prt-get remove exim dovecot

Install all necessary software;

$ prt-get depinst dnsmasq nginx postgresql \
exim-postgresql dovecot-postgresql \
mailman roundcubemail

Check the pages dnsmasq, postgresql, exim, dovecot, nginx, mailman? and roundcubemail for more documentation about each tool.


User

Check if mail user exists and where the home folder is located;

$ getent passwd mail | cut -f 6 -d :
/var/spool/mail

# usermod -m -d /srv/mail mail

Get user uid and gid;

# id mail
uid=8(mail) gid=12(mail) groups=12(mail)

Dnsmasq

Configure dnsmasq and set a mx-host;

mx-host=git.leetio.dev,10.0.0.1,50

Replace git.leetio.dev and machine by desired fqdn.


Postgresql

Create user and database;

$ sudo -u postgres -g postgres createuser \
        --pwprompt --encrypted \
        --no-createrole --no-createdb \
        db_mail_user

$ sudo -u postgres -g postgres createdb \
   --template=template0 \
   --encoding=UTF8 \
   --owner=db_mail_user db_mail

Add following to /srv/pgsql/data/pg_hba.conf;

local   db_mail         db_mail_user                            scram-sha-256
local   db_rcemail      db_rcemail_user                         scram-sha-256
#local   all             postgres                                trust
# IPv4 local connections:
host    all             postgres        127.0.0.1/32            scram-sha-256
host    db_mail         db_mail_user    127.0.0.1/32            scram-sha-256
host    db_rcemail      db_rcemail_user 127.0.0.1/32            scram-sha-256

Simple setup

To setup vexim, follow this instructions instead.

This is the database table creation SQL;

--- USE db_mail;

CREATE TABLE mailboxes (
    id          bigserial primary key,
    domain_id   int NOT NULL,
    local_part  varchar(250) NOT NULL,
    password    varchar(100) NULL,
    description varchar(250) NULL,
    active      smallint NOT NULL DEFAULT 0,
    created     TIMESTAMP,
    modified    TIMESTAMP NULL
);
ALTER TABLE mailboxes ALTER COLUMN created SET DEFAULT now();

CREATE TABLE aliases (
    id          bigserial primary key,
    domain_id   int NOT NULL,
    local_part  varchar(250) NOT NULL,
    goto        varchar(250) NOT NULL,
    description varchar(250) NULL,
    active      smallint NOT NULL DEFAULT 0,
    created     TIMESTAMP,
    modified    TIMESTAMP NULL
);
ALTER TABLE aliases ALTER COLUMN created SET DEFAULT now();

CREATE TABLE vacations (
    id          bigserial primary key,
    mailbox_id  int NOT NULL,
    subject     varchar(250) NOT NULL,
    body        text NOT NULL,
    description varchar(250) NULL,
    active      smallint NOT NULL DEFAULT 0,
    created     TIMESTAMP,
    modified    TIMESTAMP NULL
);
ALTER TABLE vacations ALTER COLUMN created SET DEFAULT now();

create type domain_type_t as enum('local', 'relay');
CREATE TABLE domains (
    id          bigserial primary key,
    fqdn        varchar(250) NOT NULL,
    domain_type domain_type_t,
    description varchar(250) NULL,
    active      smallint NOT NULL DEFAULT 0,
    created     TIMESTAMP,
    modified    TIMESTAMP NULL
);
ALTER TABLE domains ALTER COLUMN created SET DEFAULT now();

Save above to a file, example create_email_tables.sql Δ (see files) and run psql;

$ sudo -u postgres -g postgres psql -U db_mail_user -d db_mail -f "/srv/pgsql/create_email_tables.sql"

Insert first domain, replace git.leetio.dev by your fqdn;

$ sudo -u postgres -g postgres psql -U db_mail_user -d db_mail

db_mail=> insert into domains 
(fqdn, domain_type, description, active, created, modified) 
values 
('git.leetio.dev','local','git.leetio.dev email system',1,now(),now());

Insert first user, replace myname by user name and supersecret by user password;

db_mail=> insert into mailboxes 
(domain_id, local_part, password, description, active, created, modified) 
values 
(1,'myname',MD5('supersecret'),'My account for myname@git.leetio.dev',1,now(),now());

Insert first alias;

db_mail=> insert into aliases 
(domain_id, local_part, goto, description, active, created, modified) 
values 
(1, 'support', 'myname@git.leetio.dev', 'Redirecting support@git.leetio.dev to myname@git.leetio.dev', 1, now(), now());

Check tables and data;

db_mail=> \dt
db_mail=> select * from mailboxes;

vExim setup

Inside vexim2 folder run;

$ sed -i 's/OWNER vexim/OWNER db_mail_user/g' setup/pgsql.sql
$ sed -i 's/OWNER TO vexim/OWNER TO db_mail_user/g' setup/pgsql.sql

As postgres user add pgcrypto extenssion to db_mail database;

 sudo -u postgres psql -d db_mail

db_mail=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
db_mail=#

To create vexim database tables and register administrator user run following command;

$ psql -U db_mail_user -d db_mail -f setup/pgsql.sql

Above command should output that siteadmin administrator user was created and a random password. If the password shown is "CHANGE" then step of creating pgcrypto extenssion failed.


Exim

Read how to create let's encrypt certificates, also check their user and permissions in hardened environments, then in "MAIN CONFIGURATION SETTINGS" edit to;

#tls_certificate = /etc/ssl/certs/exim.crt
#tls_privatekey = /etc/ssl/keys/exim.key
tls_certificate = /etc/letsencrypt/live/git.leetio.dev/fullchain.pem;
tls_privatekey = /etc/letsencrypt/live/git.leetio.dev/privkey.pem;
tls_dhparam = /etc/exim4/dhparam.pem

Replace example.machine.org by host name.

# A list of secure ciphers for GnuTLS which is used in Debian/Ubuntu
#tls_require_ciphers = ${if =={$received_port}{25}\
#    {NORMAL:%COMPAT:-ARCFOUR-128:-ARCFOUR-40:-MD5:-VERS-SSL3.0}\
#    {SECURE128:+AES-256-CBC:+CAMELLIA-256-CBC:-ARCFOUR-128:-ARCFOUR-40:-MD5:-VERS-SSL3.0}}

# use all ciphers on port 25, use only good ciphers on port 587
#tls_require_ciphers = ${if =={$received_port}{25} \
                {DEFAULT} {HIGH:!MD5:!SHA1:!SHA2}}
# If OpenSSL is used (CentOS, FreeBSD), comment the previous block (tls_require_ciphers)
# and uncomment the following line
openssl_options = +all +no_sslv2 +no_sslv3 +no_compression +cipher_server_preference

The Diffie-Hellman group should have at least 1024 bit and can be created with this command (it can take some time):

# openssl dhparam -out /etc/exim4/dhparam.pem 2048

Run exim -bV to show if using GnuTLS or OpenSSL.

Configure database connection details. Add Macros before "MAIN CONFIGURATION SETTINGS";

###########################
#          MACROS                     
###########################

POSTGRESQL_SERVER=127.0.0.1
POSTGRESQL_DB=db_mail
POSTGRESQL_USER=db_mail_user
POSTGRESQL_PASSWORD=supersecret
hide pgsql_servers = POSTGRESQL_SERVER/POSTGRESQL_DB/POSTGRESQL_USER/POSTGRESQL_PASSWORD

Simple setup

To setup vexim, follow this instructions instead.

Edit /etc/exim/exim.conf Δ (see files) and add following to get data from database. Add after following line in "MACROS";

hide pgsql_servers = POSTGRESQL_SERVER/POSTGRESQL_DB/POSTGRESQL_USER/POSTGRESQL_PASSWORD

MAIN_LOCAL_DOMAINS=@:localhost:dsearch;/etc/exim/virtual:${lookup pgsql{SELECT fqdn AS domain FROM domains WHERE fqdn='${quote_pgsql:$domain}' AND domain_type='local' AND active=1}}
dnssec_request_domains = *
#  no_more
# List of domains considered local for exim. Domains not listed here
# need to be deliverable remotely.
domainlist local_domains = MAIN_LOCAL_DOMAINS

Comment the following;

#system_aliases:
#  driver = redirect
#  allow_fail
#  allow_defer
#  data = ${lookup{$local_part}lsearch{/etc/exim/aliases}}
## user = exim
#  file_transport = address_file
#  pipe_transport = address_pipe

And add this configuration to get aliases from database;

system_aliases:
     driver = redirect
     allow_fail 
     allow_defer
     data = ${lookup pgsql{SELECT aliases.goto AS goto FROM domains,aliases WHERE \
                   (aliases.local_part='${quote_pgsql:$local_part}' OR aliases.local_part='@') AND \
                   aliases.active=1 AND \
                   aliases.domain_id=domains.id AND \
                   domains.fqdn='${quote_pgsql:$domain}' AND \
                   domains.active=1}}

In "ROUTERS CONFIGURATION" and before "TRANSPORTS CONFIGURATION", add this after localuser declaration;

#localuser:
#  driver = accept
#  check_local_user
## local_part_suffix = +* : -*
## local_part_suffix_optional
#  transport = local_delivery
#  cannot_route_message = Unknown user

dovecot_user:
      driver = accept
        condition = ${lookup pgsql{SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) AS goto FROM domains,mailboxes WHERE \
                   mailboxes.local_part='${quote_pgsql:$local_part}' AND \
                   mailboxes.active=1 AND \
                   mailboxes.domain_id=domains.id AND \
                   domains.fqdn='${quote_pgsql:$domain}' AND \
                   domains.active=1}{yes}{no}}
     transport = dovecot_delivery

In "TRANSPORTS CONFIGURATION" after address_reply and before "RETRY CONFIGURATION" add;

dovecot_delivery:
     driver = appendfile
     maildir_format = true
     directory = /srv/mail/$domain/$local_part
     create_directory = true
     directory_mode = 0770
     mode_fail_narrower = false
     message_prefix =
     message_suffix =
     delivery_date_add
     envelope_to_add
     return_path_add
     user = mail
     group = mail
     mode = 0660

In "AUTHENTICATION CONFIGURATION" add the following after begin authenticators;

begin authenticators

auth_plain:
     driver = plaintext
     public_name = PLAIN
     server_condition = ${lookup pgsql{SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) FROM mailboxes,domains WHERE \
                       mailboxes.local_part=split_part('${quote_pgsql:$auth2}','@',1) AND \
                       mailboxes.password=MD5('${quote_pgsql:$auth3}') AND \
                       mailboxes.active=1 AND \
                       mailboxes.domain_id=domains.id AND \
                       domains.fqdn=split_part('${quote_pgsql:$auth2}','@',2) AND \
                       domains.active=1}{yes}{no}}
     server_prompts = :
     server_set_id = $auth2

auth_login:
     driver = plaintext
     public_name = LOGIN
     server_condition = ${lookup pgsql{SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) FROM mailboxes,domains WHERE \
                       mailboxes.local_part=split_part('${quote_pgsql:$auth1}','@',1) AND \
                       mailboxes.password=MD5('${quote_pgsql:$auth2}') AND \
                       mailboxes.active=1 AND \
                       mailboxes.domain_id=domains.id AND \
                       domains.fqdn=split_part('${quote_pgsql:$auth1}','@',2) AND \
                       domains.active=1}{yes}{no}}
     server_prompts = Username:: : Password::
     server_set_id = $auth1

Pre-test configuration by starting / stoping exim;

# bash /etc/rc.d/exim restart

vExim Setup

Inside vexim2 folder run;

$ grep -rl "\/usr\/local\/etc" | xargs sed -i 's=\/usr\/local\/etc=/etc=g'
$ sed -i s/_mysql/_pgsql/g docs/configure
$ sed -i s/mysql{/pgsql{/g docs/configure
$ sed -i 's/VEXIM_SA_USERNAME \= vexim/VEXIM_SA_USERNAME = mail/g' docs/configure
$ sed -i 's/exim_user \= mailnull/exim_user = mail/g' docs/configure

Edit docs/configure, replace git.leetio.dev by the same fqdn defined on dnsmasq;

primary_hostname=git.leetio.dev

Comment mysql less ALIAS_DOMAINS;

# If you are using MySQL, uncomment the following two lines:
#VIRTUAL_DOMAINS = SELECT DISTINCT domain FROM domains WHERE type = 'local' AND enabled = '1' AND domain = '${quote_pgsql:$domain}'
#RELAY_DOMAINS = SELECT DISTINCT domain FROM domains WHERE type = 'relay'  AND domain = '${quote_pgsql:$domain}'
ALIAS_DOMAINS = SELECT DISTINCT alias FROM domainalias WHERE alias = '${quote_pgsql:$domain}'

# If you are using PGSQL, uncomment the following four lines:
VIRTUAL_DOMAINS = SELECT DISTINCT domain || ' : ' FROM domains WHERE type = 'local'
RELAY_DOMAINS = SELECT DISTINCT domain || ' : ' FROM domains WHERE type = 'relay'

Replace git.leetio.dev by fqdn defined on dnsmasq and network ip's that are allowed to send mail to any domain (relay_from_hosts);

domainlist local_domains = @ : git.leetio.dev : ${lookup pgsql{VIRTUAL_DOMAINS}} : ${lookup pgsql{ALIAS_DOMAINS}}
domainlist relay_to_domains = ${lookup pgsql{RELAY_DOMAINS}}
hostlist   relay_from_hosts = localhost : @ : 10.0.0.0/8

Search for;

select smtp, users.sa_tag*10 AS sa_tag, users.on_spamassassin AND domains.spamassassin AS on_spamassassin, \

And replace with;

select smtp, users.sa_tag AS sa_tag, users.on_spamassassin, domains.spamassassin AS on_spamassassin, \

Fix local_delivery: replace file;

local_delivery:
  driver = appendfile
  file = /srv/mail/$local_part
  ...

Fix alisas system file;

system_aliases:
  driver = redirect
  allow_fail
  allow_defer
  data = ${lookup{$local_part}lsearch{/etc/exim/aliases}}

Fix's to disable this features if they are not enable on exim-postgresql port or not desired;

#acl_smtp_mime = acl_check_mime
#.include /etc/exim/vexim-acl-check-mime.conf
#av_scanner = clamd:/var/run/clamav/clamd
#spamd_address = 127.0.0.1 783

Copy configuration files;

# cp docs/configure /etc/exim/exim.conf
# cp docs/vexim-* /etc/exim/

Dovecot

Where [MAIL ID] and [MAIL GROUP ID] shows up replace by system mail user id and group id.

Edit /etc/dovecot/dovecot.conf Δ to;

# Protocols we want to be serving.
#protocols = imap pop3 lmtp submission
protocols = imap

Example only need IPv4, edit the following;

#listen = *, ::
listen=*

Edit /etc/dovecot/conf.d/10-mail.conf Δ

#mail_location =
mail_location = maildir:/srv/mail/%d/%n/Maildir

And valid users id;

#first_valid_uid = 500
first_valid_uid = [MAIL ID]
last_valid_uid = 500

Edit /etc/dovecot/conf.d/10-auth.conf Δ;

#!include auth-system.conf.ext
!include auth-sql.conf.ext

Edit /etc/dovecot/dovecot-sql.conf.ext Δ;

# Database driver: mysql, pgsql, sqlite
#driver =
driver= pgsql

And at the end add;

connect = host=127.0.0.1 \
 dbname=db_mail \
 user=db_mail_user \
 password=super_secret

Edit /etc/dovecot/conf.d/10-ssl.conf and set certificates. Replace example.machine.org by host name;

ssl_cert = </etc/letsencrypt/live/example.machine.org/fullchain.pem
ssl_key = </etc/letsencrypt/live/example.machine.org/privkey.pem

Comment all declarations in /etc/dovecot/conf.d/auth-system.conf.ext

Simple setup

Edit /etc/dovecot/dovecot-sql.conf.ext just after database connect declaration;

password_query = SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) as user, \
 mailboxes.password AS password, \
 '/srv/mail/%d/%n' AS userdb_home, \
 [MAIL ID] AS userdb_uid, \
 [MAIL GROUP ID] AS userdb_gid FROM mailboxes, \
 domains WHERE mailboxes.local_part = '%n' \
 AND mailboxes.active = 1 \
 AND mailboxes.domain_id = domains.id \
 AND domains.fqdn = '%d' \
 AND domains.active = 1

user_query = SELECT '/srv/mail/%d/%n' AS home, \
  [MAIL ID] AS uid, \
  [MAIL GROUP ID] AS gid

vExim setup

Edit /etc/dovecot/dovecot-sql.conf.ext just after database connect declaration;

default_pass_scheme = CRYPT

password_query = SELECT username AS user,\
crypt AS password,\
pop AS userdb_home,\
uid AS userdb_uid,\
gid AS userdb_gid \
FROM users WHERE username = '%u'

user_query = SELECT '/srv/mail/%d/%n' AS home, \
  [MAIL ID] AS uid, \
  [MAIL GROUP ID] AS gid

Nginx

Nginx will serve web based email and administration applications, nginx configuration with virtual servers are described on documentation. For more information check nginx and setup php pages.

Email virtual server;

vExim setup

vExim virtual server;

Inside vexim2 folder copy the files and set correct permissions;

# cd vexim2
# mkdir -p /srv/www/vexim/
# cp -r vexim/* /srv/www/vexim/
# chown www:www -R /srv/www/vexim/

Configure vexim web administration tool;

# cd /srv/www/vexim/config
# cp variables.php.example variables.php

Edit variables.php to match database configuration, example;

  $sqlserver = "localhost";
  $sqltype = "pgsql";
  $sqldb = "db_mail";
  $sqluser = "db_mail_user";
  $sqlpass = "super_secret";

Set path to email storage;

$mailroot = "/srv/mail/";

Set uid and gid to mail user;

$uid = "8";
$gid = "12";

Roundcubemail

Create user and database;

$ sudo -u postgres -g postgres createuser \
        --pwprompt --encrypted \
        --no-createrole --no-createdb \
        db_rcemail_user

$ sudo -u postgres -g postgres createdb \
   --template=template0 \
   --encoding=UTF8 \
   --owner=db_rcemail_user \
   db_rcemail

Create database;

$ psql -U db_rcemail_user -d db_rcemail -h localhost -f /srv/www/default/roundcubemail/SQL/postgres.initial.sql

When roundcubeemail port is installed it creates /srv/www/default/roundcubemail/config/defaults.inc.php Δ, edit to;

// log driver:  'syslog', 'stdout' or 'file'.
//$config['log_driver'] = 'file';
$config['log_driver'] = 'syslog';
$config['syslog_facilihy'] ='mail';
//$config['default_host'] = 'localhost';
$config['default_host'] = 'git.leetio.dev';

And copy to /srv/www/default/roundcubemail/config/config.inc.php, configure database connection;

//$config['db_dsnw'] = 'mysql://db_rcemail_user:@localhost/roundcubemail';
$config['db_dsnw'] = 'pgsql://db_rcemail_user:supersecret@localhost/
db_roundcubemail';

Configuration files

create_email_tables.sql Δ
Create database tables for email system.
/etc/exim/exim.conf Δ
Exim configuration file.
/etc/dovecot/dovecot.conf Δ
Dovecot configuration file.
/etc/dovecot/conf.d/10-mail.conf Δ
Dovecot mail configuration file.
/etc/dovecot/conf.d/10-auth.conf Δ
Dovecot auth configuration.
/etc/dovecot/dovecot-sql.conf.ext Δ
Dovecot sql database configuration.
/srv/www/default/roundcubemail/config/defaults.inc.php Δ
Roundcubemail configuration file.

Notes

Information

Check debian using mysql.

Create localhost and other machine related domains, create system aliases (exp. from exim; daemon, ftp, nobody, operator, uucp) and redirect them to desired email accounts.

Check syslog-ng documentation and configuration, install and configure logwatch.

Roundcube

Upstream roundcube was not mobile friendly, melanie2 mobile plugin contains instructions on how to install the two plugins and the skin.

Exim

If you need to build exim edit exim-4.92/src/EDITME to include postgresql library support;

LOOKUP_PGSQL=yes
LOOKUP_INCLUDE=-I /usr/local/pgsql/include
LOOKUP_LIBS=-L/usr/local/lib -lpq

vExim

Download sources: vexim2-20191202.tar.gz Δ, attention this archived version can be outdated. Use as a reference.


Comments

add/view comments