What's the first thing you do when setting up as an indie gamedev? Why, set up a mailserver, of course! This is Part 3 of the process I went through, where we set up a database of users for the mail system...

If you're here from Part 2, welcome back. If not, you've joined in the middle of me documenting the rather lengthy journey I took from an empty server to one handling email with TLS, spam and virus checking, mail accounts handled in a database, and with SPF, DKIM, and DMARC checking and compliance.

Part 3 will cover using a PostgreSQL database to set up mail accounts that aren't tied to Unix user accounts, so that you can give email accounts to users who don't have direct access to your server.

User Accounts

We can now receive emails perfectly fine, but reading them and sending them is a bit awkward. The ideal end solution is a Mail User Agent (MUA) like Thunderbird, but there are a few steps to go before that. First, let's get away from mail being delivered to actual Unix users. Postfix has a "virtual mailbox" system to allow you to put email somewhere other than /var/mail/username. Instead, we'll get all mail delivered to a series of folders owned by one mailreader user. We need some way to tell Postfix where to put incoming mail, and we'll set that up in a PostgreSQL database - we'll create a system for adding users who can receive mail (with a username and a secure password while we're at it, which we'll use with Dovecot later), and we'll put the routing information for Postfix in there as well. Once we're done, Postfix will be reading all this out of our database.

First of all, let's create our new user.

We'll start with a Unix group for the new user to belong to, called mailreader. It'll be assigned a numeric ID, called the GID, which we'll need later. It doesn't matter what it actually is, it just needs to be free. The operating system generally reserves lower IDs (say, up to 100). IDs above that will be taken up by users and groups that get added when software is installed (for example, the clamav user on my server has UID 113, and the clamav group is 119). Ubuntu starts issuing IDs for "normal" users at 1000. So, you can either specify the GID by hand, or you can just create the group and then find its ID:

groupadd mailreader
grep "mailreader" /etc/group

In my case, the GID came out at 1002. The next step is to create the user, and assign it to the new group. Run the following command:

useradd -g mailreader -d /home/mail -s /sbin/nologin mailreader
grep "mailreader" /etc/passwd

This adds a new user called mailreader (which comes from the last argument in the list). -g mailreader adds the user to the mailreader group. -d /home/mail sets the user's home directory. This is where all the mail will end up! -s /sbin/nologin sets the user's shell. /sbin/nologin just gives the user a polite message saying the account is unusable, and then exits, preventing login. We don't want anyone actually using the mailreader account for anything, so that locks it down. The second command will print out the mailreader user's entry in /etc/passwd, which will give us the user ID (UID), which in my case was 1002 as well.

This won't actually create the /home/mail directory, so we'll need to do that and assign it to the mailreader user:

mkdir /home/mail
chown mailreader:mailreader /home/mail

Now that's done, let's set up a database. For a small use-case of this size, exactly which database you use isn't terribly important. You could do this with MySQL (Linux distributions have started to move to a fork of MySQL called MariaDB since Oracle bought MySQL), I'm using PostgreSQL (or Postgres for short) because I'm more familiar with it. For bigger applications, Postgres generally scales better, but that's not particularly relevant here. Postgres is also a bit leaner - we'll need to install some extra extensions for what we're about to do, which is a bit more work, but it means you only have the bits you need and you can avoid a bit of bloat. Anyway, let's install Postgres (and Postgres support for Postfix):

apt-get install postgresql postfix-pgsql

On Ubuntu 18.04 LTS, this installs Postgres 10. Major releases happen annually(ish) and are supported for 5 years. Version 12 was released in October 2019, but 10 is still being updated for bugs and security fixes, and will be until November 2022. Once the installation has completed, Postgres should be running as a service.

There are a few things we need to do to make our new installation secure. As well as adding a new postgres Unix user, a Postgres server has its own internal users that can have a variety of privileges to connect to, read from, write to, create, and edit databases. Right now there'll only be one database user, called postgres, which has access to do everything and which we can use to set up other database users with more limited access. How users can log in is controlled by a file called pg_hba.conf (which on Ubuntu 18 with Postgres 10 is at /etc/postgresql/10/main/)

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

