User Configuration Using MySQL And Postfixadmin

t

AttachmentSize
Plain text icon postfixadmin.txt5.2 KB

MySQL mapping seemed to be the most flexible way for user administration for the postfix mailserver. A major advantage is the independency from system users and easyily maintainable user setups using web interfaces. Additionally, non administrator users can modify they preferences, aliases and passwords using a dedicated web interface without interaction by the mailserver admin. MySQL mapping is provided under ubuntu by the package named postfix-mysql and the MySQL server mysql-server. Both can be installed using the following command (as root)

sudo aptitude install postfix-mysql mysql-server

You will be asked to the MySQL root password. Choose a real secure one here, but remeber it! You can reset it using the following command (as root - I don’t think sudo works with this one):

mysqladmin password yoursecret

Don’t forget to change ‘yoursecret’ with your real secure password! You also should add a MySQL user for access by postfix and others. Hence, connect to you mysql server.

mysql -u root -p

Now you need to setup the tables for the addresses, domains and aliases. The following commands creates a database which will be filled with the domain addresses later, add a user and grant it access to the table.

CREATE DATABASE mailserver;
      GRANT ALL PRIVILEGES ON mailserver.* TO mailuser@localhost IDENTIFIED BY 'mailuserpassword';

For the actual database creation and further administration, I installed a tool called postfixadmin, which is hosted on sourceforge. I installed it using the debian package file available from the download area. Additionally, I had to install php5-imap support. Both tasks are accomplished using the following.

sudo dpkg -i postfixadmin_xxx.deb
sudo aptitude install php5-imap

For the sake of security, I prefered the installation on my TLS pages, hence I cut the line

Alias /postfixadmin /usr/share/postfixadmin

from /etc/apache2/conf.d/postfixadmin and inserted it in my ssl-page configuration file.

Before the web interface can run on all four, you need to modify the postfixadmin configuration file. For our database example, the MySQL section looks like the following - don’t forget to change the password entry!
/etc/postfixadmin/config.inc.php:

// Database Config
// mysql = MySQL 3.23 and 4.0, 4.1 or 5
// mysqli = MySQL 4.1+
// pgsql = PostgreSQL
$CONF['database_type'] = 'mysql';
$CONF['database_host'] = 'localhost';
$CONF['database_user'] = 'mailuser';
$CONF['database_password'] = 'mailuserpassword';
$CONF['database_name'] = 'mailserver';
$CONF['database_prefix'] = '';

And enable the setup.php script by setting the following entry. /etc/postfixadmin/config.inc.php:

$CONF['configured'] = true;

And set an installation password by changing the following line.

$CONF['setup_password'] = 'yourpassword';

I recently found a bug in my postfixadmin installation. To fix it, open the configuration file /etc/postfixadmin/config.inc.php and edit the string header(“Location:…”) - line 21 in my installation, so that it states the following. Note, that the location depends on your installation directory of course. Mine is simply postfixadmin.

header("Location: /postfixadmin/login.php");

Now point your browser to the setup page (https://yourdomain.com/postfixadmin/setup.php) and fill the appropriate values into the form; (use the installation password set above) and add the hashed password, printed at the end of the form by postfixadmin to config.inc.php. After that the administration page should be available under https://yourdomain.com/postfixadmin.

If you prefer to install only the database part and not postfixadmin, you can either uninstall postfixadmin or use the attached SQL script and run it as user ‘mailuser’.

After postfixadmin setup, you should create the directories for the virtual mailboxes using the following commands and do not forget to change the username and group to your ones!

sudo mkdir /var/spool/postfix/virtual
sudo chown vmail:mail /var/spool/postfix/virtual
sudo chmod a+rwx /var/spool/postfix/virtual

Now tell postfix, where to access the user data. Hence, add the following lines to your main.cf, but don’t forget to change the uid and gid to your values! Make sure the home_mailbox-entry is before these lines and mydestination (as well as /etc/mailname) does not contain any of the virtual domains, as they will occur in the MySQL DB.
/etc/postfix/main.cf

# get password from dovecot - no idea how to do that directly 
smtpd_sasl_local_domain =
smtpd_sasl_type = dovecot
smtpd_sasl_path = /var/spool/postfix/private/auth-client
smtpd_sasl_auth_enable = yes

# virtual mailbox settings
virtual_transport = dovecot
dovecot_destination_recipient_limit = 1
virtual_maps = mysql:/etc/postfix/mysql_virtual_alias_maps.cf

# change uid to postfix uid
virtual_minimum_uid = 118
virtual_uid_maps = static:118

# change gid to postfix gid
virtual_gid_maps = static:8
virtual_mailbox_base = /var/spool/postfix/virtual
virtual_mailbox_domains = mysql:/etc/postfix/mysql_virtual_domains_maps.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf
virtual_mailbox_limit = 51200000

Now, create the MySQL .cf files, to tell postfix how to access the MySQL database correctly. Hence, add the files
/etc/postfix/mysql_virtual_alias_maps.cf:

user = mailuser
password = mailuserpassword
hosts = 127.0.0.1
dbname = mailserver
table = alias
select_field = goto
where_field = address

/etc/postfix/mysql_virtual_domains_maps.cf:

user = mailuser
password = mailuserpassword
hosts = 127.0.0.1
dbname = mailserver
table = domain
select_field = description
where_field = domain

/etc/postfix/mysql_virtual_mailbox_maps.cf:

user = mailuser
password = mailuserpassword
hosts = 127.0.0.1
dbname = mailserver
table = mailbox
select_field = maildir
where_field = username

/etc/postfix/mysql_relay_domains_maps.cf:

user = mailuser
password = mailuserpassword
hosts = 127.0.0.1
dbname = mailserver
table = domain
select_field = domain
where_field = domain
additional_conditions = and backupmx = '1'

Do not forget to change all password entries in above files! By the way, it is important to write 127.0.0.1 and NOT localhost, don’t ask me why. Now restart postfix and cross your fingers ;)

Cheers, iss.

Flattr: