Storing mail accounts in MySQL

Most of our dedicated servers use Postfix and Dovecot to handle mail for the customer. This works great in most situations and is quite flexible. However, perhaps you already have an application (or a suite of them) to handle your business operations. It'd be nice to integrate your mail accounts so you don't have to maintain separate login details. From a management perspective, you can go one better and do away with the need for user accounts on the system. This won't suit everyone, but it's great if you host a lot of mailboxes that don't depend on anything else.

Who is this article for?

This article won't be too heavy on the ins and outs of configuring postfix and dovecot; that's our job, and there's plenty of good articles on the subject already. This is for the developer or DB admin who has an existing system (or is designing a new one) with a mind to integrate mail-handling. The key thing to keep in mind is to keep the database structure flexible enough to allow the necessary details to be extracted.

For the sake of keeping things simple, we'll cover the usage of MySQL as the database backend. We prefer to use PostgreSQL, but MySQL simply has a larger market share in the places we're likely to encounter it. Postfix supports both mysql and pgsql equally, so this can be quite easily adapted to a pgsql backend if you so desire.

What's required of the database

  • For mail delivery, at the very least postfix needs to be able to determine what mail addresses are acceptable and know where to deliver them. For a well-designed setup, you can leave the delivery location to postfix.
    • Under mysql this means having a column of email addresses in your designated "users" table.
    • The syntax for using pgsql is a bit different to mysql. Mysql lets you define an arbitrary query with fill-in-the-blanks, whereas pgsql requires a simple table lookup. If you want to use pgsql, you may need to define a view to work with your schema.

  • Mailbox access via POP or IMAP is provided by dovecot. Dovecot is flexible with queries and can be configured to query the database with an email address for the lookup. Dovecot also needs to authenticate the user, which we can do against the database. It's considered poor practice to store unencrypted passwords in the database, so we keep a cryptographic hash of the password instead. crypt used to be used for this, but MD5 is preferred now as it's readily available and offers better security. Something stronger like SHA1 would be ideal, but it's not assured to be supported everywhere.

An simple example configuration

We'll show a possible database schema that could be used for this. Remembering that you're integrating with other systems, there'll be some extra information in there; that's okay.

This is a very straightforward example, as we'll be using the power of mysql queries to extract the necessary data. Chances are you already have a suitable table in this format. It comes with a few limitations, but should be fairly widely applicable:

  • No support for redirecting mail to other addresses
  • No ability to disable mail for an account
  • Each distinct "user" in the system may only have one email address.

username

email

password

phone_number

address

YoshikaMiyafuji

yoshika@501jfw.mil

df402e71af9bb5d7556cce2328a6e1c3

+44-8-125-4587

3rd floor, west block, Britannia AFB

MinnaDietlindeWilcke

mdw@501jfw.mil

1661e030f2aca8281d01407645eb1f9e

+44-8-124-4428

2nd floor, west block, Britannia AFB

LynetteBishop

lynette@501jfw.mil

83d52bc7bae88fddd497e50ad27dae58

+44-8-125-3879

2nd floor, west block, Britannia AFB

PerrineHClostermann

perrinehc@501jfw.mil

8cf9747f93fdb87bd83d938aece026b3

+44-8-125-7546

2nd floor, east block, Britannia AFB

FrancescaLucchini

lucchini@501jfw.mil

9f3c26bd22f37c9aa6f2dcdfda66fe3c

+44-8-125-2323

1st floor, east block, Britannia AFB

SanyaVLitvyak

sanyav@jssdf.mil

6c79b318e1a9cd5c9f258bf2e2905693

+44-2-432-0188

Phoenix Bldg, Bluesky AFB

From this table:

  • Postfix can determine what domains we're accepting mail for
  • Postfix can determine which addresses are valid
  • Dovecot can authenticate users with a username+password or email+password combination
  • Dovecot can find a suitable path to a user's mailbox. Some examples of common setups, the differences are due to differing requirements and existing configurations:
    • /var/mail/jfw.mil/yoshika
    • /home/vmail/jssdf.mil/sanyav/Maildir
    • /home/PerrineHClostermann/Maildir

For the curious, we use various queries to get what we want to know. The LIMIT 1 clause shouldn't normally be necessary in a proper schema as the data you're extracting should be a primary/unique key anyway, which makes a multiple-row resultset impossible.

Check if we accept mail for the domain 501jfw.mil:
    SELECT 1 FROM users WHERE substring_index(email,'@',-1) = '501jfw.mil' LIMIT 1;

Check if we accept mail for perrinehc@501jfw.mil:
    SELECT 1 FROM users WHERE email = 'perrinehc@501jfw.mil' LIMIT 1;

Authenticate a user for POP/IMAP access:
    SELECT username AS user, password FROM users WHERE username='FrancescaLucchini' LIMIT 1;

A more-featureful implementation

The above example is fine if your requirements are simple, but it's quite limiting. A better approach is to normalise the data you're storing, allowing for a more flexible implementation.

With this approach, we separate the usernames and domains, allowing flexible "mailbox"@"domain" combinations. One could in theory extend this to allow arbitrary email addresses (within the constraints of the configured domains), but this would lose the desirable property of having one mailbox per user, identifiable by the address itself. In addition, we also add the ability to "alias" an address to forward the mail elsewhere.

Table structure

For this example we'll leave out the extra information that isn't directly used by postfix and dovecot. A halfway-decent SQL-hacker should have no problem using a view to extract this structure out of an existing schema.

Domains

