Codehouse

How to configure an E-mail distribution service using Postfix + Courier IMAP with SQLite as the credentials storage endpoint

Motiviation

While I was doing the migration from Ubuntu 14 to Ubuntu 16, I came across the high resource demands required by the new version of MySQL. I’m running on a very basic VPS with to few RAM, and so MySQL (was running 5.7) crashed much frequently, so I decided to switch a MySQL fork, mariaDB, in this case. Tried first with the last stable version, 10.3, than I discovered that this version was still with more problems and running with even more problems with the very low RAM, so I downgraded to 10.1, but still the problems persisted. I ran the mysqltuner script to adjust the mariaDB configuration according to this “hardware” requirements. I changed the max_connections variable and lowered the value to 2 or 3. Still discovered that the entire VPS has crashed just because of this. Then I increased the value to 10 and Postfix started to complain with authentication errors since the e-mail credentials are stored in a MySQL schema. On that MySQL there are running other schemas, one for a wordpress site with very low bandwidth, and other wordpress site for a business, with much more load than the last wordpress. You know, wordpress does not support officialy other SQL engines than MySQL, which is bad, very bad, but I discovered a plugin that allows you to use SQLite as a WordPress backend. Very good for the wordpress instance with much less load (Truly speaking, it right this one that is storing this blog). So I decided, if now I can use SQLite as the backend for an WordPress site, why not use it also to store email service credentials. Very unfrequentely I change those credentials, so I move on studying how Postfix and Courier could use a simple SQLite database to store the email credentials and other stuff Postfix implements like transports, or e-mail forwardings.

The schema

I have a very simple schema, much more simple than the one used by the postfixadmin web-based administration portal. I used the one described in this website. It consists in just four tables with all the essential information to store about the e-mail addresses like username, domain, password and maximum of quota (maximum space in disk allowed per email).

Of course the tables were created with MySQL (as the proper article suggests) in mind, I used this schema for MySQL, and even created as WebUI interface using Laravel with frontend in Bootstrap for it, it’s called fixmailadmin. I followed the entire tutorial to configure postfix and courier by hand with a MySQL database storing all the pertinent metadata. As you see the schema is very simple. The biggest table is the users with just three columns: you won’t need more than that. After configuring for MySQL as I was explaining above, I stumbled upon the problem of my VPS having very few RAM so I thought: after having success in configuring a WordPress page in using a SQLite database, why not also proceed the same method with the virtual domains database !?

Courier, which is the e-mail deliverer through POP3 or IMAP, relies on different authentication drivers , I was using auth-mysql, but I discovered there was also a sqlite-based one auth-sqlite . By default courier does not automatically install it, you’ll have to install it on Ubuntu by issuing:

apt-get install courier-authlib-sqlite

The rest is very much like the configuration used on the MySQL driver. In the README for authlib-mysql they describe one table for the authentication, but I preferred to adapt my already existing schema in MySQL for authentication with SQLite. First, I had to take the MySQL own idiosyncrasies out like the ENGINE=MyISAM and replace PRIMARY KEY by UNIQUE KEY in order for SQLite to create the tables, among other ones that now I don’t remember.

Here are the SQL script of the schema above already converted for use to be imported by SQLite:

----
-- Table structure for domains
----
CREATE TABLE `domains` (
  `domain` varchar(50) NOT NULL
);

----
-- Table structure for forwardings
----
CREATE TABLE `forwardings` (
  `source` varchar(80) NOT NULL,
  `destination` text NOT NULL
);


----
-- Table structure for transport
----
CREATE TABLE `transport` (
  `domain` varchar(128) NOT NULL DEFAULT '',
  `transport` varchar(128) NOT NULL DEFAULT ''
);

----
-- Table structure for users
----
CREATE TABLE `users` (
  `email` varchar(80) NOT NULL,
  `password` varchar(20) NOT NULL,
  `quota` int(10) DEFAULT '10485760'
);

There is no primary keys defined in this schema as there is not much entries in each table, as you can see the SQL is more or less standard. Now to the configuration of postfix to use our sqlite database file-based.

