iRedMail   //  Document Index

Upgrade iRedMail from 0.9.2 to 0.9.3

ChangeLog

• 2016-01-21: Fix incorrect permission on new sql table amavisd.outbound_wblist.
• 2016-01-14: Mention updating backup script to backup iRedAPD SQL database.
• 2015-12-23: Run a2enmod headers on Debian/Ubuntu to make sure required Apache module is enabled.
• 2015-12-16: Mention how to enable greylisting in iRedAPD.
• 2015-12-14: New section: Upgrade iRedAdmin (open source edition) to the latest stable release.
• 2015-12-14: New section: Migrate from Cluebringer to iRedAPD.
• 2015-12-14: Fix duplicate folder name in section Dovecot-2.2: Add more special folders as alias folders.

• 2015-12-14: Initial release.

General (All backends should apply these steps)

Update /etc/iredmail-release with new iRedMail version number

iRedMail stores the release version in /etc/iredmail-release after installation, it's recommended to update this file after you upgraded iRedMail, so that you can know which version of iRedMail you're running. For example:

# File: /etc/iredmail-release

0.9.3

Upgrade iRedAPD (Postfix policy server) to the latest 1.7.0

Please follow below tutorial to upgrade iRedAPD to the latest stable release: Upgrade iRedAPD to the latest stable release

Notes:

• iRedAPD-1.7.0 doesn't enable greylisting by default, please enable plugin greylisting in iRedAPD config file (/opt/iredapd/settings.py), then execute SQL command below to enable server-wide greylisting:

sql> USE iredapd;
sql> INSERT INTO greylisting (account, priority, sender, sender_priority, active) VALUES ('@.', 0, '@.', 0, 1);

• iRedAPD-1.7.0 creates a new SQL database iredapd, please update your backup script to backup this database. The backup script was set up by iRedMail during installation, default path is /var/vmail/backup/backup_mysql.sh (For OpenLDAP and MySQL/MariaDB backends) or /var/vmail/backup/backup_pgsql.sh (For PostgreSQL backend). For example:

DATABASES='... iredapd'

Detailed release notes are available here.

Migrate from Cluebringer to iRedAPD

NOTE: If your server doesn't have Cluebringer installed, please ignore this step.

