Opensmtpd and dovecot with a shared SQL database

This article will describes how to achieve a flexible and scalable email setup using opensmtpd and dovecot. For single-user or single-domain setups, this is an overkill, but feel free to read ahead, you may still find something useful.

Introduction

[permalink]

I’ve used opensmtpd and dovecot for years now, and have been hosting email for several domains for a large portion of that time.

For small sites the text-based backends work fine, however, as the amount of users, domains and virtual users grows, it’s not easy to keep track. Data needs to be duplicated between smtpd’s credentials table, the virtual users table, and dovecot’s mailbox table.

I finally decided it was time to consolidate all the data in one place. I chose sqlite to begin with, but this can be moved onto postgresql if it needs to scale even more.

Opensmtpd and dovecot interaction

[permalink]

If you attemp to use virtualusers (and you’ll want to if you’re handling many users in many domains), when receiving emails, opensmtpd maps email addresses to usernames (which can contain no @ sign). Dovecot then stores emails in mailboxes based on these usernames. Both these things make mapping many virtual users from different domains a bit compex.

I decided that the usernames I’ll be mapping to will take the form of username_domain (eg: hugo_barrera.io). This makes a few initial settings a bit complicated, but is infinitely flexible (the underscore sign in illegal for email addresses, so there’s no change for collision).

Database schema

[permalink]

I designed two database schemas, a normalized one, and a single-table one. I decided to keep the latter, since it makes inserts simpler and it’s easier to show-and-tell, but if you read through this entire article, you’ll be able to use whatever tableset you like.

CREATE TABLE users (
  username TEXT NOT NULL,
  domain TEXT NOT NULL,
  mailbox TEXT NOT NULL,
  password TEXT NULL
);

Passwords are blowfish encrypted. Opensmtpd uses this by default and dovecot also supports this (it refers to this scheme as BLF-CRYPT).

In different contexts, each column is used for something different:

• For email submission username, domain and password are used to validate authentication. The usernames smtpd receives from the client take the form of hugo@barrera.io.
• Opensmtpd needs to know what domains it’s receiving emails for. For this, it just sees if there’s any entry in the users table where this domain is present. It would make no sense to keep a separate list of domains: if there’s no address for a domain, then I’re not accepting email for this domain.
• For email receiption, username@domain is used to map to the recipient mailbox (ie: the mailbox column). Several addresses can map to a single user, and wildcards can also be used too. The @ is replaced with an _ when querying this table as well.
• For dovecot’s authentication, it needs to know if username@domain is a real user, or just an aliased address. This is simply determined by checking if password != NULL.

Passwords should be encrypted using either doveadm pw -s BLF-CRYPT or smtpctl encrypt. The output of both seems interchangeable.

DKIM

[permalink]

DKIM signing is done with DKIMProxy. There’s a bunch of examples out there, so I won’t go into detail about that. Basically, opensmtp will send emails to DKIMProxy, accepts them back, tags them, and then relays them out.

Opensmtpd configuration

[permalink]

First of all, we need to configure opensmtpd to receive email and read all the data from the sqlite database. Here’s my smtpd.conf as a reference:

# === TLS Certificates === #
pki mx1.barrera.io certificate "/path/to/certs/mx1.barrera.io.crt"
pki mx1.barrera.io key         "/path/to/certs/mx1.barrera.io.key"

pki smtp.barrera.io certificate "/path/to/certs/smtp.barrera.io.crt"
pki smtp.barrera.io key         "/path/to/certs/smtp.barrera.io.key"

# === Tables === #
table domains sqlite:/etc/mail/sqlite.conf
table virtuals sqlite:/etc/mail/sqlite.conf
table userinfo sqlite:/etc/mail/sqlite.conf
table credentials sqlite:/etc/mail/sqlite.conf

# === Listen === #

listen on lo0
listen on lo0 port 10028 tag DKIM
listen on egress port smtp       tls                            hostname "mx1.barrera.io"
listen on egress port submission tls-require auth <credentials> hostname "smtp.barrera.io"

# === Handle Messages === #

accept from any for local virtual <virtuals> \
  userbase <userinfo> deliver to lmtp "/var/dovecot/lmtp"

accept from any for domain <domains> virtual <virtuals> \
  userbase <userinfo> deliver to lmtp "/var/dovecot/lmtp"

