Sympa mailing list setup: verschil tussen versies
(→Security/Privacy consideration) |
(→Database configuration) |
||
Regel 32: | Regel 32: | ||
= Database configuration = | = 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 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; | create user mlist@$SYMPA_IP identified by '$secret' REQUIRE SSL; | ||
+ | |||
+ | Use ```mysql --defaults-file=makerspaceleiden/my.cnf``` if required. | ||
Then create a view with just the data needed: | Then create a view with just the data needed: |
Versie van 28 mrt 2023 om 18:28
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.
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).
- Admins can see the full list somewhat needlessly
- 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;
Use ```mysql --defaults-file=makerspaceleiden/my.cnf``` if required.
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