Mailboy

/

UPDATE: A new version of Mailboy is in the works, it's even getting it's own micro-site at mailboy.manurevah.com. It's not completely ready yet, I'm working on that with the little time I have these days. I'll update this notice when Mailboy will be considered "ready".

Ready.

Latest: version 0.5

changelog:
0.5
New ACL white/blacklisting
0.4.6
Added template "purplehaze"
0.4.5.6
minor stuffs like regex comments and stuff i may have forgot to update before.. .oops : ]
0.4.5.4
fixed bug: can modify aliases to become catchall. added link titles to funky tandy theme
0.4.5.3
corrected the link to this page in one of the templates because Jan was going to bug me about this forever.. : ]
0.4.5.2
fixed bug where a server admin could delete the main server admin's domain and hence account

I wrote Mailboy with the intent of this to be a complete web interface for administrating email accounts. It uses a setup with Postfix, Dovecot and Amavis based on the great Workaround.org ISP style tutorial. This web administration interface can work if you have the Etch setup or the Lenny setup (instructions included below).

Mailboy features:

  • Server admin users - users that are "admin" may do anything
  • Per domain admins - a user may be admin for just one or more specific domains
  • Users may change their password and Amavis policy
  • Manage relay domains - so you can become backup MX for others
  • Easy to theme - The appearance can be completely taken care of, 3 themes are included
  • Released under GNU General Public License

The modifications I made to the Workaround tutorial setup:

  • Database: entries must be unique (aliases, domains)
  • Database (lenny): I brought back the "views" because I prefer that
  • Database: foreign keys are all over the place to make things easier to maintain
  • Database: added tables for Relay domains, per user amavis settings and quota
  • Database: create mailadmin user
  • Database: other changes made for the admin interface to work
  • Postfix: Mysql queries in configuration needs to be changed
  • Postfix: Add Mysql file and configuration for Relay domains (optional)
  • Dovecot: Mysql queries in configuration needs to be changed
  • Dovecot: Modify configuration to add per user quota (optional)
  • Amavis: Add option to change subject for spam (applies when user policy says "modify subject Y)
  • Amavis: Edit configuration file to enable per user scan policies

Why so many changes ?

One reason is that the database layout on Etch edition used to please me, now for performance reasons Christoph Haas decided to go without using views, I'm sure that makes a huge difference on big setups but for me it means that the data can possibly be incoherent. That also goes for the domain and alias tables, they have unique keys to ensure there are no duplicate entries. All this means the database scheme is a bit different and so the programs need to query Mysql in a tiny bit different way, it's not that complicated.

Another reason was to enable per user Amavis scanning, meaning you can setup a few policies and users may chose which one they want. This includes settings such as Tag levels, or bypassing certain tests. Users should be able to chose to not have their emails scanned or not have certain checks performed, it can be useful when a user is trying to receive a particular email with an attachment, users can deactivate file checks by him/her-self.

Modifying a server based on the Workaround tutorial does not take long, if you include careful reading and creating backup files as you go this should take less than 30 minutes.

What does this look like ?

A few screenshots will better describe this:

Ok, lets do it !!

PLEASE TRY THIS ON A TEST SERVER FIRST, you can try it out using a test server be it a real one or via something like VirtualBox. You should always do this when installing any software on a production server. This means that you can possibly break your email server if you do not know what you are doing and are not patient. So take your time in a test environment.. If you are working on an email server I assume you know what you are doing anyways. :]

First thing

