Sympa mailing list setup
Mailinglist setup wtih Sympalists.eu (experimental)
Inhoud
Security/Privacy consideration
Our mailing list provider is a processed of our data; and distributes the email to our participants. This is the basis for allowing this party access to the email addresses or our participants - i.e. we only share for that purpose.
With this new interface - we can minimise the information to exactly that. And we stop sharing non-subscribers (which previously had to have nomail set).
Additional measures:
- Gather the /just/ needed data in a single purpose view (minimise)
- Reduce the need for a copy at the provider (no retention at processor)
- Specific user that is restricted to just that view and has only select/view; no write.
- Strong password & secure transfer of the password to the third party (gpg or via their admin interface (TLS/SSL)
- Require the use of SSL/TLS for this user in mysql (protect data and password)
- Lock down of this user to a specific IP in mysql
- Use of IP tables to lock down the connection as a second backstop
- Move to a non-common port (fluff)
- Data processing agreement part of contract with provider. Provider selected as it has an EU presence and sees this core to their commercial offering.
Still open:
- Does the API still need the (full) name - as mailman currently does - for use in the From: ? Or can we further limit this.
- Can we use X.509 client certs for authentication.
- how do we do digest ?
- Not clear how they manage the password / how secure we need to keep their sympa interface
- Does their sympa support 2FA ? (And should we start doing that at some point for admins).
Database configuration
Create a mysql user specific to sympa; $SYMPA_IP is the IP address of their gateway (currently just one) and $secret is a strong password:
create user mlist@$SYMPA_IP identified by '$secret' REQUIRE SSL;
Then create a view with just the data needed:
create view mailinglists_api as
select distinct
concat(first_name,' ',last_name) "comment_subscriber",
name "list",
email "user_subscriber",
CASE
WHEN mailinglists_subscription.digest
THEN "digestplain"
ELSE "mail"
END as "digest",
( members_user.is_superuser ) as "owner",
( members_user.is_superuser || members_user.is_staff ) as "moderator"
from members_user,mailinglists_subscription,mailinglists_mailinglist
where mailinglists_subscription.member_id = members_user.id and
mailinglists_subscription.mailinglist_id = mailinglists_mailinglist.id and
mailinglists_subscription.active = 1
order by email
with read only;
Add to above
and mailinglists_subscription.name = 'testlist'
to curtail it to just a test list of so required. And grant this integration user access to just that view:
GRANT SELECT, SHOW VIEW ON mslcrm.mailinglists_api TO mlist@ $SYMPA_IP;
This then in effect gives the following to the right user:
mysql> select distinct * from mailinglists_api where list='testlist';
+-----------------------+------------+----------------------+
| comment_subscriber. | list | user_subscriber | ....
+-----------------------+------------+----------------------+
| Ted de Tester | testlist | tester@xxxx.xxxx |....
...... etc.......................................................
+-----------------------+------------+----------------------+
112 row in set (0.04 sec)
Note that you can use:
SELECT host,user FROM mysql.user where user = 'mlist';
SELECT CONCAT('SHOW GRANTS FOR ' ' ',user,' ' '@' ' ',host,' ' ';') FROM mysql.user;
to check permissions and accounts. It should show:
mysql> SELECT host,user FROM mysql.user where user = 'mlist';
+---------------+-------+
| host | user |
+---------------+-------+
| 95.216.162.33 | mlist |
+---------------+-------+
mysql> SELECT CONCAT('SHOW GRANTS FOR ' ' ',user,' ' '@' ' ',host,' ' ';') FROM mysql.user where host != 'localhost';
+--------------------------------------------------------------+
| CONCAT('SHOW GRANTS FOR ' ' ',user,' ' '@' ' ',host,' ' ';') |
+--------------------------------------------------------------+
| SHOW GRANTS FOR mlist @ 95.216.162.33 ; |
+--------------------------------------------------------------+
Sympa side configuration
Then provide the mailinglist provider with the following file/data:
db_type mysql db_port $PORT db_host $OUR_IP db_user mlist db_passwd $secret db_name mslcrm
For the two lists:
sql_query
select user_subscriber, comment_subscriber, digest as reception_subscriber from mailinglists_api where list='deelnemers'
sql_query
select user_subscriber, comment_subscriber, digest as reception_subscriber from mailinglists_api where list='spacelog'
Their gpg/pgp key is at https://www.mailmanlists.net/pgppubkey.mailmanlists.support.
This is configured in the website thus
Server side configuration for XS
Configure SSL on the right port in /etc/mysql/mysql.conf.d/mysqld.conf
[mysqld] ... bind-address = 127.0.0.1, $OUR_IP port=$PORT ..... ssl_ca=msl-ca.pem ssl_cert=msl-cert.pem ssl_key=msl-cert.key require_secure_transport=ON
The above keys need to be mysql:mysql and 600 (a shared group does not seem to work, nor does a path; perhaps some chroot() going on) so this is set up by adding the script below to /etc/letsencrypt/renewal-hooks/deploy/:
#!/bin/sh
set -e
cd /var/lib/mysql
cp /etc/letsencrypt/live/makerspaceleiden.nl/cert.pem msl-cert.pem
cp /etc/letsencrypt/live/makerspaceleiden.nl/privkey.pem msl-cert.key
cp /etc/letsencrypt/live/makerspaceleiden.nl/chain.pem msl-ca.pem
chown mysql:mysql msl-*
chmod 600 msl-*
exit 0
And allow acccess:
sudo ufw allow in from $SYMPA_IP to any port $PORT
iptables -A INPUT -p tcp -s $SYMPA_IP --sport 1024:65535 -d $OUR_IP --dport $PORT \
-m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 2$OUR_IP --sport $PORT -d $SYMPA_IP --dport 1024:65535 \
-m state --state ESTABLISHED -j ACCEPT