The comments show what's going on here. The peer authentication method just means that the postgres Unix user can log in as the postgres database user without using any authentication. The postgres Unix user needs some non-interactive (i.e. not typing in a password) access to do various things. Note that this means that anyone with root or sudo access to your machine can log in as the postgres superuser. However, if you've been hacked and someone has that access, you're in trouble anyway, and making it harder for automated tasks to run maintenance on your database probably isn't a great plan. You can, however, lock down the lines below that. Currently any user can connect to any database either by the peer method over a local (Unix socket) connection, or with a password over a host (TCP/IP) connection assuming they're on the local machine, since they can only connect over the local loopback connection on IPv4 or IPv6. The md5 method means that the user needs to enter a password, but the password is hashed using the md5 algorithm before it's passed to Postgres so that the password isn't sent in the clear. On a local machine there isn't much to worry about, but to be honest if you were worried about your password security an md5 hash isn't very secure anyway, but that's a story for another time. There is a more secure method, scram-sha-256, but Dovecot doesn't currently support it.

What we can do to lock things down a bit is disable the lower three lines by commenting them out, and then allowing specific users to access specific databases (which don't exist yet). We're going to create one new database, which we can just call mail, and two Postgres users, mailreader and mailreader_admin. mailreader will be the Postgres user that we use with Postfix and Dovecot to access the mail database. It will have access to only read from the database, not to make any changes. The mailreader_admin user will be the user that owns the database, and can make changes to it. The reason to have two accounts is that we're going to have to write down the mailreader account's password in a config file that Postfix can access, which isn't very secure. By making sure that the mailreader account can't alter anything in the database, anyone getting hold of that password can't go around changing things (they will be able to find the cryptographically hashed passwords for mail users that we'll create, which they could eventually crack, but there's not much we can do about that).

So, we'll comment out the more permissive default settings (leave the replication ones below), and we'll add some of our own:

host    mail            mailreader       127.0.0.1/32           md5
host    mail            mailreader       ::1/128                md5
local   mail            mailreader_admin                        md5

Postfix/Dovecot will access our database using the mailreader user over TCP/IP, and we'll use the mailreader_admin user via psql (Postgres's command-line interface) to set things up. Save and exit the file, and get Postgres to reload its config.

/etc/init.d/postgresql reload

We then want to connect to the Postgres server, using the peer authentication mentioned before:

sudo -u postgres psql

Now we're in, let's create some stuff (note the semicolons at the end of the SQL lines):

CREATE USER mailreader;
\password mailreader

The \password command, plus the username, will ask you to enter a password. You could do it with an SQL statement like CREATE USER mailreader WITH PASSWORD 'somepassword';, but that would put the password, in plain text, in your command history (you could just press the up arrow until you found the line with the password in). \password allows you to set the password without exposing it in this way^[1]. You might have noticed that psql's response to CREATE USER is to report CREATE ROLE. Roles in Postgres are either users or groups of users, so don't worry about the different output. The other thing to note here is that Dovecot doesn't support (at least by default) as many different characters in a database password as you might expect. Once you've installed Dovecot later you can check the default character list it supports by looking it /etc/dovecot/conf.d/10-auth.conf and finding a parameter called auth_username_chars - it'll be commented out, but it'll contain the default value for your version of Dovecot. In my case it was abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@ - unless you're going to change this (and they have used a limited charset for a reason, though I'm not sure they meant to include 0 twice), make sure you pick a password for mailreader that contains only those characters. For other passwords you can go all in.

Let's do the mailreader_admin user, too, and create a database.

CREATE USER mailreader_admin;
\password mailreader_admin
CREATE DATABASE mail WITH OWNER mailreader_admin;

Now, Postgres databases are structured in a hierarchical way. Each database has one or more schemas, and a schema can contain tables (which is where the data actually goes). By default every database has a public schema, and all users can create things in the public schemas of all databases. Let's connect to the mail database and turn that off, but re-enable it for the mailreader_admin user:

\c mail
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA public TO mailreader_admin;
GRANT USAGE ON SCHEMA public TO mailreader_admin;
ALTER SCHEMA public OWNER TO mailreader_admin;

By default, the owner of the public schema, even in a database owned by another user, is postgres. By setting the owner to mailreader_admin, we give permission to make changes to schema access to the mailreader_admin user. The postgres user will have access as well, because it's a superuser that can do anything - even though we didn't re-grant it the CREATE and USAGE privileges, it still has them. You could do a similar process on the default postgres database, so that only the postgres user can fiddle with it.

Now let's connect to our new database as the mailreader_admin user, and start creating things. You can quit psql with \q

psql -d mail -U mailreader_admin -W

Now let's create a table that will allow us to create mail users (instead of having a separate Unix user per email address):

CREATE TABLE users (
  email TEXT PRIMARY KEY,
  password TEXT NOT NULL,
  realname TEXT,
  maildir TEXT NOT NULL,
  created TIMESTAMP WITH TIME ZONE DEFAULT now()
);

