转发一篇文章post+mysql+courier-imap

fastopen

Introduzione
La nostra organizzazione, in linea con la politica che vuole portare tutto sotto FreeBSD, da tempo cercava un sistema per togliere dal server Windows il servizio di mailserver. Fra parentesi l'applicativo che usiamo si è dimostrato perfettamente efficiente (era il sistema operativo che costantemente dava problemi) e cosa non da poco ci permetteva di delegare la gestione della posta elettronica direttamente al cliente. E in fondo questa era l'esigenza principale. La soluzione doveva necessariamente appoggiarsi a un database, in modo da poter implementare un set di interfacce web per la gestione. Avendo poca dimestichezza di mail in ambiente unix, abbiamo esplorato tutta una serie di howto che però erano carenti in alcuni punti della spiegazione e quindi non siamo riusciti nell'intento.



La guida "giusta"
L'howto che abbiamo alla fine utilizzato con successo l'ho trovato su www.high5.net/howto.
Siccome poi, gli howto ogni tanto spariscono, riporto qui sotto la pagina web che ho usato (aggiornata al 30 giugno 2003) con alcune annotazioni personali relative a degli aggiustamenti per farlo funzionare con FreeBSD4.6



How-to di High5


Virtual Domains and Users w/ Postfix / Courier-IMAP / MySQL




Introduction

Disclaimer

MySQL Install

MySQL Setup



Create the database

Create the Tables



Create the Alias Table

Create the Domain Table

Create the Mailbox Table


Populate the Tables


Postfix Install

Postfix Setup



main.cf

mysql_virtual_alias_maps.cf

mysql_virtual_domains_maps.cf

mysql_virtual_mailbox_maps.cf


Courier-IMAP Install

Courier-IMAP Setup



authmysqlrc


Postfix Admin



Postfix Admin Download

Postfix Admin Install

Postfix Admin Setup

Postfix Admin Usage

Who is using Postfix Admin



Introduction

The document is written for Postfix 2.0 and higher.

This document describes how to setup Virtual Domains (Aliases and Mailboxes)
with Postfix, Courier-IMAP and MySQL. I have found that this is the easiest
combination that allows you to serve Virtual Domains, and Users.
With this it's also very easy to implement webmail systems like Squirrel Mail.

TODO: Expand this document with SASL.

NOTE: One thing that you have to keep in mind is that Courier-IMAP only supports
the Maildir format.


About the used software:

Postfix attempts to be fast, easy to administer, and secure, while at
the same time being sendmail compatible enough to not upset existing users.
Thus, the outside has a sendmail-ish flavor, but the inside is completely
different.


Courier-IMAP is a server that provides IMAP access to Maildirs.
This IMAP server does NOT handle traditional mailbox files (/var/spool/mail,
and derivatives), it was written for the specific purpose of providing
IMAP access to Maildirs.


The MySQL database server is the world's most popular open source database. Its
architecture makes it extremely fast and easy to customize. Extensive reuse of code
within the software and a minimalistic approach to producing functionally-rich
features has resulted in a database management system unmatched in speed,
compactness, stability and ease of deployment. The unique separation of the core
server from the table handler makes it possible to run with strict transaction control
or with ultra-fast transactionless disk access, whichever is most appropriate for the
situation.


Back to TOP


Disclaimer

This document assumes that you have some knowledge on Postfix, Courier-IMAP and MySQL.
At least enough to get everything installed. Installing the software is outside the scope of this document


Back to TOP


3. MySQL Install

Installation of MySQL is outside the scope of this document.
I'm using an out of the box MySQL install on FreeBSD.


Back to TOP

4. MySQL Setup

4.1 Create the database

Create a Postfix user and the Postfix database.
Use "mysql" or "mysql -p" to login to the MySQL Monitor.
USE mysql;INSERT INTO user (Host, User, Password) VALUES ('localhost','postfix',password('postfix'));INSERT INTO db (Host, Db, User, Select_priv) VALUES ('localhost','postfix','postfix','Y');CREATE DATABASE postfix;
Back to TOP