# === Sign/relay === #
accept tagged DKIM for any relay
accept for any relay via smtp://127.0.0.1:10027

This is all rather self-explanatory if you’re familiar with smtpd.conf’s sytax. I use lmtp via a unix socket because I believe it’s slightly faster, but a network socket works fine too.

sqlite.conf is a bit more complex:

dbpath                  /etc/mail/smtpd.sqlite
query_credentials       SELECT username||'@'||domain, password FROM users WHERE (username||'@'||domain)=?;
query_domain            SELECT domain FROM users WHERE domain=? LIMIT 1;
query_userinfo          SELECT 7000, 7000, '/var/empty' FROM users WHERE (username||'_'||domain)=?;
query_alias             SELECT replace(mailbox, '@', '_') FROM users WHERE ? LIKE (username||'@'||domain);

• query_credentials is used to validate user credentials. Opensmtpd will pass the provided email (in the form of username@domain), and this will find mapping rows. Since aliases have password = NULL, those rows will return false.
• query_domain is used when querying if we receive emails for a domain or not. The logic is rather simple: if there’s an address for a domain, then we accept email for it and viceversa.
• query_userinfo is used for email delivery, and to check if a mailbox exists. If there’s a mapping for it, then it exists.
• query_alias returns the mailbox for a username@domain, as described above.

On the smtpd side, that’s basically it. Here’s some sample data:

INSERT INTO users VALUES('hugo', 'barrera.io', 'hugo@barrera.io', '$2b$08$CEWRsxzLeTziYlq58gJvd.35RQ0fK2jP9RW8AisoxAznmmN6GsdvK');
INSERT INTO users VALUES('%', 'barrera.io', 'hugo@barrera.io', NULL;
INSERT INTO users VALUES('contact', 'example.com', 'contact@example.com', '$2b$08$CEWRsxzLeTziYlq58gJvd.35RQ0fK2jP9RW8AisoxAznmmN6GsdvK');

• hugo@barrera.io is an actual mailbox. It’ll get emails for himself, or anyone matching %@barrera.io (remember that % is the SQL wildcard character).
• contact@example.com is another mailbox. That user will get email sent to him and just that.

If you’re going to have several overlapping delivery patterns, you probably want to have a priority column in the table, and add ORDER BY priority and LIMIT 1 to some queries.

Dovecot

[permalink]

Dovecot includes conf.d/auth-sql.conf.ext. I’ve modified it as follows:

mail_location = maildir:~/Maildir

passdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf.ext
}

userdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf.ext
  override_fields = uid=vmail gid=vmail
}

• mail_location tell dovecot where relative to the user’s home the email is, and to use maildir. You can change this as you prefer.
• override_fields tells dovecot that email is always handled by the uid/gid vmail:vmail.

The other values are the defaults. Of course, dovecot-sql.conf.ext does require more changes so as to retrieve user data from the shared SQL database:

driver = sqlite
connect = /etc/mail/smtpd.sqlite
default_pass_scheme = BLF-CRYPT

password_query = \
  SELECT password \
  FROM users \
  WHERE mailbox = replace('%u', '_', '@') AND password NOT NULL

user_query = \
  SELECT '/home/vmail/'||domain||'/'||username AS home \
  FROM users \
  WHERE mailbox = replace('%u', '_', '@') AND password NOT NULL

• connect needs to point to the same db that smtpd is using.
• default_pass_scheme must be BLF-CRYPT since that’s what smtpd uses.
• password_query is used to authenticate users (eg: those attempting to open their IMAP mailbox).
• user_query is used in two scenarios:

1. To determine where to deliver messages destined to user_domain. This is done by finding the real user who owns this mailbox (password must be null for aliased mailboxes!).
2. To determine what messages to serve to a user reading his email (eg: via IMAP). In this case, the usernames have the format of user@domain.

Final notes

[permalink]

Setting the whole thing up is a bit complicated, but adding new users is a breeze. If there’s a need to grow, the sqlite db can become a postgresql db. By using lmtp, dovecot and opensmtpd can move into different machines, giving even more scalability. Further scaling, however, will require multiple dovecot backend and some changes to the sql schema.

Please feel free to point out any issues, potential improvements, or comments, I’ll try to update this appropiately.