Photo of me

Hugo Osvaldo Barrera


Software Developer. Python Lover. IT Consultant.

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

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

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

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:

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

DKIM

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

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);

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

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

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

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
}

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

Final notes

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.


comments powered by Disqus