Posts tagged postgresql

Mail Server Setup: Part 2 – Dovecot and PostgreSQL

Jul6th
2012

Written by Rob

This is the second part in the two part posting about setting up my mail server. In this one we tackle Dovecot and PostgreSQL.

This post will be remarkably short compared to the first one. A bit of a refresher on the setup I’m aiming for:

We’re looking at the red arrows in this one, so Dovecot, as well as setting up the Virtual Mailboxes and Sieve support. Because we’re using dovecot-lda to handle delivery of mail into the actual “mailbox” on the file system we need to have completed configuring Dovecot before our setup of Postfix from Part One will be functional.

Installation of Dovecot

Dovecot itself was installed as a dependency of Postfix back in Part One. That was because we selected Dovecot2 SASL Auth option. If you want to install Dovecot manually head on over to ports and run through the usual process, selecting the following options on the config screen:

KQUEUE (default)
SSL (default)
PGSQL

[root@shana /]$ cd /usr/ports/mail/postfix
[root@shana /usr/ports/mail/postfix]$ make install clean

1 2	[root@shana /]$ cd /usr/ports/mail/postfix [root@shana /usr/ports/mail/postfix]$ make install clean

Under Dovecot2 the Sieve plugin support is provided by Pigeonhole, which can be easily installed via ports:

[root@shana /]$ cd /usr/ports/mail/dovecot2-pigeonhole
[root@shana /usr/ports/mail/dovecot2-pigeonhole]$ make install clean

1 2	[root@shana /]$ cd /usr/ports/mail/dovecot2-pigeonhole [root@shana /usr/ports/mail/dovecot2-pigeonhole]$ make install clean

Configuration

Lets separate our configuration into several bits to make it a bit easier to follow.

Virtual Mailboxes, Databases and Delivery

We need to start with the delivery of mail into the virtual mailboxes, so that we can finally test the Postfix setup and delivery of mail into the mailboxes.

From Post One, we setup dovecot-lda using the following line in /usr/local/etc/postfix/master.cf.

dovecot unix - n n - - pipe flags=DRhu user=vmail:vmail argv=/usr/local/libexec/dovecot/deliver -f ${sender} -d ${recipient}

So it calls /usr/local/libexec/dovecot/deliver with the sender’s email address passed in via the -f option, and the recipient’s email via -d. We’re also running it with a user called vmail. Lets setup the user and group first.

[root@shana /]$ adduser
# Username: vmail
# Full name: Virtual Mailbox User
# Uid (Leave empty for default): 145
# Login group [vmail]:
# Login group is vmail. Invite vmail into other groups? []:
# Login class [default]:
# Shell (sh csh tcsh bash rbash nologin) [sh]: nologin
# Home directory [/home/vmail]: /var/vmail
# Home directory permissions (Leave empty for default):
# Use password-based authentication? [yes]: no
# Lock out the account after creation? [no]: yes
# Username : vmail
# Password :
# Full Name : Virtual Mailbox User
# Uid : 145
# Class :
# Groups : vmail
# Home : /var/vmail
# Home Mode :
# Shell : /usr/sbin/nologin
# Locked : yes
# OK? (yes/no): yes
# adduser: INFO: Successfully added (vmail) to the user database.
# adduser: INFO: Account (vmail) is locked.
# Add another user? (yes/no): no
# Goodbye!

[root@shana /]$ adduser

# Username: vmail

# Full name: Virtual Mailbox User

# Uid (Leave empty for default): 145

# Login group [vmail]:

# Login group is vmail. Invite vmail into other groups? []:

# Login class [default]:

# Shell (sh csh tcsh bash rbash nologin) [sh]: nologin

# Home directory [/home/vmail]: /var/vmail

# Home directory permissions (Leave empty for default):

# Use password-based authentication? [yes]: no

# Lock out the account after creation? [no]: yes

# Username : vmail

# Password :

# Full Name : Virtual Mailbox User

# Uid : 145

# Class :

# Groups : vmail

# Home : /var/vmail

# Home Mode :

# Shell : /usr/sbin/nologin

# Locked : yes

# OK? (yes/no): yes

# adduser: INFO: Successfully added (vmail) to the user database.

# adduser: INFO: Account (vmail) is locked.

# Add another user? (yes/no): no

# Goodbye!

Now that that is done, clear out all of the skeleton files that were copied into /var/vmail and we can update the config file for LDA.

[root@shana /var/vmail]$ rm .*

1	[root@shana /var/vmail]$ rm .*

Almost all of Dovecot’s configuration can be found in /usr/local/etc/dovecot/, including the LDA settings that we’re after now. If that directory is empty for you then the README file should tell you where to find the example ones, copy the example files we need into the target directory.

[root@shana /usr/local/etc/dovecot]$ cp /usr/local/share/doc/dovecot/example-config/dovecot.conf .

[root@shana /usr/local/etc/dovecot]$ mkdir conf.d

