New Email System Docs

Overall Topology

There are four separate servers in the new system:

Two 'gateway' mail servers
- Spam filtering
- Anti-virus filtering
- Whitelisting (only valid/configured email accounts can receive email; others are bounced)
- Forwards valid/scanned email to internal mail server
- Email sent to nextalarm.com (and other supported domains) is processed here
One 'internal' mail server
- Receives email scanned and validated by gateway servers
- Relays outbound email through authsmtp.com (mail clients sending email from nextalarm.com/other supported domains thus
  connect here)
- Email storage resides here
- POP3/IMAP mail clients connect here to read email (including webmail)
One webmail server

DNS/Server Names

Gateways (MX records point to these)
- smtp1.nextalarm.com
- smtp2.nextalarm.com

Internal
- mail.nextalarm.com

Webmail
- webmail.nextalarm.com

MX records (email DNS records) point to smtp1/smtp2.nextalarm.com; each has a relative priority of 10, which provide a kind of poor man's round robin.
See http://en.wikipedia.org/wiki/MX_record for more information. The gateway servers then forward the mail to mail.nextalarm.com.

SPF records for all domains should be set to (using DNS TXT records):

v=spf1 a mx ptr a:rs3.netmeme.org include:authsmtp.com ?all

This allows outbound email from servers at that domain, as well as from AuthSMTP, as well as the old Rackspace server which incorrectly heads outbound email with the server name "rs3.netmeme.org"

Virtual Domains and Users

The domains (nextalarm.com, voipalarm.com, lasershield.net ...) hosted by the email system, and the actual email accounts or users, are both referred to as 'virtual'. I.e., virtual domains and virtual users. This is simply terminology specific to the email server applications used in this system. Essentially it means that the domains and users serviced are more dynamic than static. For example, the email accounts need not correspond to unix users.

Virtual domain and user management is done at two places - the gateway mail servers and the internal mail server. At the gateway mail servers, virtual domains are maintained in /etc/postfix/relay_domains and /etc/postfix/transport; users are maintained in /etc/postfix/relay_recipients (relay_recipients is the whitelist). At the internal mail server, both domains and users are maintained in a database.

Detailed configuration for virtual domains and users is discussed later in this document.

authsmtp.com

Many ISPs and email administrators by default reject email from Amazon's EC2 server farm. There are several options for sending email reliably from EC2 (i.e., not having it routinely rejected). Probably the simplest is to use a commercial SMTP relay service, which is what we do. We use AuthSMTP (http://authsmtp.com/) for this purpose. To connect to AuthSMTP securely, we use Cyrus SASL over SSL; see below for detailed configuration.

Webmail

Webmail access is provided for convenience. All users that wish webmail access must have IMAP accounts. Webmail can be accessed at https://webmail.nextalarm.com. Currently, RoundCube (http://roundcube.net/) is being tested as a candidate webmail system. Horde (http://www.horde.org/) will also be tested, as it provides several applications in addition to webmail, including calendaring, file storage/management, todo lists, address book/contact management, and several more.

Nagios Monitoring

A new host configuration file (/etc/nagios/conf/hosts-email.cfg) has been added to define the four new systems. Each system has been added to the standard set of host checks (SSH, disk, CPU), and three of the four have been added to the SMTP host check (webmail has not). Also, POP3/IMAP checks have been added for the internal POP3/IMAP server.

Detailed Configuration

The pertinent/important parameters for each section will be discussed; however, each parameter of each area of configuration will not be covered in detail.

Spam/Virus/Whitelist Gateway Servers
Configuration for each system is identical (with respect to configuration of the email applications).
- Postfix
  main.cf and master.cf are the two primary Postfix configuration files. main.cf is where many more general parameters are specified; by convention, more specific areas of configuration are partitioned into various other files which are linked to from or referred to from main.cf. master.cf defines which client and server processes run, their privilege model, how long they live, etc.
  - /etc/postfix/main.cf
    - Disable local delivery (only want to validate/scan messages and forward to internal mail server); 1st two intentionally blank
```
mydestination = 
local_recipient_maps = 
local_transport = error:local mail delivery is disabled
```
    - The internet hostname and domain of this system (does not affect ability to host multiple domains)
