iredmail-doc/html_bk/integration.mlmmj.mysql.html

491 lines
20 KiB
HTML

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Integrate mlmmj mailing list manager in iRedMail (MySQL/MariaDB backends)</title>
<link rel="stylesheet" type="text/css" href="./css/markdown.css" />
</head>
<body>
<div id="navigation">
<a href="https://www.iredmail.org" target="_blank">
<img alt="iRedMail web site"
src="./images/logo-iredmail.png"
style="vertical-align: middle; height: 30px;"
/>&nbsp;
<span>iRedMail</span>
</a>
&nbsp;&nbsp;//&nbsp;&nbsp;<a href="./index.html">Document Index</a></div><h1 id="integrate-mlmmj-mailing-list-manager-in-iredmail-mysqlmariadb-backends">Integrate mlmmj mailing list manager in iRedMail (MySQL/MariaDB backends)</h1>
<div class="toc">
<ul>
<li><a href="#integrate-mlmmj-mailing-list-manager-in-iredmail-mysqlmariadb-backends">Integrate mlmmj mailing list manager in iRedMail (MySQL/MariaDB backends)</a><ul>
<li><a href="#summary">Summary</a></li>
<li><a href="#backup-sql-database-first">Backup SQL database first</a></li>
<li><a href="#install-mlmmj-and-other-required-package">Install mlmmj and other required package</a></li>
<li><a href="#create-required-system-account">Create required system account</a></li>
<li><a href="#update-sql-tables-in-vmail-database">Update SQL tables in vmail database</a></li>
<li><a href="#postfix-integration">Postfix integration</a></li>
<li><a href="#amavisd-integration">Amavisd Integration</a></li>
<li><a href="#setup-mlmmjadmin-restful-api-server-used-to-manage-mlmmj-mailing-lists">Setup mlmmjadmin: RESTful API server used to manage mlmmj mailing lists</a></li>
<li><a href="#manage-subscribeable-mailing-lists">Manage subscribeable mailing lists</a></li>
<li><a href="#references">References</a></li>
<li><a href="#see-also">See Also</a></li>
</ul>
</li>
</ul>
</div>
<h2 id="summary">Summary</h2>
<p>In iRedMail-0.9.8, we integrate mlmmj (<a href="http://mlmmj.org">http://mlmmj.org</a>) - a simple and slim
mailing list manager. It uses very few resources, and requires no daemons, easy
to install, configure and manage. if offers a great set of features, including:</p>
<ul>
<li>Archive</li>
<li>Subject prefix</li>
<li>Subscribers only posting</li>
<li>Moderators only posting</li>
<li>Moderation functionality</li>
<li>Custom headers / footer</li>
<li>Fully automated bounce handling</li>
<li>Complete requeueing functionality</li>
<li>Regular expression access control</li>
<li>Delivery Status Notification (RFC1891) support</li>
<li>Rich, customisable texts for automated operations</li>
<li>and more</li>
</ul>
<p>With mlmmj integration, you can create as many mailing lists as you want. End
user can subscribe to mailing list <code>listname@domain.dom</code> by sending email to
<code>listname+subscribe@domain.com</code>, unsubscribe from the list by sending email to
<code>listname+unsubscribe@domain.com</code>. Of course you can disable the subscription
and unsubscription with a setting.</p>
<p>iRedMail team also developes a simple RESTful API server called <code>mlmmjadmin</code>
to help manage mailing lists, it also offers script tool to manage mailing
lists from command line.</p>
<p>We will show you how to integrate both mlmmj and mlmmjadmin in this tutorial.</p>
<h2 id="backup-sql-database-first">Backup SQL database first</h2>
<p>Although we don't modify any existing SQL data in this tutorial, but it's
a good idea to backup it now before you adding any new mailing lists.</p>
<p>Please run command <code>bash /var/vmail/backup/backup_mysql.sh</code> to backup SQL
databases.</p>
<h2 id="install-mlmmj-and-other-required-package">Install mlmmj and other required package</h2>
<div class="admonition attention">
<p class="admonition-title">Attention</p>
<ul>
<li><code>uwsgi</code> and other Python modules are required by the RESTful API server <code>mlmmjadmin</code>.</li>
<li><code>mlmmjadmin-3.x</code> and later releases work with only Python 3.</li>
</ul>
</div>
<ul>
<li>On RHEL/CentOS, <code>mlmmj</code> is available in <code>EPEL</code> repo, and it's enabled in
iRedMail by default. So we can install it directly:</li>
</ul>
<pre><code># RHEL/CentOS 7
yum install mlmmj uwsgi uwsgi-plugin-python36 uwsgi-logger-syslog python3-requests python3-PyMySQL
# RHEL/CentOS 8
yum install mlmmj python3-pip3 python3-requests python3-PyMySQL
pip3 install uwsgi
</code></pre>
<ul>
<li>On Debian/Ubuntu:</li>
</ul>
<pre><code>apt-get install mlmmj uwsgi uwsgi-plugin-python3 python3-requests
</code></pre>
<ul>
<li>On FreeBSD:</li>
</ul>
<pre><code>cd /usr/ports/mail/mlmmj
make install clean
cd /usr/ports/www/uwsgi
make install clean
cd /usr/ports/www/py-requests
make install clean
</code></pre>
<ul>
<li>On OpenBSD:</li>
</ul>
<pre><code>pkg_add mlmmj altermime py3-requests
</code></pre>
<h2 id="create-required-system-account">Create required system account</h2>
<p>mlmmj will be ran as user <code>mlmmj</code> and group <code>mlmmj</code>, all mailing list data will
be stored under its home directory <code>/var/vmail/mlmmj</code>:</p>
<p>On Linux or OpenBSD:</p>
<pre><code>groupadd mlmmj
useradd -m -g mlmmj -d /var/vmail/mlmmj -s /sbin/nologin mlmmj
mkdir /var/vmail/mlmmj-archive
chown -R mlmmj:mlmmj /var/vmail/mlmmj /var/vmail/mlmmj-archive
chmod -R 0700 /var/vmail/mlmmj /var/vmail/mlmmj-archive
chmod 0755 /var/vmail # Make sure this directory is accessible by other users
</code></pre>
<p>On FreeBSD:</p>
<pre><code>pw groupadd mlmmj
pw useradd mlmmj -m -g mlmmj -s /sbin/nologin -d /var/vmail/mlmmj
mkdir /var/vmail/mlmmj-archive
chown -R mlmmj:mlmmj /var/vmail/mlmmj /var/vmail/mlmmj-archive
chmod -R 0700 /var/vmail/mlmmj /var/vmail/mlmmj-archive
chmod 0755 /var/vmail # Make sure this directory is accessible by other users
</code></pre>
<h2 id="update-sql-tables-in-vmail-database">Update SQL tables in <code>vmail</code> database</h2>
<p>We need some updates in <code>vmail</code> SQL database:</p>
<ul>
<li>new SQL table <code>maillists</code>: used to store profile of mailing list.</li>
<li>new SQL column <code>forwardings.is_maillist</code></li>
<li>new SQL column <code>domain.maillists</code>: used to set per-domain limit of mailing
list accounts. This column is mostly used by iRedAdmin-Pro.</li>
</ul>
<p>This SQL structure was introduced in iRedMail-0.9.8, if you're running
an old iRedMail release, please upgrade iRedMail to the latest stable release
by following our tutorials first:</p>
<ul>
<li><a href="./iredmail.releases.html">iRedMail Upgrade Tutorials</a></li>
</ul>
<h2 id="postfix-integration">Postfix integration</h2>
<ul>
<li>
<p>Please add lines below in Postfix config file <code>/etc/postfix/master.cf</code>:</p>
<div class="admonition attention">
<p class="admonition-title">Attention</p>
<ul>
<li>Command <code>/usr/bin/mlmmj-amime-receive</code> doesn't exist yet, we will
create it later.</li>
<li>On FreeBSD and OpenBSD, it should be
<code>/usr/local/usr/bin/mlmmj-amime-receive</code> instead.</li>
</ul>
</div>
</li>
</ul>
<pre><code># ${nexthop} is '%d/%u' in transport ('mlmmj:%d/%u')
mlmmj unix - n n - - pipe
flags=ORhu user=mlmmj argv=/usr/bin/mlmmj-amime-receive -L /var/vmail/mlmmj/${nexthop}
</code></pre>
<ul>
<li>Add line below in Postfix config file <code>/etc/postfix/main.cf</code>:</li>
</ul>
<pre><code>mlmmj_destination_recipient_limit = 1
</code></pre>
<ul>
<li>Open Postfix config file <code>/etc/postfix/main.cf</code>, update existing parameter
<code>transport_maps</code>, add new sql lookup <code>/etc/postfix/mysql/transport_maps_maillist.cf</code>
AFTER <code>/etc/postfix/mysql/transport_maps_user.cf</code> like below. We will create
required sql lookup file later.</li>
</ul>
<pre><code>transport_maps =
proxy:mysql:/etc/postfix/mysql/transport_maps_user.cf
proxy:mysql:/etc/postfix/mysql/transport_maps_maillist.cf
...
</code></pre>
<ul>
<li>Now create file <code>/etc/postfix/mysql/transport_maps_maillist.cf</code>:</li>
</ul>
<div class="admonition warning">
<p class="admonition-title">Warning</p>
<p>Please update the <code>password =</code> line with the real password of SQL user
<code>vmail</code>, you can find it in files under <code>/etc/postfix/mysql/</code>.</p>
</div>
<pre><code>hosts = 127.0.0.1
port = 3306
user = vmail
password = qsescZvV03f6YUtTMN2bQTejmjatzz
dbname = vmail
query = SELECT maillists.transport FROM maillists,domain WHERE maillists.address='%s' AND maillists.active=1 AND maillists.domain = domain.domain AND domain.active=1
</code></pre>
<ul>
<li>Run commands below to create file <code>/usr/bin/mlmmj-amime-receive</code> (Linux) or
<code>/usr/local/bin/mlmmj-amime-receive</code> (FreeBSD/OpenBSD):</li>
</ul>
<div class="admonition attention">
<p class="admonition-title">Attention</p>
<p>mlmmj doesn't support signature signing very well, so we follow mlmmj
official document and create this script to sign signature properly with
command <code>altermime</code>. All iRedMail installation should have command
<code>altermime</code> (package <code>AlterMIME</code>) available, so you don't need to install
it manually</p>
</div>
<p>On Linux:</p>
<pre><code>cd /usr/bin/
wget https://github.com/iredmail/iRedMail/raw/1.0/samples/mlmmj/mlmmj-amime-receive
chown mlmmj:mlmmj mlmmj-amime-receive
chmod 0550 mlmmj-amime-receive
</code></pre>
<p>On FreeBSD or OpenBSD:</p>
<pre><code>cd /usr/local/bin/
wget https://github.com/iredmail/iRedMail/raw/1.0/samples/mlmmj/mlmmj-amime-receive
chown mlmmj:mlmmj mlmmj-amime-receive
chmod 0550 mlmmj-amime-receive
</code></pre>
<h2 id="amavisd-integration">Amavisd Integration</h2>
<p>We need Amavisd to listen on one more port <code>10027</code>, it will be used to scan
spam/virus for emails posted to mailing list.</p>
<ul>
<li>Please open Amavisd config file, find parameter <code>$inet_socket_port</code>, add new
port number <code>10027</code> in the list, like below:<ul>
<li>On RHEL/CentOS, it's <code>/etc/amavisd/amavisd.conf</code>.</li>
<li>On Debian/Ubuntu, it's <code>/etc/amavis/conf.d/50-user</code>.</li>
<li>On OpenBSD, it's <code>/etc/amavisd.conf</code>.</li>
<li>On FreeBSD, it's <code>/usr/local/etc/amavisd.conf</code>.</li>
</ul>
</li>
</ul>
<pre><code>$inet_socket_port = [10024, 10026, 10027, 9998];
</code></pre>
<ul>
<li>Add lines below in Amavisd config file. It creates a new policy bank called
<code>MLMMJ</code> for emails submitted by mlmmj from port 10027. The purpose is signing
DKIM key on outgoing emails sent by mailing list, but disable
spam/virus/banned/bad-header checks, because emails sent to mailing list will
be scanned either on port 10024 (incoming email from external senders) or
10026 (outgoing email sent by smtp authenticated users).</li>
</ul>
<pre><code>$interface_policy{'10027'} = 'MLMMJ';
$policy_bank{'MLMMJ'} = {
originating =&gt; 1, # declare that mail was submitted by our smtp client
allow_disclaimers =&gt; 0, # mailing list should use footer text instead.
enable_dkim_signing =&gt; 1, # sign DKIm signature
smtpd_discard_ehlo_keywords =&gt; ['8BITMIME'],
terminate_dsn_on_notify_success =&gt; 0, # don't remove NOTIFY=SUCCESS option
bypass_spam_checks_maps =&gt; [1], # don't check spam
bypass_virus_checks_maps =&gt; [1], # don't check virus
bypass_banned_checks_maps =&gt; [1], # don't check banned file names and types
bypass_header_checks_maps =&gt; [1], # don't check bad header
};
</code></pre>
<p>Now restart Amavisd and Postfix service, mlmmj mailing list manager is now
fully integrated.</p>
<p>We will setup <code>mlmmjadmin</code> program to make managing mailing lists easier.</p>
<h2 id="setup-mlmmjadmin-restful-api-server-used-to-manage-mlmmj-mailing-lists">Setup mlmmjadmin: RESTful API server used to manage mlmmj mailing lists</h2>
<ul>
<li>
<p>Download the latest mlmmjadmin release: <a href="https://github.com/iredmail/mlmmjadmin/releases">https://github.com/iredmail/mlmmjadmin/releases</a>,
extract downloaded package to <code>/opt/</code> directory, and create a symbol link:</p>
<div class="admonition attention">
<p class="admonition-title">Attention</p>
<p>We use version <code>3.1.3</code> for example below.</p>
<ul>
<li><code>mlmmjadmin-3.x</code> and later releases requires Python 3.</li>
<li><code>mlmmjadmin-2.x</code> and older releases requires Python 2.</li>
</ul>
</div>
</li>
</ul>
<pre><code>cd /root/
wget https://github.com/iredmail/mlmmjadmin/archive/3.1.3.tar.gz
tar zxf 3.1.3.tar.gz -C /opt
rm -f 3.1.3.tar.gz
ln -s /opt/mlmmjadmin-3.1.3 /opt/mlmmjadmin
</code></pre>
<ul>
<li>Generate config file by copying sample file, <code>settings.py.sample</code>:</li>
</ul>
<pre><code>cd /opt/mlmmjadmin
cp settings.py.sample settings.py
chown mlmmj:mlmmj settings.py
chmod 0400 settings.py
</code></pre>
<ul>
<li>Generate a random, long string as API auth token, it will be used by your
API client. For example:</li>
</ul>
<pre><code># On Linux/FreBSD
$ eval &lt;/dev/urandom tr -dc A-Za-z0-9 | (head -c $1 &amp;&gt;/dev/null || head -c 32)
43a89b7aa34354089e629ed9f9be0b3b
# On OpenBSD
$ eval &lt;/dev/random tr -cd [:alnum:] | fold -w 32 | head -1
43a89b7aa34354089e629ed9f9be0b3b
</code></pre>
<ul>
<li>Add this string in <code>/opt/mlmmjadmin/settings.py</code>, parameter <code>api_auth_tokens</code>
like below:</li>
</ul>
<pre><code>api_auth_tokens = ['43a89b7aa34354089e629ed9f9be0b3b']
</code></pre>
<p>You can add as many token as you want for different API clients. For example:</p>
<pre><code>api_auth_tokens = ['43a89b7aa34354089e629ed9f9be0b3b', '703ed37b20243d7c51c56ce6cd90e94c']
</code></pre>
<ul>
<li>if you manage mail accounts <strong>WITH</strong> iRedAdmin-Pro, please set values of
parameters <code>backend_api</code> and <code>backend_cli</code> in <code>/opt/mlmmjadmin/settings.py</code>
like below:</li>
</ul>
<pre><code>backend_api = 'bk_none'
backend_cli = 'bk_iredmail_sql'
</code></pre>
<ul>
<li>if you do <strong>NOT</strong> manage mail accounts with iRedAdmin-Pro, please set values
of parameters <code>backend_api</code> and <code>backend_cli</code> in <code>/opt/mlmmjadmin/settings.py</code>
like below:</li>
</ul>
<pre><code>backend_api = 'bk_iredmail_sql'
backend_cli = 'bk_iredmail_sql'
</code></pre>
<ul>
<li>
<p>Add extra required parameters in <code>/opt/mlmmjadmin/settings.py</code>, so that
mlmmjadmin can connect to SQL server and manage mailing lists.</p>
<div class="admonition attention">
<p class="admonition-title">Attention</p>
<p>You can find SQL server address, port, database name, SQL username and
password in iRedAdmin config file, the SQL user must have both read and
write privileges to manage <code>vmail</code> database. iRedMail server usually
use SQL user <code>vmailadmin</code> for this purpose.</p>
</div>
</li>
</ul>
<pre><code>iredmail_sql_db_type = 'mysql'
iredmail_sql_db_server = '127.0.0.1'
iredmail_sql_db_port = 3306
iredmail_sql_db_name = 'vmail'
iredmail_sql_db_user = 'vmailadmin'
iredmail_sql_db_password = '&lt;password&gt;'
</code></pre>
<ul>
<li>Add extra required parameters in <code>/opt/mlmmjadmin/settings.py</code> to use the
directory used to store mailing lists:</li>
</ul>
<pre><code>MLMMJ_SPOOL_DIR = '/var/vmail/mlmmj'
MLMMJ_ARCHIVE_DIR = '/var/vmail/mlmmj-archive'
MLMMJ_DEFAULT_PROFILE_SETTINGS.update({'smtp_port': 10027})
</code></pre>
<ul>
<li>If you're running OpenBSD or FreeBSD, please add parameter <code>MLMMJ_SKEL_DIR</code>
in <code>/opt/mlmmjadmin/settings.py</code> to set the directory which stores mlmmj mail
templates:</li>
</ul>
<pre><code>MLMMJ_SKEL_DIR = '/usr/local/share/mlmmj/text.skel'
</code></pre>
<ul>
<li>Copy rc/systemd scripts for service control:</li>
</ul>
<pre><code>#
# For RHEL/CentOS 6
#
cp /opt/mlmmjadmin/rc_scripts/mlmmjadmin.rhel /etc/init.d/mlmmjadmin
chmod 0755 /etc/init.d/mlmmjadmin
chkconfig --level 345 mlmmjadmin on
#
# For RHEL/CentOS 7
#
perl -pi -e 's#python,#python36,#' /opt/mlmmjadmin/rc_scripts/uwsgi/rhel.ini
cp /opt/mlmmjadmin/rc_scripts/systemd/rhel.service /lib/systemd/system/mlmmjadmin.service
chmod 0644 /lib/systemd/system/mlmmjadmin.service
systemctl daemon-reload
systemctl enable mlmmjadmin
#
# For Debian 8, Ubuntu 14.04 and earlier releases which does NOT use systemd
#
cp /opt/mlmmjadmin/rc_scripts/mlmmjadmin.debian /etc/init.d/mlmmjadmin
chmod 0755 /etc/init.d/mlmmjadmin
update-rc.d mlmmjadmin defaults
#
# For Debian 9 and Ubuntu 16.04 which uses systemd
#
cp /opt/mlmmjadmin/rc_scripts/systemd/debian.service /lib/systemd/system/mlmmjadmin.service
chmod 0644 /lib/systemd/system/mlmmjadmin.service
systemctl daemon-reload
systemctl enable mlmmjadmin
#
# For FreeBSD
#
cp /opt/mlmmjadmin/rc_scripts/mlmmjadmin.freebsd /usr/local/etc/rc.d/mlmmjadmin
chmod 0755 /usr/local/etc/rc.d/mlmmjadmin
echo 'mlmmjadmin_enable=YES' &gt;&gt; /etc/rc.conf.local
#
# For OpenBSD
#
cp /opt/mlmmjadmin/rc_scripts/mlmmjadmin.openbsd /etc/rc.d/mlmmjadmin
chmod 0755 /etc/rc.d/mlmmjadmin
rcctl enable mlmmjadmin
</code></pre>
<ul>
<li>Create directory used to store mlmmjadmin log file. mlmmjadmin is
configured to log to syslog directly.</li>
</ul>
<pre><code>#
# For RHEL/CentOS
#
mkdir /var/log/mlmmjadmin
chown root:root /var/log/mlmmjadmin
chmod 0755 /var/log/mlmmjadmin
#
# For Debian
#
mkdir /var/log/mlmmjadmin
chown root:adm /var/log/mlmmjadmin
chmod 0755 /var/log/mlmmjadmin
#
# For Ubuntu
#
mkdir /var/log/mlmmjadmin
chown syslog:adm /var/log/mlmmjadmin
chmod 0755 /var/log/mlmmjadmin
#
# For OpenBSD/FreeBSD
#
mkdir /var/log/mlmmjadmin
chown root:wheel /var/log/mlmmjadmin
chmod 0755 /var/log/mlmmjadmin
</code></pre>
<ul>
<li>Update syslog daemon config file to log mlmmjadmin to dedicated log file:</li>
</ul>
<p>For Linux</p>
<pre><code>cp /opt/mlmmjadmin/samples/rsyslog/mlmmjadmin.conf /etc/rsyslog.d/
service rsyslog restart
</code></pre>
<p>For OpenBSD, please append below lines in <code>/etc/syslog.conf</code>:</p>
<pre><code>!!mlmmjadmin
local5.* /var/log/mlmmjadmin/mlmmjadmin.log
</code></pre>
<p>For FreeBSD, please append below lines in <code>/etc/syslog.conf</code>:</p>
<pre><code>!mlmmjadmin
local5.* /var/log/mlmmjadmin/mlmmjadmin.log
</code></pre>
<ul>
<li>Now it's ok to start <code>mlmmjadmin</code> service, it listens on <code>127.0.0.1:7790</code>
by default:</li>
</ul>
<pre><code>#
# On Linux/FreeBSD:
#
service mlmmjadmin restart
#
# On OpenBSD
#
rcctl start mlmmjadmin
</code></pre>
<p>On Linux, you can check the port number with command <code>netstat</code> or <code>ss</code> like below:</p>
<pre><code>netstat -ntlp | grep 7790
ss -ntlp | grep 7790
</code></pre>
<p>On FreeBSD/OpenBSD, run:</p>
<pre><code>netstat -anl -p tcp | grep 7790
</code></pre>
<h2 id="manage-subscribeable-mailing-lists">Manage subscribeable mailing lists</h2>
<p>Please read document <a href="./manage.subscribeable.mailing.lists.html">Manage subscribeable mailing lists</a>.</p>
<h2 id="references">References</h2>
<ul>
<li>Mlmmj:<ul>
<li><a href="http://mlmmj.org/">web site</a></li>
<li><a href="http://mlmmj.org/docs/tunables/">Tunable parameters</a></li>
<li><a href="http://mlmmj.org/docs/readme-postfix/">Postfix integration</a></li>
</ul>
</li>
<li><a href="https://github.com/iredmail/mlmmjadmin">mlmmjadmin</a>: RESTful API server used to manage mlmmj mailing lists. Developed
and maintained by iRedMail team.</li>
</ul>
<h2 id="see-also">See Also</h2>
<ul>
<li><a href="./integration.mlmmj.ldap.html">Integrate mlmmj mailing list manager in iRedMail (OpenLDAP backend)</a></li>
<li><a href="./integration.mlmmj.mysql.html">Integrate mlmmj mailing list manager in iRedMail (MySQL/MariaDB backends)</a></li>
<li><a href="./integration.mlmmj.pgsql.html">Integrate mlmmj mailing list manager in iRedMail (PostgreSQL backend)</a></li>
</ul><div class="footer">
<p style="text-align: center; color: grey;">All documents are available in <a href="https://github.com/iredmail/docs/">GitHub repository</a>, and published under <a href="http://creativecommons.org/licenses/by-nd/3.0/us/" target="_blank">Creative Commons</a> license. You can <a href="https://github.com/iredmail/docs/archive/master.zip">download the latest version</a> for offline reading. If you found something wrong, please do <a href="https://www.iredmail.org/contact.html">contact us</a> to fix it.</p>
</div></body></html>