Postfix

I’ll be following the tutorial that I cited above. We’ll need to add six files to the /etc/posfix directory: – sqlite-virtual_domains.cf – sqlite-virtual_email2email.cf – sqlite-virtual_forwardings.cf – sqlite-virtual_mailbox_limit_maps.cf – sqlite-virtual_mailboxes.cf – sqlite-virtual_transports.cf

The first line on the six files is always the same:

dbpath = /the/path/to/your/sqlite/database

The second line differs between the six:

  • sqlite-virtual_domains.cf:

query = SELECT domain FROM domains WHERE domain='%s'

  • sqlite-virtual_email2email.cf

query = SELECT email FROM users WHERE email='%s'

  • sqlite-virtual_forwardings.cf

query = SELECT destination FROM forwardings WHERE source='%s'

  • sqlite-virtual_mailbox_limit_maps.cf

query = SELECT quota FROM users WHERE email='%s'

  • sqlite-virtual_mailboxes.cf

query = SELECT PRINTF("%s/%s/", SUBSTR(email,INSTR(email,'@')+1) , SUBSTR(email,0,INSTR(email,'@')) ) FROM users WHERE email='%s'

  • sqlite-virtual_transports.cf

query = SELECT transport FROM transport WHERE domain='%s'

Following you need to change the permissions of the files:

chmod o= /etc/postfix/sqlite-virtual_*.cf
chgrp postfix /etc/postfix/sqlite-virtual_*.cf

Next will be create a local UNIX account where the mails for the virtual domains will be stored:

groupadd -g 5000 vmail
useradd -g vmail -u 5000 vmail -d /home/vmail -m

The value of 5000 is arbitrary, you can use anyone as you like. You can use any dir outside of /home, but you’ll incur into problems later. This user will not be allowed login for that you can edit the /etc/passwd and replace the shell bash to another thing like \sbin\nologin.

Next will be edit the editing the /etc/postfix/main.cf, altering the lines according to your case:

myhostname = your.mailserver.com
mydestination = your.mailserver.com, localhost, localhost.localdomain
mynetworks_style = subnet
mynetworks = 127.0.0.0/8

mailbox_command = procmail -a "$EXTENSION"
mailbox_size_limit = 0
recipient_delimiter = +
inet_protocols = all
relay_domains = $mydestination
relay_recipient_maps =

virtual_mailbox_domains = sqlite:/etc/postfix/sqlite-virtual_domains.cf
virtual_mailbox_base = /home/vmail
virtual_mailbox_maps = sqlite:/etc/postfix/sqlite-virtual_mailboxes.cf

transport_maps = sqlite:/etc/postfix/sqlite-virtual_transports.cf
virtual_mailbox_limit_maps = sqlite:/etc/postfix/sqlite-virtual_mailbox_limit_maps.cf

virtual_minimum_uid = 100
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000

virtual_alias_domains = 
virtual_alias_maps = sqlite:/etc/postfix/sqlite-virtual_forwardings.cf, sqlite:/etc/postfix/sqlite-virtual_email2email.cf

proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $virtual_mailbox_limit_maps

#smtp auth
smtpd_sasl_auth_enable = yes
broken_sasl_auth_clients = yes
smtpd_sasl_authenticated_header = yes
smtpd_tls_auth_only = no
smtp_use_tls = yes
smtpd_sasl_type = cyrus
local_recipient_maps =
smtp_tls_note_starttls_offer   = yes

smtpd_tls_loglevel = 1
smtpd_tls_received_header   = yes
smtpd_tls_session_cache_timeout   = 3600s
tls_random_source =   dev:/dev/urandom
smtpd_sasl_security_options = noanonymous
broken_sasl_auth_clients = yes
smtpd_sender_restrictions = permit_sasl_authenticated
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination

procmail_destination_recipient_limit = 1 
home_mailbox = Maildir/