First we define the domains we accept mail for.

  • For this table (and subsequent ones) the id field isn't technically necessary, but mysql has been seen to complain when using "weird" primary key definitions

  • The choice of VARCHAR(100) is arbitrary, it should safely cover any domain you can expect to run into

CREATE TABLE domains (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(100) NOT NULL UNIQUE
) ENGINE = InnoDB;

INSERT INTO domains (domain) VALUES ('501jfw.mil');
INSERT INTO domains (domain) VALUES ('jssdf.mil');

id (auto-assigned)

domain

1

501jfw.mil

2

jssdf.mil

Users

This is the primary definition of users and their associated mailbox. Extra details on each user could be added as extra columns. We'll populate the table in the same way as the previous example.

  • This could well be a view derived from another master table
  • The domain is foreign-keyed back to the domains table. This ensures the intended address is valid as long as the domains table is correct

  • The password is a hex-encoded MD5 hash, hence the 32 character-long field
  • We've included an enabled field so we can disable an account if desired

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    domain_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(32) NOT NULL,
    enabled BOOLEAN DEFAULT true,
    CONSTRAINT email_unique UNIQUE (domain_id,username),
    FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
) ENGINE = InnoDB;

INSERT INTO users (username, domain_id, password) VALUES ( 'yoshika', (SELECT id FROM domains WHERE domain='501jfw.mil'), MD5('password') );
INSERT INTO users (username, domain_id, password) VALUES ( 'mdw', (SELECT id FROM domains WHERE domain='501jfw.mil'), MD5('password') );
INSERT INTO users (username, domain_id, password) VALUES ( 'lynette' , (SELECT id FROM domains WHERE domain='501jfw.mil'), MD5('password') );
INSERT INTO users (username, domain_id, password) VALUES ( 'perrinehc' , (SELECT id FROM domains WHERE domain='501jfw.mil'), MD5('password') );
INSERT INTO users (username, domain_id, password) VALUES ( 'lucchini' , (SELECT id FROM domains WHERE domain='501jfw.mil'), MD5('password') );
INSERT INTO users (username, domain_id, password) VALUES ( 'sanyav' , (SELECT id FROM domains WHERE domain='jssdf.mil'), MD5('password') );

id (auto-assigned)

domain_id

username

password

enabled

1

1

yoshika

cc03e747a6afbbcbf8be7668acfebee5

1

2

1

mdw

cc03e747a6afbbcbf8be7668acfebee5

1

3

1

lynette

cc03e747a6afbbcbf8be7668acfebee5

1

4

1

perrinehc

cc03e747a6afbbcbf8be7668acfebee5

1

5

1

lucchini

cc03e747a6afbbcbf8be7668acfebee5

1

6

2

sanyav

cc03e747a6afbbcbf8be7668acfebee5

1

Get all email valid email addresses
    SELECT CONCAT(username, '@', domain) AS email FROM users LEFT JOIN domains ON users.domain_id=domains.id WHERE enabled=true;

Aliases

Aliases are a convenient way to redirect mail to another address if you won't be checking your mailbox for a while, or the address is purely semantic and you'd rather retrieve the mail elsewhere. For example, you might prefer to aggregate all your mail accounts and read them through gmail.

  • The aliases table will be checked ahead of the users table

  • Multiple entries can be added to forward mail to more than one destination
  • Aliases can point to local and remote destinations

CREATE TABLE aliases (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    domain_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    destination VARCHAR(150) NOT NULL,
    FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
) ENGINE = InnoDB;

# mailing list-style
INSERT INTO aliases (domain_id, name, destination) VALUES ( 4, '2nd.floor.west', 'mdw@501jfw.mil');
INSERT INTO aliases (domain_id, name, destination) VALUES ( 4, '2nd.floor.west', 'lynette@501jfw.mil');

# redirect to an external address
INSERT INTO aliases (domain_id, name, destination) VALUES ( 5, 'sanyav', 's.v.litvyak@gmail.com');

# multiple names for the same mailbox
INSERT INTO aliases (domain_id, name, destination) VALUES ( 4, 'francesca', 'lucchini@501jfw.mil');

id (auto-assigned)

domain_id

name

destination

1

1

2nd.floor.west

mdw@501jfw.mil

2

1

2nd.floor.west

lynette@501jfw.mil

3

2

sanyav

s.v.litvyak@gmail.com

4

1

francesca

lucchini@501jfw.mil

A list of all address->address aliases
    SELECT CONCAT(name, '@', domain) AS 'from',destination AS 'to' FROM aliases LEFT JOIN domains ON aliases.domain_id=domains.id;
    +---------------------------+-----------------------+
    | from                      | to                    |
    +---------------------------+-----------------------+
    | 2nd.floor.west@501jfw.mil | mdw@501jfw.mil        | 
    | 2nd.floor.west@501jfw.mil | lynette@501jfw.mil    | 
    | sanyav@jssdf.mil          | s.v.litvyak@gmail.com | 
    | francesca@501jfw.mil      | lucchini@501jfw.mil   | 
    +---------------------------+-----------------------+

Multi-recipient alias in use
    SELECT destination FROM aliases LEFT JOIN domains ON aliases.domain_id=domains.id WHERE CONCAT(name, '@', domain)='2nd.floor.west@501jfw.mil';

What next?

At this point we can configure postfix and dovecot to work with your backend. There's a lot of details to consider, so this is by no means complete; hopefully you'll now have a good understanding of how things should fit together and what you'll need to deal with. There's a lot of considerations to be made as part of the implementation and this only scratches the surface. These are details you'd need to discuss with us, we're happy to answer questions relating to your mail server.

References