Postfix

Postfix is my preferred SMTP server. Dovecot is my preferred IMAP server.

Lately I have been working on keeping virtual host configurations in PostgreSQL and managing it all with

Quick tips

Checking the postfix queue

mailq

Testing dovecot

Dovecot is the IMAP server so it has to work alongside Postfix. http://wiki2.dovecot.org/TestInstallation

Postfix configuration

On my personal servers, most email is simply forwarded over to mailboxes handled by Google for wildsong.biz. Only special purpose or hosted domain email stays on the server.

The hosted domain mailboxes will be configured in mysql. Initially I set them up from command line just using mysql!. I like working with mysql and phpmysqladmin because they are so straightforward.

Initially I loosely followed instructions found here: http://wiki2.dovecot.org/HowTo/DovecotPostgresql

On my Debian based server, I installed everything from packages, did not have to compile anything.

sudo apt-get install dovecot-imapd dovecot-postfix postfix-mysql libsasl2-2 libsasl2-modules squirrelmail
# This command will confirm mysql support is available (and lots of other things)
postconf -m
# This command will confirm dovecot is supported
postconf -a
# This command will confirm dovecot has mysql support built
dovecot --build-options

I installed postfixadmin for web access and administration.

dbconfig-common: writing config to /etc/dbconfig-common/postfixadmin.conf

Creating config file /etc/dbconfig-common/postfixadmin.conf with new version 

Creating config file /etc/postfixadmin/dbconfig.inc.php with new version
granting access to database postfixadmin for postfixadmin@localhost: success.
verifying access for postfixadmin@localhost: success.

Squirrelmail allows users use email via web.

The user that owns the top level mail folders is vmailbox. There are UID's assigned own the individual folders, starting at 20001. See the 'users' table below. Actual mail folders are here, organized by virtual host domain name:

/var/mail/vhosts/domainname

Postfix config files

This is a config that uses Google aka gmail as a smarthost for outbound mail. This config also supports virtual mailboxes via postgresql.

main.cf

smtpd_banner = $myhostname ESMTP $mail_name (AlseaGeOS)
biff = no

# Uncomment the next line to generate "delayed mail" warnings
delay_warning_time = 4h

queue_directory = /var/spool/postfix

myorigin = alseageo.net

# ---------------------------------------------------------

relayhost = [smtp.gmail.com]:587

disable_dns_lookups = yes

# authentication via SASL
# incoming connections
smtpd_sasl_auth_enable = no
smtpd_sasl_local_domain = $myhostname

# outgoing connections
smtp_sasl_auth_enable = yes
smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd
smtp_sasl_security_options = noanonymous
smtp_sasl_tls_security_options = noanonymous

# tls 
smtp_tls_loglevel = 1

#smtp_enforce_tls = yes
smtp_tls_per_site = hash:/etc/postfix/tls_per_site

smtp_tls_enforce_peername = no

smtp_tls_CAfile = /etc/postfix/certs/cakey.pem
smtp_tls_cert_file=/etc/postfix/certs/alseageo.pem
smtp_tls_key_file=/etc/postfix/certs/alseageo.key
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
smtp_use_tls = yes

smtpd_tls_CAfile = /etc/postfix/certs/cacert.pem
smtpd_tls_cert_file=/etc/postfix/certs/alseageo.pem
smtpd_tls_key_file=/etc/postfix/certs/alseageo.key
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtpd_use_tls = yes

#-------------------------------------------                                    
# Virtual mail support                                                          
#                                                                               
virtual_mailbox_base = /var/mail/vhosts
virtual_minimum_uid = 20000
#                                                                               
# Old file version                                                              
#transport_maps = hash:/etc/postfix/transport
#virtual_uid_maps = static:20000
#virtual_gid_maps = static:20000
#virtual_mailbox_domains = dispatch.incidentview.com
#virtual_mailbox_maps = hash:/etc/postfix/vmailbox
#virtual_alias_maps = hash:/etc/postfix/virtual
#                                                                               
# New SQL version                                                               
transport_maps = pgsql:/etc/postfix/transport.cf
virtual_uid_maps = pgsql:/etc/postfix/uid.cf
virtual_gid_maps = pgsql:/etc/postfix/gid.cf
virtual_mailbox_domains = pgsql:dispatch.incidentview.com
virtual_mailbox_maps = pgsql:/etc/postfix/mailbox.cf
virtual_maps = pgsql:/etc/postfix/virtual.cf


# ---------------------------------------------------------

command_directory = /usr/sbin
daemon_directory = /usr/lib/postfix

mail_owner = postfix

myhostname = kilchis.alseageo.com
mydomain = alseageo.com

inet_interfaces = all

# This is to allow the /etc/aliases file to have an effect.    
mydestination =
        kilchis.alseageo.com kilchis
        roaring.alseageo.com roaring
        minam.alseageo.com minam
        white.alseageo.com white
        dev.alseageo.com dev
        fall.alseageo.com fall
        localhost localhost.localdomain kilchis.localdomain

mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all

mail_spool_directory=/var/spool/mail

mynetworks = 127.0.0.0/8 10.1.10.0/24 10.8.0.0/24
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases

aliases.cf

user=mailreader
password=<secret>
dbname=mail
hosts=localhost
query=SELECT forw_addr FROM aliases WHERE alias='%s'

transport.cf

user=mailreader
password=<secret>
dbname=mail
hosts=localhost
query=SELECT transport FROM transport WHERE domain='%s'

