Pulling mailserver data from MySQL

Posted by

My first time setting up a Postfix mail server was on a FreeBSD system, and I found the process complex and badly documented. So I tried to limit the complexity by using text files rather than a database for the mailbox tables. That’s a pity: for the last few years I’ve had MySQL connected to Postfix and the whole thing is simple, bombproof, functional even with large numbers of users, and best of all there’s no need to restart Postfix each time you make a change. In fact the obvious thing to do would be to write scripts in Perl or PHP which updated that database to add users or virtual mailboxes – so obvious that Michael teases me every time he realises that I haven’t quite got around to doing that yet.

Disclaimer: what follows is intended to turn an already working postfix/courier installation into one based on mysql, not to install such a system from scratch.

Here’s an overview of the system:

  • Set up the database
  • Configure Postfix normally for virtual mailbox domains, but rather than using a text file like 
    hash:/etc/postfix/vmailbox

    we specify a short config file which tells Postfix how to access the database – what table to use, which columns, and what credentials, and it sucks everything out of the database.

  • Tell saslauthd to use a database lookup to authenticate access to smtp.
  • I’m using Courier as an SSL POP server. In another article I’ll describe how to set up the certificates it’s straighforward and they’re now cheap enough (if you look around) that there’s no need to stick with self-signed. For now, though, Courier is set up normally except you configure it to look in the same tables for user info.

Detail, then.

Database

It makes sense to start with the database, because that will orient everything else. Create a database, a user with permissions on that database, and some tables as below.

mysql> create database maildb;
mysql> grant all on maildb.* to 'mail'@'localhost' identified by '<password>';

–> make the tables as below

mysql> describe users;
+---------+--------------+------+-----+-------------------------+-------+
| Field   | Type         | Null | Key | Default                 | Extra |
+---------+--------------+------+-----+-------------------------+-------+
| id      | varchar(128) | NO   | PRI | NULL                    |       |
| name    | varchar(255) | YES  |     | NULL                    |       |
| home    | varchar(128) | YES  |     | NULL                    |       |
| crypt   | varchar(128) | YES  |     | NULL                    |       |
| domain  | varchar(128) | YES  |     | NULL                    |       |
| uid     | int(5)       | YES  |     | 5000                    |       |
| gid     | int(5)       | YES  |     | 5000                    |       |
| maildir | varchar(128) | YES  |     | /var/spool/mail/virtual |       |
+---------+--------------+------+-----+-------------------------+-------+
mysql> describe virtual;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| address | varchar(255) | NO   | PRI | NULL    |       |
| goto    | varchar(255) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
mysql> describe domains;
+-----------+--------------+------+-----+----------+-------+
| Field     | Type         | Null | Key | Default  | Extra |
+-----------+--------------+------+-----+----------+-------+
| domain    | varchar(128) | NO   | PRI | NULL     |       |
| transport | varchar(128) | YES  |     | virtual: |       |
+-----------+--------------+------+-----+----------+-------+
mysql> describe bcc;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| address | varchar(128) | NO   | PRI | NULL    |       |
| sendto  | varchar(128) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

–> here’s a typical row in each table

mysql> select * from domains;
+-------------------------------+-----------+
| domain                        | transport |
+-------------------------------+-----------+
| ponderwell.net                | virtual:  |
+-------------------------------+-----------+
mysql> select * from bcc;
+------------------------------+-------------------------+
| address                      | sendto                  |
+------------------------------+-------------------------+
| [email protected]      | [email protected]    |
+------------------------------+-------------------------+
mysql> select * from users;
+---------------------------+------------+---------------+---------------+-----------------+------+------+-------------------------+
| id                        | name       | home          | crypt         | domain          | uid  | gid  | maildir                 |
+---------------------------+------------+---------------+---------------+-----------------+------+------+-------------------------+
| [email protected]  | enquiries  | enquiries/    | BSsoiBZJXfAvw | ponderwell.net  | 5000 | 5000 | /var/spool/mail/virtual |
+---------------------------+------------+---------------+---------------+-----------------+------+------+-------------------------+
mysql> select * from virtual;
+------------------------+--------------------------+
| address                | goto                     |
+------------------------+--------------------------+
| [email protected] | [email protected] |
+------------------------+--------------------------+

Don’t forget that the insert statement for users will read something like:

mysql> insert into users values ('[email protected]','enquiries','enquiries',ENCRYPT('password'),5000,5000, '/var/spool/mail/virtual');

Also, in the working system, send each new user a test email so as to get postfix to create that user’s mail store.

Postfix

For Postfix we have the following in postfix/main.cf:

# mysql stored virtual mailbox domains

virtual_mailbox_base = /var/spool/mail/virtual
virtual_mailbox_domains = mysql:/etc/postfix/mysql_domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql_mailbox.cf
virtual_alias_maps = mysql:/etc/postfix/mysql_virtual.cf
virtual_uid_maps = mysql:/etc/postfix/mysql_uid.cf
virtual_gid_maps = mysql:/etc/postfix/mysql_gid.cf
virtual_transport = virtual:

# send copy of some mail to external server - this is primarily to put a copy of POP mail on some people's phones

recipient_bcc_maps = mysql:/etc/postfix/mysql_bcc.cf