Here we're creating a table which is indexed by the user's email address, which contains a password (which can't be null), their real name (which can be), the directory we're going to store their mail in (can't be null, otherwise where would it go?), and the time they were created. This defaults to the time the row is added, so we don't have to specify anything when we add a new user and Postgres will fill that in for us.

One important question is what we're going to store in the password field. We certainly don't want to store the plaintext password, otherwise anyone getting into our database knows everyone's email passwords (eek!). We want to store an encrypted hash of each password, and we're going to have to do so in a way that Dovecot understands, since Dovecot will be using this database later to retrieve the hashes and check them. The question is then which hashing scheme to use. Once you've installed Dovecot, you can see what hashing algorithms your version supports, and go from there. We'll grab the bits of Dovecot we need now:

apt-get install dovecot-imapd dovecot-pgsql

You'll then be able to run

doveadm pw -l

to see what schemes are supported. The latest versions of Dovecot support the very strong bcrypt and Argon2, but that support hasn't made it into Ubuntu 18.04 LTS - my version supported various things but the best was PBKDF2.

To create a hashed password, therefore, run

doveadm pw -s PBKDF2

You'll be asked to enter your password twice, and the output (with all the leading {PBKDF2} etc, you want the entire line) you can use as the password field when you add a user.

You might find this easier with two terminals open, but create your password hash, then go back into psql as mailreader_admin, and add an entry to your users table:

INSERT INTO users (
    email, 
    password, 
    realname, 
    maildir
) VALUES (
    'dave@example.com', 
    '{PBKDF2}blahblahblah', 
    'Dave, son of Dave',
    'dave/'
);

A quick SELECT * FROM users; will show you the result.

There are a couple of other things we need to put into Postgres. First, we need to tell Postfix that all mail to our domain is going to the single new Unix group we created earlier. This will end up in a Postfix setting called transport_maps, so we'll call our table transports:

CREATE TABLE transports (
    domain TEXT PRIMARY KEY,
    gid INTEGER UNIQUE NOT NULL,
    transport TEXT NOT NULL
);

INSERT INTO transports (
  domain,
  gid,
  transport
) VALUES (
  'example.com',
  1002,
  'virtual:'
);

So, the entry into that table is there to tell Postfix that mail to the example.com domain gets delivered to the group with ID 1002 (or whatever it was in your case), and that this is a "virtual transport". This tells Postfix that this is the final destination of mail to example.com.

Finally, we can create what Postfix calls a virtual_alias_maps lookup table. This allows email to one address to be sent to another. So if Dave wants email sent to dave_sonofdave@example.com to be delivered to his dave@example.com inbox, we can do that. Let's create another table, we'll call it aliases.

CREATE TABLE aliases (
  alias TEXT PRIMARY KEY,
  email TEXT NOT NULL
);

INSERT INTO aliases (
    alias, 
    email
) VALUES (
    'dave_sonofdave@example.com',
    'dave@example.com'
);

Note which way round the emails are. The first entry, alias, is the address of the incoming mail, while email is the place it will end up. In theory, you can get all email sent to any address on a domain to go to a single catch-all email address by leaving off the recipient part in the alias:

INSERT INTO aliases (
    alias, 
    email
) VALUES (
    '@example.com',
    'dave@example.com'
);

The Postfix documentation is full of warnings about doing this - although it looks convenient, the downside is that a spammer targeting any old address on the example.com domain will end up delivering mail to dave@example.com (although hopefully SpamAssassin will flag it as spam first). Anyone attempting to send spam that masqueraded as having come from example.com might also generate bounce messages that end up in this catch-all mailbox. The upside is that Dave can give people any email address to reach him on. If he's signing up for an account with Some Company, and they want an email address (and who doesn't, these days?), he can give them the email somecompany@example.com, and he'll still get the emails, and it's easy to set up filtering in a mail client if he wants all the emails from Some Company to go to a particular folder. There's also the advantage that if Some Company suffers a breach (or they just play fast and loose and sell his email address) and he ends up on a spam list, all the spam will come to somecompany@example.com and it's obvious where the problem lies. He can then block email to that account. If you do implement a catch-all, you'll have to add any email addresses that are excluded from the catch-all to the aliases table - if Alice wants her email to arrive in her inbox, you'd have to add an entry like this:

INSERT INTO aliases (
    alias, 
    email
) VALUES (
    'alice@example.com',
    'alice@example.com'
);

Since that's more specific, Postfix will use that alias for anything addressed to alice@example.com, and Dave can have the rest.

The database is now as we need it, but there's one problem, which is that although the mailreader_admin user can access the database just fine, the mailreader user can't. We'll need to allow mailreader access to the public schema on the database, and the ability to run SELECT statements on the tables:

GRANT USAGE ON SCHEMA public TO mailreader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mailreader;

This way mailreader will be able to SELECT data in the tables, but won't be able to insert, update, or delete rows, and won't be able to do anything else funky like delete tables completely or create new ones. If you try to put in a dummy alias, say, as the mailreader user psql will tell you to go away:

SELECT * FROM aliases;
           alias           |      email
---------------------------+------------------
dave_sonofdave@example.com | dave@example.com

INSERT INTO aliases (
    alias, 
    email
) VALUES (
    'lol',
    'wut'
);
ERROR: permission denied for relation aliases

Now that we've set that up, we need to tell Postfix about it. We'll create a few new config files in Postfix's config directory and then edit the existing main.cf and point to the new files. Make a new directory inside /etc/postfix called pgsql, and create a new file in there called mailboxes.cf:

user=mailreader
password=your_mailreader_password
dbname=mail
table=users
select_field=maildir
where_field=email
hosts=localhost

Here you're telling Postfix to connect to the mail database with the mailreader user, using whatever password you created (this is the bit where we store the password in plain text). You're telling it how to generate the query it needs with table, select_field, and where_field - you're telling Postfix to run the query SELECT maildir FROM users WHERE email='dave@example.com'; when it gets an email for Dave. And you're telling it to connect on localhost, so it'll try to connect to Postgres over a local TCP connection. We'll do the same for the other two tables, with two other files. First, transport.cf:

user=mailreader
password=your_mailreader_password
dbname=mail
table=transports
select_field=transport
where_field=domain
hosts=localhost

Exactly the same idea, just generating a different query: SELECT transport FROM transports WHERE domain='example.com'; Finally, let's do aliases.cf:

user=mailreader
password=your_mailreader_password
dbname=mail
table=aliases
select_field=email
where_field=alias
hosts=localhost

This will give us SELECT email FROM aliases WHERE alias='dave_sonofdave@example.com';, or whoever the incoming mail is actually addressed to. Now that's done, head back into main.cf

At the bottom, set up several new parameters:

# Connect to Postgres for mailboxes, transport, and aliases
local_recipient_maps =
virtual_uid_maps = static:1002
virtual_gid_maps = static:1002
transport_maps = pgsql:/etc/postfix/pgsql/transport.cf
virtual_mailbox_base = /home/mail/
virtual_mailbox_maps = pgsql:/etc/postfix/pgsql/mailboxes.cf
virtual_alias_maps = pgsql:/etc/postfix/pgsql/aliases.cf

• So, we start out by making local_recipient_maps empty. We don't want Postfix trying to deliver mail in the usual local way, our virtual system is taking over.
• We're telling Postfix that the UID/GID combo we created earlier owns all the mailboxes with the static:1002 lines. Substitute your IDs.
• We then tell Postfix where to look for the transport, mailbox, and alias mappings. We tell it that the files are in a format to connect to a Postgres database with the pgsql: prefix.
• virtual_mailbox_base gets prepended to the directories we put in the maildir column in the users table. So Postfix will put /home/mail/ in front of dave/ to get the full path.

A reload of Postfix, and we should be there.

/etc/init.d/postfix reload

You should now be able to email any address in your users table. /home/mail should start empty, but if dave@example.com receives an email, /home/mail/dave should appear, with three directories inside it, cur, new, and tmp. If that's not working, check /var/log/mail.log, but otherwise we're ready to set up Dovecot properly.

We're now delivering incoming mail without depending on Unix accounts, but we don't have any easy way to access it. However, we're on the way to the solution, which is connecting with a mail user agent. We'll cover that in Part 4.

Local mail

One important point to mention before we finish this section. Fully-internal mail (e.g. mail sent from a service to root about a problem) will still be delivered to a local mailbox, because the transport and virtual mapping stuff we've implemented won't trigger. This is because we've set Postfix to do virtual delivery for anything addressed to example.com, so an email simply sent to root will be delivered to /var/mail/root as normal. Probably the easiest way to get root's mail delivered somewhere else is using the ~/.forward file. Put this in root's home directory. It should contain a single line with all the email addresses you'd like root's mail sent to separated by commas. So, to send root's mail to Dave, we can set the contents of /root/.forward to

dave@example.com

You can test this out by trying to email root as a Unix user simply by running mail root and filling in the email. Use Ctrl+D once you're done to send it.