CrmSympaSetup
Mailing lists are ran by a third party - this page documents its setup (which replaces the legacy mailman sometime Q2 2023).
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)
In flight:
- Data processing agreement part of contract with provider. Provider selected as it has an EU presence and sees this core to their commercial offering.
- Owner/admin - configured in API - but bug seems to prevent it propagating (with sympa)
- Fix legacy forwarder
- Remove CRM cruft in 'management-commands'
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(Anser: NO)how do we do digest ?await version update.- 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).
- Admins can see the full list somewhat needlessly
Password is kept in the clear by Sympa- unavoidable - their MariaDB lacks the crypto of MySQL on our end.
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 WITH mysql_native_password by '$secret' REQUIRE SSL;
Use mysql --defaults-file=makerspaceleiden/my.cnf if required for normal access; or sudo for low level acess.
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.
Drop the with read only --- our MySQL is too primitive (we rely on below SELECT/SHOW-VIEW).
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)
DB checking
Note that you can use:
SELECT host,user FROM mysql.user where user = 'mlist';
SELECT CONCAT('SHOW GRANTS FOR ' ' ',user,' ' '@' ' ',host,' ' ';') FROM mysql.user;
.. execute the show grant statements generated by above ...
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 @ SYMPA_IP ; |
+--------------------------------------------------------------+
mysql> SHOW GRANTS FOR mlist@ SYMPA_IP ;
+-----------------------------------------------------------------------------------+
| Grants for mlist@ SYMPA_IP |
+-----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mlist`@`SYMPA_IP ` |
| GRANT SELECT, SHOW VIEW ON `mslcrm`.`mailinglists_api` TO `mlist`@`9 SYMPA_IP ` |
+-----------------------------------------------------------------------------------+
where SYMPA_IP is the IP of the sympa server.
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
Or use the value for connection options:
mysql_ssl=1;mysql_ssl_optional=1
for MariaDB connectors (the .net version). You must then switch to native passwords.
You can confirm that this works by going to the members admin page and hitting the blue update button:
Which should show something like 50-150 'updates' - roughly proportional to the number of deelnemers:
Or wait for up to an hour or the next post to auto sync. Check the log in
/var/log/mysql/
for any errors.
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
An extra hook is installed in /etc/letsencrypt/renewal-hooks/post:
#!/bin/sh
service mysqld restart
And allow acccess:
sudo ufw allow 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
State is stored in /etc/ufw/user.rules.
to make sure that cert changes are picked up.
History
| Date | Action |
|---|---|
| Late 2022, early 2023 | First tests & refinement recipe. |
| 2023-03-28 | Created in earnest; for the noc test list (dirkx) |
| 2023-03-29 | Changed the Sympa-IP address to the right sympalists.net host interface (dirkx) |
| 2023-03-30 | Switch to the previous / native password scheme (this is to work with both MySQL and MariaDB on their end) (dirkx) |
| 2023-08-03 | update instructions for certbot that was added. |
