Subject: pglister: pgarchives does not synchronize subscribers with email delivery disabled (proposed patch)

From: Célestin Matte <[email-masked]>
Date: Mon, 22 Sep 2025 20:12:35 +0200

The pglister_sync script is supposed to synchronize subscribers from pglister to pgarchives, so they can access archives.
 From what I understand of its semantics, the "nomail" field (labeled "Disable mail delivery") is used to remain subscribed, to be able to access archives, without receiving emails.
However, due to the way the API works, this is not the case. pglister_sync.py makes a call to the API, which get subscribers from the mailinglist_subscribers SQL view. This view is built in the 0027_merge_migrations.py migrations and excludes users with nomail.
I propose a patch with a migration to change this SQL view to include nomail users (copying previous definition, without "AND NOT nomail").
Note that this changes results of the API, which might have consequences if other scripts use it (I haven't found any with some grepping).
-- 
Célestin Matte
From 37ad3abc2887f28f118d1ae478e704698b731c3b Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <[email-masked]>
Date: Mon, 22 Sep 2025 19:57:21 +0200
Subject: [PATCH] Include nomail users in API call results

Otherwise, pgarchives's pglister_sync won't give access to these users
---
 ...60_fix_mailinglist_subscribers_sql_view.py | 28 +++++++++++++++++++
 1 file changed, 28 insertions(+)
 create mode 100644 web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py

diff --git a/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py b/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py
new file mode 100644
index 0000000..5f49e48
--- /dev/null
+++ b/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py
@@ -0,0 +1,28 @@
+# -*- coding: utf-8 -*-
+from __future__ import unicode_literals
+
+from django.db import migrations, models
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('lists', '0059_rfc8058'),
+    ]
+
+    operations = [
+        migrations.RunSQL("""
+CREATE OR REPLACE VIEW mailinglist_subscribers AS
+   SELECT list_id AS listid,
+         ls.subscriber_id AS subscriberaddress_id,
+      email,
+      eliminatecc,
+      token,
+      s.user_id AS userid,
+      (SELECT array_agg(listtag_id) FROM lists_listsubscription_tags lst WHERE lst.listsubscription_id=ls.id) AS tags
+   FROM lists_listsubscription ls
+   INNER JOIN lists_subscriberaddress sa ON sa.id=ls.subscriber_id
+   LEFT JOIN lists_subscriber s ON s.user_id=sa.subscriber_id
+   WHERE confirmed AND subscription_confirmed
+"""),
+    ]
-- 
2.51.0

---

From: Magnus Hagander <[email-masked]>
Date: Mon, 1 Dec 2025 21:00:47 +0100

On Mon, 22 Sept 2025 at 20:18, Célestin Matte <[email-masked]> wrote:

