Sympa mailing list setup

Mailing lists are ran by a third party - this page documents its setup (which replaces the legacy mailman sometime Q2 2023).

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:

1. Gather the /just/ needed data in a single purpose view (minimise)
2. Reduce the need for a copy at the provider (no retention at processor)
3. Specific user that is restricted to just that view and has only select/view; no write.
4. Strong password & secure transfer of the password to the third party (gpg or via their admin interface (TLS/SSL)
5. Require the use of SSL/TLS for this user in mysql (protect data and password)
6. Lock down of this user to a specific IP in mysql
7. Use of IP tables to lock down the connection as a second backstop
8. Move to a non-common port (fluff)

In flight:

1. Data processing agreement part of contract with provider. Provider selected as it has an EU presence and sees this core to their commercial offering.
2. Owner/admin - configured in API - but bug seems to prevent it propagating (with sympa)
3. Fix legacy forwarder
4. Remove CRM cruft in 'management-commands'

Still open:

1. Does the API still need the (full) name - as mailman currently does - for use in the From: ? Or can we further limit this.
2. Can we use X.509 client certs for authentication (Anser: NO)
3. how do we do digest ? await version update.
4. Not clear how they manage the password / how secure we need to keep their sympa interface
5. Does their sympa support 2FA ? (And should we start doing that at some point for admins).
6. Admins can see the full list somewhat needlessly
7. 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.