You will need a mail server that should be based on the Workaround.org ISP style email tutorial (Don't worry if you are still using the Etch version, it'll in fact be even easier). If you have not installed your mail server than follow the tutorial, it's a good document in that the instructions are precise yet complete, it doesn't lack explanations (so you can actually learn what's going on) and mostly it works just fine. You can for sure manage to use Mailboy in other situations but for that you are on your own (unless you hire me). :]

This page contains information for upgrading from Etch tutorial and Lenny tutorial, all steps are for both systems unless they are color coded as follows:

This is only for Etch tutorial
This is only for Lenny tutorial

Everything else is for both

DB operations

We need to change the way the database looks and add new tables, the simplest way is to (you should know how to do this):

  1. Export/backup the required data (virtual_domains, virtual_users, virtual_aliases)
  2. Drop all the tables on database 'mailserver'
  3. Create new database scheme

    For this there is an included file called database.sql which basically can be copy pasted to PhpMyAdmin or to Mysql shell...

  4. Insert old data
    The data from Etch tutorial should be compliant with the new database structure unless there are double entries.
    On Lenny tutorial user is called "email", we'll need to replace email by user. like this: INSERT INTO `virtual_users` (`id`, `domain_id`, `password`, `email`) VALUES should become: INSERT INTO `virtual_users` (`id`, `domain_id`, `password`, `user`) VALUES We also need to replace things like user@domain.tld to just user. With a text editor you can find and replace, using vim you can use: :%s/@.*'/'/

    The same issue occurs with the table virtual_aliases, the source field should be without the "@domain.tld" part, use the vim trick to get rid of that stuff

  5. Add mailadmin user.

    This user can read/write/delete from the mailserver database, it is needed by Mailboy. From within mysql you may simply type (replace password of course!)

    GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES , CREATE VIEW , SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON mailserver . * TO 'mailadmin'@'localhost' IDENTIFIED BY 'crazy_secure_password'; FLUSH PRIVILEGES ;
  6. IMPORTANT NOTE: If you get an error like ""constraint exists blah blah" it is probably just because the data is not inserted in the correct order (and so an email cannot exist unless the domain it's referenced to by domain_id exists). Just insert data in this order:
    1. virtual_domains
    2. virtual_users
    3. virtual_aliases

Postfix configuration

Lenny needs a few minor changes, basically we'll need to change the query lines in the following files:
  1. /etc/postfix/mysql-virtual-mailbox-maps.cf query = SELECT 1 FROM view_users WHERE email='%s'
  2. /etc/postfix/mysql-virtual-alias-maps.cf SELECT destination FROM view_aliases WHERE email='%s'
  3. /etc/postfix/mysql-email2email.cf SELECT email FROM view_users WHERE email='%s'

To add the Relay domain functionality add the new file /etc/postfix/mysql-relay-domains.cf and inside write:

user = mailuser password = mailuser_password hosts = 127.0.0.1 dbname = mailserver query = SELECT domain FROM relay_domains WHERE domain like '%s'

change permissions

chown root:postfix /etc/postfix/mysql-relay-domains.cf chmod 640 /etc/postfix/mysql-relay-domains.cf

configure Postfix to use the new file

postconf -e relay_domains=mysql:/etc/postfix/mysql-relay-domains.cf postconf -e relay_recipient_maps=

If you want to scan aliases addressed to accounts on this server according to the destination account's Amavis settings we should remove the no_address_mappings from main.cf, and add it to master.cf.

comment or delete from main.cf:

# receive_override_options = no_address_mappings

add in master.cf:

-o receive_override_options=no_header_body_checks,no_unknown_recipient_checks,no_address_mappings

NEW (0.5): We need to add a couple more files and edit main.cf again to use the new ACL settings. This will allow the admin to block or unblock IPs and/or email addresses.

NOTE: If you are upgrading from 0.4.X please read the included "UPGRADE" file for instructions on updating Mailboy's config and database (easy).

Edit /etc/postfix/mysql-smtpd_sender_restrictions.cf:

user = mailuser password = mailuser_password hosts = 127.0.0.1 dbname = mailserver query = SELECT state FROM domain_access WHERE domain = '%s'

Edit /etc/postfix/mysql-ip-access.cf:

user = mailuser password = mailuser_password hosts = 127.0.0.1 dbname = mailserver query = SELECT state FROM ip_access WHERE ip = '%s'

Change permissions:

chmod 640 /etc/postfix/mysql-ip-access.cf chmod 640 /etc/postfix/mysql-smtpd_sender_restrictions.cf chown root:postfix /etc/postfix/mysql-ip-access.cf chown root:postfix /etc/postfix/mysql-smtpd_sender_restrictions.cf

Edit /etc/postfix/main.cf, add at the bottom:

# you should normally uncomment the following line #smtpd_helo_required = yes smtpd_helo_restrictions = permit_mynetworks, check_client_access mysql:/etc/postfix/mysql-ip-access.cf, reject_non_fqdn_helo_hostname, reject_invalid_helo_hostname # check twice because otherwise allowed IPs may not pass the second test smtpd_client_restrictions = permit_mynetworks, check_client_access mysql:/etc/postfix/mysql-ip-access.cf, reject_unknown_client_hostname smtpd_sender_restrictions = mysql:/etc/postfix/mysql-smtpd_sender_restrictions.cf

Because we modified some things you might want to restart Postfix

/etc/init.d/postfix restart

Dovecot

On Lenny edition edit /etc/dovecot/dovecot-sql.conf and change the password_query line to:

password_query = SELECT email as user, password FROM view_users WHERE email='%u';

If you want to use per user quota settings (optional) you should add just under the previous line (this assumes you store the emails in /var/vmail, otherwise change that):

user_query = SELECT CONCAT('/var/vmail/',CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1))) AS home, 5000 AS uid, 5000 AS gid, CONCAT('maildir:storage=',quota_kb,':messages=',quota_messages) AS quota FROM view_users WHERE email='%u';

