Virtual Users and Domains with Courier-IMAP and MySQL


Virtual Users and Domains with Courier-IMAP and MySQL



From Postfix Wiki








Contents

[hide]




Introduction


This 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 SquirrelMail.


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.


SASL is the Simple Authentication and Security Layer, a method for adding authentication support to connection-based protocols. To use SASL, a protocol includes a command for identifying and authenticating a user to a server and for optionally negotiating protection of subsequent protocol interactions. If its use is negotiated, a security layer is inserted between the protocol and the connection.


Postfix VDA enables quota support for Postfix.



If you are planning to use this howto as a basis for Postfix Admin, please be aware that there is some differences in the tables.


Please read the TABLE_CHANGES.TXT



Disclaimer


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



MySQL Install


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


On slackware 10 I do this way:

% chown mysql.mysql /var/lib/mysql/ -R && mysql_install_db && sh /etc/rc.d/rc.mysqld start
&& mysql_secure_installation && chown mysql.mysql /var/lib/mysql/ -R && mysqld_safe –user=mysql&
&& mysqladmin -u root password ‘sua_senha, your password’


MySQL Setup



Create the database

% mysqladmin -u root –password=’sua_senha, your password’ create postfix

These columns are used to make your life easier together with Postfix Admin:



  • created
  • modified
  • active

The “active” column is not used at the moment.



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 ”,
`created` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`modified` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`active` tinyint(1) NOT NULL default ‘1’,
PRIMARY KEY (address)
) TYPE=MyISAM COMMENT=’Postfix Admin – Virtual Aliases’;

Postfix: is using the “address” and “goto” column. Courier: is not using this table.


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



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 ”,
`aliases` int(10) NOT NULL default ‘0’,
`mailboxes` int(10) NOT NULL default ‘0’,
`maxquota` int(10) NOT NULL default ‘0’,
`transport` varchar(255) default NULL,
`backupmx` tinyint(1) NOT NULL default ‘0’,
`created` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`modified` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`active` tinyint(1) NOT NULL default ‘1’,
PRIMARY KEY (domain)
) TYPE=MyISAM COMMENT=’Postfix Admin – Virtual Domains’;


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



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 ”,
`quota` int(10) NOT NULL default ‘0’,
`domain` varchar(255) NOT NULL default ”,
`created` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`modified` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`active` tinyint(1) NOT NULL default ‘1’,
PRIMARY KEY (`username`)
) TYPE=MyISAM COMMENT=’Postfix Admin – Virtual Mailboxes’;

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



Populate the tables

USE postfix;
INSERT INTO domain (domain,description) VALUES (‘domain.tld’,’Test Domain’);
INSERT INTO alias (address,goto) VALUES (‘[email protected]’, ‘[email protected]’);
INSERT INTO mailbox (username,password,name,maildir) VALUES (‘[email protected]’,’$1$caea3837$gPafod/Do/8Jj5M9HehhM.’,’Mailbox User’,’[email protected]/’);

The password (MD5 encrypted) is “secret” ($1$caea3837$gPafod/Do/8Jj5M9HehhM.)


This is a “standard” MD5 encrypted password out of /etc/passwd.


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


The second INSERT is a 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 built in MySQL routine to generate MD5 passwords. This is not compatible with Courier-IMAP. If you want you can also use clear text or encrypted passwords.


To make sure that the new MySQL users are working, do the following from the command line.

% mysqladmin -u root –password=’sua_senha, your password’ reload


Postfix Install


Build and install Postfix 2.x, or the latest snapshot. Make sure that you at least build it with MySQL & Postfix VDA (quota). Apply patch like this:

% zcat postfix-x.x.x-vda.patch.gz | patch -p0

I built everything in FreeBSD and the default location is /usr/local/etc/postfix. Your configuration might be different.


RedHat 9 (including SASL):

% make makefiles ‘CCARGS=-DHAS_MYSQL -I/usr/include/mysql -DUSE_SASL_AUTH -I/usr/include/sasl’
‘AUXLIBS=-L/usr/lib/mysql -lmysqlclient -lz -lm -L/usr/lib -lsasl’

% make; make install;


Slackware 10 (need cyrus-sasl-mysql from LinuxPackages):

