Configure a FreeBSD Email Server Using Postfix, Dovecot, MySQL, DAVICAL and SpamAssassin

Introduction

In this tutorial, we are going to configure a FreeBSD email server with virtual domains and users, using Postfix, Dovecot, MySQL and SpamAssassin, ClamAV (w/o Amavis), DKIM, DMARC, CalDAV, CardDAV, Dashboard and RoundCube on FreeBSD 11.0.

Package List (recommended)

ap24-mod_security apache24 apr arc arj autoconf autoconf-wrapper automake automake-wrapper bash bison ca_root_nss cclient clamav-milter cmake cmake-modules
curl cvsps davical db5 dialog4ports dovecot-pigeonhole dovecot2 expat ezjail freetype2 gdbm gettext-runtime gettext-tools git gmake gnupg1 help2man icu indexinfo
jpeg-turbo json-c jsoncpp kbproto ldns lha libICE libSM libX11 libXau libXaw libXdmcp libXext libXmu libXp libXpm libXt libarchive libedit libevent libffi libiconv
libidn libltdl liblz4 libmcrypt libpthread-stubs libspf2 libtool libuv libxcb libxml2 libzip lua52 lzo2 m4 mod_php56 munin-common mysql56-client mysql56-server nano
oniguruma5 opendkim opendmarc p5-Algorithm-C3 p5-Authen-NTLM p5-Authen-SASL p5-B-Hooks-EndOfScope p5-BerkeleyDB p5-Bit-Vector p5-Cache p5-Cache-Cache p5-Carp-Clan
p5-Class-C3 p5-Class-Data-Inheritable p5-Class-Inspector p5-Class-Method-Modifiers p5-Class-Singleton p5-Crypt-CBC p5-Crypt-DES p5-Crypt-OpenSSL-Bignum p5-Crypt-OpenSSL-RSA
p5-Crypt-OpenSSL-Random p5-DBD-Pg p5-DBD-mysql p5-DBI p5-Data-OptList p5-Date-Calc p5-DateTime p5-DateTime-HiRes p5-DateTime-Locale p5-DateTime-TimeZone p5-Devel-StackTrace
p5-Digest-HMAC p5-Digest-SHA1 p5-Dist-CheckConflicts p5-Encode-Detect p5-Encode-Locale p5-Error p5-Eval-Closure p5-Exception-Class p5-File-Listing p5-File-NFSLock p5-File-ShareDir
p5-Filter p5-GSSAPI p5-HTML-Parser p5-HTML-Tagset p5-HTTP-Cookies p5-HTTP-Daemon p5-HTTP-Date p5-HTTP-Message p5-HTTP-Negotiate p5-Heap p5-IO-HTML p5-IO-Multiplex p5-IO-Socket-INET6
p5-IO-Socket-IP p5-IO-Socket-SSL p5-IO-String p5-IO-stringy p5-IPC-ShareLite p5-LWP-MediaTypes p5-List-AllUtils p5-List-SomeUtils p5-List-SomeUtils-XS p5-List-UtilsBy p5-Locale-gettext
p5-MRO-Compat p5-Mail-DKIM p5-Mail-SPF p5-Mail-Tools p5-Module-Build p5-Module-Implementation p5-Module-Runtime p5-Mozilla-CA p5-Net-CIDR p5-Net-DNS p5-Net-DNS-Resolver-Programmable p5-Net-HTTP
p5-Net-IDN-Encode p5-Net-LibIDN p5-Net-SMTP-SSL p5-Net-SNMP p5-Net-SSLeay p5-Net-Server p5-NetAddr-IP p5-Package-Stash p5-Package-Stash-XS p5-Params-Util p5-Params-Validate
p5-Params-ValidationCompiler p5-Parse-Syslog p5-Role-Tiny p5-Scalar-List-Utils p5-Socket p5-Socket6 p5-Specio p5-Sub-Exporter p5-Sub-Exporter-Progressive p5-Sub-Identify p5-Sub-Install p5-Switch
p5-TimeDate p5-Try-Tiny p5-URI p5-Variable-Magic p5-WWW-RobotRules p5-XML-LibXML p5-XML-NamespaceSupport p5-XML-Parser p5-XML-SAX p5-XML-SAX-Base p5-YAML p5-libwww p5-namespace-autoclean
p5-namespace-clean pcre pear pear-Auth pear-Auth_SASL pear-Net_SMTP pear-Net_Socket pecl-intl perl5 pflogsumm php-libawl php56 php56-calendar php56-ctype php56-curl php56-dom php56-extensions
php56-filter php56-gd php56-gettext php56-hash php56-iconv php56-imap php56-json php56-mbstring php56-mcrypt php56-mysql php56-mysqli php56-opcache php56-openssl php56-pdo php56-pdo_mysql
php56-pdo_pgsql php56-pdo_sqlite php56-pgsql php56-phar php56-posix php56-session php56-simplexml php56-sqlite3 php56-tokenizer php56-xml php56-xmlreader php56-xmlwriter php56-zip php56-zlib pkg
pkgconf png portmaster postfix postgresql93-client postgresql93-server postgrey printproto pwgen py27-Babel py27-Jinja2 py27-MarkupSafe py27-alabaster py27-authres py27-dns py27-docutils py27-fail2ban
py27-imagesize py27-ipaddr py27-postfix-policyd-spf-python py27-pygments py27-pyspf py27-pystemmer py27-pytz py27-setuptools py27-six py27-snowballstemmer py27-sphinx py27-sphinx_rtd_theme py27-sqlite3
python2 python27 re2c readline rhash rsync scons screen spamassassin sqlite3 t1lib unzoo vsftpd-ssl xextproto xproto yajl

File: /etc/rc.conf

zfs_enable="YES" # For ZFS filesystems
ntpd_enable="YES"
sshd_enable="YES"
sendmail_enable="NO"
sendmail_submit_enable="NO"
sendmail_outbound_enable="NO"
sendmail_msp_queue_enable="NO"
mysql_enable="YES"
postgresql_enable="YES"
apache24_enable="YES"
postfix_enable="YES"
dovecot_enable="YES"
spamd_enable="YES"
spamd_flags="-d -m5 -x -q -Q -u nobody"
milteropendkim_enable="YES"
milteropendkim_uid="opendkim"
opendmarc_enable="YES"
opendmarc_runas="opendmarc"
clamav_clamd_enable="YES"
clamav_freshclam_enable="YES"
clamav_milter_enable="YES"
loginscript_enable="YES"

File: /usr/local/etc/apache24/httpd.conf

ServerName demo.domain.tld

Execute:

cp /usr/local/etc/php.ini-production /usr/local/etc/php.ini

File: /usr/local/etc/php.ini

date.timezone = UTC

Configure Dovecot

mkdir -p /etc/skel/Maildir/{cur,new,tmp}
mkdir -p /usr/local/vhosts
mkdir -p /usr/local/etc/postfix/keys

Create or Copy Certificates in: /usr/local/etc/postfix/keys/

/usr/local/etc/postfix/keys/server.crt
/usr/local/etc/postfix/keys/server.key

Create vmail user, group

pw groupadd -n vmail -g 5000
pw adduser -n vmail -c "Virtual mail user" -u 5000 -g 5000 -d /usr/local/vhosts -s /usr/sbin/nologin

Change permissions, owner, group

chown -R vmail:vmail /usr/local/vhosts/
chown -R vmail:dovecot /usr/local/etc/dovecot/
chmod -R o-rwx /usr/local/etc/dovecot/

Create Log file: /var/log/dovecot.log (if not exists)

touch /var/log/dovecot.log
chown vmail:vmail /var/log/dovecot.log

File: /usr/local/etc/dovecot/dovecot.conf