What’s in these mysql .cf files? Just the credentials and column names which postfix will use to pull the usual data but from mysql instead of a text file. Here is /etc/postfix/mysql_mailbox.cf

user=mail
password=<password>
dbname=maildb
table=users
select_field=home
where_field=id
hosts=127.0.0.1

and likewise mysql_domains.cf and mysql_uid.cf

user=mail
password=<password>
dbname=maildb
table=domains
select_field=domain
where_field=domain
hosts=127.0.0.1
user=mail
password=<password>
dbname=maildb
table=users
select_field=uid
where_field=id
hosts=127.0.0.1

While we’re here, make sure the saslauthd is being called.

smtpd_sasl_auth_enable = yes
smtpd_sasl_security_options = noanonymous
broken_sasl_auth_clients = yes
smtpd_sasl_path = smtpd

Saslauthd

We’ll use saslauthd to mediate smtp authentication for postfix. Edit /etc/pam.d/smtp which should read like this:

auth required pam_mysql.so user=mail passwd=<password> host=127.0.0.1 db=maildb table=users usercolumn=id passwdcolumn=crypt crypt=1
account sufficient pam_mysql.so user=mail passwd=<password> host=127.0.0.1 db=maildb table=users usercolumn=id passwdcolumn=crypt crypt=1

Now for the sasl conf, which is normally in postfix/sasl/smtpd.conf

pwcheck_method: saslauthd
mech_list: PLAIN LOGIN
allow_plaintext: true
auxprop_plugin: mysql
sql_hostnames: 127.0.0.1
sql_user: mail
sql_passwd: <password>
sql_database: maildb
sql_select: select crypt from users where id = '%u'
log_level: 100

Courier

Now Courier, in this case pop3d-ssl. I don’t see any point in allowing non-ssl login. The file courier/authdaemonrc needs this line:

authmodulelist="authmysql"

while the file courier/authmysqlrc tells courier where to find everything in the database. The significant lines read:

MYSQL_SERVER            localhost
MYSQL_USERNAME          mail
MYSQL_PASSWORD          <password>

# MYSQL_SOCKET          /var/run/mysqld/mysqld.sock

MYSQL_DATABASE          maildb

# MYSQL_CHARACTER_SET latin1

MYSQL_USER_TABLE        users

MYSQL_CRYPT_PWFIELD     crypt
#MYSQL_CLEAR_PWFIELD    clear

MYSQL_UID_FIELD         uid
MYSQL_GID_FIELD         gid
MYSQL_LOGIN_FIELD       id
MYSQL_HOME_FIELD        maildir
MYSQL_NAME_FIELD        name
MYSQL_MAILDIR_FIELD     concat(maildir,'/',home)

that maildir field is the tricky part, honestly.

Time to restart everything!

/etc/init.d/postfix restart
/etc/init.d/saslauthd restart
/etc/init.d/courier-pop-ssl

Dropbox and KeePass: the password mashup

Posted by

Mashup: Dropbox and KeepassLet’s face it, working with clients with complex networking infrastructures is a pain.  As a contractor, you are offsite, outside of the normal water-cooler knowledge system.  You don’t hear that XYZ website forced John to change the password to the company’s twitter feed.

In a perfect world, every auxiliary system your clients would use would allow access to the same data from two discreet accounts,  Google does a good job of this with Analytics, Facebook even allows multiple people to manage fan pages as admins, but there are quite a few sites or services that simply allow 1 user.  Network equipment is another class of systems that quite frequently doesn’t allow you to have a discreet account.

My solution is to keep a Dropbox share with each client team (useful for many other reasons), and keep a Keepass password file in that share.

Dropbox is a free utility that syncs data from your computers to the cloud, and allows you to share that data between computers.  It also allows users to share folders with other users, allowing it to be a big, cloudbased network drive.

Keepass is a free, opensource password manager.  You can use Keepass and it’s variants  (KeepassX for mac/linux) on the same password file, so if you are using Windows, you can still share passwords with your client on a mac, or even your strange uncle running Linux.  Keepass encrypts all the passwords stored inside it into a file that is locked up safe behind a “Master Password”

Now the mashup:  You keep one Dropbox share with each client team with which you work.  This is useful for moving files with just them.  I’ve never had a client that once they “caught on” to what Dropbox does, hasn’t begun using it in every way they can.

Inside your Dropbox share with the client, place your Keepass password file.  I usually prestock this with every “shared” password I know about concerning the client.  Then show them Keepass.  With small teams, it usually takes off like wildfire, everyone is tired of sticky notes or being locked out of something dreadfully important when Jane is on vacation.

Keepass has a “Bookmarks” tab, much like a browser, so I create a bookmark for each client.

Security: Whenever possible don’t use shared passwords.  It’s bad form, for many reasons.  But I’m a realist, I know there is a whole class of passwords that can’t help being shared.   At least this is better than sticky notes.  Make sure you and your team only put these kind of passwords in your shared KeePass file, and not credentials to your personal accounts.

The one hitch: KeePass is a single user system, so only one person can edit it at the same time.  But, to the rescue, it does create a lock file and if you try to open a file that someone else is using, you get a warning that it’s in use, do you want to open read-only.   Since you are usually only grabbing some ancient credentials for an ftp site, and not adding entries, this isn’t a big deal.