Postfix will authenticate external users that have email addresses on your virtualdomains managed by your mail server through the same passwords used to get the email through POP3 or IMAP. But first we’ll need to generate a SSL certificate that is needed for TLS:

openssl req -new -outform PEM -out smtpd.cert -newkey rsa:2048 -nodes -keyout smtpd.key -keyform PEM -days 365 -x509
chmod o= smtpd.key

In the questions that openssl prompts you have to have particular care with the Common Name (eg, Your name) [] here you should put the fully qualified name of your server, which is, in our example, your.mailserver.com . To the Email Address you can put any valid email address .

Saslauthd

Saslauthd is the authenticator for Postfix SMTP and by default on Debian/Ubuntu uses the PAM authenticaton method, but after getting have problems, and since we’ll have authentication through IMAP we can use it, so I replaced the MECHANISMS value from “pam” to “rimap” . But before that you should use create the directory /var/spool/postfix/var/run/saslauthd which is necessary for Postfix since it runs by default on Debian/Ubuntu in a chroot environment.

Here is my /etc/default/saslauthd :

START=yes
MECHANISMS="rimap"
MECH_OPTIONS=""
THREADS=2
OPTIONS="-c -m /var/spool/postfix/var/run/saslauthd -r -O localhost"
PARAMS="-m /var/spool/postfix/var/run/saslauthd -r"

On the OPTIONS avove the value after the -O flag is the location of the IMAP server which we’ll authenticate against. In my case, the IMAP Server (Courierd) is running in the same server, so is localhost.

Next we edit or create if does not exist, /etc/postfix/sasl/smtp.confd :

pwcheck_method: saslauthd
mech_list: plain login
allow_plaintext: true

Next we’ll be adding the user postfix to the sasl in order for the later be able to use the sasl authenticator daemon:

adduser postfix sasl.

Then is restart postfix and saslauthd:

service postfix restart
service saslauthd restart

Courier

Now comes the way of Courier, the daemon responsable to read the emails stored inside the /home/vmail for each user on their one virtual domain. In this case we move to the /etc/courier dir and start to change the authdaemonrc file:

authmodulelist="authsqlite"
DEBUG_LOGIN=1

The original authmodulelist has a whole list of drivers activated for in the case the firsts fail in the authentication, the next well be tried until the first has sucess. Since it is not the case remove all other values (or copy the original inside a comment as I like to do).

Now comes the essential file for the Sqlite authentication, authsqliterc :

SQLITE_DATABASE     /the/path/to/your/sqlite/database
SQLITE_USER_TABLE   users
SQLITE_CRYPT_PWFIELD    password
SQLITE_UID_FIELD        "5000"
SQLITE_GID_FIELD        "5000"
SQLITE_LOGIN_FIELD  email
SQLITE_HOME_FIELD   "/home/vmail"
SQLITE_MAILDIR_FIELD    PRINTF("%s/%s/", SUBSTR(email,INSTR(email,'@')+1) , SUBSTR(email,0,INSTR(email,'@')) )
SQLITE_QUOTA_FIELD  quota

Next step we’ll be generate the keys for IMAP-SSL and POP3-SSL: Remove the ones that were generated during the installation that used localhost as the name for the FQN: rm -f imapd.key pop3d.key

and

edit the files imapd.cnf and pop3d.cnf changing the following line in both files:

CN=your.mailserver.com

Then recreate the certificates:

mkimapdcert
mkipopdcert

and restart courier (in this case, since Courier is 5 different services I use the following snippet to speed things:

for s in /etc/init.d/courier* 
do
 basename=$(basename $s)
 service $basename restart 
done

and the 5 services are restarted in sequence !

Then try to test the authentication you can use the command authtest :

authtest yourmail@your.mailserver.com.

The one missing problem to solve

One problem arrived while I was testing this configuration, how I was going to generate the hashes for the passwords MySQL uses the C crypt() function to generate the hashes in their CRYPT function. How I’m going to make SQLite to generate the hashs !?

NOTE: this file were converted from Markdown to HTML with the help of Pandoc.

Posted

in

by

admin

Tags:

, , , ,