(optional)To enable per user quota settings we need to just add the user_query line to /etc/dovecot/dovecot-sql.conf (this assumes mails are stored in /home/vmail, otherwise change this part)

user_query = SELECT CONCAT('/home/vmail/',CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1))) AS home, 5000 AS uid, 5000 AS gid, CONCAT('maildir:storage=',quota_kb,':messages=',quota_messages) AS quota FROM view_users WHERE email='%u';

To enable per user quota we also need to edit /etc/dovecot/dovecot.conf. We need to search for the sections protocol imap { and add:

mail_plugins = quota imap_quota

and protocol pop3 { mail_plugins = quota

and protocol lda { mail_plugins = cmusieve quota

If you are using the Etch tutorial you may still be using the Lenny version Debian. However I'm not sure the quota stuff will work on Dovecot on Debian Etch

You may now restart Dovecot

/etc/init.d/dovecot restart

Amavis

This is where we tell Amavis to actually use the policy table in the database, if you don't do this part everything should still work but users may not change their scan policies

So let's edit /etc/amavis/conf.d/50-user and change or add a few things (if the setting is not in the file then you may add it)

  1. change #$sa_spam_subject_tag = undef; to: $sa_spam_subject_tag = 'SPAM:';
  2. change the $sql_select_policy to $sql_select_policy = 'SELECT * FROM view_users_policies WHERE email IN (%k)';
  3. change or add $sql_select_white_black_list, it should be: $sql_select_white_black_list = undef;

We can finally install Mailboy

At this point your server should be working as usual, so make sure that it works before continuing.

Now you'll need to download Mailboy, for that you may click here. Unpack it where you wish to use it (you should be fluent with this kind of thing).

Copy the example database configuration file db_config.php.example to db_config.php

  1. $db_user: in this case should be "mailadmin"
  2. $db_pass: the crazy secure password
  3. $db_host: in most cases "localhost" should work
  4. $db_database: unless you changed something: mailserver
  5. $super_dooper_user: this sould be set to the id of the master user. This prevents other server admins from modifying this account or managing server admins.

Then copy the example configuration file config.php.example to config.php and change a few things:

  • $mailboy_root: this is the path to the Mailboy install, if you installed Mailboy at the root of your web directory then this should be empty ("")

Other options are:

  • $template: yes, it's completely templatabel :]
  • $privacy_level: 0 is secure, however if you want to allow others to list/add/edit/delete certain things you can change this setting (more info in the config file)

Any user can already log into Mailboy and change their passwords, however there are no admins yet, you should start by manually adding yourself as admin. You can do this via phpMyAdmin or via Mysql (in this case the target user id is 42:

UPDATE virtual_users SET `admin` =1 WHERE id = 42;

Now the user with the id 42 may log in as server admin, the user may now define per domain admins, policies etc etc etc..

I worked quite a while on this after getting some positive feedback on the Bash script that serves this same purpose, so any comments are welcome (via the comment form or the "contact" page).. I hope this project is useful to others. : ]