4.2 Create the Tables

In order to use MySQL with Postfix we need to create 3 tables.
All these tables have information for Postfix, and some for Courier-IMAP
as noted by each table. There are a couple of columns that are not used by
either Postfix or Courier-IMAP. These columns are:


domain (in some tables)

create_date

change_date

active

These columns are used to make your life easier together with Postfix Admin.
The "active" column is not used at the moment.


Back to TOP

4.2.1 Create the Alias Table
## Table structure for table alias#USE postfix;CREATE TABLE alias (  address varchar(255) NOT NULL default '',  goto text NOT NULL,  domain varchar(255) NOT NULL default '',  create_date datetime NOT NULL default '0000-00-00 00:00:00',  change_date datetime NOT NULL default '0000-00-00 00:00:00',  active tinyint(4) NOT NULL default '1',  PRIMARY KEY (address)) TYPE=MyISAM COMMENT='Virtual Aliases - mysql_virtual_alias_maps';
Postfix: is using the "address" and "goto" column.
Courier: is not using this table.

NOTE: This table can be used for virtual .foward files. This table is nothing more then /etc/aliases
that you will find on any *nix OS. Multiple destination email addresses need to be separated by a "," (comma).

Back to TOP

4.2.2 Create the Domain Table
## Table structure for table domain#USE postfix;CREATE TABLE domain (  domain varchar(255) NOT NULL default '',  description varchar(255) NOT NULL default '',  create_date datetime NOT NULL default '0000-00-00 00:00:00',  change_date datetime NOT NULL default '0000-00-00 00:00:00',  active tinyint(4) NOT NULL default '1',  PRIMARY KEY (domain)) TYPE=MyISAM COMMENT='Virtual Domains - mysql_virtual_domains_maps';


Postfix: is using the "domain" and "description" column.
Courier: is not using this table.


Back to TOP

4.2.3 Create the Mailbox Table

## Table structure for table mailbox#USE postfix;CREATE TABLE mailbox (  username varchar(255) NOT NULL default '',  password varchar(255) NOT NULL default '',  name varchar(255) NOT NULL default '',  maildir varchar(255) NOT NULL default '',  domain varchar(255) NOT NULL default '',  create_date datetime NOT NULL default '0000-00-00 00:00:00',  change_date datetime NOT NULL default '0000-00-00 00:00:00',  active tinyint(4) NOT NULL default '1',  PRIMARY KEY (username)) TYPE=MyISAM COMMENT='Virtual Mailboxes - mysql_virtual_mailbox_maps';


Postfix: is using the "username" and "maildir" column.
Courier: is using the "username, "password", "name" and "maildir" column.


Back to TOP

4.3 Populate the Tables

USE postfix;INSERT INTO domain (domain,description) VALUES ('domain.tld','Test Domain');INSERT INTO alias (address,goto) VALUES ('alias@domain.tld', 'user@domain.tld');INSERT INTO mailbox (username,password,name,maildir) \        VALUES ('user@domain.tld','$1$DOrHOomo$U6MleaFKGwWLB3iS9P1Yx0','Mailbox User','user@domain.tld/');


The first INSERT is to let Postfix know that this domain is a virtual domain and should
be handeled by Postfix. It's also possible to have everything in one table but I think
this is nicer.


The second INSERT is an virtual alias pointing to the third INSERT.

The third INSERT is an actual Virtual Mailbox, as you can see I'm using MD5 password
for backwards compatibility with local defined mail accounts. If you are using MD5 passwords,
make sure you don't use the builtin MySQL routine to generate MD5 passwords.
This is not compatible with Courier-IMAP. If you want you can also use cleartext or encrypted passwords.
To make sure that the new MySQL users are working, do the following from the command line.

% mysqladmin reload


Back to TOP

5. Postfix Install