% make makefiles ‘CCARGS=-DHAS_MYSQL -I/usr/include/mysql -DUSE_SASL_AUTH -I/usr/include/sasl’
‘AUXLIBS=-L/usr/lib/mysql -lmysqlclient -lz -lm -L/usr/lib -lsasl2’

% make; make install;


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 771 /usr/local/virtual


Postfix Setup



main.cf


The example below 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, gid_maps and minimum_uid values. These values should be the ones from the postfix user and group. You can find these in your /etc/passwd file.

virtual_alias_maps = mysql:/usr/local/etc/postfix/mysql_virtual_alias_maps.cf
virtual_gid_maps = static:1001
virtual_mailbox_base = /usr/local/virtual
virtual_mailbox_domains = mysql:/usr/local/etc/postfix/mysql_virtual_domains_maps.cf
virtual_mailbox_limit = 51200000
virtual_mailbox_maps = mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_maps.cf
virtual_minimum_uid = 1001
virtual_transport = virtual
virtual_uid_maps = static:1001
# Additional for quota support
virtual_create_maildirsize = yes
virtual_mailbox_extended = yes
virtual_mailbox_limit_maps = mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_limit_maps.cf
virtual_mailbox_limit_override = yes
virtual_maildir_limit_message = Sorry, the user’s maildir has overdrawn his diskspace quota, please try again later.
virtual_overquota_bounce = yes

User comment: virtual_gid_maps and virtual_uid_maps should be set to your ‘postfix’ gid/uid. You may also have to set virtual_minimum_uid which should be no more than virtual_uid_maps, as well.


If you want to use MySQL also to store your Backup MX domains add this as well

relay_domains = mysql:/usr/local/etc/postfix/mysql_relay_domains_maps.cf

Performance and reliability under high load will be much improved if you use the Postfix proxymap service with your MySQL interface. This allows MySQL query connections to be shared among Postfix smtpd processes; without it, you will need much higher-end database hardware as Postfix will need to spawn a number of SQL connections for every smtpd or cleanup process. This problem typically only shows up under high load, just when you least want to see it.


To access MySQL via proxymap, change the MySQL maps lines above to read:

virtual_alias_maps = proxy:mysql:/usr/local/etc/postfix/mysql_virtual_alias_maps.cf
virtual_mailbox_domains = proxy:mysql:/usr/local/etc/postfix/mysql_virtual_domains_maps.cf
virtual_mailbox_maps = proxy:mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_maps.cf

and

relay_domains = proxy:mysql:/usr/local/etc/postfix/mysql_relay_domains_maps.cf

To proxy your virtual_mailbox_limit_maps queries, you must add the map to the proxy_read_maps variable, as that map gets added via the VDA quota patch and is not included in the list of maps which Postfix will automatically allow to be proxied:

virtual_mailbox_limit_maps = proxy:mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_limit_maps.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


master.cf


If you plan to run your postfix chrooted, be careful not to run virtual chrooted.


If virtual is running chrooted your mysql_*.cf will not be found by postfix.
Example for such an error:

postfix/virtual[8431]: fatal: open /etc/postfix/mysql_virtual_mailbox_maps.cf: No such file or directory

NOTE: for SuSE Systems have a look at #SuSE_Linux_note

# ==========================================================================
# service type private unpriv chroot wakeup maxproc command + args
# (yes) (yes) (yes) (never) (100)
# ==========================================================================
smtp inet n – y – – smtpd
[…]
virtual unix – n n – – virtual
[…]


mysql_virtual_alias_maps.cf


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

user = postfix
password = postfix
hosts = localhost
dbname = postfix
table = alias
select_field = goto
where_field = address

Syntax with postfix 2.2.x:
user = postfix
password = postfix
hosts = localhost
dbname = postfix
query = SELECT goto FROM alias WHERE address=’%s’ AND active = 1



mysql_virtual_domains_maps.cf


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

user = postfix
password = postfix
hosts = localhost
dbname = postfix
table = domain
select_field = domain
where_field = domain
#additional_conditions = and backupmx = ‘0’ and active = ‘1’

Syntax with postfix 2.2.x:
user = postfix
password = postfix
hosts = localhost
dbname = postfix
query = SELECT domain FROM domain WHERE domain=’%s’
#optional query to use when relaying for backup MX
#query = SELECT domain FROM domain WHERE domain=’%s’ and backupmx = ‘0’ and active = ‘1’



mysql_virtual_mailbox_maps.cf


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

user = postfix
password = postfix
hosts = localhost
dbname = postfix
table = mailbox
select_field = maildir
where_field = username
#additional_conditions = and active = ‘1’

Syntax with postfix 2.2.x:
user = postfix
password = postfix
hosts = localhost
dbname = postfix
query = SELECT maildir FROM mailbox WHERE username=’%s’ AND active = 1



mysql_virtual_mailbox_limit_maps.cf


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

user = postfix
password = postfix
hosts = localhost
dbname = postfix
table = mailbox
select_field = quota
where_field = username
#additional_conditions = and active = ‘1’

Syntax with postfix 2.2.x:
user = postfix
password = postfix
hosts = localhost
dbname = postfix
query = SELECT quota FROM mailbox WHERE username=’%s’



mysql_relay_domains_maps.cf


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

user = postfix
password = postfix
hosts = localhost
dbname = postfix
table = domain
select_field = domain
where_field = domain
additional_conditions = and backupmx = ‘1’

Syntax with postfix 2.2.x:
user = postfix
password = postfix
hosts = localhost
dbname = postfix
query = SELECT domain FROM domain WHERE domain=’%s’ and backupmx = ‘1’



For security you should do

chmod 640 mysql_*
chgrp postfix mysql_*

User question/comment: Read last line here: http://www.postfix.org/faq.html#virtual_command. On RedHat based systems chmoding to 640 on mysql_* files might cause virtual errors like:

postfix/virtual[22114]: fatal: open /etc/postfix/mysql_virtual_mailbox_maps.cf: Permission denied

In this case try:

chmod 644 mysql_*


MySQL note


Tip for chroot/jailed enviroment (default in Debian, Ubuntu and others).


Using “localhost” means that a local socket will be used to connect to mysql, which cause problems in a chroot’d environment. You must either create a link to the socket in the chroot environment or use hostname 127.0.0.1 to connect using TCP/IP instead. Postfix runs in a jailed enviroment in the /var/spool/postfix, and that’s why it cannot connect to mysql.



  • If you have skip-networking in your my.cnf that means mysql accept connections thru socket only. You need to create a link to the mysql.sock in the postfix jail. Just run this commands:
mkdir -p /var/spool/postfix/var/run/mysqld
chown mysql /var/spool/postfix/var/run/mysqld
ln /var/run/mysqld/mysqld.sock /var/spool/postfix/var/run/mysqld/mysqld.sock


  • If your mysql listen on the loopback interface (localhost), the way is to change all the hosts in /etc/postfix/mysql_*_maps.cf files from localhost to 127.0.0.1. Quote from mysql_table(5):
NOTE: if you specify localhost as a hostname (even if you prefix it with
inet:), MySQL will connect to the default UNIX domain socket. In order
to instruct MySQL to connect to localhost over TCP you have to specify
hosts = 127.0.0.1




SuSE Linux note


Tip for SuSE Users and SuSEconfig (done on SuSE 9.1 Pro)


For having SuSEconfig work with “master.cf” when running postfix chroot/jailed and not want to have “virtual” chroot/jailed apply the following patch to /sbin/conf.d/SuSEconfig.postfix
NOTE: make a backup of /sbin/conf.d/SuSEconfig.postfix but don’t leave your backup inside /sbin/conf.d, because SuSEconfig will then run SuSEconfig.postfix and you backup. :)

do the following as root:
cp -a /sbin/conf.d/SuSEconfig.postfix ~/SuSEconfig.postfix-orig

NOTE: this patch makes #MySQL note obsolete. Link for mysql.sock is created by SuSEconfig.postfix

— SuSEconfig.postfix-orig 2006-02-05 14:34:03.000000000 +0100
+++ SuSEconfig.postfix 2006-02-06 13:52:31.501589142 +0100
@@ -78,6 +78,8 @@
rm -rvf etc lib usr var proc
elif [ “$(echo “$POSTFIX_UPDATE_CHROOT_JAIL” | tr ‘A-Z’ ‘a-z’ )” != “no” ]; then
echo “checking postfix chroot environment…”
+ echo “removing mysql.sock”
+ rm -rvf var/lib/mysql