auth_mechanisms = plain login
auth_verbose = yes
default_client_limit = 2560
default_process_limit = 512
dict {
  acl = mysql:/usr/local/etc/dovecot/dovecot-dict-sql.conf.ext
  quota = mysql:/usr/local/etc/dovecot/dovecot-dict-sql.conf.ext
}
log_path = /var/log/dovecot.log
mail_home = /usr/local/vhosts/mail/%d/%n
mail_location = maildir:/usr/local/vhosts/mail/%d/%n:LAYOUT=fs
mail_max_userip_connections = 20
mail_plugins = quota acl
mail_privileged_group = vmail
mail_shared_explicit_inbox = yes
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope encoded-character vacation subaddress comparator-i;ascii-numeric relational regex imap4flags copy include variables body enotify environment mailbox date index ihave duplicate mime foreverypart extracttext
mbox_write_locks = fcntl
namespace {
  inbox = no
  list = children
  location = maildir:/usr/local/vhosts/mail/%%d/%%n:LAYOUT=fs:INDEX=/usr/local/vhosts/indexes/%d/%n/shared/%%u:INDEXPVT=/usr/local/vhosts/indexes/%d/%n/shared/%%u
  prefix = shared/%%d/%%n/
  separator = /
  subscriptions = no
  type = shared
}
namespace inbox {
  inbox = yes
  list = yes
  location =
  mailbox Drafts {
    auto = subscribe
    special_use = \Drafts
  }
  mailbox Junk {
    auto = subscribe
    special_use = \Junk
  }
  mailbox Sent {
    auto = subscribe
    special_use = \Sent
  }
  mailbox Trash {
    auto = subscribe
    special_use = \Trash
  }
  prefix =
  separator = /
  type = private
}
passdb {
  args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
plugin {
  acl = vfile
  acl_shared_dict = proxy::acl
  quota = dict:User quota::proxy::quota
  quota_rule2 = Trash:storage=+100M
  sieve = /usr/local/vhosts/mail/%d/%n/.dovecot.sieve
  sieve_before = /usr/local/vhosts/sieve/before.d/
  sieve_dir = /usr/local/vhosts/mail/%d/%n
  sieve_global_dir = /usr/local/vhosts/sieve/%d
  sieve_global_path = /usr/local/vhosts/sieve/%d/default.sieve
}
protocols = imap lmtp sieve
service auth-worker {
  user = vmail
}
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0660
    user = postfix
  }
  unix_listener auth-userdb {
    mode = 0600
    user = vmail
  }
  user = dovecot
}
service dict {
  unix_listener dict {
    mode = 0600
    user = vmail
  }
}
service imap-login {
  inet_listener imap {
    port = 143
  }
}
service lmtp {
  unix_listener /var/spool/postfix/private/dovecot-lmtp {
    group = postfix
    mode = 0660
    user = postfix
  }
}
service managesieve-login {
  inet_listener sieve {
    port = 4190
  }
  process_min_avail = 0
  service_count = 1
  vsz_limit = 64 M
}
ssl_cert = </usr/local/etc/postfix/keys/server.crt
ssl_key =  </usr/local/etc/postfix/keys/server.key
userdb {
  args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
protocol lmtp {
  mail_plugins = quota acl sieve
}
protocol lda {
  mail_plugins = quota acl sieve acl
  postmaster_address = root
}
protocol imap {
  imap_client_workarounds = tb-extra-mailbox-sep
  mail_plugins = quota acl imap_quota imap_acl
}

File: /usr/local/etc/dovecot/dovecot-dict-sql.conf.ext

connect = host=127.0.0.1 dbname=mailserver user=mailadmin password=< password >
map {
  pattern = priv/quota/storage
  table = quota2
  username_field = username
  value_field = bytes
}

map {
  pattern = priv/quota/messages
  table = quota2
  username_field = username
  value_field = messages
}

map {
  pattern = shared/shared-boxes/user/$to/$from
  table = user_shares
  value_field = dummy
 
  fields {
    from_user = $from
    to_user = $to
  }
}
 
map {
  pattern = shared/shared-boxes/anyone/$from
  table = anyone_shares
  value_field = dummy
 
  fields {
    from_user = $from
  }
}

File: /usr/local/etc/dovecot/dovecot-sql.conf.ext

driver = mysql
connect = host=127.0.0.1 dbname=mailserver user=mailuser password=< password >
default_pass_scheme = SHA512-CRYPT
user_query = SELECT CONCAT('*:messages=1000000:bytes=', quota) as quota_rule, 5000 AS uid, 5000 AS gid FROM mailbox WHERE username = '%u' AND active = '1'
password_query = SELECT username as user, password FROM mailbox WHERE username = '%u' AND active = '1'
iterate_query = SELECT username AS user FROM mailserver.mailbox

Configure POSTFIX

mv /usr/local/etc/postfix/main.cf{,.orig}
mv /usr/local/etc/postfix/master.cf{,.orig}

File: /usr/local/etc/postfix/main.cf

alias_database = hash:/etc/aliases
alias_maps = hash:/etc/aliases
append_dot_mydomain = no
biff = no
command_directory = /usr/local/sbin
compatibility_level = 2
daemon_directory = /usr/local/libexec/postfix
data_directory = /var/db/postfix
debug_peer_level = 2
debugger_command = PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin ddd $daemon_directory/$process_name $process_id & sleep 5
delay_warning_time = 4h
dovecot-spamass_destination_recipient_limit = 1
html_directory = /usr/local/share/doc/postfix
inet_interfaces = all
inet_protocols = all
mail_owner = postfix
mailbox_command = /usr/local/libexec/dovecot/deliver
mailbox_size_limit = 0
mailq_path = /usr/local/bin/mailq
manpage_directory = /usr/local/man
message_size_limit = 52428800
milter_default_action = accept
milter_protocol = 2
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
newaliases_path = /usr/local/bin/newaliases
non_smtpd_milters = $smtpd_milters
policyd-spf_time_limit = 3600
queue_directory = /var/spool/postfix
readme_directory = /usr/local/share/doc/postfix
recipient_bcc_maps = proxy:mysql:/usr/local/etc/postfix/recipient_bcc_maps_user.cf, proxy:mysql:/usr/local/etc/postfix/recipient_bcc_maps_domain.cf
recipient_delimiter = +
relay_domains = proxy:mysql:/usr/local/etc/postfix/mysql-relay-domains-maps.cf
sample_directory = /usr/local/etc/postfix
sender_bcc_maps = proxy:mysql:/usr/local/etc/postfix/sender_bcc_maps_user.cf, proxy:mysql:/usr/local/etc/postfix/sender_bcc_maps_domain.cf
sender_dependent_default_transport_maps = hash:/usr/local/etc/postfix/sender_transport
sendmail_path = /usr/local/sbin/sendmail.postfix
setgid_group = maildrop
smtp_tls_session_cache_database = btree:$data_directory/smtp_tls_session_cache
smtp_use_tls = yes
smtpd_banner = $myhostname ESMTP $mail_name
smtpd_milters = inet:127.0.0.1:8891, inet:127.0.0.1:8893, unix:/var/run/clamav/clmilter.sock
smtpd_recipient_restrictions = permit_sasl_authenticated, permit_mynetworks, reject_unauth_destination, check_policy_service unix:private/policyd-spf, reject_rbl_client bl.spamcop.net, reject_rbl_client zen.spamhaus.org, permit
smtpd_sasl_auth_enable = yes
smtpd_sasl_path = private/auth
smtpd_sasl_type = dovecot
smtpd_sender_restrictions = hash:/usr/local/etc/postfix/access
smtpd_tls_cert_file = /usr/local/etc/postfix/keys/server.crt
smtpd_tls_key_file = /usr/local/etc/postfix/keys/server.key
smtpd_tls_loglevel = 1
smtpd_tls_received_header = yes
smtpd_tls_security_level = may
smtpd_tls_session_cache_timeout = 3600s
smtpd_use_tls = yes
tls_random_source = dev:/dev/urandom
transport_maps = hash:/usr/local/etc/postfix/transport
unknown_local_recipient_reject_code = 550
virtual_alias_maps = proxy:mysql:/usr/local/etc/postfix/mysql-virtual-alias-maps.cf, proxy:mysql:/usr/local/etc/postfix/mysql-virtual-alias-domain-maps.cf, proxy:mysql:/usr/local/etc/postfix/mysql-virtual-alias-domain-catchall-maps.cf, regexp:/usr/local/etc/postfix/virtual_regexp
virtual_mailbox_domains = proxy:mysql:/usr/local/etc/postfix/mysql-virtual-mailbox-domains.cf
virtual_mailbox_maps = proxy:mysql:/usr/local/etc/postfix/mysql-virtual-mailbox-maps.cf, proxy:mysql:/usr/local/etc/postfix/mysql-virtual-alias-domain-mailbox-maps.cf
virtual_transport = dovecot-spamass

File: /usr/local/etc/postfix/master.cf

smtp     inet  n   -   n   -   -   smtpd
  -o myhostname=built102.domain.tld
submission inet n       -       -       -       -       smtpd
smtps     inet  n       -       -       -       -       smtpd
  -o smtpd_tls_wrappermode=yes
  -o smtpd_sasl_auth_enable=yes
  -o smtp_helo_name=demo.domain.tld
  -o smtpd_client_restrictions=permit_sasl_authenticated,reject
policyd-spf  unix  -       n       n       -       0       spawn
  user=nobody argv=/usr/local/bin/policyd-spf
pickup    unix  n       -       n       60      1       pickup
cleanup   unix  n       -       n       -       0       cleanup
qmgr      unix  n       -       n       300     1       qmgr
tlsmgr    unix  -       -       n       1000?   1       tlsmgr
rewrite   unix  -       -       n       -       -       trivial-rewrite
bounce    unix  -       -       n       -       0       bounce
defer     unix  -       -       n       -       0       bounce
trace     unix  -       -       n       -       0       bounce
verify    unix  -       -       n       -       1       verify
flush     unix  n       -       n       1000?   0       flush
proxymap  unix  -       -       n       -       -       proxymap
proxywrite unix -       -       n       -       1       proxymap
smtp      unix  -       -       n       -       -       smtp
relay     unix  -       -       n       -       -       smtp
showq     unix  n       -       n       -       -       showq
error     unix  -       -       n       -       -       error
retry     unix  -       -       n       -       -       error
discard   unix  -       -       n       -       -       discard
local     unix  -       n       n       -       -       local
virtual   unix  -       n       n       -       -       virtual
lmtp      unix  -       -       n       -       -       lmtp
anvil     unix  -       -       n       -       1       anvil
scache    unix  -       -       n       -       1       scache
dovecot-spamass   unix  -   n   n   -   -       pipe 
  flags=DRhu user=vmail:vmail argv=/usr/local/bin/spamc -u ${recipient} -e /usr/local/libexec/dovecot/dovecot-lda -f ${sender} -d ${recipient}

Configure Postfix to work with MySQL (based on PostfixAdmin DB schema)

File: /usr/local/etc/postfix/recipient_bcc_maps_user.cf

user = mailuser
password = < password >
hosts = 127.0.0.1
dbname = mailserver
query = SELECT recipient_bcc_user.bcc_address FROM recipient_bcc_user,domain WHERE recipient_bcc_user.username='%s' AND recipient_bcc_user.domain='%d' AND recipient_bcc_user.domain=domain.domain AND domain.backupmx=0 AND domain.active=1 AND recipient_bcc_user.active=1

File: /usr/local/etc/postfix/recipient_bcc_maps_domain.cf

user = mailuser
password = < password >
hosts = 127.0.0.1
dbname = mailserver
query = SELECT bcc_address FROM recipient_bcc_domain WHERE domain='%d' AND active=1

File: /usr/local/etc/postfix/mysql-relay-domains-maps.cf

user = mailuser
password = < password >
hosts = 127.0.0.1
dbname = mailserver
query = SELECT domain FROM domain WHERE domain='%s' and backupmx = '1'

File: /usr/local/etc/postfix/sender_bcc_maps_user.cf

user = mailuser
password = < password >
hosts = 127.0.0.1
dbname = mailserver
query = SELECT sender_bcc_user.bcc_address FROM sender_bcc_user,domain WHERE sender_bcc_user.username='%s' AND sender_bcc_user.domain='%d' AND sender_bcc_user.domain=domain.domain AND domain.backupmx=0 AND domain.active=1 AND sender_bcc_user.active=1

File: /usr/local/etc/postfix/sender_bcc_maps_domain.cf

user = mailuser
password = < password >
hosts = 127.0.0.1
dbname = mailserver
query = SELECT bcc_address FROM sender_bcc_domain WHERE domain='%d' AND active=1

File: /usr/local/etc/postfix/mysql-virtual-alias-maps.cf

user = mailuser
password = < password >
hosts = 127.0.0.1
dbname = mailserver
query = SELECT goto FROM alias WHERE address='%s' AND active = '1'

File: /usr/local/etc/postfix/mysql-virtual-alias-domain-maps.cf

user = mailuser
password = < password >
hosts = 127.0.0.1
dbname = mailserver
query = SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = '%d' and alias.address = CONCAT('%u', '@', alias_domain.target_domain) AND alias.active = 1 AND alias_domain.active='1'

File: /usr/local/etc/postfix/mysql-virtual-alias-domain-catchall-maps.cf

user = mailuser
password = < password >
hosts = 127.0.0.1
dbname = mailserver
query  = SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = '%d' and alias.address = CONCAT('@', alias_domain.target_domain) AND alias.active = 1 AND alias_domain.active='1'

File: /usr/local/etc/postfix/mysql-virtual-mailbox-domains.cf

user = mailuser
password = < password >
hosts = 127.0.0.1
dbname = mailserver
query = SELECT domain FROM domain WHERE domain='%s' AND backupmx = '0' AND active = '1'

File: /usr/local/etc/postfix/mysql-virtual-mailbox-maps.cf

user = mailuser
password = < password >
hosts = 127.0.0.1
dbname = mailserver
query = SELECT maildir FROM mailbox WHERE username='%s' AND active = '1'

File: /usr/local/etc/postfix/mysql-virtual-alias-domain-mailbox-maps.cf

user = mailuser
password = < password >
hosts = 127.0.0.1
dbname = mailserver
query = SELECT maildir FROM mailbox,alias_domain WHERE alias_domain.alias_domain = '%d' and mailbox.username = CONCAT('%u', '@', alias_domain.target_domain) AND mailbox.active = 1 AND alias_domain.active='1'

File: /usr/local/etc/postfix/mysql-virtual-mailbox-limit-maps.cf

user = mailuser
password = < password >
hosts = 127.0.0.1
dbname = mailserver
query = SELECT quota FROM mailbox WHERE username='%s' AND active = '1'

Create the following files if they don’t exist and then execute

postmap hash:/usr/local/etc/postfix/sender_transport
postmap hash:/usr/local/etc/postfix/access
postmap hash:/usr/local/etc/postfix/transport
touch /usr/local/etc/postfix/virtual_regexp
      
newaliases

Set up MySQL DB schemas for Virtual Domains and Users

Initialize MySQL ( service mysql-server start )

/usr/local/etc/mysql/my.cnf

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysql
tmpdir                          = /var/db/mysql_tmpdir
slave-load-tmpdir               = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
log-bin                         = mysql-bin
log-output                      = TABLE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
innodb_data_home_dir            = /var/db/mysql
innodb_log_group_home_dir       = /var/db/mysql

Cerate Database ‘mailserver’ (based on PostfixAdmin DB schema)

-- MySQL dump 10.13  Distrib 5.6.35, for FreeBSD11.0 (amd64)
--
-- Host: localhost    Database: mailserver
-- ------------------------------------------------------
-- Server version 5.6.35

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `mailserver`
--

/*!40000 DROP DATABASE IF EXISTS `mailserver`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mailserver` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `mailserver`;

--
-- Table structure for table `admin`
--

DROP TABLE IF EXISTS `admin`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `admin` (
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `superadmin` tinyint(1) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Admins';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `alias`
--

DROP TABLE IF EXISTS `alias`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `alias` (
  `address` varchar(255) NOT NULL,
  `goto` text NOT NULL,
  `domain` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`address`),
  KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Aliases';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `alias_domain`
--

DROP TABLE IF EXISTS `alias_domain`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `alias_domain` (
  `alias_domain` varchar(255) NOT NULL,
  `target_domain` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`alias_domain`),
  KEY `active` (`active`),
  KEY `target_domain` (`target_domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Domain Aliases';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `anyone_shares`
--

DROP TABLE IF EXISTS `anyone_shares`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `anyone_shares` (
  `from_user` varchar(100) NOT NULL,
  `dummy` char(1) DEFAULT '1',
  PRIMARY KEY (`from_user`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `cake_d_c_users_phinxlog`
--

DROP TABLE IF EXISTS `cake_d_c_users_phinxlog`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cake_d_c_users_phinxlog` (
  `version` bigint(20) NOT NULL,
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `config`
--

DROP TABLE IF EXISTS `config`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `value` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='PostfixAdmin settings';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `domain`
--

DROP TABLE IF EXISTS `domain`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `domain` (
  `domain` varchar(255) NOT NULL,
  `description` varchar(255) CHARACTER SET utf8 NOT NULL,
  `aliases` int(10) NOT NULL DEFAULT '0',
  `mailboxes` int(10) NOT NULL DEFAULT '0',
  `maxquota` bigint(20) NOT NULL DEFAULT '0',
  `quota` bigint(20) NOT NULL DEFAULT '0',
  `transport` varchar(255) NOT NULL,
  `backupmx` tinyint(1) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Domains';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `domain_admins`
--

DROP TABLE IF EXISTS `domain_admins`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `domain_admins` (
  `username` varchar(255) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Domain Admins';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `fetchmail`
--

DROP TABLE IF EXISTS `fetchmail`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fetchmail` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `mailbox` varchar(255) NOT NULL,
  `src_server` varchar(255) NOT NULL,
  `src_auth` enum('password','kerberos_v5','kerberos','kerberos_v4','gssapi','cram-md5','otp','ntlm','msn','ssh','any') DEFAULT NULL,
  `src_user` varchar(255) NOT NULL,
  `src_password` varchar(255) NOT NULL,
  `src_folder` varchar(255) NOT NULL,
  `poll_time` int(11) unsigned NOT NULL DEFAULT '10',
  `fetchall` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `keep` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `protocol` enum('POP3','IMAP','POP2','ETRN','AUTO') DEFAULT NULL,
  `usessl` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `sslcertck` tinyint(1) NOT NULL DEFAULT '0',
  `sslcertpath` varchar(255) CHARACTER SET utf8 DEFAULT '',
  `sslfingerprint` varchar(255) DEFAULT '',
  `extra_options` text,
  `returned_text` text,
  `mda` varchar(255) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `log`
--

DROP TABLE IF EXISTS `log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `log` (
  `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `username` varchar(255) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `action` varchar(255) NOT NULL,
  `data` text NOT NULL,
  KEY `timestamp` (`timestamp`),
  KEY `domain_timestamp` (`domain`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Log';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `mailbox`
--

DROP TABLE IF EXISTS `mailbox`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mailbox` (
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 NOT NULL,
  `maildir` varchar(255) NOT NULL,
  `quota` bigint(20) NOT NULL DEFAULT '0',
  `local_part` varchar(255) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`username`),
  KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Mailboxes';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `quota`
--

DROP TABLE IF EXISTS `quota`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `quota` (
  `username` varchar(255) NOT NULL,
  `path` varchar(100) NOT NULL,
  `current` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`username`,`path`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `quota2`
--

DROP TABLE IF EXISTS `quota2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `quota2` (
  `username` varchar(100) NOT NULL,
  `bytes` bigint(20) NOT NULL DEFAULT '0',
  `messages` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `recipient_bcc_domain`
--

DROP TABLE IF EXISTS `recipient_bcc_domain`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `recipient_bcc_domain` (
  `domain` varchar(255) NOT NULL DEFAULT '',
  `bcc_address` varchar(255) NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `expired` datetime NOT NULL DEFAULT '9999-12-31 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`domain`),
  KEY `bcc_address` (`bcc_address`),
  KEY `expired` (`expired`),
  KEY `active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `recipient_bcc_user`
--

DROP TABLE IF EXISTS `recipient_bcc_user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `recipient_bcc_user` (
  `username` varchar(255) NOT NULL DEFAULT '',
  `bcc_address` varchar(255) NOT NULL DEFAULT '',
  `domain` varchar(255) NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `expired` datetime NOT NULL DEFAULT '9999-12-31 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`username`),
  KEY `bcc_address` (`bcc_address`),
  KEY `expired` (`expired`),
  KEY `active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `sender_bcc_domain`
--

DROP TABLE IF EXISTS `sender_bcc_domain`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sender_bcc_domain` (
  `domain` varchar(255) NOT NULL DEFAULT '',
  `bcc_address` varchar(255) NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `expired` datetime NOT NULL DEFAULT '9999-12-31 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`domain`),
  KEY `bcc_address` (`bcc_address`),
  KEY `expired` (`expired`),
  KEY `active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `sender_bcc_user`
--

DROP TABLE IF EXISTS `sender_bcc_user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sender_bcc_user` (
  `username` varchar(255) NOT NULL DEFAULT '',
  `bcc_address` varchar(255) NOT NULL DEFAULT '',
  `domain` varchar(255) NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `expired` datetime NOT NULL DEFAULT '9999-12-31 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`username`),
  KEY `bcc_address` (`bcc_address`),
  KEY `domain` (`domain`),
  KEY `expired` (`expired`),
  KEY `active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `share_folder`
--

DROP TABLE IF EXISTS `share_folder`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `share_folder` (
  `from_user` varchar(255) CHARACTER SET ascii NOT NULL,
  `to_user` varchar(255) CHARACTER SET ascii NOT NULL,
  `dummy` char(1) DEFAULT NULL,
  PRIMARY KEY (`from_user`,`to_user`),
  KEY `from_user` (`from_user`),
  KEY `to_user` (`to_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `social_accounts`
--

DROP TABLE IF EXISTS `social_accounts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `social_accounts` (
  `id` char(36) NOT NULL,
  `user_id` varchar(36) NOT NULL,
  `provider` varchar(255) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `reference` varchar(255) NOT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `description` text,
  `link` varchar(255) NOT NULL,
  `token` varchar(500) NOT NULL,
  `token_secret` varchar(500) DEFAULT NULL,
  `token_expires` datetime DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `data` text NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `user_shares`
--

DROP TABLE IF EXISTS `user_shares`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_shares` (
  `from_user` varchar(100) NOT NULL,
  `to_user` varchar(100) NOT NULL,
  `dummy` char(1) DEFAULT '1',
  PRIMARY KEY (`from_user`,`to_user`),
  KEY `to_user` (`to_user`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` char(36) NOT NULL,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `token` varchar(255) DEFAULT NULL,
  `token_expires` datetime DEFAULT NULL,
  `api_token` varchar(255) DEFAULT NULL,
  `activation_date` datetime DEFAULT NULL,
  `tos_date` datetime DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `is_superuser` tinyint(1) NOT NULL DEFAULT '0',
  `role` varchar(255) DEFAULT 'user',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vacation`
--

DROP TABLE IF EXISTS `vacation`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vacation` (
  `email` varchar(255) NOT NULL,
  `subject` varchar(255) CHARACTER SET utf8 NOT NULL,
  `body` text CHARACTER SET utf8 NOT NULL,
  `activefrom` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
  `activeuntil` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
  `cache` text,
  `domain` varchar(255) NOT NULL,
  `interval_time` int(11) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`email`),
  KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Vacation';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vacation_notification`
--

DROP TABLE IF EXISTS `vacation_notification`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vacation_notification` (
  `on_vacation` varchar(255) CHARACTER SET latin1 NOT NULL,
  `notified` varchar(255) CHARACTER SET latin1 NOT NULL,
  `notified_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`on_vacation`,`notified`),
  CONSTRAINT `vacation_notification_pkey` FOREIGN KEY (`on_vacation`) REFERENCES `vacation` (`email`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Postfix Admin - Virtual Vacation Notifications';
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Cerate Database ‘spamassassin’

-- MySQL dump 10.13  Distrib 5.6.35, for FreeBSD11.0 (amd64)
--
-- Host: localhost    Database: spamassassin
-- ------------------------------------------------------
-- Server version 5.6.35

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `spamassassin`
--

/*!40000 DROP DATABASE IF EXISTS `spamassassin`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `spamassassin` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `spamassassin`;

--
-- Table structure for table `awl`
--

DROP TABLE IF EXISTS `awl`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `awl` (
  `username` varchar(100) NOT NULL DEFAULT '',
  `email` varbinary(255) NOT NULL DEFAULT '',
  `ip` varchar(40) NOT NULL DEFAULT '',
  `count` int(11) NOT NULL DEFAULT '0',
  `totscore` float NOT NULL DEFAULT '0',
  `signedby` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`username`,`email`,`signedby`,`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `bayes_expire`
--

DROP TABLE IF EXISTS `bayes_expire`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bayes_expire` (
  `id` int(11) NOT NULL DEFAULT '0',
  `runtime` int(11) NOT NULL DEFAULT '0',
  KEY `bayes_expire_idx1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `bayes_global_vars`
--

DROP TABLE IF EXISTS `bayes_global_vars`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bayes_global_vars` (
  `variable` varchar(30) NOT NULL DEFAULT '',
  `value` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`variable`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `bayes_seen`
--

DROP TABLE IF EXISTS `bayes_seen`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bayes_seen` (
  `id` int(11) NOT NULL DEFAULT '0',
  `msgid` varchar(200) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `flag` char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`msgid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `bayes_token`
--

DROP TABLE IF EXISTS `bayes_token`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bayes_token` (
  `id` int(11) NOT NULL DEFAULT '0',
  `token` binary(5) NOT NULL DEFAULT '\0\0\0\0\0',
  `spam_count` int(11) NOT NULL DEFAULT '0',
  `ham_count` int(11) NOT NULL DEFAULT '0',
  `atime` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`token`),
  KEY `bayes_token_idx1` (`id`,`atime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `bayes_vars`
--

DROP TABLE IF EXISTS `bayes_vars`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bayes_vars` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(200) NOT NULL DEFAULT '',
  `spam_count` int(11) NOT NULL DEFAULT '0',
  `ham_count` int(11) NOT NULL DEFAULT '0',
  `token_count` int(11) NOT NULL DEFAULT '0',
  `last_expire` int(11) NOT NULL DEFAULT '0',
  `last_atime_delta` int(11) NOT NULL DEFAULT '0',
  `last_expire_reduce` int(11) NOT NULL DEFAULT '0',
  `oldest_token_age` int(11) NOT NULL DEFAULT '2147483647',
  `newest_token_age` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `bayes_vars_idx1` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `userpref`
--

DROP TABLE IF EXISTS `userpref`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `userpref` (
  `username` varchar(100) NOT NULL DEFAULT '',
  `preference` varchar(50) NOT NULL DEFAULT '',
  `value` varchar(100) NOT NULL DEFAULT '',
  `prefid` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`prefid`),
  KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Initializing data for table ‘bayes_global_vars’

LOCK TABLES `bayes_global_vars` WRITE;
/*!40000 ALTER TABLE `bayes_global_vars` DISABLE KEYS */;
INSERT INTO `bayes_global_vars` VALUES ('VERSION','3');
/*!40000 ALTER TABLE `bayes_global_vars` ENABLE KEYS */;
UNLOCK TABLES;

Initializing data for table ‘userpref’ (suggested values)

LOCK TABLES `userpref` WRITE;
/*!40000 ALTER TABLE `userpref` DISABLE KEYS */;
INSERT INTO `userpref` VALUES ('$GLOBAL','required_hits','5.0',1),('$GLOBAL','report_safe','1',2),('$GLOBAL','use_bayes','1',3),('$GLOBAL','use_dcc','1',4),('$GLOBAL','score SPF_FAIL','4.5',21),('$GLOBAL','score SPF_HELO_FAIL','4.5',22),('$GLOBAL','score SPF_SOFTFAIL','2.0',23),('$GLOBAL','score SPF_HELO_SOFTFAIL','2.0',24),('$GLOBAL','score SPF_NEUTRAL','2.0',25),('$GLOBAL','score T_DKIM_INVALID','2.0',45),('$GLOBAL','score DKIM_ADSP_NXDOMAIN','2.0',46),('$GLOBAL','score SPF_NONE','2.0',47),('$GLOBAL','score RDNS_NONE','1.5',48),('$GLOBAL','score RCVD_IN_SORBS_DUL','3.0',49),('$GLOBAL','score RCVD_IN_SORBS_WEB','3.0',50),('$GLOBAL','score RCVD_IN_SORBS_SMTP','3.0',51),('$GLOBAL','score RCVD_IN_SORBS_HTTP','3.0',52),('$GLOBAL','score URIBL_WS_SURBL','3.0',53),('$GLOBAL','score RCVD_IN_XBL','3.0',54),('$GLOBAL','score RCVD_IN_BRBL_LASTEXT','7.0',55);
/*!40000 ALTER TABLE `userpref` ENABLE KEYS */;
UNLOCK TABLES;

Cerate Database ‘a0001’ — Administration DashBoard

-- MySQL dump 10.13  Distrib 5.6.35, for FreeBSD11.0 (amd64)
--
-- Host: localhost    Database: a0001
-- ------------------------------------------------------
-- Server version 5.6.35

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `a0001`
--

/*!40000 DROP DATABASE IF EXISTS `a0001`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `a0001` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `a0001`;

--
-- Table structure for table `accounts`
--

DROP TABLE IF EXISTS `accounts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `accounts` (
  `id` char(36) NOT NULL,
  `datasource` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `cake_d_c_users_phinxlog`
--

DROP TABLE IF EXISTS `cake_d_c_users_phinxlog`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cake_d_c_users_phinxlog` (
  `version` bigint(20) NOT NULL,
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `shards`
--

DROP TABLE IF EXISTS `shards`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `shards` (
  `id` char(36) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `shards_accounts`
--

DROP TABLE IF EXISTS `shards_accounts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `shards_accounts` (
  `shard_id` char(36) NOT NULL DEFAULT '',
  `account_id` char(36) NOT NULL DEFAULT '',
  PRIMARY KEY (`shard_id`,`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `social_accounts`
--

DROP TABLE IF EXISTS `social_accounts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `social_accounts` (
  `id` char(36) NOT NULL,
  `user_id` varchar(36) NOT NULL,
  `provider` varchar(255) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `reference` varchar(255) NOT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `description` text,
  `link` varchar(255) NOT NULL,
  `token` varchar(500) NOT NULL,
  `token_secret` varchar(500) DEFAULT NULL,
  `token_expires` datetime DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `data` text NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` char(36) NOT NULL,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `token` varchar(255) DEFAULT NULL,
  `token_expires` datetime DEFAULT NULL,
  `api_token` varchar(255) DEFAULT NULL,
  `activation_date` datetime DEFAULT NULL,
  `tos_date` datetime DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `is_superuser` tinyint(1) NOT NULL DEFAULT '0',
  `role` varchar(255) DEFAULT 'user',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `users_shards`
--

DROP TABLE IF EXISTS `users_shards`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users_shards` (
  `user_id` char(36) NOT NULL DEFAULT '',
  `shard_id` char(36) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`,`shard_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Update mysql Database

-- mailuser: 'SELECT' PRIVILEGES.
-- mailadmin: 'SELECT, INSERT, UPDATE, DELETE' PRIVILEGES.

GRANT SELECT ON mailserver.* TO 'mailuser'@'localhost' IDENTIFIED BY '< password >';
GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY '< password >';

GRANT SELECT ON spamassassin.* TO 'mailuser'@'localhost' IDENTIFIED BY '< password >';
GRANT SELECT ON spamassassin.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY '< password >';

GRANT ALL PRIVILEGES ON mailserver.* TO 'mailadmin'@'localhost' IDENTIFIED BY '< password >';
GRANT ALL PRIVILEGES ON mailserver.* TO 'mailadmin'@'127.0.0.1' IDENTIFIED BY '< password >';

GRANT ALL PRIVILEGES ON spamassassin.* TO 'mailadmin'@'localhost' IDENTIFIED BY '< password >';
GRANT ALL PRIVILEGES ON spamassassin.* TO 'mailadmin'@'127.0.0.1' IDENTIFIED BY '< password >';

GRANT ALL PRIVILEGES ON a0001.* TO 'mailadmin'@'localhost' IDENTIFIED BY '< password >';
GRANT ALL PRIVILEGES ON a0001.* TO 'mailadmin'@'127.0.0.1' IDENTIFIED BY '< password >';

GRANT ALL PRIVILEGES ON roundcubemail.* TO 'mailadmin'@'localhost' IDENTIFIED BY '< password >';
GRANT ALL PRIVILEGES ON roundcubemail.* TO 'mailadmin'@'127.0.0.1' IDENTIFIED BY '< password >';

FLUSH PRIVILEGES;

Configurtation Test (Be sure to replace example.com with you domain)

postmap -q example.com mysql:/usr/local/etc/postfix/mysql-virtual-mailbox-domains.cf
postmap -q email1@example.com mysql:/usr/local/etc/postfix/mysql-virtual-mailbox-maps.cf
postmap -q alias@example.com mysql:/usr/local/etc/postfix/mysql-virtual-alias-maps.cf

Configure Opendkim

mv /usr/local/etc/mail/opendkim.conf{,.orig}
mkdir -p /usr/local/etc/opendkim/
mkdir -p /usr/local/etc/opendkim/keys

Create opendkim user, group

pw groupadd -n opendkim -g 5001
pw adduser -n opendkim -c "opendkim user" -u 5001 -g 5001 -d /usr/local/etc/opendkim -s /usr/sbin/nologin

Create if not exist

touch /usr/local/etc/opendkim/SigningTable
touch /usr/local/etc/opendkim/KeyTable

File: /usr/local/etc/mail/opendkim.conf

AutoRestart             Yes
AutoRestartRate         10/1h
LogWhy                  Yes
Syslog                  Yes
SyslogSuccess           Yes
Mode                    sv
Canonicalization        relaxed/simple
ExternalIgnoreList      refile:/usr/local/etc/opendkim/TrustedHosts
InternalHosts           refile:/usr/local/etc/opendkim/TrustedHosts
KeyTable                refile:/usr/local/etc/opendkim/KeyTable
SigningTable            refile:/usr/local/etc/opendkim/SigningTable
SignatureAlgorithm      rsa-sha256
Socket                  inet:8891@localhost
UMask                   022
UserID                  opendkim:opendkim
TemporaryDirectory      /var/tmp

File: /usr/local/etc/opendkim/TrustedHosts

127.0.0.1

Fix ownership

chown -R opendkim:opendkim /usr/local/etc/opendkim

Configure Opendmarc

mkdir -p /usr/local/etc/opendmarc/

Create opendmarc user, group

pw groupadd -n opendmarc -g 5002
pw adduser -n opendmarc -c "opendmarc user" -u 5002 -g 5002 -d /usr/local/etc/opendmarc -s /usr/sbin/nologin

File: /usr/local/etc/mail/opendmarc.conf

AuthservID demo.domain.tld
FailureReports true
FailureReportsSentBy postmaster@demo.domain.tld
HistoryFile /var/run/opendmarc/opendmarc.dat
IgnoreAuthenticatedClients true
IgnoreHosts /usr/local/etc/opendmarc/ignore.hosts
IgnoreMailFrom demo.domain.tld
MilterDebug 0
RejectFailures false
ReportCommand /usr/local/sbin/sendmail -t
RequiredHeaders true
Socket inet:8893@localhost
SPFSelfValidate true
Syslog true
SyslogFacility mail
TrustedAuthservIDs demo.domain.tld
UserID opendmarc

File: /usr/local/etc/opendmarc/ignore.hosts

127.0.0.1

Fix ownership

chown -R opendmarc:opendmarc /usr/local/etc/opendmarc

Configure SPAMASSASSIN

mv /usr/local/etc/mail/spamassassin/local.cf{,.orig}

File: /usr/local/etc/mail/spamassassin/local.cf

loadplugin Mail::SpamAssassin::Plugin::AWL
loadplugin Mail::SpamAssassin::Plugin::DCC

user_scores_dsn                 DBI:mysql:spamassassin:localhost
user_scores_sql_username        mailadmin
user_scores_sql_password        < password >   
user_scores_sql_custom_query    SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC

auto_whitelist_factory          Mail::SpamAssassin::SQLBasedAddrList
user_awl_dsn                    DBI:mysql:spamassassin:localhost
user_awl_sql_username           mailadmin
user_awl_sql_password           < password >
user_awl_sql_table                      awl
auto_whitelist_distinguish_signed       1

bayes_store_module              Mail::SpamAssassin::BayesStore::MySQL
bayes_sql_dsn                   DBI:mysql:spamassassin:localhost
bayes_sql_username              mailadmin
bayes_sql_password              < password >

Update spamassassin definitions (optional)

sa-update

Verify spamassassin configuration (optional)

spamassassin --lint -D

Spamassassin performance tune-up (suggestion)

bayes_auto_expire   0
bayes_expiry_max_db_size 1000000

## Place in crontab: sa-learn --force-expire

Configure DAVICAL

service postgresql initdb
mv /usr/local/www/davical/config/config.php{,.orig}

File: /usr/local/share/postgresql/pg_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# davical
local   davical    davical_app   trust
local   davical    davical_dba   trust

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               ident

File: /usr/local/www/davical/config/config.php

<?php $c->authenticate_hook['call'] = 'IMAP_PAM_check';
$c->authenticate_hook['config'] = array('imap_url' => '{localhost:143/imap/tls/novalidate-cert}');
include('drivers_imap_pam.php');
$c->admin_email = 'support@domain.tld';
$c->system_name = 'demo.domain.tld';
$c->pg_connect[] = 'dbname=davical port=5432 user=davical_app';
$c->enable_auto_schedule = false;
?>

Execute DB configuration script

service postgresql restart
chmod +x /usr/local/share/davical/dba/create-database.sh
su - pgsql
cd /usr/local/share/davical/
dba/create-database.sh
exit

chmod -x /usr/local/share/davical/dba/create-database.sh

** REMEMBER: Take note of the shown password for the 'admin' user

Recover lost Admin password
(password is the bit after the ‘**’ in the ‘password’ field)

su - pgsql
psql davical -c 'select username, password from usr;'

Configure APACHE

mkdir -p /usr/local/etc/ssl/apache/

** Create or copy certificates
/usr/local/etc/ssl/apache/server.crt
/usr/local/etc/ssl/apache/server.key

File: /usr/local/etc/apache24/Includes/davical-localhost.conf

<VirtualHost 127.0.0.1:80>

<IfModule security2_module>
    SecRuleEngine Off # Either enable or disable mod_security
</IfModule>


  DocumentRoot /usr/local/www/davical/htdocs
  DirectoryIndex index.php index.html
<Directory "/usr/local/www/davical/htdocs">
        Options Indexes FollowSymLinks MultiViews
        AllowOverride All
        Require all granted
</Directory>

  php_value include_path /usr/local/share/awl/inc/
  php_value magic_quotes_gpc 0
  php_value register_globals 0
  php_value error_reporting "E_ALL & ~E_NOTICE"
  php_value default_charset "utf-8"
</VirtualHost>

File: /usr/local/etc/apache24/httpd.conf

LoadModule ssl_module libexec/apache24/mod_ssl.so
LoadModule socache_shmcb_module libexec/apache24/mod_socache_shmcb.so
LoadModule deflate_module libexec/apache24/mod_deflate.so
LoadModule cgi_module libexec/apache24/mod_cgi.so
LoadModule rewrite_module libexec/apache24/mod_rewrite.so
Include etc/apache24/extra/httpd-default.conf
DirectoryIndex index.html index.htm index.php

<FilesMatch "\.php$">
    SetHandler application/x-httpd-php
</FilesMatch>
<FilesMatch "\.phps$">
     SetHandler application/x-httpd-php-source
</FilesMatch>
# <Directory "/usr/local/www/apache24/data"> ... </Directory>

/usr/local/etc/apache24/Includes/ssl.conf

Listen 443
SSLCipherSuite HIGH:MEDIUM:!MD5:!RC4
SSLProxyCipherSuite HIGH:MEDIUM:!MD5:!RC4
SSLHonorCipherOrder on 
SSLProtocol all -SSLv3
SSLProxyProtocol all -SSLv3
SSLPassPhraseDialog  builtin
SSLSessionCache        "shmcb:/var/run/ssl_scache(512000)"
SSLSessionCacheTimeout  300

File: /usr/local/etc/apache24/Includes/mail.conf
(Including RoundCube and Webadmin virtual hosts)

<VirtualHost *:443>

    SSLEngine On
    SSLCertificateFile /usr/local/etc/ssl/apache/server.crt
    SSLCertificateKeyFile /usr/local/etc/ssl/apache/server.key


DocumentRoot /usr/local/www/davical/htdocs
DirectoryIndex index.php index.html
<Directory "/usr/local/www/davical/htdocs">
        Options Indexes FollowSymLinks MultiViews
        AllowOverride All
        Require all granted
</Directory>

  php_value include_path /usr/local/share/awl/inc/
  php_value magic_quotes_gpc 0
  php_value register_globals 0
  php_value error_reporting "E_ALL & ~E_NOTICE"
  php_value default_charset "utf-8"


Alias /webmail "/usr/local/www/roundcube/"
<Directory "/usr/local/www/roundcube/">
   Options Indexes
   AllowOverride All
   Require all granted
</Directory>

Alias /webadmin "/usr/local/www/webadmin/"
<Directory "/usr/local/www/webadmin/">
   Options Indexes FollowSymLinks
   AllowOverride All
   Require all granted
</Directory>

</VirtualHost>

Configure Dashboard
For details check: https://github.com/sophimail/webadmin/blob/master/INSTALLATION.md

composer clearcache (optional)
git clone https://github.com/sophimail/webadmin.git
cd webadmin
composer install
chown -R :your-web-server-group-name webadmin

Update file: /usr/local/www/webadmin/config/app.php

<?php return [ 'debug' => filter_var(env('DEBUG', false), FILTER_VALIDATE_BOOLEAN),


    'reCaptcha' => [
    'secret' => '**** CHANGE ME ****',
    'key' => '**** CHANGE ME ****'
    ],

    '_Constants' => [
        'encrypt' => 'SHA512-CRYPT',
        'quota_multiplier' => '1048576', // Constant to convert Bytes to MB and vice versa. Either use '1024000' or '1048576'
        'password_length' => '5',
        'strong_password' => true, // at least two letters, at least two numbers, at least one special character (Validation rules: MailboxTable.php)
        'aliases' => '10',
        'mailboxes' => '10',
        'maxquota' => '10',
        'unlimited' => '10485760', // 10TB - define unlimited in MB (eg. total storage capacity)
        'domain_quota_default' => '2000',
        'transport_options' => ['virtual' => 'virtual',  'local' => 'local',  'relay' => 'relay'],
        'transport_default' => 'virtual',        
        'domain_in_mailbox' => false,
    ],
    
    'App' => [
        'namespace' => 'App',
        'title' => 'Company Dashboard',
        'encoding' => env('APP_ENCODING', 'UTF-8'),
        'defaultLocale' => env('APP_DEFAULT_LOCALE', 'es-ES'),
        'base' => false,
        'dir' => 'src',
        'webroot' => 'webroot',
        'wwwRoot' => WWW_ROOT,
        // 'baseUrl' => env('SCRIPT_NAME'),
        'fullBaseUrl' => false,
        'imageBaseUrl' => 'img/',
        'cssBaseUrl' => 'css/',
        'jsBaseUrl' => 'js/',
        'paths' => [
            'plugins' => [ROOT . DS . 'plugins' . DS],
            'templates' => [APP . 'Template' . DS],
            'locales' => [APP . 'Locale' . DS],
        ],
    ],

    'Security' => [
        'salt' => env('SECURITY_SALT', '**** CHANGE ME ****'),
    ],

    'Asset' => [
        // 'timestamp' => true,
    ],

    'Cache' => [
        'default' => [
            'className' => 'File',
            'path' => CACHE,
            'url' => env('CACHE_DEFAULT_URL', null),
        ],

        '_cake_core_' => [
            'className' => 'File',
            'prefix' => 'myapp_cake_core_',
            'path' => CACHE . 'persistent/',
            'serialize' => true,
            'duration' => '+2 minutes',
            'url' => env('CACHE_CAKECORE_URL', null),
        ],

        '_cake_model_' => [
            'className' => 'File',
            'prefix' => 'myapp_cake_model_',
            'path' => CACHE . 'models/',
            'serialize' => true,
            'duration' => '+2 minutes',
            'url' => env('CACHE_CAKEMODEL_URL', null),
        ],
    ],

    'Error' => [
        'errorLevel' => E_ALL & ~E_DEPRECATED,
        'exceptionRenderer' => 'Cake\Error\ExceptionRenderer',
        'skipLog' => [],
        'log' => true,
        'trace' => true,
    ],

    'EmailTransport' => [
        'default' => [
            'className' => 'Mail',
            // The following keys are used in SMTP transports
            'port' => 25,
            'timeout' => 30,
            'client' => null,
            'tls' => null,
            'url' => env('EMAIL_TRANSPORT_DEFAULT_URL', null),
        ],
    ],

    'Email' => [
        'default' => [
            'transport' => 'default',
            'from' => 'support@domain.tld',
            //'charset' => 'utf-8',
            //'headerCharset' => 'utf-8',
        ],
    ],

    'Datasources' => [
        'default' => [
            'className' => 'Cake\Database\Connection',
            'driver' => 'Cake\Database\Driver\Mysql',
            'persistent' => false,
            'host' => 'localhost',
            //'port' => 'non_standard_port_number',
            'username' => 'mailadmin',
            'password' => '< password >',
            'database' => 'a0001',
            'encoding' => 'utf8',
            'timezone' => 'UTC',
            'flags' => [],
            'cacheMetadata' => true,
            'log' => true,

            'quoteIdentifiers' => false,

            //'init' => ['SET GLOBAL innodb_stats_on_metadata = 0'],
            
            'url' => env('DATABASE_URL', null),
        ],

        'DB1' => [
            'className' => 'Cake\Database\Connection',
            'driver' => 'Cake\Database\Driver\Mysql',
            'persistent' => false,
            'host' => 'localhost',
            //'port' => 'non_standard_port_number',
            'username' => 'mailadmin',
            'password' => '< password >',
            'database' => 'mailserver',
            'encoding' => 'utf8',
            'timezone' => 'UTC',
            'flags' => [],
            'cacheMetadata' => true,
            'log' => true,            

            'quoteIdentifiers' => false,

            //'init' => ['SET GLOBAL innodb_stats_on_metadata = 0'],

            'url' => env('DATABASE_URL', null),
        ],
        
        'test' => [
            'className' => 'Cake\Database\Connection',
            'driver' => 'Cake\Database\Driver\Mysql',
            'persistent' => false,
            'host' => 'localhost',
            //'port' => 'non_standard_port_number',
            'username' => 'my_app',
            'password' => 'secret',
            'database' => 'test_myapp',
            'encoding' => 'utf8',
            'timezone' => 'UTC',
            'cacheMetadata' => true,
            'quoteIdentifiers' => false,
            'log' => false,
            //'init' => ['SET GLOBAL innodb_stats_on_metadata = 0'],
            'url' => env('DATABASE_TEST_URL', null),
        ],        
    ],

    'Log' => [
        'debug' => [
            'className' => 'Cake\Log\Engine\FileLog',
            'path' => LOGS,
            'file' => 'debug',
            'levels' => ['notice', 'info', 'debug'],
            'url' => env('LOG_DEBUG_URL', null),
        ],
        'error' => [
            'className' => 'Cake\Log\Engine\FileLog',
            'path' => LOGS,
            'file' => 'error',
            'levels' => ['warning', 'error', 'critical', 'alert', 'emergency'],
            'url' => env('LOG_ERROR_URL', null),
        ],
    ],

    'Session' => [
        'defaults' => 'php',
    ],
];
Important Changes:

1.   'Security' => [
        'salt' => env('SECURITY_SALT', '**** CHANGE ME ****'),
    ],


2.  'reCaptcha' => [
    'secret' => '**** CHANGE ME ****',
    'key' => '**** CHANGE ME ****'
    ],

3.  Datasources: DB Connections

Create Superadmin

Execute: /usr/local/www/webadmin/bin/cake users addSuperuser

Login: https://<IP-Address>/webadmin

Username: superadmin
Password: <copy the output of command above>

Configure Roundcube

cd /usr/local/www
fetch https://github.com/roundcube/roundcubemail/releases/download/1.2.5/roundcubemail-1.2.5-complete.tar.gz
tar zxvf roundcubemail-1.2.5-complete.tar.gz
mv roundcubemail-1.2.5 roundcube
chown -R root:wheel roundcube/

Cerate Database ’roundcubemail’

-- MySQL dump 10.13  Distrib 5.6.35, for FreeBSD11.0 (amd64)
--
-- Host: localhost    Database: roundcubemail
-- ------------------------------------------------------
-- Server version 5.6.35
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `roundcubemail`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `roundcubemail` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `roundcubemail`;

--
-- Table structure for table `attachments`
--

DROP TABLE IF EXISTS `attachments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `attachments` (
  `attachment_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(11) unsigned NOT NULL DEFAULT '0',
  `filename` varchar(255) NOT NULL DEFAULT '',
  `mimetype` varchar(255) NOT NULL DEFAULT '',
  `size` int(11) NOT NULL DEFAULT '0',
  `data` longtext NOT NULL,
  PRIMARY KEY (`attachment_id`),
  KEY `fk_attachments_event_id` (`event_id`),
  CONSTRAINT `fk_attachments_event_id` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `attachments`
--


--
-- Table structure for table `cache`
--

DROP TABLE IF EXISTS `cache`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cache` (
  `user_id` int(10) unsigned NOT NULL,
  `cache_key` varchar(128) CHARACTER SET ascii NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `expires` datetime DEFAULT NULL,
  `data` longtext NOT NULL,
  KEY `expires_index` (`expires`),
  KEY `user_cache_index` (`user_id`,`cache_key`),
  CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `cache`
--


--
-- Table structure for table `cache_index`
--

DROP TABLE IF EXISTS `cache_index`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cache_index` (
  `user_id` int(10) unsigned NOT NULL,
  `mailbox` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `expires` datetime DEFAULT NULL,
  `valid` tinyint(1) NOT NULL DEFAULT '0',
  `data` longtext NOT NULL,
  PRIMARY KEY (`user_id`,`mailbox`),
  KEY `expires_index` (`expires`),
  CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `cache_index`
--


--
-- Table structure for table `cache_messages`
--

DROP TABLE IF EXISTS `cache_messages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cache_messages` (
  `user_id` int(10) unsigned NOT NULL,
  `mailbox` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `uid` int(11) unsigned NOT NULL DEFAULT '0',
  `expires` datetime DEFAULT NULL,
  `data` longtext NOT NULL,
  `flags` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`,`mailbox`,`uid`),
  KEY `expires_index` (`expires`),
  CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `cache_messages`
--


--
-- Table structure for table `cache_shared`
--

DROP TABLE IF EXISTS `cache_shared`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cache_shared` (
  `cache_key` varchar(255) CHARACTER SET ascii NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `expires` datetime DEFAULT NULL,
  `data` longtext NOT NULL,
  KEY `expires_index` (`expires`),
  KEY `cache_key_index` (`cache_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `cache_shared`
--


--
-- Table structure for table `cache_thread`
--

DROP TABLE IF EXISTS `cache_thread`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cache_thread` (
  `user_id` int(10) unsigned NOT NULL,
  `mailbox` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `expires` datetime DEFAULT NULL,
  `data` longtext NOT NULL,
  PRIMARY KEY (`user_id`,`mailbox`),
  KEY `expires_index` (`expires`),
  CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `cache_thread`
--


--
-- Table structure for table `caldav_props`
--

DROP TABLE IF EXISTS `caldav_props`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `caldav_props` (
  `obj_id` int(11) NOT NULL,
  `obj_type` enum('vcal','vevent','vtodo','') NOT NULL,
  `url` varchar(255) NOT NULL,
  `tag` varchar(255) DEFAULT NULL,
  `user` varchar(255) DEFAULT NULL,
  `pass` varchar(1024) DEFAULT NULL,
  `last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `obj_id` (`obj_id`,`obj_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `caldav_props`
--


--
-- Table structure for table `calendars`
--

DROP TABLE IF EXISTS `calendars`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `calendars` (
  `calendar_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL,
  `color` varchar(8) NOT NULL,
  `showalarms` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`calendar_id`),
  KEY `user_name_idx` (`user_id`,`name`),
  CONSTRAINT `fk_calendars_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `calendars`
--


--
-- Table structure for table `carddav_addressbooks`
--

DROP TABLE IF EXISTS `carddav_addressbooks`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `carddav_addressbooks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `active` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `user_id` int(10) unsigned NOT NULL,
  `last_updated` timestamp NOT NULL DEFAULT '2000-01-01 00:00:01',
  `refresh_time` time NOT NULL DEFAULT '01:00:00',
  `sync_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `authentication_scheme` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'auto',
  `presetname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `use_categories` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `carddav_addressbooks_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `carddav_addressbooks`
--


--
-- Table structure for table `carddav_contacts`
--

DROP TABLE IF EXISTS `carddav_contacts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `carddav_contacts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `abook_id` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `firstname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `surname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `organization` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `showas` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `vcard` longtext COLLATE utf8_unicode_ci NOT NULL,
  `etag` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `uri` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cuid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uri` (`uri`,`abook_id`),
  UNIQUE KEY `cuid` (`cuid`,`abook_id`),
  KEY `abook_id` (`abook_id`),
  CONSTRAINT `carddav_contacts_ibfk_1` FOREIGN KEY (`abook_id`) REFERENCES `carddav_addressbooks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `carddav_contacts`
--


--
-- Table structure for table `carddav_group_user`
--

DROP TABLE IF EXISTS `carddav_group_user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `carddav_group_user` (
  `group_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`group_id`,`contact_id`),
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `carddav_group_user_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `carddav_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `carddav_group_user_ibfk_2` FOREIGN KEY (`contact_id`) REFERENCES `carddav_contacts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `carddav_group_user`
--


--
-- Table structure for table `carddav_groups`
--

DROP TABLE IF EXISTS `carddav_groups`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `carddav_groups` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `abook_id` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `vcard` text COLLATE utf8_unicode_ci NOT NULL,
  `etag` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `uri` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cuid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uri` (`uri`,`abook_id`),
  UNIQUE KEY `cuid` (`cuid`,`abook_id`),
  KEY `abook_id` (`abook_id`),
  CONSTRAINT `carddav_groups_ibfk_1` FOREIGN KEY (`abook_id`) REFERENCES `carddav_addressbooks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `carddav_groups`
--


--
-- Table structure for table `carddav_migrations`
--

DROP TABLE IF EXISTS `carddav_migrations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `carddav_migrations` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `filename` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `processed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `filename` (`filename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `carddav_migrations`
--


--
-- Table structure for table `carddav_xsubtypes`
--

DROP TABLE IF EXISTS `carddav_xsubtypes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `carddav_xsubtypes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `typename` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `subtype` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `abook_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `typename` (`typename`,`subtype`,`abook_id`),
  KEY `abook_id` (`abook_id`),
  CONSTRAINT `carddav_xsubtypes_ibfk_1` FOREIGN KEY (`abook_id`) REFERENCES `carddav_addressbooks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `carddav_xsubtypes`
--


--
-- Table structure for table `contactgroupmembers`
--

DROP TABLE IF EXISTS `contactgroupmembers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `contactgroupmembers` (
  `contactgroup_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  PRIMARY KEY (`contactgroup_id`,`contact_id`),
  KEY `contactgroupmembers_contact_index` (`contact_id`),
  CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`) REFERENCES `contactgroups` (`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `contactgroupmembers`
--


--
-- Table structure for table `contactgroups`
--

DROP TABLE IF EXISTS `contactgroups`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `contactgroups` (
  `contactgroup_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`contactgroup_id`),
  KEY `contactgroups_user_index` (`user_id`,`del`),
  CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `contactgroups`
--


--
-- Table structure for table `contacts`
--

DROP TABLE IF EXISTS `contacts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `contacts` (
  `contact_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL DEFAULT '',
  `email` text NOT NULL,
  `firstname` varchar(128) NOT NULL DEFAULT '',
  `surname` varchar(128) NOT NULL DEFAULT '',
  `vcard` longtext,
  `words` text,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`contact_id`),
  KEY `user_contacts_index` (`user_id`,`del`),
  CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `contacts`
--


--
-- Table structure for table `dictionary`
--

DROP TABLE IF EXISTS `dictionary`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dictionary` (
  `user_id` int(10) unsigned DEFAULT NULL,
  `language` varchar(5) NOT NULL,
  `data` longtext NOT NULL,
  UNIQUE KEY `uniqueness` (`user_id`,`language`),
  CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `dictionary`
--


--
-- Table structure for table `events`
--

DROP TABLE IF EXISTS `events`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `events` (
  `event_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `calendar_id` int(11) unsigned NOT NULL DEFAULT '0',
  `recurrence_id` int(11) unsigned NOT NULL DEFAULT '0',
  `uid` varchar(255) NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `sequence` int(1) unsigned NOT NULL DEFAULT '0',
  `start` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `end` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `recurrence` varchar(255) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `location` varchar(255) NOT NULL DEFAULT '',
  `categories` varchar(255) NOT NULL DEFAULT '',
  `url` varchar(255) NOT NULL DEFAULT '',
  `all_day` tinyint(1) NOT NULL DEFAULT '0',
  `free_busy` tinyint(1) NOT NULL DEFAULT '0',
  `priority` tinyint(1) NOT NULL DEFAULT '0',
  `sensitivity` tinyint(1) NOT NULL DEFAULT '0',
  `alarms` varchar(255) DEFAULT NULL,
  `attendees` text,
  `notifyat` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`),
  KEY `uid_idx` (`uid`),
  KEY `recurrence_idx` (`recurrence_id`),
  KEY `calendar_notify_idx` (`calendar_id`,`notifyat`),
  CONSTRAINT `fk_events_calendar_id` FOREIGN KEY (`calendar_id`) REFERENCES `calendars` (`calendar_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `events`
--


--
-- Table structure for table `identities`
--

DROP TABLE IF EXISTS `identities`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `identities` (
  `identity_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `standard` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL,
  `organization` varchar(128) NOT NULL DEFAULT '',
  `email` varchar(128) NOT NULL,
  `reply-to` varchar(128) NOT NULL DEFAULT '',
  `bcc` varchar(128) NOT NULL DEFAULT '',
  `signature` longtext,
  `html_signature` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`identity_id`),
  KEY `user_identities_index` (`user_id`,`del`),
  KEY `email_identities_index` (`email`,`del`),
  CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `identities`
--


--
-- Table structure for table `itipinvitations`
--

DROP TABLE IF EXISTS `itipinvitations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `itipinvitations` (
  `token` varchar(64) NOT NULL,
  `event_uid` varchar(255) NOT NULL,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `event` text NOT NULL,
  `expires` datetime DEFAULT NULL,
  `cancelled` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`token`),
  KEY `uid_idx` (`user_id`,`event_uid`),
  CONSTRAINT `fk_itipinvitations_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `itipinvitations`
--


--
-- Table structure for table `searches`
--

DROP TABLE IF EXISTS `searches`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `searches` (
  `search_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `type` int(3) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL,
  `data` text,
  PRIMARY KEY (`search_id`),
  UNIQUE KEY `uniqueness` (`user_id`,`type`,`name`),
  CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `searches`
--


--
-- Table structure for table `session`
--

DROP TABLE IF EXISTS `session`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `session` (
  `sess_id` varchar(128) NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `ip` varchar(40) NOT NULL,
  `vars` mediumtext NOT NULL,
  PRIMARY KEY (`sess_id`),
  KEY `changed_index` (`changed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `session`
--


--
-- Table structure for table `system`
--

DROP TABLE IF EXISTS `system`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `system` (
  `name` varchar(64) NOT NULL,
  `value` mediumtext,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `system`
--


--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `mail_host` varchar(128) NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `last_login` datetime DEFAULT NULL,
  `failed_login` datetime DEFAULT NULL,
  `failed_login_counter` int(10) unsigned DEFAULT NULL,
  `language` varchar(5) DEFAULT NULL,
  `preferences` longtext,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`,`mail_host`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--


--
-- Dumping routines for database 'roundcubemail'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

File: /usr/local/www/roundcube/config/config.inc.php

<?php
$config = array();
$config['db_dsnw'] = 'mysql://mailadmin:< password >@localhost/roundcubemail';
$config['default_host'] = 'localhost';
$config['smtp_server'] = 'localhost';
$config['smtp_port'] = 25;
$config['smtp_user'] = '';
$config['smtp_pass'] = '';
$config['support_url'] = 'http://www.domain.tld';
$config['product_name'] = 'Company Webmail';
$config['des_key'] = 'rcmail-!24ByteDESkey*Str';
$config['plugins'] = array(
    'archive',
    'zipdownload',
    'managesieve',
    'acl',
    'password',
    'carddav',
    'calendar'
);
$config['skin'] = 'larry';
Change: $config['des_key'] = '*** SECRET ***';

/usr/local/www/roundcube/plugins/password/config.inc.php
Copy config.inc.php.dist to config.inc.php

$config['password_db_dsn'] = 'mysqli://mailadmin:< password >@localhost/mailserver';
$config['password_query'] = 'UPDATE mailserver.mailbox SET password=%c,modified=now() WHERE username=%u and domain=%d LIMIT 1';

4 Comments

Leave a Reply

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close