Build and Install Postfix 2.x, or the latest snapshot. Make sure that you at least build it with MySQL.
I have build everything in FreeBSD and the default location is /usr/local/etc/postfix.
Your configuration might be different.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# portupgrade -N postfix
Come opzioni seleziono SASL e MySQL
Come opzioni addizionali di SASL ho scelto NDBM, MySQL, SASLAUTHD (che dovrebbero essere i default)
You need user "postfix" added to group "mail". Would you like me to add it [y]? y
Would you like to activate Postfix in /etc/mail/mailer.conf [n]? y

Dopo che la compilazione è finita, ho aggiunto la seguente riga al mio /etc/rc.conf:
sendmail_enable="NONE"

Poi creo il seguente link simbolico:
# cd /usr/local/etc/rc.d
# ln -s /usr/local/sbin/postfix postfix.sh

Edito (o creo) il file /etc/periodic.conf e ci inserisco le seguenti istruzioni:
daily_clean_hoststat_enable="NO"
daily_status_mail_rejects_enable="NO"
daily_status_include_submit_mailq="NO"
daily_submit_queuerun="NO"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

After that you have to create a directory to have all your virtual users mail dropped in, this directory needs
to be owned by Postfix.


% mkdir /usr/local/virtual% chown -R postfix:postfix /usr/local/virtual% chmod -R 751 /usr/local/virtual


Back to TOP

6. Postfix Setup

6.1 main.cf

The below example is the part that goes into your main.cf file of Postfix.
The path to the mysql files might be different on your setup. The same might be for
uid_maps and gid_maps values. These values should be the ones from the postfix user and group.
You can find these in your /etc/passwd file.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Nel mio caso la riga corrispondente è:
postfix:*:1008:1009ostfix Mail System:/var/spool/postfix:/sbin/nologin
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


virtual_alias_maps = mysql:/usr/local/etc/postfix/mysql_virtual_alias_maps.cfvirtual_uid_maps = static:1008virtual_gid_maps = static:1009virtual_mailbox_base = /usr/local/virtualvirtual_mailbox_domains = mysql:/usr/local/etc/postfix/mysql_virtual_domains_maps.cfvirtual_mailbox_maps = mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_maps.cfvirtual_mailbox_limit = 51200000virtual_transport = virtual


Back to TOP

6.2 mysql_virtual_alias_maps.cf

You will need to put this into a text file for postfix to pickup.


user = postfixpassword = postfixhosts = localhostdbname = postfixtable = aliasselect_field = gotowhere_field = address  


Back to TOP

6.3 mysql_virtual_domains_maps.cf

You will need to put this into a text file for postfix to pickup.


user = postfixpassword = postfixhosts = localhostdbname = postfixtable = domainselect_field = descriptionwhere_field = domain  


Back to TOP

6.4 mysql_virtual_mailbox_maps.cf

You will need to put this into a text file for postfix to pickup.


user = postfixpassword = postfixhosts = localhostdbname = postfixtable = mailboxselect_field = maildirwhere_field = username  


Back to TOP

7. Courier-IMAP Install

Build and Install Courier-IMAP, make sure that this is build with MySQL.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# portupgrade -N -m '-DWITH_MYSQL' courier-imap

Una volta compilato:
# cd /usr/local/etc/rc.d/
# mv courier-imap-imapd.sh.sample  courier-imap-imapd.sh
# mv courier-imap-pop3d.sh.sample  courier-imap-pop3d.sh

# cd /etc
# ln -s /etc/mail/aliases.db
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Back to TOP


8. Courier-IMAP Setup

8.1 authmysqlrc

NOTE: Make sure that there are no (trailing) spaces in this file, only tabs!!

The below is a part of the authmysqlrc file that is relevant to our setup.
The things that you might need to change are the default_domain, mysql_password,
mysql_uid and mysql_gid.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Il file è in /usr/local/etc/courier-imap/
Qui inizialmente i files hanno tutti estensione ".dist" che deve essere eliminata!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