mailbox.cf

user=mailreader
password=secret
dbname=mail
hosts=localhost
query=SELECT mailbox FROM postfix_mailboxes WHERE userid='%s'

uid.cf

user=mailreader
password=<secret>
dbname=mail
hosts=localhost
query=SELECT uid FROM users WHERE userid='%s'

gid.cf

user=mailreader
password=<secret>
dbname=mail
hosts=localhost
query=SELECT gid FROM users WHERE userid='%s'

virtual.cf

user=mailreader
password=secret
dbname=mail
hosts=localhost
query=SELECT userid FROM postfix_virtual WHERE address='%s'

virtual-domains.cf

user=mailreader
password=secret
dbname=mail
hosts=localhost
query=SELECT domain FROM virtual_domains WHERE domain='%s'

PostgreSQL

For a while I was looking into use PostgreSQL as the back end. I ended up going with MySQL, but left these notes here just in case.

Database for postfix

In addition to adding the tables don't forget to set up pg_hba.conf entry if needed

$ createdb -U postgres mail
$ psql -U postgres mail

Tables

The first two (aliases and transport) are generic, the rest are specific to virtual hosts support.

query = SELECT forw_addr FROM aliases WHERE alias='%s';

CREATE TABLE aliases (
  alias VARCHAR(255) NOT NULL UNIQUE,
  forw_addr VARCHAR(255) NOT NULL DEFAULT('root'),
  comment TEXT,
  PRIMARY KEY (alias)
);
-- Standard aliases are still in the /etc/aliases file
INSERT INTO aliases (alias, forw_addr, comment) VALUES ('root', 'bwilson', 'Person who should get root email');
INSERT INTO aliases (alias, forw_addr, comment) VALUES ('bwilson', '[email protected]', '');
# Now a group in our google set up
#INSERT INTO aliases (alias, forw_addr, comment) VALUES ('early-warning', 'bwilson', 'Part of Crow monitoring system');

CREATE TABLE transport (
  domain VARCHAR(128) NOT NULL,
  transport VARCHAR(128) NOT NULL,
  PRIMARY KEY (domain)
);
INSERT INTO transport (domain, transport) values('gmail.com', 'smtp:[smtp.gmail.com]');

-- Create a list of all the domains for which we are the final destination
CREATE TABLE virtual_domains (
  domain VARCHAR(255) NOT NULL,
  PRIMARY KEY (domain)
); 
INSERT INTO virtual_domains (domain) VALUES ('dispatch.incidentview.com');

-- This is completely separate from /etc/passwd and you use different id ranges for each supported domain.
CREATE TABLE users (
  userid VARCHAR(128) NOT NULL, -- note this is text
  password VARCHAR(128),
  realname VARCHAR(128),
  uid INTEGER NOT NULL,
  gid INTEGER NOT NULL,
  domain VARCHAR(255) REFERENCES virtual_domains(domain),
  home VARCHAR(128),   -- maildir folder where mail messages will be written
  mail VARCHAR(255),   -- alias for resending? 
  PRIMARY KEY (userid)
);
INSERT INTO users (userid, password, uid, gid, domain, home)  values('[email protected]', md5('atomiczombie'), 20001, 20000, 'dispatch.incidentview.com', 'bwilson/');
INSERT INTO users (userid, password, uid, gid, domain, home)  values('[email protected]', md5('landoflakes'), 20002, 20000, 'dispatch.incidentview.com', 'lebanon/');
INSERT INTO users (userid, password, uid, gid, domain, home)  values('[email protected]', md5('bigriver'), 20003, 20000, 'dispatch.incidentview.com', 'corvallis/');
INSERT INTO users (userid, password, uid, gid, domain, home)  values('[email protected]', md5('clocktower'), 20004, 20000, 'dispatch.incidentview.com', 'spokanefd9/');

-- Maps a full address to another alias, I use this for administrative accounts like postmaster
CREATE TABLE virtual (
  address VARCHAR(255) NOT NULL, -- full address including domain, like '[email protected]'
  userid VARCHAR(255) NOT NULL,  -- this is text, not a foreign key so it can be in aliases database
  PRIMARY KEY (address)
);
INSERT INTO virtual (address, userid) VALUES('[email protected]', 'postmaster');

-- Views that postfix uses, replaces /etc/postfix/vmailbox,
-- which maps a virtual user to a folder in /var/spool/mail/
CREATE VIEW postfix_mailboxes as
  SELECT userid, domain||'/'||home as mailbox from users
  UNION all
  SELECT domain AS userid, 'dummy' AS mailbox FROM transport;
--
-- View that replaces /etc/postfix/virtual,
--  which maps a virtual email address to a virtual user name or local host in /etc/aliases
CREATE VIEW postfix_virtual AS
  SELECT userid, userid AS address FROM users
  UNION all
  SELECT userid, address FROM virtual;

Roles

Set some real passwords when you run these!

-- postfix and dovecot only need read access (mail messages are not stored in postgresql)
CREATE USER mailreader PASSWORD 'secret';
GRANT SELECT ON aliases, transport, users, virtual, postfix_mailboxes, postfix_virtual, virtual_domains TO mailreader;

-- web ui will need write access
CREATE USER mailwriter password 'secret';
GRANT SELECT,INSERT,UPDATE,DELETE ON aliases, transport, users, virtual, postfix_mailboxes, postfix_virtual, virtual_domains  TO mailwriter;