public inbox for pgsql-www@postgresql.org
help / color / mirror / Atom feedpglister: pgarchives does not synchronize subscribers with email delivery disabled (proposed patch)
3+ messages / 3 participants
[nested] [flat]
* pglister: pgarchives does not synchronize subscribers with email delivery disabled (proposed patch)
@ 2025-09-22 18:12 Célestin Matte <contact@cmatte.me>
0 siblings, 1 reply; 3+ messages in thread
From: Célestin Matte @ 2025-09-22 18:12 UTC (permalink / raw)
To: PostgreSQL WWW <pgsql-www@lists.postgresql.org>
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
Attachments:
[text/x-patch] 0001-Include-nomail-users-in-API-call-results.patch (1.6K, 2-0001-Include-nomail-users-in-API-call-results.patch)
download | inline diff:
From 37ad3abc2887f28f118d1ae478e704698b731c3b Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <dev@cmatte.me>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pglister: pgarchives does not synchronize subscribers with email delivery disabled (proposed patch)
@ 2025-12-01 20:00 Magnus Hagander <magnus@hagander.net>
parent: Célestin Matte <contact@cmatte.me>
0 siblings, 1 reply; 3+ messages in thread
From: Magnus Hagander @ 2025-12-01 20:00 UTC (permalink / raw)
To: Célestin Matte <contact@cmatte.me>; +Cc: PostgreSQL WWW <pgsql-www@lists.postgresql.org>
On Mon, 22 Sept 2025 at 20:18, Célestin Matte <contact@cmatte.me> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pglister: pgarchives does not synchronize subscribers with email delivery disabled (proposed patch)
@ 2025-12-18 11:22 Célestin Matte <celestin.matte@cmatte.me>
parent: Magnus Hagander <magnus@hagander.net>
0 siblings, 0 replies; 3+ messages in thread
From: Célestin Matte @ 2025-12-18 11:22 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: PostgreSQL WWW <pgsql-www@lists.postgresql.org>
> 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
Attachments:
[text/x-patch] 0001-Include-nomail-users-in-API-call-results.patch (1.6K, 2-0001-Include-nomail-users-in-API-call-results.patch)
download | inline diff:
From 37ad3abc2887f28f118d1ae478e704698b731c3b Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <dev@cmatte.me>
Date: Mon, 22 Sep 2025 19:57:21 +0200
Subject: [PATCH 1/3] 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.52.0
[text/x-patch] 0002-Add-nomail-in-mailinglist_subscribers-view.patch (1.3K, 3-0002-Add-nomail-in-mailinglist_subscribers-view.patch)
download | inline diff:
From cc778a4360fa7e3788eeff260b6cdaf313a62fc7 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <dev@cmatte.me>
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
[text/x-patch] 0003-mail-handler-don-t-send-email-to-users-with-nomail-s.patch (1.8K, 4-0003-mail-handler-don-t-send-email-to-users-with-nomail-s.patch)
download | inline diff:
From 351155d94417bdade7e6cbaff0b04f430566067a Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <dev@cmatte.me>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-12-18 11:22 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-22 18:12 pglister: pgarchives does not synchronize subscribers with email delivery disabled (proposed patch) Célestin Matte <contact@cmatte.me>
2025-12-01 20:00 ` Magnus Hagander <magnus@hagander.net>
2025-12-18 11:22 ` Célestin Matte <celestin.matte@cmatte.me>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox