CrmSympaSetup

Mailing lists are ran by a third party - we currently use a hack to manage these by faking browser access to their form API.

This page documents a new mailinglist setup wtih Sympalists.eu (experimental) - that may be more robust.

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).
6. Admins can see the full list somewhat needlessly
7. Password is kept in the clear by Sympa

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;
     .. 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

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

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