if rpmqpack pam_ldap &> /dev/null; then
cpifnewer /etc/openldap/ldap.conf etc/openldap
@@ -133,6 +135,27 @@
fi

chown -R root /var/spool/postfix/{etc,lib,usr,var}
+
+ # something for having postfix comunicate with mysql via socket
+ HERE=”/var/spool/postfix”
+ MS_DIR=”var/lib/mysql”
+ MYSQL_SOCKET=”/var/lib/mysql/mysql.sock”
+ CHR_MYSQL_SOCKET=”var/lib/mysql/mysql.sock”
+ [ ! -d $MS_DIR ] && {
+ mkdir -p $MS_DIR
+ chown mysql: $MS_DIR
+ }
+ if [ ! -S $MYSQL_SOCKET ]; then
+ warn_user “tMySQL has to be started to have mysql.sock linkedn
++tinto postfix chroot. Please start mysql and runn
++tSuSEconfig again!”
+ else
+ [ ! -e $CHR_MYSQL_SOCKET -a ! -S $CHR_MYSQL_SOCKET ] && {
+ #echo “does not exist, linking socket”
+ ln $MYSQL_SOCKET $HERE/$CHR_MYSQL_SOCKET
+ }
+ fi
+
fi
}

@@ -513,7 +536,7 @@

