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.
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.
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:
query = SELECT domain FROM domains WHERE domain='%s'
query = SELECT email FROM users WHERE email='%s'
query = SELECT destination FROM forwardings WHERE source='%s'
query = SELECT quota FROM users WHERE email='%s'
query = SELECT PRINTF("%s/%s/", SUBSTR(email,INSTR(email,'@')+1) , SUBSTR(email,0,INSTR(email,'@')) ) FROM users WHERE email='%s'
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
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 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
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
Next we edit or create if does not exist,
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
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 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,
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
edit the files
pop3d.cnf changing the following line in both files:
Then recreate the certificates:
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
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.