```
mydomain = voipalarm.us
```
    - Relay for these domains only (refers to another file for configuration)
```
relay_domains = /etc/postfix/relay_domains
```
    - Lookup table with mappings from recipient address -> message delivery transport
```
transport_maps = hash:/etc/postfix/transport
```
    - "Plus addressing" - so you can receive email as "myemail+something@...". Must match with Amavis configuration for subfolder delivery of spam.
```
recipient_delimiter=+
```
    - Whitelist
```
relay_recipient_maps = hash:/etc/postfix/relay_recipients
```
    - Rejection rules based on RCPT TO; note that we are lenient here due to the large number of SMTP servers that are not standards-compliant
```
smtpd_recipient_restrictions =
    permit_mynetworks
    # reject_unknown_client_hostname
    # reject_unknown_reverse_client_hostname
    # reject_unauth_destination: destination address not in $mydestination
    reject_unauth_destination
    # reject_unknown_sender_domain: no A or MX record for domain
    reject_unknown_sender_domain
    # reject_invalid_hostname:  HELO or EHLO syntax is invalid
    reject_invalid_hostname
```
    - Realtime Blackhole List (RBL) Spam Blocking (http://en.wikipedia.org/wiki/DNSBL)
      Various free public blackhole list servers are available; they are usable at the Postfix level to reject mail from known/listed sources of spam. The configuration is in main.cf, also under the smtpd_recipient_restrictions:
```
reject_rbl_client cbl.abuseat.org
    reject_rbl_client bl.spamcop.net
    reject_rbl_client sbl.spamhaus.org
    reject_rbl_client dnsbl.njabl.org
    reject_rbl_client dnsbl.sorbs.net
    permit
```
    - Anti-virus/spam filtering; the first line directs Postfix to use smtp-amavis, a process defined in master.cf, for content filtering all messages. The second line directs Postfix to disable canonical address mapping, virtual alias map expansion, address masquerading, and automatic BCC recipients.
```
content_filter=smtp-amavis:[localhost]:10024
receive_override_options=no_address_mappings
```
    - Tarpit bots/clients/spammers who send errors or scan for accounts
```
smtpd_error_sleep_time = 60
smtpd_soft_error_limit = 60
smtpd_hard_error_limit = 10
```
    - Semi-anonymous banner as part of the 220 greeting; myhostname is required by the RFC
```
smtpd_banner = $myhostname ESMTP MTA
```
  - /etc/postfix/master.cf
    - Hand off mail to amavis, which invokes anti-virus and spam filtering
```
/etc/postfix/transportsmtp-amavis unix -      -       n       -       4       smtp
    -o smtp_data_done_timeout=1200
    -o smtp_send_xforward_command=yes
    -o disable_dns_lookups=yes
    -o max_use=20
```
    - Receive scanned mail back from amavis
```
127.0.0.1:10025 inet n  -       -     -       -  smtpd
    -o content_filter=
    -o local_recipient_maps=
    -o relay_recipient_maps=
    -o smtpd_restriction_classes=
    -o smtpd_delay_reject=no
    -o smtpd_client_restrictions=permit_mynetworks,reject
    -o smtpd_helo_restrictions=
    -o smtpd_sender_restrictions=
    -o smtpd_recipient_restrictions=permit_mynetworks,reject
    -o smtpd_data_restrictions=reject_unauth_pipelining
    -o smtpd_end_of_data_restrictions=
    -o mynetworks=127.0.0.0/8
    -o smtpd_error_sleep_time=0
    -o smtpd_soft_error_limit=1001
    -o smtpd_hard_error_limit=1000
    -o smtpd_client_connection_count_limit=0
    -o smtpd_client_connection_rate_limit=0
    -o receive_override_options=no_header_body_checks,no_unknown_recipient_checks
    -o local_header_rewrite_clients=
```
  - /etc/postfix/relay_recipients
    This is the whitelist. Every email address that should receive email must be listed in this file. The file is in the format [email address] [x], where [x] is simply a placeholder and is in fact the letter x; this is because Postfix configuration files require a specific form of syntax. Example (used in testing):
```
bryan-pop@voipalarm.us     x
bryan-pop@emacnetwork.com       x
bryan-imap@voipalarm.us         x
bryan-imap@emacnetwork.com      x
dan-pop@voipalarm.us            x
dan-pop@emacnetwork.com         x
dan-imap@voipalarm.us           x
dan-imap@emacnetwork.com        x
ken-pop@voipalarm.us            x
ken-pop@emacnetwork.com         x
ken-imap@voipalarm.us           x
ken-imap@emacnetwork.com        x
robert-pop@voipalarm.us         x
robert-pop@emacnetwork.com      x
robert-imap@voipalarm.us        x
robert-imap@emacnetwork.com     x
postmaster@voipalarm.us         x
postmaster@emacnetwork.com      x
```
  - /etc/postfix/relay_domains
    Which destination domains we will accept email for; as with relay_recipients, the right hand side is a placeholder to satisfy configuration file syntax.
```
voipalarm.us OK
emacnetwork.com OK
```
  - /etc/postfix/virtual
    Alias-like lookup map, not related directly to the virtual domains/users setup discussed later.
  - /etc/postfix/transport
    Transport (protocol, delivery method) to use on a per-domain basis; current configuration always forwards on SMTP to the internal server
```
voipalarm.us    smtp:[174.129.220.253]
emacnetwork.com      smtp:[174.129.220.253]
```
- Amavis
  - /etc/amavisd/amavisd.conf
    - Used in other settings
```
$mydomain = 'voipalarm.us';
```
    - Another variable used in other settings
```
$MYHOME = '/var/spool/amavisd';
```
    - Number of child processes to pre-fork
```
$max_servers = 10;
```
    - Send virus-infected emails to specific email address
```
$virus_quarantine_to = "virusmail\@voipalarm.us";
```
    - List of domains to scan for
```
@local_domains_maps = ( [".$mydomain", ".emacnetwork.com"] );
```
    - Listen on which port - must correspond to content_filter setting in Postfix main.cf
```
$inet_socket_port = 10024;
```
    - Spam tagging thresholds
```
$sa_tag_level_deflt  = -999;  # add spam info headers if at, or above that level (always add)
$sa_tag2_level_deflt = 6.2;  # add 'spam detected' headers at that level
$sa_kill_level_deflt = 6.9;  # triggers spam evasive actions (e.g. blocks mail)
$sa_dsn_cutoff_level = 10;   # spam level beyond which a DSN is not sent
$penpals_threshold_high = $sa_kill_level_deflt;  # don't waste time on hi spam
$sa_mail_body_size_limit = 400*1024; # don't waste time on SA if mail is larger
```
    - Send notification here when virus-infected email is found
```
$virus_admin = "robert-pop\@voipalarm.us";
```
    - Send notification emails through this mechanism; must match an entry in Postfix master.cf
```
$notify_method  = 'smtp:[127.0.0.1]:10025';
```
    - Mangle to: on spam thusly - robert@ ==> robert+spam@ and add '[SPAM] ' to subject line; recipient_delimiter must match in Postfix main.cf
```
$recipient_delimiter = '+';
@addr_extension_spam_maps = ('spam');
$sa_spam_subject_tag = '[SPAM] ';
```
    - Final handling of virus, spam, banned, bad header emails; note that virus-infected mail is directed to an email address thus it is discarded rather than bounced/sent on/etc; spam is passed on to recipient having had subject line and to: mangled
```
$final_virus_destiny      = D_DISCARD;
$final_banned_destiny     = D_BOUNCE;
$final_spam_destiny       = D_PASS;
$final_bad_header_destiny = D_BOUNCE;
```
    - Antivirus scanner to run
```
@av_scanners = (
['ClamAV-clamd',
  #\&ask_daemon, ["CONTSCAN {}\n", "/var/spool/amavisd/amavisd.sock"],
  \&ask_daemon, ["CONTSCAN {}\n", '127.0.0.1:3310'],
  qr/\bOK$/, qr/\bFOUND$/,
  qr/^.*?: (?!Infected Archive)(.*) FOUND$/ ]);
```
- ClamAV
  Infected files are quarantined in /mnt/quarantine/ on both gateway servers.
  - /etc/clamd.d/amavisd.conf
- SpamAssassin
  - /usr/share/spamassassin/
    This directory holds the spam filtering rules used by SpamAssassin. Amavis loads the Perl SpamAssassin libraries and executes the rule checks itself, without calling an external SpamAssassin application.
- DCC
  DCC (Distributed Checksum Clearinghouse) (http://www.dcc-servers.net/dcc/) is an anti-spam content filter. It is installed from source on the two gateway systems. The configuration is in /usr/share/spamassassin/init.pre; the line below is added:
```
loadplugin Mail::SpamAssassin::Plugin::DCC
```
Internal Mail Server
- Postfix
  - For SASL authentication configuration see section below.
  - /etc/postfix/main.cf
    - Virtual domain/user setup (refers to other files, explained below)
      See section on virtual domains/users below.
    - Specifying user/group ID ownership for of virtual mailboxes; note that this requires a user/group set up w/ID 5100
```
virtual_uid_maps = static:5100
virtual_gid_maps = static:5100
```
```
$ grep 5100 /etc/passwd
vmail:x:5100:5100::/home/vmail:/bin/bash
$ grep 5100 /etc/group
vmail:x:5100:
```
    - Specify that the 'dovecot' transport (defined in master.cf) is used for virtual domains
```
virtual_transport = dovecot
dovecot_destination_recipient_limit = 1
```
    - Stay below authsmtp's threshold (http://www.authsmtp.com/faqs/faq-61.html)
```
default_destination_concurrency_limit = 4
```
  - /etc/postfix/master.cf
    - Listen on 587 as well as 25 for SMTP
```
587       inet  n       -       n       -       -       smtpd
```
    - Define the dovecot virtual transport for delivery to the appropriate mailbox location (see virtual_transport in main.cf above); ${extension} is expanded for delivery using +spam if it's been added to the username portion of an email address. If there is no corresponding directory matching the Maildir standard that matches the right side of a plus-addressed username, the message will be delivered the regular inbox (this allows users to use plus-addressing arbitrarily).
```
dovecot   unix  -       n       n       -       -       pipe
   flags=DRhu user=vmail:vmail argv=/usr/libexec/dovecot/deliver -f ${sender} -d ${user}@${nexthop} -n -m ${extension}
```
  - /etc/postfix/mysql-virtual_alias_maps.cf
    The virtual_alias_maps directive in main.cf references lookup tables that alias specific mail addresses or domains to other local or remote address. In this system this aliasing is managed in a database; this file defines database access and queries for Postfix to fetch this information. See the section on the virtual domain/user database below for more information.
  - /etc/postfix/mysql-email2email.cf
    Included in the virtual_alias_maps directive; this allows virtual user mappings to work while allowing catch-all addressing. See the section on the virtual domain/user database below for more information.
  - /etc/postfix/mysql-virtual_mailbox_domains.cf
    Declares access to the database table that defines the virtual domains served by this system. See the section on the virtual domain/user database below for more information.
  - /etc/postfix/mysql-virtual_mailbox_maps.cf
    Declares access to the database table that defines the virtual users served by this system. See the section on the virtual domain/user database below for more information.
- Dovecot
  - /etc/dovecot/dovecot.conf
    - Run-time data; pipes, pidfiles, etc.
```
base_dir = /var/run/dovecot/
```
    - Do not turn off SSL
```
ssl_disable = no
```
    - PKI files
```
ssl_key_file = /etc/pki/dovecot/private/dovecot.pem
ssl_cert_file = /etc/pki/dovecot/certs/dovecot.pem
ssl_ca_file = /etc/pki/dovecot/certs/dovecot.pem
```
    - User to run POP3/IMAP server as
```
login_user = dovecot
```
    - IMAP-specific parameters (see dovecot.conf for details/comments)
```
protocol imap {
  imap_client_workarounds = outlook-idle
}
```
    - POP3-specific parameters (see dovecot.conf for details/comments)
```
protocol pop3 {
  pop3_uidl_format = %08Xu%08Xv
}
```
    - LDA (local delivery agent)-specific parameters
```
protocol lda {
  postmaster_address = postmaster@voipalarm.us
  log_path = /home/vmail/dovecot-deliver.log
  # UNIX socket path to master authentication server to find users.
  auth_socket_path = /var/run/dovecot/auth-master
}
```
    - It is highly recommended to read the Dovecot wiki for the auth configuration details: http://wiki.dovecot.org/FrontPage#Dovecot_configuration
- SASL
  SASL authentication is used in two cases. First, when this email system is acting as a 'server'; this case includes users sending email or reading their email. Dovecot SASL is used in this case. Second, when this email system is acting as a 'client'. This means sending email from users to AuthSMTP, our SMTP relay provider. Cyrus SASL is used in this case.
  Note that the Postfix server in this case had to be built from source in order to use both Dovecot and Cyrus SASL and also MySQL-based virtual domains/users.
  - Dovecot SASL
    - /etc/postfix/main.cf
      - Enable server SASL
        smtpd_sasl_auth_enable = yes
      - Reject email unless SASL is authenticated and the client IP address matches any network or network address listed in $mynetworks
        smtpd_recipient_restrictions= permit_mynetworks permit_sasl_authenticated reject_unauth_destination
      - Report the SASL authenticated user name in the smtpd(8) Received message header
        smtpd_sasl_authenticated_header = yes
      - Enable inter-operability with SMTP clients (microsoft) that implement an obsolete version of the AUTH command (RFC 4954)
        broken_sasl_auth_clients=yes
      - Server SASL auth type to use
        smtpd_sasl_type=dovecot
      - Implementation-specific information that the Postfix SMTP server passes through to the SASL plug-in implementation that is selected with smtpd_sasl_type
        smtpd_sasl_path=private/auth
      - Postfix SMTP server SASL security options
        smtpd_sasl_security_options=noanonymous
      - TLS security level for the SMTP server - allows non-TLS (but see smtpd_tls_auth_only, which forces SASL over TLS)
        smtpd_tls_security_level = may
      - smtpd_use_tls is obsolete but still supported
        smtpd_use_tls=yes
      - Do not announce or accept SASL authentication over unencrypted connections
        smtpd_tls_auth_only=yes
      - PKI files
        smtpd_tls_key_file=/etc/ssl/private/postfix.pem smtpd_tls_cert_file=/etc/ssl/certs/postfix.pem smtpd_tls_CAfile=/etc/ssl/certs/postfix.pem
  - Cyrus SASL
    - /etc/postfix/main.cf
      - Enable client SASL authentication
        smtp_sasl_auth_enable = yes
      - Client SASL type
        smtp_sasl_type = cyrus
      - Enable connection caching to our SMTP relay service
        smtp_connection_cache_destinations = mail.authsmtp.com
      - SASL authentication credentials
        smtp_sasl_password_maps = hash:/etc/postfix/sasl_credentials
      - Client SASL auth mechanism restrictions
        smtp_sasl_security_options = noanonymous
      - Allowed client SASL auth mechanisms
        smtp_sasl_mechanism_filter = plain, login
      - Next-hop destination of non-local email (which will use client SASL auth)
        relayhost = [mail.authsmtp.com]
    - /etc/postfix/sasl_credentials
      - Client SASL auth credentials (AuthSMTP account credentials)
        [mail.authsmtp.com] username:password
- Mailbox/email storage
  Mail is stored in the filesystem in the Maildir format (Dovecot has non-standard extensions but it remains entirely compatible with the standard). The physical location is in a EBS volume, mounted on /mnt/vmail and linked from /home/vmail (for configuration purposes, all mail is stored by a 'vmail' user - see previous configuration info). Within /mnt/vmail, each virtual domain has a directory. Within each virtual domain directory, each virtual user in that domain has a directory within which is the actual Maildir directory.
```
[root@email-dest ~]# cd /home/vmail/
[root@email-dest vmail]# ls
./  ../  .bash_logout  .bash_profile  .bashrc  dovecot-deliver.log  emacnetwork.com/  voipalarm.us/
[root@email-dest vmail]# ls voipalarm.us/
./  ../  bryan-pop/  ken-pop/  list1/  postmaster/  robert-imap/  robert-pop/  virusmail/
[root@email-dest vmail]# ls voipalarm.us/bryan-pop/Maildir/
./   .Apple Mail To Do/  dovecot.index        dovecot.index.log  dovecot-uidlist  .Sent Messages/  tmp/
../  cur/                dovecot.index.cache  dovecot-keywords   new/             .spam/           .Test Folder/
[root@email-dest vmail]# ls voipalarm.us/robert-imap/Maildir/
./  ../  cur/  dovecot.index  dovecot.index.cache  dovecot.index.log  dovecot-uidlist  new/  .spam/  tmp/
```
  Email is delivered (stored) by the Dovecot deliver command (invoked by Postfix's virtual_transport configuration), and is served (read) by the Dovecot IMAP and POP3 daemons.

Virtual Domain/User Database
Virtual domains allow for flexibility in serving email for more than one domain (obviously). Virtual users allow user/email accounts to be used that do not correspond to unix users. One of the options for virtual domains/users is a SQL/relational database, which is what's used in this system (MySQL for now).

Database setup: Table creation

Virtual domains

CREATE TABLE `virtual_domains` (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
) ENGINE = InnoDB;

mysql> select * from virtual_domains;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | voipalarm.us    |
|  2 | emacnetwork.com |
+----+-----------------+

Virtual users

CREATE TABLE `virtual_users` (
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
domain_id INT(11) NOT NULL,
user VARCHAR(40) NOT NULL,
password VARCHAR(32) NOT NULL,
CONSTRAINT UNIQUE_EMAIL UNIQUE (domain_id,user),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE = InnoDB;

mysql> select * from virtual_users;
+----+-----------+---------------+----------------------------------+
| id | domain_id | user          | password                         |
+----+-----------+---------------+----------------------------------+
| 15 |         1 | bryan-pop     | 482c811da5d5b4bc6d497ffa98491e38 |
| 16 |         2 | bryan-pop     | 482c811da5d5b4bc6d497ffa98491e38 |
| 17 |         1 | bryan-imap    | 482c811da5d5b4bc6d497ffa98491e38 |
| 18 |         2 | bryan-imap    | 482c811da5d5b4bc6d497ffa98491e38 |
| 19 |         1 | dan-pop       | 482c811da5d5b4bc6d497ffa98491e38 |
| 20 |         2 | dan-pop       | 482c811da5d5b4bc6d497ffa98491e38 |
| 21 |         1 | dan-imap      | 482c811da5d5b4bc6d497ffa98491e38 |
... etc.

Virtual aliases (forwarding, lists)

CREATE TABLE `virtual_aliases` (
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
domain_id INT(11) NOT NULL,
source VARCHAR(40) NOT NULL,
destination VARCHAR(80) NOT NULL,
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE = InnoDB;

mysql> select * from virtual_aliases;
+----+-----------+--------+--------------------------+
| id | domain_id | source | destination              |
+----+-----------+--------+--------------------------+
|  1 |         1 | list1  | bryan-pop@voipalarm.us   |
|  2 |         1 | list1  | robert-imap@voipalarm.us |
+----+-----------+--------+--------------------------+

Database setup: View creation

Users view

CREATE VIEW view_users AS
SELECT CONCAT(virtual_users.user, '@', virtual_domains.name) AS email,
    virtual_users.password
FROM virtual_users
LEFT JOIN virtual_domains ON virtual_users.domain_id=virtual_domains.id;

mysql> select * from view_users;
+-------------------------------+----------------------------------+
| email                         | password                         |
+-------------------------------+----------------------------------+
| bryan-pop@voipalarm.us        | 482c811da5d5b4bc6d497ffa98491e38 |
| bryan-pop@emacnetwork.com     | 482c811da5d5b4bc6d497ffa98491e38 |
| bryan-imap@voipalarm.us       | 482c811da5d5b4bc6d497ffa98491e38 |
| bryan-imap@emacnetwork.com    | 482c811da5d5b4bc6d497ffa98491e38 |
| dan-pop@voipalarm.us          | 482c811da5d5b4bc6d497ffa98491e38 |
| dan-pop@emacnetwork.com       | 482c811da5d5b4bc6d497ffa98491e38 |
| dan-imap@voipalarm.us         | 482c811da5d5b4bc6d497ffa98491e38 |
... etc.

Aliases view

CREATE VIEW view_aliases AS
SELECT CONCAT(virtual_aliases.source, '@', virtual_domains.name) AS email,
       destination
FROM virtual_aliases
LEFT JOIN virtual_domains ON virtual_aliases.domain_id=virtual_domains.id;

mysql> select * from view_aliases;
+--------------------+--------------------------+
| email              | destination              |
+--------------------+--------------------------+
| list1@voipalarm.us | bryan-pop@voipalarm.us   |
| list1@voipalarm.us | robert-imap@voipalarm.us |
+--------------------+--------------------------+

Postfix configuration
Postfix (having been compiled with MySQL support) can delegate the functionality of its lookup tables to database queries. This is accomplished by referencing a file that provides database connection information and the query for the lookup table in question. The Postfix main.cf file references virtual_alias_maps, virtual_mailbox_maps, and virtual_mailbox_domains.
- main.cf
```
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual_alias_maps.cf,mysql:/etc/postfix/mysql-email2email.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual_mailbox_maps.cf
virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual_mailbox_domains.cf
```
- mysql-virtual_alias_maps.cf translates (provides a query) an 'external' email address to an 'internal' or alias email address
```
user = [username]
password = [password]
hosts = 127.0.0.1
dbname = mailserver
query = SELECT destination FROM view_aliases WHERE email='%s'
```
- mysql-email2email.cf is required if catchall addresses are used
```
user = [username]
password = [password]
hosts = 127.0.0.1
dbname = mailserver
query = SELECT email FROM view_users WHERE email='%s'
```
- mysql-virtual_mailbox_maps.cf provides a query that verifies (looks up) email addresses, and also returns the MD5 hashed password for authentication
```
user = [username]
password = [password]
hosts = 127.0.0.1
dbname = mailserver
query = SELECT email FROM view_users WHERE email='%s'
```
- mysql-virtual_mailbox_domains.cf provides a query that retrieves domain name and ID for the virtual domains
```
user = [username]
password = [password]
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_domains WHERE name='%s'
```

Misc

Converting mbox to Maildir format
Running
```
./mb2md-3.20.pl -s /root/tmp/mbox -d /root/tmp
```
(it's in the path, located in /usr/local/sbin) will take the mbox file specified as /root/tmp/mbox and create populated Maildir folders in /root/tmp (new, cur, and tmp).
The files created under cur, new and tmp must then be moved to the corresponding cur/new/tmp folders for the user whose mailbox you are converting. Permissions and ownership must be set the same as the existing files under cur/new/tmp (ie, those that already exist, not the ones you just created).

Anytime a Postfix configuration file (other than main.cf or master.cf) is edited, postmap [filename] needs to be run on the file to regenerate the hash db file, and then Postfix should be restarted (/etc/init.d/postfix restart or just 'postfix reload')
Adding a new domain
- Add domain to /etc/postfix/relay_domains on gateway machines
- Add domain to /etc/postfix/transport on gateway machines
- Add domain to database
```
insert into virtual_domains(name) values('nextviewcam.com');
```
- Restart Postfix on gateway machines
- Restart Postfix and Dovecot on internal mail machine
Adding a new user
- Add user to /etc/postfix/relay_recipients on gateway machines
- Add user to database
```
insert into virtual_users(domain_id, user, password) values(1, 'username', md5('password'));
```
- Restart Postfix on gateway machines
Adding a mailing list
- Add new user representing mailing list (see 'Adding a new user')
- For each member of the mailing list:
```
insert into virtual_aliases(domain_id, source, destination) values(1, 'list1', 'robert-imap@voipalarm.us');
```

Adding a new mail forward

Insert into database:

insert into virtual_aliases(domain_id, source, destination) values(2, 'alias-source', 'alias-destination@nextalarm.com');

Locking out a user

Change the password in MySQL

update virtual_users set password = md5('new password') where user = 'user-without-@domain';

SASL Authentication Caching
We are not caching authentication/sessions. See here for details if it is desired: http://wiki.dovecot.org/Authentication/Caching

Building Postfix with Dovecot and Cyrus SASL, and with MySQL
Only Postfix was actually downloaded as source; dependencies were installed using yum. Postfix version 2.5.6 was downloaded from http://www.postfix.org/download.html.

Postfix READMEs
https://redmine.nextalarm.com/versions/download/3?attachment_id=391

YUM/RPM packages

[root@email-dest postfix-2.5.6]# yum list installed|grep -e cyrus -e dove -e pam -e sasl -e pop -e imap -e smtp -e post -e mysql -e spam
cyrus-imapd-devel.i386                       2.3.11-1.fc8              installed
cyrus-sasl.i386                              2.1.22-8.fc8              installed
cyrus-sasl-devel.i386                        2.1.22-8.fc8              installed
cyrus-sasl-lib.i386                          2.1.22-8.fc8              installed
cyrus-sasl-plain.i386                        2.1.22-8.fc8              installed
dovecot.i386                                 1:1.0.15-16.fc8           installed
dovecot-mysql.i386                           1:1.0.15-16.fc8           installed
libdbi-dbd-mysql.i386                        0.8.2-1.2.fc8             installed
libnss-mysql.i386                            1.5-6.fc8                 installed
mysql.i386                                   5.0.45-6.fc8              installed
mysql++.i386                                 2.3.2-2.fc8               installed
mysql++-devel.i386                           2.3.2-2.fc8               installed
mysql-devel.i386                             5.0.45-6.fc8              installed
mysql-libs.i386                              5.0.45-6.fc8              installed
mysql-server.i386                            5.0.45-6.fc8              installed
pam.i386                                     0.99.8.1-17.1.fc8         installed
pam_ccreds.i386                              4-3.fc8                   installed
pam_krb5.i386                                2.2.18-2.fc8              installed
pam_passwdqc.i386                            1.0.4-4                   installed
pam_pkcs11.i386                              0.5.3-25                  installed
pam_smb.i386                                 1.1.7-7.2.2               installed
popt.i386                                    1.13-4.fc8                installed
postfix.i386                                 2:2.5.5-1.fc8             installed
spamassassin.i386                            3.2.5-1.fc8               installed

Build options

SYSTYPE = LINUX2
AR      = ar
ARFL    = rv
RANLIB  = ranlib
SYSLIBS = -L/usr/lib -L/usr/lib/sasl2 -L/usr/lib/mysql/ -L/usr/lib/openssl/ -lmysqlclient -lz -lm -lssl -lcrypto -lsasl2 -ldb -lnsl -lresolv
CC      = gcc $(WARN) -DUSE_SASL_AUTH -DUSE_CYRUS_SASL -DDEF_SERVER_SASL_TYPE=\"dovecot\" -DUSE_TLS -DHAS_MYSQL -I/usr/include/mysql/ -I/usr/include/sasl/
OPT     = -O
DEBUG   = -g
AWK     = awk
STRCASE =
EXPORT  = AUXLIBS='-L/usr/lib -L/usr/lib/sasl2 -L/usr/lib/mysql/ -L/usr/lib/openssl/ -lmysqlclient -lz -lm -lssl -lcrypto -lsasl2' CCARGS='-DUSE_SASL_AUTH -DUSE_CYRUS_SASL -DDEF_SERVER_SASL_TYPE=\"dovecot\" -DUSE_TLS -DHAS_MYSQL -I/usr/include/mysql/ -I/usr/include/sasl/' OPT='-O' DEBUG='-g'
WARN    = -Wall -Wno-comment -Wformat -Wimplicit -Wmissing-prototypes \
        -Wparentheses -Wstrict-prototypes -Wswitch -Wuninitialized \
        -Wunused
SHELL   = /bin/sh
WARN    = -Wmissing-prototypes -Wformat
OPTS    = 'CC=$(CC)'
DIRS    = src/util src/global src/dns src/tls src/xsasl src/milter src/master \
        src/postfix src/smtpstone \
        src/sendmail src/error src/pickup src/cleanup src/smtpd src/local \
        src/trivial-rewrite src/qmgr src/oqmgr src/smtp src/bounce \
        src/pipe src/showq src/postalias src/postcat src/postconf src/postdrop \
        src/postkick src/postlock src/postlog src/postmap src/postqueue \
        src/postsuper src/qmqpd src/spawn src/flush src/verify \
        src/virtual src/proxymap src/anvil src/scache src/discard src/tlsmgr
MANDIRS = proto man html