my $match = 0;
foreach my $serv ( ( “smtp”, “pickup”, “cleanup”, “qmgr”, “rewrite”,
– “bounce”, “defer”, “showq”, “error”, “virtual”,
+ “bounce”, “defer”, “showq”, “error”,
“lmtp”, “smtps”, “tlsmgr”, “localhost:10025” ) ) {
if( $line =~ /^$servs+/ ) {
$line =~ /(^$servs+w+s+[yn-]?s+[yn-]?s+)[yn-]?(.*)/;



SASL2 Install


The easiest is to get SASL2 compiled with Courier-IMAP authdaemon support. When doing this SASL2 will hand-off the authentication to authdaemond.
On FreeBSD: make install WITH_AUTHDAEMON=yes


The following has been built on Fedora Core 1-4 and has worked perfectly, if you have followed the directions above.


The best way to install SASL for Fedora is to use the following configure script. Change the paths for MySQL and Berkeley DB to reflect your paths.


Get SASL here: ftp://ftp.andrew.cmu.edu/pub/cyrus-mail/cyrus-sasl-2.1.19.tar.gz


Then get this patch: http://frost.ath.cx/software/cyrus-sasl-patches/dist/2.1.19/cyrus-sasl-2.1.19-checkpw.c.patch


Apply the patch (from INSIDE the cyrus-sasl-2.1.19 directory):

$ patch -p0 < ../cyrus-sasl-2.1.19-checkpw.c.patch

Install:

$ export CPPFLAGS=”-I/usr/local/mysql/include”

$ ./configure
–prefix=/usr/local/sasl2
–disable-cmulocal
–enable-sample
–enable-static=no
–enable-shared=yes
–enable-fast-install=yes
–without-gnu-ld
–disable-libtool-lock
–enable-staticdlopen=no
–without-purecov
–without-purify
–enable-java=no
–with-javabase=no
–without-dbpath
–with-dblib=berkeley
–with-bdb-libdir=/usr/local/bdb/lib
–with-bdb-incdir=/usr/local/bdb/include
–with-gdbm=no
–with-pam=no
–with-saslauthd=no
–with-pwcheck=no
–with-ipctype=unix
–disable-alwaystrue
–disable-checkapop
–disable-cram
–with-des=yes
–disable-digest
–with-openssl=/usr/bin/openssl
–disable-otp
–with-opie=no
–disable-srp
–disable-srp-setpass
–disable-krb4
–disable-gssapi
–enable-plain
–disable-anon
–disable-login
–disable-ntlm
–with-ldap=no
–enable-sql
–with-authdaemon=yes
–with-mysql=/usr/local/mysql/lib
–with-plugindir=/usr/local/lib/sasl2
–with-rc4
–without-dmalloc
–without-sfio



SASL2 Setup



Postfix main.cf


The below example is the part that goes into your main.cf file of Postfix. There are also some additional UCE examples to block some spam. For more information on UCE check:




broken_sasl_auth_clients = yes
smtpd_recipient_restrictions =
permit_mynetworks,
permit_sasl_authenticated,
reject_non_fqdn_hostname,
reject_non_fqdn_sender,
reject_non_fqdn_recipient,
reject_unauth_destination,
reject_unauth_pipelining,
reject_invalid_hostname,
reject_rbl_client opm.blitzed.org,
reject_rbl_client list.dsbl.org,
reject_rbl_client bl.spamcop.net,
reject_rbl_client sbl-xbl.spamhaus.org
smtpd_sasl_auth_enable = yes
smtpd_sasl_local_domain = $myhostname
smtpd_sasl_security_options = noanonymous


SASL2 smtpd.conf


smtpd.conf locations:



  • FreeBSD is in /usr/local/lib/sasl2
  • Debian Sarge is in /etc/postfix/sasl

Your smtpd.conf should contain something like:

pwcheck_method: authdaemond
log_level: 3
mech_list: PLAIN LOGIN
authdaemond_path:/usr/local/var/spool/authdaemon/socket

socket locations:



  • FreeBSD with courier-authlib-mysql-0.55 is /var/run/authdaemond/socket
  • Debian Sarge is /var/run/courier/authdaemon/socket

Tip for FreeBSD (tested in 6.0-RELEASE by Jett Tayer)


Courier’s autdaemond socket and pid directory must be readable by Postfix

chmod 755 /var/run/authdaemond

Tip for Debian (tested in sarge)


As for the mysql socket, you must create a link to the authdaemon socket in postfix’s jail (as root):

mkdir -p /var/spool/postfix/var/run/courier/authdaemon
ln /var/run/courier/authdaemon/socket /var/spool/postfix/var/run/courier/authdaemon/socket
chown -R daemon:daemon /var/spool/postfix/var/run/courier

Don’t forget check the permission of the path of /var/run/authdaemond/socket.



Please note


If your postfix runs in a jailed environment (like in Debian default package), you must recreate the hard link everytime the authdaemond restarts, or you will get lots of “Connection refused” on the log. This happens because authdaemond recreate the socket every time it starts, invalidating the old link.


In Debian, the simplest way is to put this on /etc/init.d/courier-authdaemond after authdaemond start

if [ -a /var/spool/postfix/var/run/courier/authdaemon/socket ]
then
rm -rf /var/spool/postfix/var/run/courier/authdaemon/socket
fi

ln /var/run/courier/authdaemon/socket /var/spool/postfix/var/run/courier/authdaemon/socket



Please note you must restart saslauthd whenever making any changes are made to the smtpd.conf file. If you followed the above instructions to build cyrus-sasl, you will have disabled saslauthd, so this note does not apply.


Reload postfix:



  • FreeBSD “postfix reload” or “/usr/local/etc/postfix reload” or “/etc/rc.d/sendmail restart” depending on how your /etc/rc.conf is configured.
  • Debian Sarge “postfix reload” or “/etc/init.d/postfix reload”


  • from chatran on slackware 10: I do this way:
pwcheck_method: saslauthd auxprop
mech_list: login plain
auxprop_plugin: sql
sql_engine: mysql
sql_hostnames: localhost
sql_user: postfix
sql_database: postfix
sql_passwd: postfix
sql_select: select password from mailbox where username = ‘%u@%r’

Note: this will only work if you use clear-text password, or if you patch the sql plugin as described in this article. –Beuc



  • Also remember to chown 777 the directory of the socket, otherwise postfix will not be able to read it.

With FreeBSD you can add postfix to the group courier. Edit /etc/group and change the line courier:*:465: in courier:*:465:postfix



SASL2 PAM install


Alternatively (for example if you don’t want to recompile sasl package and use your distribution’s — tested on Debian), you can use PAM authentication. Install pam_mysql.so in addition to sasl2 (In debian, you need: sasl2-bin libsasl2-modules libpam-mysql). Then set up /etc/postfix/sasl/smtpd.conf like this:

pwcheck_method: saslauthd
mech_list: PLAIN LOGIN
minimum_layer: 0

Then create /etc/pam.d/smtp and include something like this:

auth       required     pam_nologin.so
auth required pam_mysql.so user=postfix passwd=password host=localhost db=postfix table=mailbox usercolumn=username passwdcolumn=password crypt=1
#auth required pam_unix.so
auth required pam_env.so # [1]

account sufficient pam_mysql.so user=postfix passwd=password host=localhost db=postfix table=mailbox usercolumn=username passwdcolumn=password crypt=1
account required pam_unix.so


(change password= to your database password). Everything else can be done as if you were using authdaemon from sasl (including main.cf changes).



Courier-IMAP Install


Build and install Courier-IMAP, make sure that this is built with MySQL. The authentication daemon of Courier-IMAP is now destributed seperately, courier-authlib. This means that the MySQL integration has moved from Courier-IMAP to courier-authlib. Make sure that you build courier-authlib with MySQL support.
On FreeBSD: make WITH_MYSQL=yes install
Also on FreeBSD: Don’t forget to add “courier_authdaemond_enable=”YES” to your /etc/rc.conf. It’s not obvious because courier-authlib is installed with courier-imap during a “make install”, and the status message after install only reminds you to add courier-imap to your startup config.
On Debian: apt-get install courier-authdaemon courier-authmysql



Courier-IMAP Setup


First, make sure you include authmysql in authmodulelist of Courier’s authdaemonrc



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.

#DEFAULT_DOMAIN         domain.tld
MYSQL_CRYPT_PWFIELD password
MYSQL_DATABASE postfix
MYSQL_GID_FIELD ‘1001’
MYSQL_HOME_FIELD ‘/usr/local/virtual’
MYSQL_LOGIN_FIELD username
MYSQL_MAILDIR_FIELD maildir
MYSQL_NAME_FIELD name
MYSQL_OPT 0
MYSQL_PASSWORD postfix
#MYSQL_PORT 0
# Uncomment below if you want quota support.
#MYSQL_QUOTA_FIELD quota
MYSQL_SERVER localhost
# Default FreeBSD Socket
#MYSQL_SOCKET /var/mysql/mysql.sock
# Default RedHat Socket
#MYSQL_SOCKET /var/lib/mysql/mysql.sock
# Default Debian Sarge Socket
#MYSQL_SOCKET /var/run/mysqld/mysqld.sock
MYSQL_UID_FIELD ‘1001’
MYSQL_USERNAME postfix
MYSQL_USER_TABLE mailbox
#MYSQL_WHERE_CLAUSE server=’example.domain.com’




  • Make sure that there are NO spaces in the authmysqlrc file, only tabs.
  • Make sure that there are only single quotes ‘ around static values like: ‘/usr/local/virtual’, ‘UID’, ‘GID’
  • NO single quotes around localhost!
  • Make sure that localhost exists in your /etc/hosts file.
  • Including IPv6 during the compilation could cause a problem.
  • The MYSQL_GID_FIELD and MYSQL_UID_FIELD are for the UID and GID of the postfix user and group, NOT for the MySQL user and group.
  • If you have more than one authdaemon in /usr/lib/courier-imap/authlib, make sure that version=”authdaemond.mysql” in /etc/courier/authdaemonrc

User question/comment: I thought MYSQL_CRYPT_PWFIELD only handles the ENCRYPT() function in stead of MD5() (see postfix-mysql setup). Correct me when I’m wrong


User question/comment: MYSQL_HOME_FIELD ‘/usr/local/virtual’ is not a field name and is not used as a literal by courier-authlib-0.57 although it should not be deleted or changed to ‘ ‘ as the default field of ‘home’ will be substituted causing errors. Try MYSQL_MAILDIR_FIELD CONCAT(“/usr/local/virtual/”,maildir) as a workaround.



imapd


Your Courier-IMAP imapd file should have a line similar to this.

IMAP_CAPABILITY=”IMAP4rev1 UIDPLUS CHILDREN NAMESPACE THREAD=ORDEREDSUBJECT THREAD=REFERENCES SORT QUOTA IDLE”

This document was started by Mischa 10:07, 20 Apr 2005 (CEST)


Substack subscription form sign up