#DEFAULT_DOMAIN                domain.tldMYSQL_CRYPT_PWFIELD        passwordMYSQL_DATABASE                postfixMYSQL_GID_FIELD                '1009'MYSQL_HOME_FIELD        '/usr/local/virtual'MYSQL_LOGIN_FIELD        usernameMYSQL_MAILDIR_FIELD        maildirMYSQL_NAME_FIELD        nameMYSQL_OPT                0MYSQL_PASSWORD                postfix#MYSQL_PORT                0#MYSQL_QUOTA_FIELD        quotaMYSQL_SERVER                localhost# Default FreeBSD Socket#MYSQL_SOCKET                /var/mysql/mysql.sock# Default RedHat Socket#MYSQL_SOCKET                /var/lib/mysql/mysql.sockMYSQL_UID_FIELD                '1008'MYSQL_USERNAME                postfixMYSQL_USER_TABLE        mailbox#MYSQL_WHERE_CLAUSE        server='mailhost.example.com'  


When you have trouble logging in make sure that:



localhost exists in your /etc/hosts file, otherwise change localhost for 127.0.0.1


there are NO spaces in this file only tabs


the mailbox exists, including cur/tmp/new


the /usr/local/virtual has the correct permissions


  Back to TOP

9. Postfix Admin

Here you can find the Postfix Admin tool that I have written in PHP. It's in
production at 2 sites that I maintain. If you want to install this make sure
that you have a WebServer running that handles PHP correctly
and is able to handle .htaccess fiels. I'm not going to answer any questions
on your WebServer / PHP install.


Postfix Admin is a way to give Virtual Domain owners total controll over thier domain.


Back to TOP

9.1 Postfix Admin Download

You can download Postfix Admin here.

Back to TOP

9.2 Postfix Admin Install

Unpack Postfix Admin in the directory where you want it. For example: /usr/local/www/<site>;/postfixadmin
There is also an Admin Admin part, change directory to the "admin" directory and change the path to the
.htpasswd file in the .htaccess file.


Some other information that you might want to look at is in the site_lib.php file.


In order to be able to read & write from the database I have created a seperate user in MySQL.
I do this because Postfix Admin needs to have more rights on the Postfix database.
If you are worried about the password for the database. I have Postfix Admin running as the
WebServer owner:group, that way your postfix username and password are somewhat protected against local users.

USE mysqlINSERT INTO user (Host, User, Password) VALUES ('localhost','postfixadmin',password('postfixadmin'));INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv) VALUES ('localhost', 'postfix', 'postfixadmin', 'Y', 'Y', 'Y', 'Y');
Make sure you reload MySQL.
% mysqladmin reload
Back to TOP

9.3 Postfix Admin Setup

In order to use Postfix Admin you have to add another table to the Postfix database.

## Table structure for table admin#USE postfix;CREATE TABLE admin (  username varchar(255) NOT NULL default '',  password varchar(255) NOT NULL default '',  domain varchar(255) NOT NULL default '',  create_date datetime NOT NULL default '0000-00-00 00:00:00',  change_date datetime NOT NULL default '0000-00-00 00:00:00',  active tinyint(4) NOT NULL default '1',  PRIMARY KEY (username)) TYPE=MyISAM COMMENT='Virtual Admins - Store Virtual Domain Admins';
Back to TOP

9.4 Postfix Admin Usage

Once you have done all this you can take your browser and browse to the location
where you have Postfix Admin installed. Goto the admin part first in order to put
some real domains that you host in the database. http://your.domain.tld/postfixadmin/admin
The default login is admin / admin.


Back to TOP

9.5 Who is using Postfix Admin

            

BSD WebSolutions


EuroSoftwareParks


ILYA


workAV


YazzY.org


shikoku.gods.de


Misamis University - Ozamiz City, Philippines


Back to TOP

This document was written by Mischa Peters. You can reach me at mischa at high5 dot net.
Let me know if you like it.

Version 0.9 - 2003/06/23
2003 &amp; High5!