[root@shana /usr/local/etc/dovecot]$ cp /usr/local/share/doc/dovecot/example-config/conf.d/* conf.d/

[root@shana /usr/local/etc/dovecot]$ cp /usr/local/share/doc/dovecot/example-config/dovecot.conf .

[root@shana /usr/local/etc/dovecot]$ mkdir conf.d

[root@shana /usr/local/etc/dovecot]$ cp /usr/local/share/doc/dovecot/example-config/conf.d/* conf.d/

There are some things we need to setup in order to get the LDA working, including Sieve, the Passdb, Userdb and authentication. Lets go in that order.

To enable sieve, edit /usr/local/etc/dovecot/conf.d/15-lda.conf and set mail_plugins to sieve.

protocol lda {
# Space separated list of plugins to load (default is global mail_plugins).
mail_plugins = sieve
}

Because we’re delivering mail as vmail, you may need to adjust the first_valid_uid and first_valid_gid settings in /usr/local/etc/dovecot/conf.d/10-mail.conf.

Set the value to whatever UID and GID your vmail user has, unless the ID is greater than 500, in which case the default is fine. Even better, set the last_valid_uid and last_valid_gid to your vmail UID and GID. That means only vmail can deliver mail, no other user.

first_valid_uid = 145
last_valid_uid = 145

first_valid_gid = 145
last_valid_gid = 145

To setup the authentication properly, open up /usr/local/etc/dovecot/conf.d/10-auth.conf, comment out the existing include for auth-system, and uncomment the auth-sql.conf.ext line, it should look like this:

#!include auth-system.conf.ext
!include auth-sql.conf.ext
#!include auth-ldap.conf.ext
#!include auth-passwdfile.conf.ext
#!include auth-checkpassword.conf.ext
#!include auth-vpopmail.conf.ext
#!include auth-static.conf.ext

Then we can edit /usr/local/etc/dovecot/conf.d/auth-sql.conf.ext to point to the correct SQL configuration file, this happens under passdb and userdb.

passdb {
driver = sql

# Path for SQL configuration file, see example-config/dovecot-sql.conf.ext
args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
}

userdb {
driver = sql
args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
}

Then we want to open up /usr/local/etc/dovecot/dovecot-sql.conf.ext and make it look like the following. I’ll step through it after the paste.

# This file is opened as root, so it should be owned by root and mode 0600.
#

# Database driver: mysql, pgsql, sqlite
driver = pgsql

# Database connection string. This is driver-specific setting.
connect = host=/tmp dbname=mail user=mail password=xxx

# Query to retrieve the password.
password_query = SELECT DISTINCT ON (mbox) mbox as user, password, '/var/vmail/' || mbox || '/home/' as home, 'maildir:/var/vmail/' || mbox || '/' as mail, 145 as uid, 145 as gid FROM COALESCE((SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE (mailboxes.mbox = '%u' AND mailboxes.disabled = false) OR (aliases.alias = '%u' AND mailboxes.disabled = false)), (SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE (aliases.alias = '@%d' AND mailboxes.disabled = false)), (SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE (aliases.alias = '%n@' AND mailboxes.disabled = false))) as mbox JOIN mailboxes USING (mbox);

# Query to retrieve the user information.
user_query = SELECT '/var/vmail/' || mbox || '/home/' as home, 'maildir:/var/vmail/' || mbox || '/' as mail, 145 as uid, 145 as gid FROM COALESCE((SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE mailboxes.mbox = '%u' OR aliases.alias = '%u'), (SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE aliases.alias = '@%d'), (SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE aliases.alias = '%n@')) as mbox;

driver = pgsql - Tells Dovecot that we want to connect to a PostgreSQL server
connect – The connection string. Set host to where your UNIX socket lives (usually /tmp), and dbname, user and password to the same credentials you used configuring Postfix in Post One. Alternatively, you can use different credentials, make sure you grant the appropriate permissions to the database.
password_query – This is the query that Dovecot will execute to retrieve the hashed password from the database. It then compares it to the hash it creates of the password given to it by the mail client to see if they match.
user_query – This query tells Dovecot where to find information on the users and mailboxes as configured.

These queries should look similar to the ones from Part One. Lets break them down a bit for better understanding.

SELECT
DISTINCT ON (mbox) mbox as user,
password,
'/var/vmail/' || mbox || '/home/' as home,
'maildir:/var/vmail/' || mbox || '/' as mail,
143 as uid,
143 as gid
FROM
COALESCE (
(SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE (mailboxes.mbox = '%u' AND mailboxes.disabled = false) OR (aliases.alias = '%u' AND mailboxes.disabled = false)),
(SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE (aliases.alias = '@%d' AND mailboxes.disabled = false)),
(SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE (aliases.alias = '%n@' AND mailboxes.disabled = false))
) as mbox
JOIN mailboxes USING (mbox);

SELECT

DISTINCT ON (mbox) mbox as user,

password,

'/var/vmail/' || mbox || '/home/' as home,

'maildir:/var/vmail/' || mbox || '/' as mail,

143 as uid,

143 as gid

FROM

COALESCE (

(SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE (mailboxes.mbox = '%u' AND mailboxes.disabled = false) OR (aliases.alias = '%u' AND mailboxes.disabled = false)),

(SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE (aliases.alias = '@%d' AND mailboxes.disabled = false)),

(SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE (aliases.alias = '%n@' AND mailboxes.disabled = false))

) as mbox

JOIN mailboxes USING (mbox);

So it executes three subqueries that are almost identical to the ones used by Postfix, except the substitutions are different this time around.

%u = the full email address being looked up
%d = only the domain (the bit after the @)
%n = only the username (the bit before the @)

It returns a lot more information though from the coalesced queries, and it checks if the email address in question is an alias too. Whereas Postfix was only looking for validation before handing it to dovecot-lda, Dovecot needs to know where and how to deliver the message to. The columns returned are as follows.

Column	Description
user	The email address of the user in question. Used as a username of sorts.
password	The hashed password.
home	The full path on the filesystem to the user’s home directory. In this case it is in a subfolder of their virtual mailbox: /var/vmail/email/home/. This is where the Sieve files live. The trailing slash is important.
mail	The full path on the filesystem to the user’s mailbox. In this case it is in a subfolder of their virtual mailbox: /var/vmail/email/. The trailing slash is important.
kid	The user ID of the local account dovecot should use when accessing the mailbox. This should be the ID vmail user we setup earlier, and you need to make sure it has read and write access to the folders above.
kid	The group ID of the local group dovecot should use when accessing the mailbox. This should be the ID vmail group we setup earlier.

Now that we have that out of the way, its time to test mail delivery! Oh, but we need a user account.

Creating User Accounts

There are two things you need to do to create a user account:

Add the user in the mailboxes table in the database.
Create the user’s mailbox on the filesystem.

To add the user, connect to your database using psql and run the following. Change <password> to the password you generated using doveadm pw -s ssha512.

INSERT INTO mailboxes (mbox, password) VALUES ('test@test.com', '{SSHA512}cZm4VMAkPzYdA+pRb0DpmQUyx9HT8JA8AmklE7V2TUK7L2Td+I3Z8P0phNO+i7fAwC82J9IC0rFQUcX2u2toFkJ+0IU=');

1	INSERT INTO mailboxes (mbox, password) VALUES ('test@test.com', '{SSHA512}cZm4VMAkPzYdA+pRb0DpmQUyx9HT8JA8AmklE7V2TUK7L2Td+I3Z8P0phNO+i7fAwC82J9IC0rFQUcX2u2toFkJ+0IU=');

Then create the directory on the filesystem to match.

[root@shana /]$ mkdir /var/vmail/test@test.com

[root@shana /]$ chown vmail:vmail /var/vmail/test\@test.com/

[root@shana /]$ mkdir /var/vmail/test@test.com

[root@shana /]$ chown vmail:vmail /var/vmail/test\@test.com/

Then we can test!

Testing the mail delivery

If you haven’t already, we’ll need to configure Dovecot to start, add this line to your /etc/rc.conf file.

dovecot_enable="YES"

Now lets start Postfix first.

[root@shana /]$ /usr/local/etc/rc.d/postfix start
postfix/postfix-script: starting the Postfix mail system

1 2	[root@shana /]$ /usr/local/etc/rc.d/postfix start postfix/postfix-script: starting the Postfix mail system

If everything went well there shouldn’t be any errors there, or any in /var/log/maillog.

Jul 5 22:49:05 shana postfix/postfix-script[86436]: starting the Postfix mail system
Jul 5 22:49:05 shana postfix/master[86437]: daemon started -- version 2.9.3, configuration /usr/local/etc/postfix

We don’t need to start Dovecot to test mail delivery as dovecot-lda is called automatically by Postfix. Lets connect and see what happens (the comments are server responses, the rest are commands I’m typing).

[bok@tyrande ~]$ telnet 124.168.107.116 25
# Trying 124.168.107.116...
# Connected to 124-168-107-116.dyn.iinet.net.au.
# Escape character is '^]'.
# 220 mail.odynia.org ESMTP Postfix
HELO tyrande.odynia.org
# 250 mail.odynia.org
MAIL FROM:
# 250 2.1.0 Ok
RCPT TO:
# 250 2.1.5 Ok
DATA
# 354 End data with .
test
.
# 250 2.0.0 Ok: queued as 26E051E3
QUIT
# 221 2.0.0 Bye
# Connection closed by foreign host.

[bok@tyrande ~]$ telnet 124.168.107.116 25

# Trying 124.168.107.116...

# Connected to 124-168-107-116.dyn.iinet.net.au.

# Escape character is '^]'.

# 220 mail.odynia.org ESMTP Postfix

HELO tyrande.odynia.org

# 250 mail.odynia.org

MAIL FROM:

# 250 2.1.0 Ok

RCPT TO:

# 250 2.1.5 Ok

DATA

# 354 End data with .

test

# 250 2.0.0 Ok: queued as 26E051E3

QUIT

# 221 2.0.0 Bye

# Connection closed by foreign host.

Accepted! Lets see where that went by checking /var/log/maillog.

Jul 5 23:44:03 shana postfix/smtpd[87478]: connect from tyrande.odynia.org[202.62.159.130]
Jul 5 23:44:23 shana postfix/policy-spf[87495]: : SPF softfail (Mechanism '~all' matched): Envelope-from: test@test.com
Jul 5 23:44:23 shana postfix/policy-spf[87495]: handler sender_policy_framework: is decisive.
Jul 5 23:44:23 shana postfix/policy-spf[87495]: : Policy action=PREPEND Received-SPF: softfail (test.com: Sender is not authorized by default to use 'test@test.com' in 'mfrom' identity, however domain is not currently prepared for false failures (mechanism '~all' matched)) receiver=unknown; identity=mailfrom; envelope-from="test@test.com"; helo=tyrande.odynia.org; client-ip=202.62.159.130
Jul 5 23:44:23 shana postfix/smtpd[87478]: 26E051E3: client=tyrande.odynia.org[202.62.159.130]
Jul 5 23:44:29 shana postfix/cleanup[87497]: 26E051E3: message-id=&lt;&gt;
Jul 5 23:44:29 shana postfix/qmgr[87474]: 26E051E3: from=, size=519, nrcpt=1 (queue active)
Jul 5 23:44:29 shana dovecot: lda: Error: userdb lookup: connect(/var/run/dovecot/auth-userdb) failed: No such file or directory
Jul 5 23:44:29 shana dovecot: lda: Fatal: Internal error occurred. Refer to server log for more information.
Jul 5 23:44:29 shana postfix/pipe[87498]: 26E051E3: to=, relay=dovecot, delay=10, delays=9.9/0.01/0/0.04, dsn=4.3.0, status=deferred (temporary failure)
Jul 5 23:44:35 shana postfix/smtpd[87478]: disconnect from tyrande.odynia.org[202.62.159.130]

Hmm, a temporary lookup failure trying to use the wrong userdb. Lets see what we missed.

Oh!

So when you’re using the SQL userdb and passdb setup you do need to have Dovecot running so it creates the authentication sockets that dovecot-lda needs. Lets start it up now then.

[root@shana /]$ /usr/local/etc/rc.d/dovecot start
Starting dovecot.
doveconf: Fatal: Error in configuration file /usr/local/etc/dovecot/conf.d/10-ssl.conf line 12: ssl_cert: Can't open file /etc/ssl/certs/dovecot.pem: No such file or directory
/usr/local/etc/rc.d/dovecot: WARNING: failed to start dovecot

[root@shana /]$ /usr/local/etc/rc.d/dovecot start

Starting dovecot.

doveconf: Fatal: Error in configuration file /usr/local/etc/dovecot/conf.d/10-ssl.conf line 12: ssl_cert: Can't open file /etc/ssl/certs/dovecot.pem: No such file or directory

/usr/local/etc/rc.d/dovecot: WARNING: failed to start dovecot

Uh oh! We missed a step in setting up Dovecot. We forgot to generate the SSL certificate that is configured by default in /usr/local/etc/dovecot/conf.d/10-ssl.conf. You have a few choices here.

Go buy a certificate from a trusted vendor and copy your public and private key files (PEM files) into /etc/ssl/certs/dovecot.pem and /etc/ssl/private/dovecot.pem respectively. This is the recommended and most secure option.
Generate a self-signed certificate using the instructions on the Dovecot Wiki. (Note that the files mentioned in doc/ are actually in /usr/local/share/examples/dovecot/ under FreeBSD).
Disable SSL (not recommended at all)

Once you’ve done one of those we can start Dovecot again.

[root@shana /]$ /usr/local/etc/rc.d/dovecot start
Starting dovecot.

1 2	[root@shana /]$ /usr/local/etc/rc.d/dovecot start Starting dovecot.

Better. Nothing in the logs?

Jul 6 00:08:01 shana dovecot: master: Dovecot v2.1.7 starting up

Right, lets try that delivery again, that previous message was still in the queue, so lets flush it.

[root@shana /]$ postqueue -f

1	[root@shana /]$ postqueue -f

So, was that successful?

Jul 6 00:05:54 shana postfix/qmgr[87474]: 26E051E3: from=, size=519, nrcpt=1 (queue active)
Jul 6 00:05:54 shana dovecot: auth: pgsql(/tmp): Connected to database mail
Jul 6 00:05:54 shana dovecot: lda(test@odynia.org): msgid=unspecified: saved mail to INBOX
Jul 6 00:05:54 shana postfix/pipe[87829]: 26E051E3: to=, relay=dovecot, delay=1295, delays=1295/0.01/0/0.1, dsn=2.0.0, status=sent (delivered via dovecot service)
Jul 6 00:05:54 shana postfix/qmgr[87474]: 26E051E3: removed

Yay! It was delivered to the folder, lets check the contents.

[root@shana /]$ cat /var/vmail/test\@odynia.org/new/1341497154.M243352P87831.shana.itransit.com.au\,S\=571\,W\=579
# Return-Path:
# Delivered-To: test@odynia.org
# Received-SPF: softfail (test.com: Sender is not authorized by default to use 'test@test.com' in 'mfrom' identity, however domain is not currently prepared for false failures (mechanism '~all' matched)) receiver=unknown; identity=mailfrom; envelope-from="test@test.com"; helo=tyrande.odynia.org; client-ip=202.62.159.130
# Received: from tyrande.odynia.org (tyrande.odynia.org [202.62.159.130])
# by mail.odynia.org (Postfix) with SMTP id 26E051E3
# for ; Thu, 5 Jul 2012 23:44:19 +1000 (EST)
#
# test

[root@shana /]$ cat /var/vmail/test\@odynia.org/new/1341497154.M243352P87831.shana.itransit.com.au\,S\=571\,W\=579

# Return-Path:

# Delivered-To: test@odynia.org

# Received-SPF: softfail (test.com: Sender is not authorized by default to use 'test@test.com' in 'mfrom' identity, however domain is not currently prepared for false failures (mechanism '~all' matched)) receiver=unknown; identity=mailfrom; envelope-from="test@test.com"; helo=tyrande.odynia.org; client-ip=202.62.159.130

# Received: from tyrande.odynia.org (tyrande.odynia.org [202.62.159.130])

# by mail.odynia.org (Postfix) with SMTP id 26E051E3

# for ; Thu, 5 Jul 2012 23:44:19 +1000 (EST)

# test

One complete message, and the softfail there shows SPF is working correctly also (test.com’s SPF record is set to softfail).

Configuring IMAP and POP3

All that is left now is configuring IMAP and POP3 access, including accessing both via SSL.

But wait! It is already working. You can configure your favourite mail client to connect via POP3 or IMAP and it will already be configured and working. There is nothing additional you need to do.

Jul 6 00:15:50 shana dovecot: imap-login: Login: user=, method=PLAIN, rip=172.16.0.127, lip=172.16.0.4, mpid=87945, TLS, session=
Jul 6 00:15:50 shana dovecot: imap(test@odynia.org): Connection closed in=17 out=352

I configured Mail on my Macbook to connect as test@odynia.org, and its already coming in over SSL for IMAP too. But what about sieve?

Configuring and Testing Sieve

Lets try a simple test, create a Folder in your Mail client and we’ll create a sieve file to redirect matching mail to that folder.

We’ll need to create the home folder inside the mailbox, like so:

[root@shana /]$ mkdir /var/vmail/test\@odynia.org/home
[root@shana /]$ chown vmail:vmail /var/vmail/test\@odynia.org/home

1 2	[root@shana /]$ mkdir /var/vmail/test\@odynia.org/home [root@shana /]$ chown vmail:vmail /var/vmail/test\@odynia.org/home

Then we can create a .dovecot.sieve file in there. So in this example it is /var/vmail/test@odynia.org/home/.dovecot.sieve. Make sure it is owned by vmail:vmail also.

require ["fileinto"];
if anyof (header :contains "Subject" "[test]")
{
fileinto "Test Folder";
stop;
}

So if we send an email again with a subject that includes “[test]” it should get redirected automatically to that folder.

[bok@tyrande ~]$ telnet 124.168.107.116 25
# Trying 124.168.107.116...
# Connected to 124-168-107-116.dyn.iinet.net.au.
# Escape character is '^]'.
# 220 mail.odynia.org ESMTP Postfix
HELO tyrande.odynia.org
# 250 mail.odynia.org
MAIL FROM:
# 250 2.1.0 Ok
RCPT TO:
# 250 2.1.5 Ok
DATA
# 354 End data with .
From: Me
To: You
Subject: [test] This is a test

Do you like my test?
.
# 250 2.0.0 Ok: queued as E3F212D0
QUIT
# 221 2.0.0 Bye
# Connection closed by foreign host.

[bok@tyrande ~]$ telnet 124.168.107.116 25

# Trying 124.168.107.116...

# Connected to 124-168-107-116.dyn.iinet.net.au.

# Escape character is '^]'.

# 220 mail.odynia.org ESMTP Postfix

HELO tyrande.odynia.org

# 250 mail.odynia.org

MAIL FROM:

# 250 2.1.0 Ok

RCPT TO:

# 250 2.1.5 Ok

DATA

# 354 End data with .

From: Me

To: You

Subject: [test] This is a test

Do you like my test?

# 250 2.0.0 Ok: queued as E3F212D0

QUIT

# 221 2.0.0 Bye

# Connection closed by foreign host.

Lets check the logs.

Jul 6 00:26:50 shana postfix/smtpd[87988]: connect from tyrande.odynia.org[202.62.159.130]
Jul 6 00:27:01 shana postfix/policy-spf[87994]: : SPF softfail (Mechanism '~all' matched): Envelope-from: test@test.com
Jul 6 00:27:01 shana postfix/policy-spf[87994]: handler sender_policy_framework: is decisive.
Jul 6 00:27:01 shana postfix/policy-spf[87994]: : Policy action=PREPEND Received-SPF: softfail (test.com: Sender is not authorized by default to use 'test@test.com' in 'mfrom' identity, however domain is not currently prepared for false failures (mechanism '~all' matched)) receiver=unknown; identity=mailfrom; envelope-from="test@test.com"; helo=tyrande.odynia.org; client-ip=202.62.159.130
Jul 6 00:27:01 shana postfix/smtpd[87988]: E3F212D0: client=tyrande.odynia.org[202.62.159.130]
Jul 6 00:27:17 shana postfix/cleanup[87996]: E3F212D0: message-id=&lt;&gt;
Jul 6 00:27:17 shana postfix/qmgr[87474]: E3F212D0: from=, size=586, nrcpt=1 (queue active)
Jul 6 00:27:17 shana dovecot: lda(test@odynia.org): sieve: msgid=unspecified: stored mail into mailbox 'Test Folder'
Jul 6 00:27:17 shana postfix/pipe[88010]: E3F212D0: to=, relay=dovecot, delay=19, delays=19/0.01/0/0.04, dsn=2.0.0, status=sent (delivered via dovecot service)
Jul 6 00:27:17 shana postfix/qmgr[87474]: E3F212D0: removed
Jul 6 00:27:19 shana postfix/smtpd[87988]: disconnect from tyrande.odynia.org[202.62.159.130]

Yep. Sieve also requires no additional setup.

I sure do like your test.

ManageSieve

Now, because of the length of this post I’m going to leave setting up ManageSieve to another day. I only use it for one service anyway, which is RoundCube webmail, so we’ll setup ManageSieve when we do RoundCube.

That’s it for another day. I hope this has been helpful.

Server Build dovecot, dovecot-lda, freebsd, imap, mail, pop3, postfix, server build, shana, sieve, smtp, SPF, ssl

Mail Server Setup: Part 1 – Postfix and PostgreSQL

Jul2nd
2012

Written by Rob

I have an uncommon setup for my mail server. In addition to standard Postfix and a bunch of anti-spam tools, I also use PostgreSQL for storing account information, including wildcard matching of aliases.

I’ve split this post into two so as not to write a novel. In this one I will run through installing and configuring Postfix to my liking, as well as hooking it up to PostgreSQL. In the second post I’ll cover setting Dovecot up in the same way for IMAP/POP3.

WARNING: Despite the split, this is still a very long post. You may need a cup of coffee.

Overview

But first, an overview of my setup, for which a picture tells many words.

This is highly simplified as there are a few extra services in there, such as ManageSieve. In a nutshell though; incoming mail is filtered first through SPF and a few other spam checks. Then the To: address is looked up in the database, in both the mailbox and alias tables, and the delivery mailbox address returned to Postfix. Postfix then delivers that mail into the correct directory. I’ll handle the rest of that picture in Post Two.

Aliasing

The aliasing system I use is slightly complex, but highly worthwhile. It allows me to use a couple of wildcard patterns to direct mail to a specific mailbox.

Direct matching: An email address is matched in full.
*@domain.com: A catch-all for a specific domain.
user@*: A specific user across all domains (useful for postmaster@)

This can be easily expanded upon by adjusting the SQL query as the need arises.

Resources

Before we get too far in it is a good idea to list some of the resources that I used to learn all of this in the first place.

Using the Dovecot LDA with Postfix on the Dovecot Wiki

I’ll add to this list when I do Part Two, as it has been so long since I set this up the first time, and a lot of it I just remember.

Installing

I installed PostgreSQL back in this post, so I won’t cover that again here. You can swap out PostgreSQL for MySQL if you desire, just change the options when we install Postfix.

Installing Postfix is very straightforward, just install the port. We want the following options.

PCRE (selected by default)
SASL2
DOVECOT2
TLS
PGSQL

For dependencies PCRE and SASL2, leave the default options selected. It will then prompt you to install Dovecot (as required by the Dovecot2 option above). Use the following options for Dovecot (covered in more detail in the next post).

KQUEUE (default)
SSL (default)
PGSQL

When asked if you want to activate Postfix in /etc/mail/mailer.conf, you say yes!

[root@shana /]$ cd /usr/ports/mail/postfix

[root@shana /usr/ports/mail/postfix]$ make install clean
# ===> Found saved configuration for postfix-2.9.3,1
# => postfix-2.9.3.tar.gz doesn't seem to exist in /usr/ports/distfiles/postfix.
# => Attempting to fetch ftp://ftp.porcupine.org/mirrors/postfix-release/official/postfix-2.9.3.tar.gz
# postfix-2.9.3.tar.gz 100% of 3672 kB 43 kBps 00m00s
# ===> Extracting for postfix-2.9.3,1
# ===> Found saved configuration for postfix-2.9.3,1
# => SHA256 Checksum OK for postfix/postfix-2.9.3.tar.gz.
# ===> postfix-2.9.3,1 depends on file: /usr/local/bin/perl5.12.4 - found
# ===> Patching for postfix-2.9.3,1
# ===> postfix-2.9.3,1 depends on file: /usr/local/bin/perl5.12.4 - found
# ===> Applying FreeBSD patches for postfix-2.9.3,1
# ===> postfix-2.9.3,1 depends on file: /usr/local/bin/perl5.12.4 - found
# ===> postfix-2.9.3,1 depends on shared library: pcre - not found
# ===> Verifying install for pcre in /usr/ports/devel/pcre
#
# ===> Registering installation for pcre-8.30_2
# ===> Returning to build of postfix-2.9.3,1
# ===> postfix-2.9.3,1 depends on shared library: sasl2.2 - not found
# ===> Verifying install for sasl2.2 in /usr/ports/security/cyrus-sasl2
#
# ===> Registering installation for cyrus-sasl-2.1.25_2
# ===> Returning to build of postfix-2.9.3,1
# ===> postfix-2.9.3,1 depends on shared library: pq.5 - found
# ===> Configuring for postfix-2.9.3,1
#
# ===> Installing rc.d startup script(s)
# Would you like to activate Postfix in /etc/mail/mailer.conf [n]? y
# # Fix compressed man pages
# To enable postfix startup script please add postfix_enable="YES" in
# your rc.conf
#
# If you not need sendmail anymore, please add in your rc.conf:
#
# sendmail_enable="NO"
# sendmail_submit_enable="NO"
# sendmail_outbound_enable="NO"
# sendmail_msp_queue_enable="NO"
#
# And you can disable some sendmail specific daily maintenance routines in your
# /etc/periodic.conf file:
#
# daily_clean_hoststat_enable="NO"
# daily_status_mail_rejects_enable="NO"
# daily_status_include_submit_mailq="NO"
# daily_submit_queuerun="NO"
#
# If /etc/periodic.conf does not exist please create it and add those values.
#
# If you are using SASL, you need to make sure that postfix has access to read
# the sasldb file. This is accomplished by adding postfix to group mail and
# making the /usr/local/etc/sasldb* file(s) readable by group mail (this should
# be the default for new installs).
#
# If you are upgrading from Postfix 2.6 or earlier, review the RELEASE_NOTES to
# familiarize yourself with new features and incompatabilities.
# ===> Correct pkg-plist sequence to create group(s) and user(s)
# ===> Compressing manual pages for postfix-2.9.3,1
# ===> Registering installation for postfix-2.9.3,1
# ===> SECURITY REPORT:
#
# ===> Cleaning for pcre-8.30_2
# ===> Cleaning for cyrus-sasl-2.1.25_2
# ===> Cleaning for dovecot-2.1.7
# ===> Cleaning for postfix-2.9.3,1

[root@shana /]$ cd /usr/ports/mail/postfix

[root@shana /usr/ports/mail/postfix]$ make install clean

# ===> Found saved configuration for postfix-2.9.3,1

# => postfix-2.9.3.tar.gz doesn't seem to exist in /usr/ports/distfiles/postfix.

# => Attempting to fetch ftp://ftp.porcupine.org/mirrors/postfix-release/official/postfix-2.9.3.tar.gz

# postfix-2.9.3.tar.gz 100% of 3672 kB 43 kBps 00m00s

# ===> Extracting for postfix-2.9.3,1

# ===> Found saved configuration for postfix-2.9.3,1

# => SHA256 Checksum OK for postfix/postfix-2.9.3.tar.gz.

# ===> postfix-2.9.3,1 depends on file: /usr/local/bin/perl5.12.4 - found

# ===> Patching for postfix-2.9.3,1

# ===> postfix-2.9.3,1 depends on file: /usr/local/bin/perl5.12.4 - found

# ===> Applying FreeBSD patches for postfix-2.9.3,1

# ===> postfix-2.9.3,1 depends on file: /usr/local/bin/perl5.12.4 - found

# ===> postfix-2.9.3,1 depends on shared library: pcre - not found

# ===> Verifying install for pcre in /usr/ports/devel/pcre

# ===> Registering installation for pcre-8.30_2

# ===> Returning to build of postfix-2.9.3,1

# ===> postfix-2.9.3,1 depends on shared library: sasl2.2 - not found

# ===> Verifying install for sasl2.2 in /usr/ports/security/cyrus-sasl2

# ===> Registering installation for cyrus-sasl-2.1.25_2

# ===> Returning to build of postfix-2.9.3,1

# ===> postfix-2.9.3,1 depends on shared library: pq.5 - found

# ===> Configuring for postfix-2.9.3,1

# ===> Installing rc.d startup script(s)

# Would you like to activate Postfix in /etc/mail/mailer.conf [n]? y

# # Fix compressed man pages

# To enable postfix startup script please add postfix_enable="YES" in

# your rc.conf

# If you not need sendmail anymore, please add in your rc.conf:

# sendmail_enable="NO"

# sendmail_submit_enable="NO"

# sendmail_outbound_enable="NO"

# sendmail_msp_queue_enable="NO"

# And you can disable some sendmail specific daily maintenance routines in your

# /etc/periodic.conf file:

# daily_clean_hoststat_enable="NO"

# daily_status_mail_rejects_enable="NO"

# daily_status_include_submit_mailq="NO"

# daily_submit_queuerun="NO"

# If /etc/periodic.conf does not exist please create it and add those values.

# If you are using SASL, you need to make sure that postfix has access to read

# the sasldb file. This is accomplished by adding postfix to group mail and

# making the /usr/local/etc/sasldb* file(s) readable by group mail (this should

# be the default for new installs).

# If you are upgrading from Postfix 2.6 or earlier, review the RELEASE_NOTES to

# familiarize yourself with new features and incompatabilities.

# ===> Correct pkg-plist sequence to create group(s) and user(s)

# ===> Compressing manual pages for postfix-2.9.3,1

# ===> Registering installation for postfix-2.9.3,1

# ===> SECURITY REPORT:

# ===> Cleaning for pcre-8.30_2

# ===> Cleaning for cyrus-sasl-2.1.25_2

# ===> Cleaning for dovecot-2.1.7

# ===> Cleaning for postfix-2.9.3,1

The hints and tips in the message at the end of the port install are useful, you should read them. Some of which I’ll repeat shortly.

Once that is done we should install postfix-policyd-spf, which handles the SPF lookups. I use the Perl client found in ports.

[root@shana /]$ cd /usr/ports/mail/postfix-policyd-spf-perl

[root@shana /usr/ports/mail/postfix-policyd-spf-perl]$ make install clean
# => postfix-policyd-spf-perl-2.007.tar.gz doesn't seem to exist in /usr/ports/distfiles/.
# => Attempting to fetch http://www.openspf.org/blobs/postfix-policyd-spf-perl-2.007.tar.gz
# postfix-policyd-spf-perl-2.007.tar.gz 100% of 13 kB 17 kBps
# ===> Extracting for postfix-policyd-spf-perl-2.007
#
# ===> Checking if mail/postfix-policyd-spf-perl already installed
# The service is not enabled by default. Enable it by doing the following:
#
# 1. Add the following to /etc/postfix/master.cf:
#
# spf-policy unix - n n - 0 spawn
# user=nobody argv=/usr/local/sbin/postfix-policyd-spf-perl
#
# The user nobody is fine if you have no other daemons running as nobody.
# Otherwise, you should use a dedicated user and group for this policy
# service.
#
# 2. Add "spf-policy_time_limit = 3600" to main.cf.
#
# 3. Configure the Postfix policy service in /usr/local/etc/postfix/main.cf:
#
# smtpd_recipient_restrictions =
# ...
# reject_unauth_destination
# ...
# check_policy_service unix:private/spf-policy
# ...
#
# NOTE: Specify check_policy_service AFTER reject_unauth_destination or your
# system may become an open relay.
#
# 4. Restart Postfix.
# ===> Registering installation for postfix-policyd-spf-perl-2.007

[root@shana /]$ cd /usr/ports/mail/postfix-policyd-spf-perl

[root@shana /usr/ports/mail/postfix-policyd-spf-perl]$ make install clean

# => postfix-policyd-spf-perl-2.007.tar.gz doesn't seem to exist in /usr/ports/distfiles/.

# => Attempting to fetch http://www.openspf.org/blobs/postfix-policyd-spf-perl-2.007.tar.gz

# postfix-policyd-spf-perl-2.007.tar.gz 100% of 13 kB 17 kBps

# ===> Extracting for postfix-policyd-spf-perl-2.007

# ===> Checking if mail/postfix-policyd-spf-perl already installed

# The service is not enabled by default. Enable it by doing the following:

# 1. Add the following to /etc/postfix/master.cf:

# spf-policy unix - n n - 0 spawn

# user=nobody argv=/usr/local/sbin/postfix-policyd-spf-perl

# The user nobody is fine if you have no other daemons running as nobody.

# Otherwise, you should use a dedicated user and group for this policy

# service.

# 2. Add "spf-policy_time_limit = 3600" to main.cf.

# 3. Configure the Postfix policy service in /usr/local/etc/postfix/main.cf:

# smtpd_recipient_restrictions =

# ...

# reject_unauth_destination

# ...

# check_policy_service unix:private/spf-policy

# ...

# NOTE: Specify check_policy_service AFTER reject_unauth_destination or your

# system may become an open relay.

# 4. Restart Postfix.

# ===> Registering installation for postfix-policyd-spf-perl-2.007

Configuration

Enabling Postfix / Disabling Sendmail

To enable Postfix, add the following to your /etc/rc.conf file. It won’t start though until we tell it to, or you reboot your box.

postfix_enable="YES"

We should disable all of the sendmail stuff too that it tells us in /etc/rc.conf as we won’t need it anymore.

sendmail_enable="NO"
sendmail_submit_enable="NO"
sendmail_outbound_enable="NO"
sendmail_msp_queue_enable="NO"

And remove the sendmail maintenance routines from /etc/periodic.conf (create it if it doesn’t exist).

daily_clean_hoststat_enable="NO"
daily_status_mail_rejects_enable="NO"
daily_status_include_submit_mailq="NO"
daily_submit_queuerun="NO"

General Postfix Configuration

We want to change some general settings now for Postfix, particularly those that tell it server names, IP Addresses, ports and which domains to receive for. We’ll step through these.

Note: every setting that is not mentioned should be left with its default values.

Start by turning on soft_bounce, you don’t want to lose any email while we’re checking.

soft_bounce = yes

Set myhostname to what you want your server to appear as in mail logs. I prefer that my server splash her name every where, so as far as other email servers are concerned she is mail.odynia.org.

myhostname = mail.odynia.org

You can leave the mydomain one alone, the default is based on your myhostname. In my case the default mydomain would be odynia.org.

The myorigin option is the domain that is used for outgoing emails from locally posted emails, such as bounce messages and emails from root, etc. The default is to use myhostname, but I feel having mail come from @mail.odynia.org to be a bit off putting. I set it to mydomain.

myorigin = $mydomain

There is a lot of text there about my destination, but I prefer to set it to myhostname. That makes it the only domain that would receive mail should things go sour, which is perfect. All other domains will be virtual.

mydestination = $myhostname

Likewise, empty out the local_recipient_maps, recipient maps are stored in PostgreSQL as virtual mailboxes.

local_recipient_maps =

Set up your mynetworks to be a list of the IP addresses on your machine (localhost, internal and external). This way we don’t trust any other mail server, or anyone pretending to be a mail server.

mynetworks = 127.0.0.0/8, x.x.x.x/32

This is an important one. Here’s a healthy tip from me: don’t try to be an outgoing email server. Set relayhost to your ISP’s SMTP server and save yourself the headache. The most time consuming and hardest part is keeping your email server off blacklists and other fun anti-spam measures, despite the fact that you’re sending legitimate email. It’s just not worth it.

relayhost = smtp.yourisp.com

Virtual Domains and Dovecot

The mailbox_command option is where we specify Dovecot as the local delivery agent (LDA).

mailbox_command = /usr/local/libexec/dovecot/deliver

I also set the dovecot_destination_recipient_limit to 1 in order to allow a Delivered-To header to be added to my messages. When you have almost unlimited email addresses and a spammer doesn’t put you in the To header it is helpful to know which email address it was delivered to.

dovecot_destination_recipient_limit = 1

Now to configure our virtual domains. First set your virtual_mailbox_domains to a list of domains (comma separated) that you want to receive mail for.

virtual_mailbox_domains = domain.com, somedomain.com, someotherdomain.com

And set the virtual_mailbox_maps to look in a PostgreSQL configuration file. This is the key bit and the contents of this file is discussed further down in PostgreSQL Configuration. The file will be stored in /usr/local/etc/postfix/pgsql.cf.

virtual_mailbox_maps = proxy:pgsql:$config_directory/pgsql.cf

Then set your virtual_transport to dovecot.

virtual_transport = dovecot

To actually use the dovecot transport though, you will need to add the following line to your master process configuration file /usr/local/etc/postfix/master.cf.

dovecot unix - n n - - pipe flags=DRhu user=vmail:vmail argv=/usr/local/libexec/dovecot/deliver -f ${sender} -d ${recipient}

In greater detail:

flags=DRhu: The flags to pass to dovecot. The D flag is for setting the Delivered-To header, but i have no idea what the others are. Every resource says you need to set those flags though.
user=vmail:vmail: The user and group to run the dovecot-lda as. I use vmail as per the examples, but you will need to create the user account and group manually. This is covered more in Part Two.
argv=/usr/local/libexec/dovecot/deliver -f ${sender} -d ${recipient}: The command line to execute dovecot-lda. The ${sender} and ${recipient} are replaced by the sender and recipient of the email being delivered. The email body is passed to dovecot-lda on STDIN.

And so ends this portion of the Dovecot setup. The rest is covered in Part Two. It is important to note that you will not be able to receive email until the Dovecot setup has been completed.

Security and Anti-Spam

There are some basic things you should do to beef up your Postfix server’s security, even just that little bit, back over in /usr/local/etc/postfix/main.cf.

Set your smtpd_helo_required to yes. It is a basic thing but some lazy spammers don’t implement HELO in their mail clients. All “real” mail clients should implement it correctly.

smtpd_helo_required = yes

And now for the recipient restrictions. You can configure any number of restrictions about who can deliver mail to your server, these are a few of the more common ones, detailed below.

smtpd_recipient_restrictions =
reject_non_fqdn_sender,
reject_non_fqdn_recipient,
reject_unknown_sender_domain,
reject_unknown_recipient_domain,
reject_unauth_destination,
reject_unauth_pipelining,
reject_rbl_client bl.spamcop.net,
reject_rbl_client cbl.abuseat.org,
reject_unauth_destination,
check_client_access hash:$config_directory/proct,
check_policy_service unix:private/policyd

reject_non_fqdn_sender: Reject when the sender is not from a fully qualified domain (ie. someuser@fakedomain as opposed to someuser@fakedomain.com)
reject_non_fqdn_recipient: Same again but for the recipient
reject_unknown_sender_domain: This does a DNS lookup to verify that the domain is a real domain.
reject_unknown_recipient_domain: Same again but for the recipient.
reject_unauth_destination: Reject emails sent to domains that Postfix is not configured to receive mail for. We make sure this is here to prevent our server becoming an open relay.
reject_unauth_pipelining: Pipelining is a way of sending all the SMTP commands to the server without waiting for each response, which speeds up delivery. It is also commonly implemented by bulk senders, so this prevents poorly built mail clients from assuming pipelining is enabled instead of asking first.
reject_rbl_client <server>: This rejects emails by looking them up in a Relay Block List (RBL), a common, if antiquated, anti-spam technique.
check_policy_service unix:private/policyd: This is the key to our SPF setup. It checks all emails against the SPF Policyd service, which is configured below.

Now that we have our SPF setup in the smtpd_recipient_restrictions we need to configure the policy service. To do so you need to add the following to your master process configuration file, or /usr/local/etc/postfix/master.cf for short.

policyd unix - n n - - spawn user=nobody argv=/usr/local/sbin/postfix-policyd-spf-perl

This will spawn a new instance of the postfix-policyd-spf-perl binary for each SPF lookup, running as the user nobody. And thats all it takes.

Running as a Backup Mail Exchange

I also want Shana to operate as a backup mail server for some domains that are hosted on Google Apps or Office 365. To do so you need to add the following to your /usr/local/etc/postfix/main.cf:

transport_maps = hash:$config_directory/transport

This will check /usr/local/etc/postfix/transport for domains we should use an alternate delivery transport for. To add a domain, we first need to add it to virtual_mailbox_domains or some other list of domains in /usr/local/etc/postfix/main.cf, otherwise Postfix will reject the email.

Then in your /usr/local/etc/postfix/transport file:

somedomain.com relay:mx.server.name
* :

Make sure you keep the last line, which means all other domains should still be delivered locally. Then run postmap to create the database file that Postfix uses (/usr/local/etc/postfix/transport.db).

[root@shana /usr/local/etc/postfix]$ postmap /usr/local/etc/postfix/transport

1	[root@shana /usr/local/etc/postfix]$ postmap /usr/local/etc/postfix/transport

PostgreSQL Configuration

Now that we have Postfix all configured, we just need to setup our database and write the query that will look up the recipient in the database. The database design itself is very simple, a simple of mailboxes and a table of aliases.

In this case, we need to create a mail database, and a mail PostgreSQL user (with a password!):

[root@shana /]$ sudo -u pgsql -s

[pgsql@shana /]$ createdb mail

[pgsql@shana /]$ createuser -P mail
# Enter password for new role:
# Enter it again:
# Shall the new role be a superuser? (y/n) n
# Shall the new role be allowed to create databases? (y/n) n
# Shall the new role be allowed to create more new roles? (y/n) n

[root@shana /]$ sudo -u pgsql -s

[pgsql@shana /]$ createdb mail

[pgsql@shana /]$ createuser -P mail

# Enter password for new role:

# Enter it again:

# Shall the new role be a superuser? (y/n) n

# Shall the new role be allowed to create databases? (y/n) n

# Shall the new role be allowed to create more new roles? (y/n) n

Mailboxes Table

The Mailboxes Table has four columns:

Column	Type	Description
mbox	text	The mailbox name. Typically an email address. A mailbox needs to be created on the the system with this name. Primary Key.
password	text	The password for this mailbox. Use doveadm pw -s ssha256 to generate the passwords. Leaving a password blank will prevent that user from logging in.
quota	integer	The quota or maximum mailbox size in megabytes.
disabled	boolean	Whether this mailbox is disabled or not.

You can use the following SQL to create the table:

CREATE TABLE mailboxes (
"mbox" text not null primary key,
"password" text not null,
"quota" integer default 0,
"disabled" boolean not null default false
);

CREATE TABLE mailboxes (

"mbox" text not null primary key,

"password" text not null,

"quota" integer default 0,

"disabled" boolean not null default false

);

Then populate it with whatever you need to:

INSERT INTO mailboxes (mbox, password) VALUES ('someuser@somedomain.com', '{SSHA256}tUSmEcHBXAoyLpbwGUMKenMgguavaBS+sDbk2wIL+fdQ7wWj');

1	INSERT INTO mailboxes (mbox, password) VALUES ('someuser@somedomain.com', '{SSHA256}tUSmEcHBXAoyLpbwGUMKenMgguavaBS+sDbk2wIL+fdQ7wWj');

Aliases Table

The Aliases Table is a way of creating an alias for a mailbox. Both Postfix and Dovecot will treat an alias as if it really was the mailbox. That means you can login to Dovecot as an alias, should the need arise.

The Aliases Table has two columns:

Column	Type	Description
alias	text	The alias. Should be an email address or a wildcard in the form of @domain.com, or user@. Primary Key
mbox	text	The mailbox name. Should reference a row in the Mailboxes table.

You can use the following SQL to create the table:

CREATE TABLE aliases (
"alias" text not null primary key,
"mbox" text not null references mailboxes (mbox)
);

CREATE TABLE aliases (

"alias" text not null primary key,

"mbox" text not null references mailboxes (mbox)

);

Then populate it with whatever you need to:

-- Aliasing one email address to another
INSERT INTO aliases (alias, mbox) VALUES ('anotheruser@somedomain.com', 'someuser@somedomain.com');

-- Catch all for a specific domain
INSERT INTO aliases (alias, mbox) VALUES ('@xxxdomain.com', 'someuser@somedomain.com');

-- Catch all for a user at all domains
INSERT INTO aliases (alias, mbox) VALUES ('postmaster@', 'someuser@somedomain.com');

-- Aliasing one email address to another

INSERT INTO aliases (alias, mbox) VALUES ('anotheruser@somedomain.com', 'someuser@somedomain.com');

-- Catch all for a specific domain

INSERT INTO aliases (alias, mbox) VALUES ('@xxxdomain.com', 'someuser@somedomain.com');

-- Catch all for a user at all domains

INSERT INTO aliases (alias, mbox) VALUES ('postmaster@', 'someuser@somedomain.com');

Configuring the Postfix -> PostgreSQL Mapping

Back up a bit under Virtual Domains and Dovecot we setup our virtual_mailbox_maps to proxy:pgsql:$config_directory/pgsql.cf. This tells Postfix to lookup virtual mailbox maps (mapping an email address to a virtual mailbox) using the PostgreSQL proxy.

Once you’ve created your database we need to create the pgsql.cf file to match. Open up /usr/local/etc/postfix/pgsql.cf and add the following:

hosts = /tmp
user = mail
dbname = mail
password = somepassword

query = SELECT DISTINCT ON (mbox) mbox as userid FROM COALESCE((SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE (mailboxes.mbox = '%s' OR aliases.alias = '%s') AND mailboxes.disabled = false), (SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE aliases.alias = '@%d' AND mailboxes.disabled = false), (SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE aliases.alias = '%u@' AND mailboxes.disabled = false)) as mbox;

The hosts option it set to /tmp so that it uses the default PostgreSQL unix socket, as opposed to a TCP socket. Set your dbname, user and password to match the database you created earlier.

The query is the interesting bit of all of this, lets reformat it and break it down.

SELECT
DISTINCT ON (mbox) mbox as userid
FROM
COALESCE
(
(SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE (mailboxes.mbox = '%s' OR aliases.alias = '%s') AND mailboxes.disabled = false),
(SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE aliases.alias = '@%d' AND mailboxes.disabled = false),
(SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE aliases.alias = '%u@' AND mailboxes.disabled = false)
) as mbox;

SELECT

DISTINCT ON (mbox) mbox as userid

FROM

COALESCE

(

(SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE (mailboxes.mbox = '%s' OR aliases.alias = '%s') AND mailboxes.disabled = false),

(SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE aliases.alias = '@%d' AND mailboxes.disabled = false),

(SELECT DISTINCT ON (mbox) mbox FROM mailboxes LEFT OUTER JOIN aliases USING (mbox) WHERE aliases.alias = '%u@' AND mailboxes.disabled = false)

) as mbox;

Hopefully that will make it a little clearer, apologies for the scroll. It basically runs three subqueries for the different types of lookups and returns a unique mbox from the first subquery that returns results.

Query Details

Lets look at each of those subqueries in a bit more detail.

SELECT
DISTINCT ON (mbox) mbox
FROM
mailboxes LEFT OUTER JOIN aliases USING (mbox)
WHERE
(mailboxes.mbox = '%s' OR aliases.alias = '%s') AND mailboxes.disabled = false

SELECT

DISTINCT ON (mbox) mbox

FROM

mailboxes LEFT OUTER JOIN aliases USING (mbox)

WHERE

(mailboxes.mbox = '%s' OR aliases.alias = '%s') AND mailboxes.disabled = false

This will look in the mbox column of the mailboxes table and the alias columns of the aliases table for “%s”, which PostgreSQL will expand to be the full email address of the recipient.

SELECT
DISTINCT ON (mbox) mbox
FROM
mailboxes LEFT OUTER JOIN aliases USING (mbox)
WHERE
aliases.alias = '@%d' AND mailboxes.disabled = false

SELECT

DISTINCT ON (mbox) mbox

FROM

mailboxes LEFT OUTER JOIN aliases USING (mbox)

WHERE

aliases.alias = '@%d' AND mailboxes.disabled = false

This will look in the alias column of the alias table for “@%d”, or the @ symbol followed by the domain name only portion of the recipient’s email address.

SELECT
DISTINCT ON (mbox) mbox
FROM
mailboxes LEFT OUTER JOIN aliases USING (mbox)
WHERE
aliases.alias = '%u@' AND mailboxes.disabled = false

SELECT

DISTINCT ON (mbox) mbox

FROM

mailboxes LEFT OUTER JOIN aliases USING (mbox)

WHERE

aliases.alias = '%u@' AND mailboxes.disabled = false

This will look in the alias column of the alias table for “%u@”, or the username only portion of the recipient’s email address.

The End For Now

And thats it! A fully configured Postfix setup with PostgreSQL lookups. If you stayed with me through this absurdly long post, thanks!

It is important to know though before you leave here that you can’t yet receive email until the Dovecot setup has been completed. For that, you will need to read Part Two, which is coming tomorrow.

Server Build dovecot, freebsd, postfix, server build, shana, SPF

Server Build: PostgreSQL Installation and SSD Benchmarking

Jun27th
2012

Written by Rob

The guts of this post was written back in November 2011. It covers installing and configuring PostgreSQL for FreeBSD, and then runs through some benchmarks to evaluate how best to use the SSD.

Installing PostgreSQL 9.1

The installation process is as simple as ever, just build the port:

[root@shana /home]$ cd /usr/ports/databases/postgresql91-server/
[root@shana /usr/ports/databases/postgresql91-server]$ make install clean
#
# ======================================================================
#
# To initialize the database, run
#
# /usr/local/etc/rc.d/postgresql initdb
#
# You can then start PostgreSQL by running:
#
# /usr/local/etc/rc.d/postgresql start
#
# For postmaster settings, see ~pgsql/data/postgresql.conf
#
# NB. FreeBSD's PostgreSQL port logs to syslog by default
# See ~pgsql/data/postgresql.conf for more info
#
# ======================================================================
#
# To run PostgreSQL at startup, add
# 'postgresql_enable="YES"' to /etc/rc.conf
#
# ======================================================================
# ===&gt; Installing rc.d startup script(s)
# ===&gt; Correct pkg-plist sequence to create group(s) and user(s)
# ===&gt; Registering installation for postgresql-server-9.1.1_1
# ===&gt; SECURITY REPORT:
# This port has installed the following files which may act as network
# servers and may therefore pose a remote security risk to the system.
# /usr/local/bin/postgres
#
# This port has installed the following startup scripts which may cause
# these network services to be started at boot time.
# /usr/local/etc/rc.d/postgresql
#
# If there are vulnerabilities in these programs there may be a security
# risk to the system. FreeBSD makes no guarantee about the security of
# ports included in the Ports Collection. Please type 'make deinstall'
# to deinstall the port if this is a concern.
#
# For more information, and contact details about the security
# status of this software, see the following webpage:
# http://www.postgresql.org/
# ===&gt; Cleaning for libxml2-2.7.8_1
# ===&gt; Cleaning for postgresql-client-9.1.1_1
# ===&gt; Cleaning for postgresql-server-9.1.1_1

[root@shana /home]$ cd /usr/ports/databases/postgresql91-server/

[root@shana /usr/ports/databases/postgresql91-server]$ make install clean

# ======================================================================

# To initialize the database, run

# /usr/local/etc/rc.d/postgresql initdb

# You can then start PostgreSQL by running:

# /usr/local/etc/rc.d/postgresql start

# For postmaster settings, see ~pgsql/data/postgresql.conf

# NB. FreeBSD's PostgreSQL port logs to syslog by default

# See ~pgsql/data/postgresql.conf for more info

# ======================================================================

# To run PostgreSQL at startup, add

# 'postgresql_enable="YES"' to /etc/rc.conf

# ======================================================================

# ===> Installing rc.d startup script(s)

# ===> Correct pkg-plist sequence to create group(s) and user(s)

# ===> Registering installation for postgresql-server-9.1.1_1

# ===> SECURITY REPORT:

# This port has installed the following files which may act as network

# servers and may therefore pose a remote security risk to the system.

# /usr/local/bin/postgres

# This port has installed the following startup scripts which may cause

# these network services to be started at boot time.

# /usr/local/etc/rc.d/postgresql

# If there are vulnerabilities in these programs there may be a security

# risk to the system. FreeBSD makes no guarantee about the security of

# ports included in the Ports Collection. Please type 'make deinstall'

# to deinstall the port if this is a concern.

# For more information, and contact details about the security

# status of this software, see the following webpage:

# http://www.postgresql.org/

# ===> Cleaning for libxml2-2.7.8_1

# ===> Cleaning for postgresql-client-9.1.1_1

# ===> Cleaning for postgresql-server-9.1.1_1

I’m installing the postgresql-contrib package also, mainly for benchmarking using pgbench below.

[root@shana /usr/ports/databases]$ cd postgresql91-contrib/
[root@shana /usr/ports/databases/postgresql91-contrib]$ make install clean

1 2	[root@shana /usr/ports/databases]$ cd postgresql91-contrib/ [root@shana /usr/ports/databases/postgresql91-contrib]$ make install clean

Configuring and Starting PostgreSQL

To enable PostgreSQL to start, just add the following lines to your /etc/rc.conf.

postgresql_enable="YES"
postgresql_data="/path/to/your/db"

then to start it.

[root@shana /]$ /usr/local/etc/rc.d/postgresql initdb
[root@shana /]$ /usr/local/etc/rc.d/postgresql start

1 2	[root@shana /]$ /usr/local/etc/rc.d/postgresql initdb [root@shana /]$ /usr/local/etc/rc.d/postgresql start

Benchmarking

I’m using pgbench for the benchmarking process.

Setup

The process of setting up the directories and configuring the PostgreSQL for each benchmark is:

Stop any running instances of PostgreSQL.

Shell

$ /usr/local/etc/rc.d/postgresql stop

1

$ /usr/local/etc/rc.d/postgresql stop
Create the target directory that you would like to store the database in, make sure you set owner of that directory correctly.

Shell

[root@shana /]$ mkdir /pgdbtmp [root@shana /]$ chown pgsql:pgsql /pgdbtmp

1
2

[root@shana /]$ mkdir /pgdbtmp
[root@shana /]$ chown pgsql:pgsql /pgdbtmp
Update the /etc/rc.conf file to set the data directory.
```
postgresql_data="/pgdbtmp"
```
Now init the database and start PostgreSQL again.

Shell

[root@shana /]$ /usr/local/etc/rc.d/postgresql initdb [root@shana /]$ /usr/local/etc/rc.d/postgresql start

1
2

[root@shana /]$ /usr/local/etc/rc.d/postgresql initdb
[root@shana /]$ /usr/local/etc/rc.d/postgresql start

The process of running pgbench is pretty straight forward. Its best to run it as the PostgreSQL superuser.

Switch to the PostgreSQL superuser account pgsql.

Shell

[root@shana /]$ sudo -u pgsql -s

1

[root@shana /]$ sudo -u pgsql -s
Create the pgbench database. It doesn’t have to be called pgbench, you can call it anything.

Shell

[pgsql@shana /]$ createdb pgbench

1

[pgsql@shana /]$ createdb pgbench
Initialise the pgbench database, this populates the data and schema for testing.

Shell

[pgsql@shana /]$ pgbench -i pgbench

1

[pgsql@shana /]$ pgbench -i pgbench

Run pgbench.

[pgsql@shana /]$ pgbench -c 10 -s 10 -t 10000 pgbench
# Scale option ignored, using pgbench_branches table count = 1
# starting vacuum...end.
# transaction type: TPC-B (sort of)
# scaling factor: 1
# query mode: simple
# number of clients: 10
# number of threads: 1
# number of transactions per client: 10000
# number of transactions actually processed: 100000/100000
# tps = 1469.204180 (including connections establishing)
# tps = 1470.132408 (excluding connections establishing)

[pgsql@shana /]$ pgbench -c 10 -s 10 -t 10000 pgbench

# Scale option ignored, using pgbench_branches table count = 1

# starting vacuum...end.

# transaction type: TPC-B (sort of)

# scaling factor: 1

# query mode: simple

# number of clients: 10

# number of threads: 1

# number of transactions per client: 10000

# number of transactions actually processed: 100000/100000

# tps = 1469.204180 (including connections establishing)

# tps = 1470.132408 (excluding connections establishing)

For the purposes of these benchmarks, I’ll keep it simple and run 10 concurrent clients (-c 10), and 10,000 transactions for each client (-t 10000) which is a total of 100,000 transactions. The tps value at the end is what we’re interested in. We’ll also exclude the connection overhead, its transaction execution that we’re interested in.

Setups to be tested

We’re after a few different configuration options to test here, based on the recommendations of some websites. A ZFS record size of 8k is recommended by other PostgreSQL SSD optimisation posts, as is setting the primarycache to metadata. I used these slides as part of my research for this. The configurations are:

Mirrored HDDs (zroot) with no ZFS optimisations (this is our baseline)
Mirrored HDDs (zroot) with ZFS recordsize=8k, primarycache=metadata
Mirrored HDDs with SSD ZIL and L2ARC cache (zapps) with no ZFS optimisations
Mirrored HDDs with SSD ZIL and L2ARC cache (zapps) with ZFS recordsize=8k, primarycache=metadata
Mirrored SSDs (zflash) with no ZFS optimisations
Mirrored SSDs (zflash) with ZFS recordsize=8k, primarycache=metadata

These tests were all completed with the data and transaction logs on the same volume. Lets try offloading the transaction logs to the SSD as well. In all three of these the transaction logs are on zflash.

You can move the transaction logs to a different directory by using the postgresql_initdb_flags option in /etc/rc.conf like so:

postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C -X /pglogtmp"

Mirrored HDDs (zroot) with no ZFS optimisations.
Mirrored HDDs (zroot) with ZFS recordsize=8k, primarycache=metadata.
Mirrored HDDs with SSD ZIL and L2ARC cache (zapps) and no ZFS optimisations
Mirrored HDDs with SSD ZIL and L2ARC cache (zapps) and ZFS recordsize=8k, primarycache=metadata.

Results

The results are rather interesting, the best result in bold.

Volume	ZFS Optimised?	Transactions Per Second
Volume	ZFS Optimised?	One Volume	SSD Transaction Logs
zroot (Mirrored HDDs)	No	1443.13	1664.21
zroot (Mirrored HDDs)	Yes	1444.58	1521.68
zapps (Mirrored HDDs with ZIL/L2ARC)	No	1033.59	1660.72
zapps (Mirrored HDDs with ZIL/L2ARC)	Yes	1062.12	1670.07
zflash (Mirrored SSDs)	No	1667.84	-
zflash (Mirrored SSDs)	Yes	1647.74	-

And graphically (green is with the SSD Transaction Logs).

PostgreSQL Optimisation Results

Conclusions

We can draw a few small conclusions from these benchmarks:

The biggest improvement came from the SSDs, this is perhaps unsurprising
The ZFS optimisations were not a huge improvement, and in the case of the ZFS optimised HDDs with SSD Transaction Logs it actually made performance worse.
The ZIL and L2ARC make PostgreSQL performance significantly worse when the transaction logs reside on that volume.
If you haven’t setup with a ZIL and L2ARC, don’t bother. Just offloading the Transaction Logs to the SSDs is a fantastic gain.

So what setup am I going with? I was never considering storing everything on the SSDs due to their volatility, so I was pleased at the end when the Mirrored HDDs with ZIL/L2ARC with ZFS Optimisations and SSD Transaction Logs came out on top, if only just barely. It also lines up with the way I setup the volumes originally for space, which is fortunate for me.

Server Build benchmarks, hdd, pgbench, server build, shana, ssd, zfs

Server Build: Shana’s Role

Nov2nd
2011

Written by Rob

Typically when you’re building a new server, you need to know what you’re going to use it for. As mentioned in a previous post, Shana will be replacing the existing Tyrande server. So she will need to take on all the roles that Tyrande already fulfils. After that Tyrande will be rebuilt as Taiga, then the two will share the roles, with a bias towards Shana as the more powerful server.

The current roles running on Tyrande:

Web Server (Apache)
Database Server (PostgreSQL)
Database Server (MySQL)
SMTP Mail Server (Postfix)
POP/IMAP Mail Server (Dovecot)
Domain Name Server (DNS – Bind)

In addition to typical services that I run on all my servers:

Time Server (NTPD)
Firewall (pf)
Secure Remote Shell (OpenSSH)
Custom Server Monitoring (Meidon)
Nightly backups using zfs snapshots and rsync
Nightly security updates and vulnerability checks

And the synchronisation, load balancing and failover between Shana and Taiga.

These are the things I’ll be dedicating the following blog posts to. Some are obviously going to take multiple posts. Lets give a bit of an overview here though:

Web Server

I use Apache as my web server. I always have. It hosts blogs and custom projects, and everything in between. Typically I have a preference for PHP over perl, python or ruby. Again as with my other posts, these blogs aren’t to debate my software preferences. You can fight that elsewhere

I prefer the OpenBSD Apache layout too, hence the /var/www/ filesystems in the ZFS setup post. I’ll go into detail on installing Apache, PHP, Subversion and its dependencies. We’ll be running the web root of Apache mainly on the SSD, so we’ll need to look at keeping a copy on the normal disk too.

Database Server (PostgreSQL)

PostgreSQL is my SQL database of choice. All of my projects use it for the SQL data storage. I wouldn’t touch MySQL with a 50 foot poll. I’ll go into detail installing it too, and putting its data in /var/pgsql. As a bonus we’ll be running PostgreSQL mainly on the SSD and configuring asynchronous replication to a second instance of PostgreSQL running on Shana that writes to the HDD. This way we can keep a constant backup on less volatile disk that we can manually failover to in an instant.

Database Server (MySQL)

I run MySQL only because of WordPress. That is all.

SMTP Mail Server / POP3/IMAP Mail Server

I use Postfix as my SMTP server of choice and Dovecot for POP3/IMAP. Its fairly lightly utilised but I prefer the flexibility of running my own mail server. Note: I always use my ISPs server for outgoing SMTP, so I don’t have to deal with the difficulties of running my own server – namely blacklists. I’ll detail installing and configuring them to use a SQL database (PostgreSQL) as its list of valid user accounts with aliasing that works at the account level (so an alias can login, even), catch-alls and a bunch of fun stuff.

Domain Name Server

I host my own DNS too using BIND. I have accounts over at easyDNS to have global redundant DNS servers, but they all slave off my primary server. Then I can use normal zone files to manage my domains instead of a web interface (I like the flexibility). I’ll cover setting all that up too.

Time Server

I configure the built in ntpdate and ntpd to keep my local machine time synchronised with an appropriate time server.

Firewall

I use OpenBSD’s Packet Filter (pf) for my firewalling. It is above and beyond the best open source firewall package I’ve found. I think even OS X Lion incorporates pf now. I don’t do too much fancy stuff in pf, just some normalisation, default block, country-based blacklists and bruteforce blacklisting on SSH. (You try to connect to port 22 on my server more than 5 times in 60 seconds and you’re blacklisted).

Secure Remote Shell

Standard config of the built in OpenSSH sshd.

Custom Server Monitoring

I wrote a small PHP-based package a few years ago that runs some commands every minute and saves the output to a PostgreSQL database. It can alert via push notification if something goes wrong, and display a little dashboard thing:

Meidon Dashboard

Nightly Backups using ZFS and Rsync

I set this up a while ago. Its an awesome set of scripts called rsbackup that basically take a list of servers with individual configurations, connects to them, runs any pre-backup commands (like taking ZFS snapshots), then rsyncs any changes down and runs some post-backup commands.

So nightly, my media centre Mac Mini calls out to Tyrande (just as it will do for Shana and Taiga just by adding a configuration file), asks her to take snapshots of the MySQL database, run a pg_dump of the PostgreSQL database and then copies it all back to my Drobo. Neat eh?

I initially did ZFS snapshots for PostgreSQL too but stopped for reasons that I can’t remember. I’ll investigate whether PostgreSQL 9.1 plays more friendly with snapshots and re-evaluate the best way forward for Shana.

Nightly security updates and vulnerability checks

How to configure the FreeBSD to check for security/operating system updates nightly, and ensure that the built in nightly security checks are emailed to you! Can’t stress how important this is.

Sychronisation, Load Balancing and Failover

As mentioned in the original server overview post, I’ll be using CARP for failover and load balancing between Shana and Taiga. So I’ll cover off how to configure all of that too.

So that’s all of the stuff that I’ll be setting up for Shana, and then for Taiga. Should be fun!

Server Build apache, carp, dovecot, freebsd, meidon, mysql, named, ntpd, opensshd, pf, postfix, rsbackup, security