Email Server

Notes on how to configure email server with domain and account data saved on postgresql database and web interface;


Install

Install all the software;

$ prt-get depinst dnsmasq nginx postgresqll

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

Backup exim from core collection configuration before removing and installing exim-postgresql from ports.

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
$ sudo prt-get remove exim
$ sudo prt-get depinst exim-postgresql

Backup dovecot from opt collection configuration before removing and installing dovecot-postgresql from ports.

$ sudo prt-get remove dovecot
$ sudo prt-get depinst dovecot

To use the port roundcubemail install it with prt-get;

$ sudo prt-get depinst roundcubemail


Dnsmasq

Configure dnsmasq and set a mx-host;

mx-host=hive.gnu.systems,10.0.0.1,50

Replace hive.gnu.systems and machine by desired fqdn.


Nginx

Configure nginx to serve roundcubemail?.

location /email {
        alias /srv/www/default/roundcubemail;
        access_log /var/log/nginx/roundcube_access.log;
        error_log /var/log/nginx/roundcube_error.log;
        index index.php;
# Favicon
        location ~ ^/email/favicon.ico$ {
            root /srv/www/default/roundcubemail/skins/classic/images;
            log_not_found off;
            access_log off;
            expires max;
        }
# Robots file
        location ~ ^/email/robots.txt {
            allow all;
            log_not_found off;
            access_log off;
        }

# Deny Protected directories
        location ~ ^/email/(config|temp|logs)/ {
            deny all;
        }
        location ~ ^/email/(README|INSTALL|LICENSE|CHANGELOG|UPGRADING)$ {
            deny all;
        }
        location ~ ^/email/(bin|SQL)/ {
            deny all;
        }
# Hide .md files
        location ~ ^/email/(.+\.md)$ {
            deny all;
        }
# Hide all dot files
        location ~ ^/email/\. {
            deny all;
            access_log off;
            log_not_found off;
        }
 }

 location ~  /email/.*\.php {
        alias /srv/www/default/roundcubemail;
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_index index.php;
        try_files $uri /index.php =404;
        include /etc/nginx/fastcgi_params;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        fastcgi_pass 127.0.0.1:9000;
}

Postgresql

Create user and database;

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

$ sudo -u postgres -g postgres createdb \
   --template=template0 \
   --encoding=UTF8 \
   --owner=email_server db_email

This is the database table creation SQL;

--- USE db_email;

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 email_server -d db_email -f "/srv/pgsql/create_email_tables.sql"

Insert first domain, replace hive.gnu.systems by your fqdn;

$ sudo -u postgres -g postgres psql -U email_server -d db_email

db_email=> insert into domains 
(fqdn, domain_type, description, active, created, modified) 
values 
('hive.gnu.systems','local','hive.gnu.systems email system',1,now(),now());

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

db_email=> insert into mailboxes 
(domain_id, local_part, password, description, active, created, modified) 
values 
(1,'myname',MD5('supersecret'),'My account for myname@hive.gnu.systems',1,now(),now());

Insert first alias;

db_email=> insert into aliases 
(domain_id, local_part, goto, description, active, created, modified) 
values 
(1, 'support', 'myname@hive.gnu.systems', 'Redirecting support@hive.gnu.systems to myname@hive.gnu.systems', 1, now(), now());

Check tables and data;

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

Exim

Edit /etc/exim/exim.conf (see files) and add following to get data from database. Add Macros before "MAIN CONFIGURATION SETTINGS";

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

POSTGRESQL_SERVER=127.0.0.1
POSTGRESQL_DB=db_email
POSTGRESQL_USER=email_server
POSTGRESQL_PASSWORD=supersecret
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}}

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/hive.gnu.systems/fullchain.pem;
tls_privatekey = /etc/letsencrypt/live/hive.gnu.systems/privkey.pem;
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 = /var/spool/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


Dovecot

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

$ id mail

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:/var/spool/mail/%d/%n

And valid users id;

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

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_email \
 user=email_server \
 password=supersecret

password_query = SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) as user, \
 mailboxes.password AS password, \
 '/var/spool/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 '/var/spool/mail/%d/%n' AS home, \
  [MAIL ID] AS uid, \
  [MAIL GROUP ID] AS gid

Roundcubemail

Create user and database;

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

$ sudo -u postgres -g postgres createdb \
   --template=template0 \
   --encoding=UTF8 \
   --owner=roundcubemail \
   db_roundcubemail

Create database;

$ psql -U roundcubemail -d db_roundcubemail -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'] = 'hive.gnu.systems';

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

//$config['db_dsnw'] = 'mysql://roundcube:@localhost/roundcubemail';
$config['db_dsnw'] = 'pgsql://roundcubemail: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

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.

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


add/view comments