In iRedMail-0.9.3, Cluebringer has been removed and replaced by iRedAPD. Cluebringer is not under active development and no new release since 2013 (the latest stable release doesn't support IPv6). iRedAPD offers greylisting and throttling supports, please follow tutorial below to migrate greylisting and throttling settings from Cluebringer to iRedAPD:

• Migrate from Cluebringer to iRedAPD

Note: We also plan to completely remove code of Policyd/Cluebringer support in next iRedAdmin-Pro release.

Upgrade iRedAdmin (open source edition) to the latest stable release

Please follow this tutorial to upgrade iRedAdmin open source edition to the latest stable release: Upgrade iRedAdmin to the latest stable release

Upgrade Roundcube webmail to the latest stable release

Please follow Roundcube official tutorial to upgrade Roundcube webmail to the latest stable release immediately: How to upgrade Roundcube.

Note: package rsync must be installed on your server before upgrading.

Postfix: Add additional aliases

ClamAV may detect virus in email, notification will be sent to system account virusalert.

Steps to add alias accounts:

• For Linux and OpenBSD: please open file /etc/postfix/aliases, if you already have line virusalert: root, please ignore this step. if not, please run commands below to add it.

perl -pi -e 's/(virusalert:.*)/#${1}/g' /etc/postfix/aliases
echo -e '\nvirusalert: root' >> /etc/postfix/aliases
postalias /etc/postfix/aliases

• For FreeBSD: please open file /usr/local/etc/postfix/aliases, if you already have line virusalert: root, please ignore this step. if not, please run commands below to add it.

perl -pi -e 's/(virusalert:.*)/#${1}/g' /usr/local/etc/postfix/aliases
echo -e '\nvirusalert: root' >> /usr/local/etc/postfix/aliases
postalias /usr/local/etc/postfix/aliases

Amavisd: Fix incorrect setting which treats external sender as internal user

In iRedMail-0.9.2 and earlier releases, Amavisd was incorrectly configured which causes it treats external sender as internal user, and it (incorrectly) signs DKIM on inbound message. This is wrong. Please follow steps below to fix it.

With below changes, Amavisd will apply policy bank 'ORIGINATING' to emails submitted through submission (port 587) by smtp authenticated user. This way we clearly separate emails submitted by authenticated users and inbound message sent by others, and Amavisd won't sign DKIM on inbound message anymore.

• Open Amavisd config file, make sure you have below settings. If they don't exist, please add them or update them.

  • on RHEL/CentOS: it's /etc/amavisd/amavisd.conf.
  • on Debian/Ubuntu: it's /etc/amavis/conf.d/50-user.
  • on FreeBSD: it's /usr/local/etc/amavisd.conf.
  • on OpenBSD: it's /etc/amavisd.conf.

$inet_socket_port = [10024, 10026, 9998];
$interface_policy{'10026'} = 'ORIGINATING';

We will configure Postfix to pipe email submitted by authenticated user through port 10026, others through port 10024. And port 9998 is used to manage quarantined mails.

• Find $policy_bank{'ORIGINATING'} = { block, comment out forward_method line in the block:

  #forward_method => 'smtp:[127.0.0.1]:10027',

• Comment out below line in Amavisd config file:

WARNING:

There're several $originating =1; in amavisd config file, but there's only one of them is NOT defined inside any $policy_bank = {} block, and this is the one we need to comment out.

$originating = 1;

• Comment out the whole $policy_bank{'MYUSERS'} block:

#$policy_blank{'MYUSERS'} = {
#   ...
#}

• Restart Amavisd service.

• Open Postfix config file /etc/postfix/master.cf (Linux/OpenBSD) or /usr/local/etc/postfix/master.cf (FreeBSD), update transport submission to uncomment content_filter=smtp-amavis:[127.0.0.1]:10026 line, so that we can use Amavisd with policy bank ORIGINATING as content filter. like below:

submission inet n       -       n       -       -       smtpd
  ... [omit other settings here] ...
  -o content_filter=smtp-amavis:[127.0.0.1]:10026

• Restart Postfix service.

Dovecot: Fix incorrect quota warning priorities

iRedMail configures Dovecot to send warning message to local user when the mailbox quota is 85%, 90% or 95% full, but the priorities is wrong. Please fix it with steps below.

• Find below setting in Dovecot config file /etc/dovecot/dovecot.conf (Linux/OpenBSD) or /usr/local/etc/dovecot/dovecot.conf (FreeBSD):

    quota_warning = storage=85%% quota-warning 85 %u
    quota_warning2 = storage=90%% quota-warning 90 %u
    quota_warning3 = storage=95%% quota-warning 95 %u

quota_warning has the highest priority, quota_warning3 has the lowest priority. Only the command for the first exceeded limit is executed, so we must configure the highest limit first.

With above setting, when the mailbox quota goes from 70% to 98% directly, it sends warning message to notify user that the quota is 85% full, this is wrong, it's expected to be warned as 95% full instead.

• Update them to below ones to fix it. Please pay close attention to the percent numbers:

    quota_warning = storage=95%% quota-warning 95 %u
    quota_warning2 = storage=90%% quota-warning 90 %u
    quota_warning3 = storage=85%% quota-warning 85 %u

Restart Dovecot service is required.

For more details, please read Dovecot document: Quota Configuration

Dovecot-2.2: Add more special folders as alias folders

Note: This is applicable to Dovecot-2.2.x. if you're running Dovecot-2.1.x or earlier versions, please skip this step.

Check Dovecot version number with below command first:

# dovecot --version

Open Dovecot config file /etc/dovecot/dovecot.conf (Linux/OpenBSD) or /usr/local/etc/dovecot/dovecot.conf (FreeBSD), find below setting:

namespace {
    type = private
    ...
    inbox = yes
    ...
}

Add below alias folders inside the same namespace {} block:

    mailbox "Sent Items" {
        auto = no
        special_use = \Sent
    }

    mailbox "Deleted Messages" {
        auto = no
        special_use = \Trash
    }

    mailbox "Deleted Items" {
        auto = no
        special_use = \Trash
    }

    # Archive
    mailbox Archive {
        auto = no
        special_use = \Archive
    }
    mailbox Archives {
        auto = no
        special_use = \Archive
    }

Restart Dovecot service is required.

Roundcube webmail: Add daily cron job to cleanup Roundcube SQL database

It's recommended to setup a daily cron job to keep Roundcube SQL database slick and clean, it removes all records that are marked as deleted.

Please edit root's cron job with command below:

# crontab -e -u root

Then add cron job like below:

• RHEL/CentOS:

# Cleanup Roundcube SQL database.
2   2   *   *   *   php /var/www/roundcubemail/bin/cleandb.sh >/dev/null

• Debian/Ubuntu:

# Cleanup Roundcube SQL database.
2   2   *   *   *   php /opt/www/roundcubemail/bin/cleandb.sh >/dev/null

WARNING: with old iRedMail release, Roundcube directory is /usr/share/apache2/roundcubemail, please make sure you're using the correct one on your server.

• FreeBSD:

# Cleanup Roundcube SQL database.
2   2   *   *   *   php /usr/local/www/roundcube/bin/cleandb.sh >/dev/null

• OpenBSD:

# Cleanup Roundcube SQL database.
2   2   *   *   *   php /var/www/roundcubemail/bin/cleandb.sh >/dev/null

Web server: Enable HSTS (HTTP Strict Transport Security) support

HTTP Strict Transport Security (often abbreviated as HSTS) is a security feature that lets a web site tell browsers that it should only be communicated with using HTTPS, instead of using HTTP.

For more details, please read this article: HTTP Strict Transport Security

Apache

For Apache, please edit its config file which manages SSL related settings, and append below settings right after SSLEngine on line:

• On RHEL/CentOS, it's /etc/httpd/conf.d/ssl.conf.
• On Debian/Ubuntu, it's /etc/apache2/sites-enabled/default-ssl or default-ssl.conf.
• On FreeBSD: it's /usr/local/etc/apache24/extra/httpd-ssl.conf.

# Use HTTP Strict Transport Security to force client to use secure connections only.
# Reference:
# https://developer.mozilla.org/en-US/docs/Web/Security/HTTP_strict_transport_security
# Module mod_headers is required. 15768000 seconds = 6 months.
Header always set Strict-Transport-Security "max-age=15768000"

On Debian 8 and Ubuntu, run command below to make sure Apache module headers is enabled:

a2enmod headers
service apache2 restart

Nginx

For Nginx, please edit its config file which manages SSL related settings, and append below settings right after ssl on line:

• On Linux and OpenBSD, it's /etc/nginx/conf.d/default.conf.
• On FreeBSD, it's /usr/local/etc/nginx/conf.d/default.conf.

# Use HTTP Strict Transport Security to force client to use secure connections only.
# Reference:
# https://developer.mozilla.org/en-US/docs/Web/Security/HTTP_strict_transport_security
add_header Strict-Transport-Security "max-age=15768000";

SOGo: Fix improper settings in Apache/Nginx config file

iRedMail-0.9.2 has improper settings in Apache/Nginx config files:

• when you try to view attachment in email, it will redirect the URL to https://127.0.0.1/....
• iOS mobile devices will try to access web url https://.../.well-known/carddav, but it's not defined in Apache/Nginx config files.

Apache

1: Comment out incorrect settings

For Apache: Please make sure below settings are commented out in Apache config file, then restart Apache service.

• On RHEL/CentOS, it's /etc/httpd/conf.d/SOGo.conf.
• On Debian/Ubuntu, it's /etc/apache2/conf-available/SOGo.conf.
• FreeBSD: iRedMail-0.9.2 and earlier releases doesn't support SOGo on FreeBSD, so it's not appliable on FreeBSD.

#RequestHeader set "x-webobjects-server-port" "443"
#RequestHeader set "x-webobjects-server-name" "yourhostname"
#RequestHeader set "x-webobjects-server-url" "https://yourhostname"

2: Redirect /.well-known/carddav access to SOGo

Find below line in SOGo.conf:

  RewriteRule ^/.well-known/caldav/?$ /SOGo/dav [R=301]

Add a new line right after above line:

  RewriteRule ^/.well-known/caldav/?$ /SOGo/dav [R=301]
  RewriteRule ^/.well-known/carddav/?$ /SOGo/dav [R=301]

Restarting Apache service is required.

Nginx

1: Comment out incorrect settings

For Nginx: Please make sure below settings are commented out in Nginx config file, then restart or reload Nginx service.

• On Linux and OpenBSD, it's /etc/nginx/conf.d/default.conf.
• On FreeBSD, it's /usr/local/etc/nginx/conf.d/default.conf.

#proxy_set_header x-webobjects-remote-host 127.0.0.1;
#proxy_set_header x-webobjects-server-name $server_name;
#proxy_set_header x-webobjects-server-url $scheme://$host;

2: Redirect /.well-known/carddav access to SOGo

iRedMail doesn't have /.well-known redirection in Nginx by default, so please add lines below in the server { listen 443; ...} block, in file default.conf:

rewrite ^/.well-known/caldav    /SOGo/dav permanent;
rewrite ^/.well-known/carddav   /SOGo/dav permanent;

Restarting Nginx service is required.

SOGo: The Dovecot Master User used by SOGo doesn't work due to incorrect username.

Note: you can skip this step if you don't run SOGo groupware, and iRedMail doesn't install SOGo on FreeBSD due to missing required ports in official ports tree.

The Dovecot Master User created by iRedMail and used by SOGo doesn't contain a mail domain name, this will cause login failure.

If you don't append a (non-exist) mail domain name in Dovecot Master User account, Dovecot will use the domain name of your login username. For example, if your real user is myuser@mydomain.com, when you try to access this user's mailbox as Dovecot Master User myuser@mydomain.com*my_master_user, it will trigger Dovecot to verify user my_master_user@mydomain.com which doesn't exist on your server, then this login attempt fails.

Please follow steps below to fix it.

• Open file /etc/dovecot/dovecot-master-users (Linux/OpenBSD), find the account used by SOGo:

sogo_sieve_master:...

• Append any mail domain name which is not hosted on your server to this account, save your change. for example:

sogo_sieve_master@not-exist.com:...

• Open file /etc/sogo/sieve.cred, append the same mail domain name for the sieve account:

sogo_sieve_master@not-exist.com:...

That's all.

SOGo: cron jobs which run every minute must be grouped in one job.

Note: this is applicable to iRedMail server which has SOGo groupware installed and running.

iRedMail sets up 3 cron jobs for SOGo, 2 of them are running every minute. You can check the cron jobs with command below. Note:

• SOGo daemon user is sogo on all Linux distributions.
• SOGo daemon user is _sogo on OpenBSD.
• with iRedMail-0.9.2 and earlier releases, there's no SOGo support on FreeBSD.

# crontab -u sogo -l

*   *   *   *   *   /usr/sbin/sogo-tool expire-sessions 30
*   *   *   *   *   /usr/sbin/sogo-ealarms-notify

It always complains with error message like below:

sogo-tool[27443] Failed to create lock directory '/var/lib/sogo/GNUstep/Defaults/.lck/.GNUstepDefaults.lck'

sogo-ealarms-notify[27790] Warning ... someone broke our lock (/var/lib/sogo/GNUstep/Defaults/.lck/.GNUstepDefaults.lck) ... and may have interfered with updating defaults data in file.

According to SOGo mailing list, replied by SOGo developer Christian Mack, This is a known problem, but harmless, as the lock is not really needed here. The work around is to use one cron entry only for both (jobs).

Please edit the cron job with command below:

# crontab -u sogo -e

Then group those 2 jobs into one cron job like below (note, use semicolon ; to separate jobs):

*   *   *   *   *   /usr/sbin/sogo-tool expire-sessions 30; /usr/sbin/sogo-ealarms-notify

That's all.

SOGo: Use correct sieve folder encoding

SOGo uses UTF-7 as sieve folder encoding by default, this is improper, we must use UTF-8 instead, otherwise mail folder names with non-ASCII characters cannot be correctly created or displayed.

To fix this, please add below setting in SOGo config file /etc/sogo/sogo.conf (Linux/OpenBSD) or /usr/local/etc/sogo/sogo.conf (FreeBSD):

    SOGoSieveFolderEncoding = UTF-8;

Restarting SOGo service is required.

[RHEL/CentOS 7] Remove daemonze = line in /etc/uwsgi.ini

NOTE: this is required by RHEL/CentOS 7, and not applicable to other Linux/BSD distributions.

daemonze = line set in /etc/uwsgi.ini is required by RHEL/CentOS 6, but not RHEL/CentOS 7, and it will cause uwsgi service fail. Please remove or comment out this line and restart uwsgi service.

[RHEL/CentOS 7] Fix incorrect default firewall zone name

NOTE: this is required by RHEL/CentOS 7, and not applicable to other Linux/BSD distributions.

iRedMail-0.9.2 and earlier versions won't set default firewall zone if you didn't choose to restart firewall immediately, so after iRedMail installation, you must set the default firewall zone manually with steps below.

• Open file /etc/firewalld/firewalld.conf, find parameter DefaultZone=. If it's not set by iRedMail installer, it will be DefaultZone=public:

DefaultZone=public

• Please replace public by iredmail, it will open ports required by ssh and mail services. The zone file is /etc/firewalld/zones/iredmail.xml, please make sure you have correct ssh port number in this file.

DefaultZone=iredmail

• Reload firewall rules with command below:

firewall-cmd --complete-reload

[OPTIONAL] Fixed: Not preserve the case of ${extension} while delivering message to mailbox

With iRedMail-0.9.2 and earlier releases, email sent to user username+Ext@domain.com (upper case E) will be delivered to folder ext (lower case e) of username@domain.com's mailbox. This fix will preserve the case of address extension.

• Open file /etc/postfix/master.cf (Linux/OpenBSD) or /usr/local/etc/postfix/master.cf (FreeBSD), find below lines:

# Use dovecot deliver program as LDA.
dovecot unix    -       n       n       -       -      pipe
    flags=DRhu ...

• Replace flags=DRhu by flags=DRh (remove u) in the third line:

    flags=DRh ...

[OPTIONAL] Fail2ban: Update regular expression to catch postscreen log

We added one new regular expression to catch postscreen log to help reduce spam, please follow steps below to add it.

Open file /etc/fail2ban/filter.d/postfix.iredmail.conf or /usr/local/etc/fail2ban/filter.d/postfix.iredmail.conf (on FreeBSD), append below line under [Definition] section:

            reject: RCPT from (.*)\[<HOST>\]:([0-9]{4,5}:)? 550

Restarting Fail2ban service is required.

[OPTIONAL] Postfix: Remove one non-spam HELO identity in helo restriction

iRedMail ships a Postfix HELO rule file, /etc/postfix/helo_access.pcre, it contains some HELO identities which were treated as spammers by analizing Postfix log files, and one of them, bezeqint.net is not spammer and we should remove it.

Please find below line in /etc/postfix/helo_access.pcre (Linux and OpenBSD) or /usr/local/etc/postfix/helo_access.pcre (FreeBSD), and remove it.

/(bezeqint\.net)$/ REJECT ACCESS DENIED. Your email was rejected because the sending mail server does not identify itself correctly (${1})

[OPTIONAL] Postfix: add some more restriction methods

Note: this is an optional operation, not required but recommended.

If you need flexible rules to restrict senders, this change will be helpful. for example, reject spammer whom sends emails with different domain names.

Please open Postfix config file main.cf, add below 2 settings:

• On Linux and OpenBSD, it's /etc/postfix/main.cf.
• On FreeBSD, it's /usr/local/etc/postfix/main.cf. WARNING: in below settings, all new files must be placed under /usr/local/etc/postfix/.

header_checks = pcre:/etc/postfix/header_checks
body_checks = pcre:/etc/postfix/body_checks.pcre

• In main.cf, find parameter smtpd_sender_restrictions =, add a new setting check_sender_access pcre:/etc/postfix/sender_access.pcre right after permit_sasl_authenticated like below:

smtpd_sender_restrictions =
    ...
    permit_sasl_authenticated
    check_sender_access pcre:/etc/postfix/sender_access.pcre
    ...

• Create required files:

# touch /etc/postfix/{header_checks,body_checks.pcre,sender_access.pcre}

• Reloading or restarting Postfix service is required.

Note: each time you changed the pcre file, you should reload (not restart) Postfix service so that Postfix can read the changes.

OpenLDAP backend special

Fixed: improper ACL control

With default OpenLDAP ACL control set by iRedMail, every mail user has permission to query the whole LDAP tree (although cannot query sensitive info like password), we'd better remove this ACL control due to security concern.

• Please open OpenLDAP config file slapd.conf, and find below lines:

  • on RHEL/CentOS: it's /etc/openldap/slapd.conf.
  • on Debian/Ubuntu: it's /etc/ldap/slapd.conf.
  • on FreeBSD: it's /usr/local/etc/openldap/slapd.conf.
  • on OpenBSD: it's /etc/openldap/slapd.conf.

access to dn.subtree="o=domains,dc=example,dc=com"
    by anonymous                    auth
    by self                         write
    by dn.exact="cn=vmail,dc=example,dc=com"   read
    by dn.exact="cn=vmailadmin,dc=example,dc=com"  write
    by dn.regex="mail=[^,]+,ou=Users,domainName=$1,o=domains,dc=example,dc=com$" read
    by users                        read

The LDAP suffix dc=example,dc=com might be different on your server.

• Remove the 6th line (by dn.regex="mail=..."), and replace the line by users read by by users none.

access to dn.subtree="o=domains,dc=example,dc=com"
    by anonymous                    auth
    by self                         write
    by dn.exact="cn=vmail,dc=example,dc=com"   read
    by dn.exact="cn=vmailadmin,dc=example,dc=com"  write
    by users                        none

• Save your change and restart OpenLDAP service.

Fixed: Dovecot Master User doesn't work with ACL plugin

iRedMail has both Dovecot Master User and Dovecot acl plugin enabled by default, if acl plugin is enabled, the Master User is still subject to ACLs just like any other user, which means that by default the Master User has no access to any mailboxes of the user. Please fix this issue by following steps below.

• Open file /etc/dovecot/dovecot-ldap.conf (Linux/OpenBSD) or /usr/local/etc/dovecot/dovecot-ldap.conf (FreeBSD), find below line:

user_attrs      = mail=user, ...

• Add new setting mail=master_user in user_attrs like below:

user_attrs      = mail=master_user,mail=user, ...

• Restart Dovecot service.

Add new SQL table outbound_wblist in amavisd database

We need a new SQL table outbound_wblist in amavisd database, it's used to store white/blacklists for outbound message, required by iRedAPD plugin amavisd_wblist.

Please connect to MySQL server as MySQL root user, create new table:

$ mysql -uroot -p
mysql> USE amavisd;
mysql> CREATE TABLE outbound_wblist (rid integer unsigned NOT NULL, sid integer unsigned NOT NULL, wb varchar(10) NOT NULL, PRIMARY KEY (rid,sid));

After table created, please restart iRedAPD service.

Add new column delete_date in SQL table iredadmin.deleted_mailboxes

We need a SQL column to store the date we schedule to delete the mailbox after removing mail account. This new column might be used by iRedAdmin and other scripts used to delete mailboxes.

Please connect to MySQL server as MySQL root user, create new table:

$ mysql -uroot -p
sql> USE iredadmin;
sql> ALTER TABLE deleted_mailboxes ADD COLUMN delete_date DATE DEFAULT NULL;
sql> CREATE INDEX idx_delete_date ON deleted_mailboxes (delete_date);

That's it.

MySQL/MariaDB backend special

Add new SQL columns in vmail database: alias.is_alias, alias.alias_to

iRedMail-0.9.3 offers per-user alias address support, that means mail user john.smith@domain.com can have additional email addresses like john@domain.com, js@domain.com and more, all emails sent to these addresses will be delivered to same mailbox. With per-user alias address support, you don't need to create many mail alias accounts anymore.

Per-user alias address requires 2 new SQL columns:

• alias.is_alias: this column marks a SQL record is a per-user alias account.
• alias.alias_to: this column stores the target address (it's john.smith@domain.com as described above). Its value is same as alias.goto when this sql record is a per-user alias, but alias.goto is not good for indexed searching, so we create alias.alias_to as an alternative.

Please follow steps below to create required SQL columns:

$ mysql -uroot -p
sql> USE vmail;
sql> ALTER TABLE alias ADD COLUMN is_alias TINYINT(1) NOT NULL DEFAULT 0;
sql> ALTER TABLE alias ADD COLUMN alias_to VARCHAR(255) NOT NULL DEFAULT '';
sql> ALTER TABLE alias ADD INDEX (is_alias);
sql> ALTER TABLE alias ADD INDEX (alias_to);

Sample usage: add additional email addresses extra@domain.com for existing user user@domain.com:

sql> USE vmail;
sql> INSERT INTO alias (address, goto, is_alias, alias_to, domain)
                VALUES ('extra@domain.com', 'user@domain.com', 1, 'user@domain.com', 'domain.com');

Notes:

• Values of column alias.goto and alias.alias_to are the same.
• You can add as many additional email addresses as you want.
• In above sample, extra@domain.com can be an email address belong to your alias domain.

Add new SQL table outbound_wblist in amavisd database

We need a new SQL table outbound_wblist in amavisd database, it's used to store white/blacklists for outbound message, required by iRedAPD plugin amavisd_wblist.

Please connect to MySQL server as MySQL root user, create new table:

$ mysql -uroot -p
mysql> USE amavisd;
mysql> CREATE TABLE outbound_wblist (rid integer unsigned NOT NULL, sid integer unsigned NOT NULL, wb varchar(10) NOT NULL, PRIMARY KEY (rid,sid));

After table created, please restart iRedAPD service.

Add new column delete_date in SQL table vmail.deleted_mailboxes

We need a SQL column to store the date we schedule to delete the mailbox after removing mail account. This new column might be used by iRedAdmin and other scripts used to delete mailboxes.

Please connect to MySQL server as MySQL root user, create new table:

$ mysql -uroot -p
sql> USE vmail;
sql> ALTER TABLE deleted_mailboxes ADD COLUMN delete_date DATE DEFAULT NULL;
sql> CREATE INDEX idx_delete_date ON deleted_mailboxes (delete_date);

[OPTIONAL] SOGo: enable isolated per-domain global address book

iRedMail doesn't enable global address book by default, this step will help you enable isolated per-domain global address book.

iRedMail creates a SQL VIEW sogo.users in SOGo SQL database, but it doesn't contain a domain column, if you enable global address book, every user is able to search ALL mail accounts hosted on iRedMail server, so we need to drop existing SQL VIEW, then re-create it with domain column for isolated per-domain global address book.

Now connect to MySQL server as root user, drop existing SQL VIEW sogo.users, then re-create it:

$ mysql -uroot -p
sql> USE sogo;
sql> DROP VIEW users;
sql> CREATE VIEW sogo.users (c_uid, c_name, c_password, c_cn, mail, domain) AS SELECT username, username, password, name, username, domain FROM vmail.mailbox WHERE active=1;

Open SOGo config file /etc/sogo/sogo.conf (Linux, OpenBSD) or /usr/local/etc/sogo/sogo.conf (FreeBSD), find the SOGoUserSources block defined for SQL backend. for example:

    // Authentication using SQL
    SOGoUserSources = (
        {
            ...

            //isAddressBook = YES;
            //displayName = "Global Address Book";
        }
    );

Uncomment isAddressBook and displayName lines, and add two new parameters like below:

            isAddressBook = YES;
            displayName = "Global Address Book";
            SOGoEnableDomainBasedUID = YES;
            DomainFieldName = "domain";

Restart SOGo service is required.

PostgreSQL backend special

Add new SQL columns in vmail database: alias.is_alias, alias.alias_to

iRedMail-0.9.3 offers per-user alias address support, that means mail user john.smith@domain.com can have additional email addresses like john@domain.com, js@domain.com and more, all emails sent to these addresses will be delivered to same mailbox. With per-user alias address support, you don't need to create many mail alias accounts anymore.

Per-user alias address requires 2 new SQL columns:

• alias.is_alias: this column marks a SQL record is a per-user alias account.
• alias.alias_to: this column stores the target address (it's john.smith@domain.com as described above). Its value is same as alias.goto when this sql record is a per-user alias, but alias.goto is not good for indexed searching, so we create alias.alias_to as an alternative.

Please follow steps below to create required SQL columns:

# su - postgres
$ psql -d vmail
sql> ALTER TABLE alias ADD COLUMN is_alias INT2 NOT NULL DEFAULT 0;
sql> ALTER TABLE alias ADD COLUMN alias_to VARCHAR(255) NOT NULL DEFAULT '';
sql> CREATE INDEX idx_alias_is_alias ON alias (is_alias);
sql> CREATE INDEX idx_alias_alias_to ON alias (alias_to);

Sample usage: add additional email addresses extra@domain.com for existing user user@domain.com:

sql> USE vmail;
sql> INSERT INTO alias (address, goto, is_alias, alias_to, domain)
                VALUES ('extra@domain.com', 'user@domain.com', 1, 'user@domain.com', 'domain.com');

Notes:

• Values of column alias.goto and alias.alias_to are the same.
• You can add as many additional email addresses as you want.
• In above sample, extra@domain.com can be an email address belong to your alias domain.

Add new SQL table outbound_wblist in amavisd database

We need a new SQL table outbound_wblist in amavisd database, it's used to store white/blacklists for outbound message, required by iRedAPD plugin amavisd_wblist.

Please switch to PostgreSQL daemon user, then execute SQL commands to import it:

• On Linux, PostgreSQL daemon user is postgres.
• On FreeBSD, PostgreSQL daemon user is pgsql.
• On OpenBSD, PostgreSQL daemon user is _postgresql.

# su - postgres
$ psql -d amavisd
sql> CREATE TABLE outbound_wblist (rid integer NOT NULL CHECK (rid >= 0), sid integer NOT NULL CHECK (sid >= 0), wb varchar(10) NOT NULL, PRIMARY KEY (rid,sid));
sql> ALTER TABLE outbound_wblist OWNER TO amavisd;

After table created, please restart iRedAPD service.

Add new column delete_date in SQL table vmail.deleted_mailboxes

We need a SQL column to store the date we schedule to delete the mailbox after removing mail account. This new column might be used by iRedAdmin and other scripts used to delete mailboxes.

Please switch to PostgreSQL daemon user, then execute SQL commands to import it:

• On Linux, PostgreSQL daemon user is postgres.
• On FreeBSD, PostgreSQL daemon user is pgsql.
• On OpenBSD, PostgreSQL daemon user is _postgresql.

# su - postgres
$ psql -d vmail
sql> ALTER TABLE deleted_mailboxes ADD COLUMN delete_date DATE DEFAULT NULL;
sql> CREATE INDEX idx_delete_date ON deleted_mailboxes (delete_date);

That's it.

[OPTIONAL] SOGo: enable isolated per-domain global address book

iRedMail doesn't enable global address book by default, this step will help you enable isolated per-domain global address book.

iRedMail creates a SQL VIEW sogo.users in SOGo SQL database, but it doesn't contain a domain column, if you enable global address book, every user is able to search ALL mail accounts hosted on iRedMail server, so we need to drop existing SQL VIEW, then re-create it with domain column for isolated per-domain global address book.

Before we go further, we must find the SQL username/password used to query vmail SQL database in /etc/postfix/pgsql/*.cf (on FreeBSD, it's /usr/local/etc/postfix/pgsql/*.cf). for example:

hosts       = 127.0.0.1
port        = 3306
user        = vmail
password    = NGtLm0jFiwwOH5AeQtTsSAkScUMdFc
dbname      = vmail

We need SQL server address, port, user, password and database name.

Now connect to PostgreSQL server as admin user, drop existing SQL VIEW sogo.users, and re-create it.

WARNING: You must replace the vmail database username and password by the real ones found in /etc/postfix/pgsql/*.cf.

# su - postgres
$ psql -d sogo
sql> DROP TABLE users;
sql> CREATE VIEW users AS SELECT * FROM dblink('host=127.0.0.1 port=5432 user=vmail password=NGtLm0jFiwwOH5AeQtTsSAkScUMdFc dbname=vmail', 'SELECT username AS c_uid, username AS c_name, password AS c_password, name AS c_cn, username AS mail, domain AS domain FROM mailbox WHERE active=1') AS users (c_uid VARCHAR(255), c_name VARCHAR(255), c_password VARCHAR(255), c_cn VARCHAR(255), mail VARCHAR(255), domain VARCHAR(255));
sql> ALTER TABLE users OWNER TO sogo;

Open SOGo config file /etc/sogo/sogo.conf (Linux, OpenBSD) or /usr/local/etc/sogo/sogo.conf (FreeBSD), find the SOGoUserSources block defined for SQL backend. for example:

    // Authentication using SQL
    SOGoUserSources = (
        {
            ...

            //isAddressBook = YES;
            //displayName = "Global Address Book";
        }
    );

Uncomment isAddressBook and displayName lines, and add two new parameters like below:

            isAddressBook = YES;
            displayName = "Global Address Book";
            SOGoEnableDomainBasedUID = YES;
            DomainFieldName = "domain";

Restart SOGo service is required.