> The pglister_sync script is supposed to synchronize subscribers from
> pglister to pgarchives, so they can access archives.
>  From what I understand of its semantics, the "nomail" field (labeled
> "Disable mail delivery") is used to remain subscribed, to be able to access
> archives, without receiving emails.
> However, due to the way the API works, this is not the case.
> pglister_sync.py makes a call to the API, which get subscribers from the
> mailinglist_subscribers SQL view. This view is built in the
> 0027_merge_migrations.py migrations and excludes users with nomail.
> I propose a patch with a migration to change this SQL view to include
> nomail users (copying previous definition, without "AND NOT nomail").
> Note that this changes results of the API, which might have consequences
> if other scripts use it (I haven't found any with some grepping).
>

Hi!

mailinglist_subscribers is used for actual mail delivery as well, isn't it?
If you just change the view, it will change all deliveries and basically
the nomail switch to not work at all?

While you're on the right track to include them, don't we also need to
include the nomail column as a column in the view and then filter on that
when sending email?

//Magnus

---

From: Célestin Matte <[email-masked]>
Date: Thu, 18 Dec 2025 12:22:21 +0100

> mailinglist_subscribers is used for actual mail delivery as well, isn't it? If you just change the view, it will change all deliveries and basically the nomail switch to not work at all?
> 
> While you're on the right track to include them, don't we also need to include the nomail column as a column in the view and then filter on that when sending email?
Correct. I added nomail in the mailinglist_subscribers view, then made just it was filtered upon in mailhandler.py. Series of patches attached

Thanks for the review!
-- 
Célestin Matte

From 37ad3abc2887f28f118d1ae478e704698b731c3b Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <[email-masked]>
Date: Mon, 22 Sep 2025 19:57:21 +0200
Subject: [PATCH 1/3] Include nomail users in API call results

2.52.0

From cc778a4360fa7e3788eeff260b6cdaf313a62fc7 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <[email-masked]>
Date: Thu, 18 Dec 2025 11:21:04 +0100
Subject: [PATCH 2/3] Add nomail in mailinglist_subscribers view

So we can filter based on it during email delivery
---
 .../migrations/0060_fix_mailinglist_subscribers_sql_view.py    | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

diff --git a/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py b/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py
index 5f49e48..a8e2971 100644
--- a/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py
+++ b/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py
@@ -19,7 +19,8 @@ CREATE OR REPLACE VIEW mailinglist_subscribers AS
       eliminatecc,
       token,
       s.user_id AS userid,
-      (SELECT array_agg(listtag_id) FROM lists_listsubscription_tags lst WHERE lst.listsubscription_id=ls.id) AS tags
+      (SELECT array_agg(listtag_id) FROM lists_listsubscription_tags lst WHERE lst.listsubscription_id=ls.id) AS tags,
+      nomail
    FROM lists_listsubscription ls
    INNER JOIN lists_subscriberaddress sa ON sa.id=ls.subscriber_id
    LEFT JOIN lists_subscriber s ON s.user_id=sa.subscriber_id
-- 
2.52.0

From 351155d94417bdade7e6cbaff0b04f430566067a Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <[email-masked]>
Date: Thu, 18 Dec 2025 12:04:08 +0100
Subject: [PATCH 3/3] mail handler: don't send email to users with nomail set

---
 lib/handlers/mailhandler.py | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/lib/handlers/mailhandler.py b/lib/handlers/mailhandler.py
index 71a7d4e..c248871 100644
--- a/lib/handlers/mailhandler.py
+++ b/lib/handlers/mailhandler.py
@@ -866,7 +866,7 @@ ORDER BY 1""",
                     'Mail to list {0} delivered to {1} tags ({2})'.format(self.mlist.name, len(self.tags), ",".join(self.tagnames)),
                     self.messageid)
 
-            curs.execute("INSERT INTO outgoing_recipients (outgoing_id, subscriberaddress_id, recipient_headers) SELECT %(outid)s, subscriberaddress_id, CASE WHEN l.disable_list_headers THEN '' ELSE recipient_list_headers(%(webroot)s, %(listid)s, token) END FROM mailinglist_subscribers s INNER JOIN lists_list l ON l.id=s.listid WHERE listid=%(listid)s {0} AND NOT (COALESCE(eliminatecc, false) AND email=ANY(%(cclist)s)) ORDER BY split_part(email, '@', 2)".format(qextra), params)
+            curs.execute("INSERT INTO outgoing_recipients (outgoing_id, subscriberaddress_id, recipient_headers) SELECT %(outid)s, subscriberaddress_id, CASE WHEN l.disable_list_headers THEN '' ELSE recipient_list_headers(%(webroot)s, %(listid)s, token) END FROM mailinglist_subscribers s INNER JOIN lists_list l ON l.id=s.listid WHERE listid=%(listid)s {0} AND NOT (COALESCE(eliminatecc, false) AND email=ANY(%(cclist)s)) AND not nomail ORDER BY split_part(email, '@', 2)".format(qextra), params)
             recipientcount = curs.rowcount
             curs.execute("NOTIFY outgoing")
 
-- 
2.52.0