Sympa mailing list setup

Mailinglist setup wtih Sympalists.eu (experimental)

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)
9. 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:

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.
3. how do we do digest ?
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).

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)

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;

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

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