[fossil-users] email testing - no subscriber table?

[Jungle Boogie]

Hi All,

I think I've brought my repo up to trunk, did a little configuring of the email
page and attempted to add a subscriber, but I have a database error:

----
SQLITE_ERROR: no such table: subscriber
Database Error

no such table: subscriber SELECT 1 FROM subscriber WHERE suname='jungle'
----

I don't see the subscriber table at /repo_schema, if that means anything.

Perhaps someone else can double-check this; I've done a fossil rebuild but no
change.

The /stat page doesn't show a new schema version:
Schema Version: 2015-01-24, sha3

Should there be a schema version update?

Thanks!

[Richard Hipp]

Post by Jungle Boogie
no such table: subscriber SELECT 1 FROM subscriber WHERE suname='jungle'

The email notification tables are created on-demand. Apparently I
have missed a call to "email_schema()" someplace in the code. Fix
this by running the command:

fossil email reset

Note that the email notification schema is still in flux. It will
likely change again, multiple times, before release. To update the
schema run "fossil email reset" again. Bare in mind that when you do
so, it will erase all of your subscriber information.

Some kind of automatic schema upgrade that preservers subscriber
information will be incorporated prior to release.

[Richard Hipp]

Rough developers notes are available at
https://www.fossil-scm.org/fossil/doc/trunk/www/emaildesign.md for
anybody who wants to experiment with or contribute to this
work-in-progress.

[Svyatoslav Mishyn]

Hi,

Post by Richard Hipp
Rough developers notes are available at
https://www.fossil-scm.org/fossil/doc/trunk/www/emaildesign.md for
anybody who wants to experiment with or contribute to this
work-in-progress.

quick typos:

Index: src/email.c
==================================================================
--- src/email.c
+++ src/email.c
@@ -855,11 +855,11 @@
** WEBPAGE: subscribe
**
** Allow users to subscribe to email notifications.
**
** This page is usually run by users who are not logged in.
-** A logged-in user can add email notificates on the /alerts page.
+** A logged-in user can add email notifications on the /alerts page.
** Access to this page by a logged in user (other than an
** administrator) results in a redirect to the /alerts page.
**
** Administrators can visit this page in order to sign up other
** users.

Index: www/emaildesign.md
==================================================================
--- www/emaildesign.md
+++ www/emaildesign.md
@@ -6,11 +6,11 @@
understanding of how Fossil handles email notification, to help
with doing custom configurations, or to help contribute features.

This document assumes expert-level systems knowledge. A separate
tutorial for setting up email notification by non-experts will be
-generated once the email notification systems stablizes.
+generated once the email notification systems stabilizes.

Email notification is under active development as of this writing
(2018-06-23). Check back frequently for updates.

Data Design
@@ -76,11 +76,11 @@
email messages to the Postfix mail transfer agent. There is
an example TCL script in the
[tools/email-monitor.tcl](/file/tools/email-monitor.tcl) file
of the source tree that shows how to do this.

- 3. <b>"dir"</b> $rarr; Write outgoing email messages as individual
+ 3. <b>"dir"</b> &rarr; Write outgoing email messages as individual
files in a designated directory. This might be useful for
testing and debugging.

Internally, there is a fourth email sending method named "stdout"
which simply writes the text of the email message on standard output.


and thank you!

[Svyatoslav Mishyn]

another one (+ previous one):

-generated once the email notification systems stablizes.
+generated once the email notification systems stabilizes.

and

- of the source tree that shows how to this is done.
+ of the source tree that shows how this is done.


(I don't know, should I add such small edits in full attachment patch
file, or is it ok to just inline them?)

[Jungle Boogie]

Post by Svyatoslav Mishyn
-generated once the email notification systems stablizes.
+generated once the email notification systems stabilizes.
and
- of the source tree that shows how to this is done.
+ of the source tree that shows how this is done.
(I don't know, should I add such small edits in full attachment patch
file, or is it ok to just inline them?)

Patches are probably easier to add, and I've inlined them in the past.

[Richard Hipp]

Just FYI:

I have opened up email notifications on the canonical Fossil
repository. To subscribe, visit:

https://fossil-scm.org/fossil/subscribe

Your help in finding creative ways of breaking the new system is appreciated.

Please note that this is still a work-in-progress. All subscription
data may be erased at any time. Email notifications might be disabled
at any time, in order to close security holes or otherwise work on the
system.

[Jungle Boogie]

Post by Richard Hipp
I have opened up email notifications on the canonical Fossil
https://fossil-scm.org/fossil/subscribe
Your help in finding creative ways of breaking the new system is appreciated.

Looks like + signs are not allowed for email addresses.

Without quotes, local-parts may consist of any combination of
alphabetic characters, digits, or any of the special characters

! # $ % & ' * + - / = ? ^ _ ` . { | } ~

https://tools.ietf.org/html/rfc3696#section-3

Post by Richard Hipp
Please note that this is still a work-in-progress. All subscription
data may be erased at any time. Email notifications might be disabled
at any time, in order to close security holes or otherwise work on the
system.
--
D. Richard Hipp

[Olivier R.]

Post by Richard Hipp
I have opened up email notifications on the canonical Fossil
https://fossil-scm.org/fossil/subscribe
Your help in finding creative ways of breaking the new system is appreciated.
Please note that this is still a work-in-progress. All subscription
data may be erased at any time. Email notifications might be disabled
at any time, in order to close security holes or otherwise work on the
system.

I tried to subscribe to announcements. When clicking on the confirmation
link, I got an error message:

SQLITE_ERROR: near "WHERE": syntax error
Database Error

near "WHERE": syntax error: {UPDATE subscriber SET sdonotcall=0,
sdigest=0, ssub='a', smtime=julianday('now'),
smip='2a01:e34:ef81:f330:9d31:8a60:9929:cf57', WHERE
subscriberCode=hextoblob('19A13F7F9591E417BC2CAAEAF0FA824ABFD51762410C87016A593809027557CB')}

[j. van den hoff]

I get an sqlite error when following the verification link and hitting the
`submit' button there:

SQLITE_ERROR: near "WHERE": syntax error

Database Error
near "WHERE": syntax error: {UPDATE subscriber SET sdonotcall=0,
sdigest=0, ssub='c', smtime=julianday('now'), smip='*****', WHERE
subscriberCode=hextoblob('*****')}

Post by Richard Hipp
I have opened up email notifications on the canonical Fossil
https://fossil-scm.org/fossil/subscribe
Your help in finding creative ways of breaking the new system is appreciated.
Please note that this is still a work-in-progress. All subscription
data may be erased at any time. Email notifications might be disabled
at any time, in order to close security holes or otherwise work on the
system.

[Richard Hipp]

The UPDATE syntax error should be fixed now. Please try it again.

Post by j. van den hoff
I get an sqlite error when following the verification link and hitting the
SQLITE_ERROR: near "WHERE": syntax error
Database Error
near "WHERE": syntax error: {UPDATE subscriber SET sdonotcall=0,
sdigest=0, ssub='c', smtime=julianday('now'), smip='*****', WHERE
subscriberCode=hextoblob('*****')}

Post by Richard Hipp
I have opened up email notifications on the canonical Fossil
https://fossil-scm.org/fossil/subscribe
Your help in finding creative ways of breaking the new system is appreciated.
Please note that this is still a work-in-progress. All subscription
data may be erased at any time. Email notifications might be disabled
at any time, in order to close security holes or otherwise work on the
system.

--
Using Opera's revolutionary email client: http://www.opera.com/mail/

[j. van den hoff]

Post by Richard Hipp
The UPDATE syntax error should be fixed now. Please try it again.

yes, it works now. thank you. NB: I received the notification email
regarding the fix prior to actually confirming the subscription again --
so my confirmation went through despite the SQL error? or is this
indication of some flaw in the subscription logic?

Post by Richard Hipp

Post by j. van den hoff
I get an sqlite error when following the verification link and hitting the
SQLITE_ERROR: near "WHERE": syntax error
Database Error
near "WHERE": syntax error: {UPDATE subscriber SET sdonotcall=0,
sdigest=0, ssub='c', smtime=julianday('now'), smip='*****', WHERE
subscriberCode=hextoblob('*****')}

Post by Richard Hipp
I have opened up email notifications on the canonical Fossil
https://fossil-scm.org/fossil/subscribe
Your help in finding creative ways of breaking the new system is appreciated.
Please note that this is still a work-in-progress. All subscription
data may be erased at any time. Email notifications might be disabled
at any time, in order to close security holes or otherwise work on the
system.

--
Using Opera's revolutionary email client: http://www.opera.com/mail/

[Richard Hipp]

Post by j. van den hoff

Post by Richard Hipp
The UPDATE syntax error should be fixed now. Please try it again.

yes, it works now. thank you. NB: I received the notification email
regarding the fix prior to actually confirming the subscription again --
so my confirmation went through despite the SQL error? or is this
indication of some flaw in the subscription logic?

You had already been verify by the fact of visiting the page itself.
That was a separate UPDATE (with the correct syntax!) that was
performed in a different transaction.

Thanks for pointing this out, though. I did have to think about it
for a few seconds.

[j. van den hoff]

Post by Richard Hipp

Post by j. van den hoff

Post by Richard Hipp
The UPDATE syntax error should be fixed now. Please try it again.

yes, it works now. thank you. NB: I received the notification email
regarding the fix prior to actually confirming the subscription again --
so my confirmation went through despite the SQL error? or is this
indication of some flaw in the subscription logic?

You had already been verify by the fact of visiting the page itself.
That was a separate UPDATE (with the correct syntax!) that was
performed in a different transaction.
Thanks for pointing this out, though. I did have to think about it
for a few seconds.

thanks for this explanation...

regarding the format/wording of the notification, it currently reads:

8<--
This is an automated email sent by the Fossil repository at
https://fossil-scm.org/fossil to report changes.

== 2018-06-24 10:07:29 Check-In ==
Fix an SQL syntax error. (user: drh tags: trunk)
https://fossil-scm.org/fossil/info/0398e41aa6f72c0da60b

------------------------------------------------------------------------
Subscription info: https://fossil-scm.org/fossil/alerts/*****
8<--

personally, I would prefer to simply omit the first line or to put it at
the bottom of the footer, since I would recognize the nature of the mail
without this explanation, so it's essentially explaining the obvious and a
minor distraction from the actual content of the mail (and the repos
identity can also be taken from the `subscription info' link).

additionally, mabye shorten the footer separation line to exactly two
`--', treating the footer as the sender's "affiliation/identity" (which
usually leads to a less prominent display by the email client).

[Dingyuan Wang]

Hello,

I previously set to receive these before unsubscribing:

[x] Announcements
[x] Check-ins
[ ] Ticket changes
[x] Wiki
[x] Daily digest only

I just got 50 exactly same "[fossil-src] activity alert" emails
describing 5 check-ins in three minutes. What's happening to the server?

Besides the mail server problem, this subject is too general. Something
like "Daily digest: 5 check-ins by drh[, usera, userb]" is better.

Post by j. van den hoff

Post by Richard Hipp

Post by j. van den hoff

The UPDATE syntax error should be fixed now.  Please try it again.

yes, it works now. thank you. NB: I received the notification email
regarding the fix prior to actually confirming the subscription again --
so my confirmation went through despite the SQL error? or is this
indication of some flaw in the subscription logic?

You had already been verify by the fact of visiting the page itself.
That was a separate UPDATE (with the correct syntax!) that was
performed in a different transaction.
Thanks for pointing this out, though.  I did have to think about it
for a few seconds.

thanks for this explanation...
8<--
This is an automated email sent by the Fossil repository at
https://fossil-scm.org/fossil to report changes.
== 2018-06-24 10:07:29 Check-In ==
Fix an SQL syntax error. (user: drh tags: trunk)
https://fossil-scm.org/fossil/info/0398e41aa6f72c0da60b
------------------------------------------------------------------------
Subscription info: https://fossil-scm.org/fossil/alerts/*****
8<--
personally, I would prefer to simply omit the first line or to put it at
the bottom of the footer, since I would recognize the nature of the mail
without this explanation, so it's essentially explaining the obvious and
a minor distraction from the actual content of the mail (and the repos
identity can also be taken from the `subscription info' link).
additionally, mabye shorten the footer separation line to exactly two
`--', treating the footer as the sender's "affiliation/identity" (which
usually leads to a less prominent display by the email client).

[Richard Hipp]

Post by Dingyuan Wang
I just got 50 exactly same "[fossil-src] activity alert" emails

Thanks for the report. The problem should be fixed now.

There is a variable in the database that remembers when the last
digest was sent. Do to a missed COMMIT, that variable was never being
updated. Hence, the digests were being sent over, and over, and over.
I think I have the problem fixed now, but I do need to better
instrument the code to detect these kinds of things and provide better
diagnostics.

[Richard Hipp]

Post by Dingyuan Wang
I just got 50 exactly same "[fossil-src] activity alert" emails
describing 5 check-ins in three minutes. What's happening to the server?

Did today's digest arrive successfully, and only once?

[Dingyuan Wang]

Yes.

Post by Richard Hipp

Post by Dingyuan Wang
I just got 50 exactly same "[fossil-src] activity alert" emails
describing 5 check-ins in three minutes. What's happening to the server?

Did today's digest arrive successfully, and only once?

[Andy Goth]

Post by j. van den hoff
additionally, mabye shorten the footer separation line to exactly two
`--', treating the footer as the sender's "affiliation/identity" (which
usually leads to a less prominent display by the email client).

If you're talking about email signatures, they are preceded by the magic
character sequence dash-dash-space-newline.

[j. van den hoff]

Post by Andy Goth

Post by j. van den hoff
additionally, mabye shorten the footer separation line to exactly two
`--', treating the footer as the sender's "affiliation/identity" (which
usually leads to a less prominent display by the email client).

If you're talking about email signatures, they are preceded by the magic
character sequence dash-dash-space-newline.

right. actually, I was not aware of the mandatory space before the
newline. thank you.

[Jungle Boogie]

Post by Richard Hipp
I have opened up email notifications on the canonical Fossil
https://fossil-scm.org/fossil/subscribe
Your help in finding creative ways of breaking the new system is appreciated.

Not a way of breaking it, but what do you think about having a link to the rss
feed at the bottom of the horizontal divide? "Follow the project via rss", or
something similar to that?

rss may not be a cool as messages to your slack channel, but it's still quite
reliable and really simple ;).

[Stéphane Aulery]

Hello,

Post by Richard Hipp
I have opened up email notifications on the canonical Fossil
https://fossil-scm.org/fossil/subscribe
Your help in finding creative ways of breaking the new system is appreciated.

I subscribed at 2018-06-24 18:22:53 and never received the message of
confirmation.

Regards,

[Richard Hipp]

Post by Dingyuan Wang
Hello,

Post by Richard Hipp
I have opened up email notifications on the canonical Fossil
https://fossil-scm.org/fossil/subscribe
Your help in finding creative ways of breaking the new system is appreciated.

I subscribed at 2018-06-24 18:22:53 and never received the message of
confirmation.

This is what your email system said to the Fossil server when it tried
to send your verification email:

Jun 24 18:32:49 ubuntu postfix/smtp[2330]: C2148CE08:
to=<***@free.fr>, relay=mx1.free.fr[212.27.48.6]:25, delay=0.98,
delays=0/0/0.51/0.47, dsn=5.0.0, status=bounced (host
mx1.free.fr[212.27.48.6] said: 550 spam detected (in reply to end of
DATA command))

I do not (yet) have the bounce-processing logic working in the new
email notification system working, and so Fossil was not able to
detect that your confirmation request had bounced.

[The Tick]

Post by Richard Hipp
I do not (yet) have the bounce-processing logic working in the new
email notification system working, and so Fossil was not able to
detect that your confirmation request had bounced.

Thought I'd mention this. My ISP implements an anti-spam technique that
saves the sender's hostname and/or IP and bounces the first email from
that sender as temporarily unavailable (or something like that).
Subsequent emails are accepted. The idea being that a spammer's email
software won't bother dealing with temporary failures. It seems to work
pretty well.

[Stéphane Aulery]

Post by Richard Hipp

Post by Dingyuan Wang
Hello,

Post by Richard Hipp
I have opened up email notifications on the canonical Fossil
https://fossil-scm.org/fossil/subscribe
Your help in finding creative ways of breaking the new system is appreciated.

I subscribed at 2018-06-24 18:22:53 and never received the message of
confirmation.

This is what your email system said to the Fossil server when it tried
delays=0/0/0.51/0.47, dsn=5.0.0, status=bounced (host
mx1.free.fr[212.27.48.6] said: 550 spam detected (in reply to end of
DATA command))
I do not (yet) have the bounce-processing logic working in the new
email notification system working, and so Fossil was not able to
detect that your confirmation request had bounced.

Ok. This one is already on heavy load, becauc^se I follow a lot of lists.

I tried with an other address and it's worked.

Thanks,

[jungle Boogie]

Post by Richard Hipp
I have opened up email notifications on the canonical Fossil
https://fossil-scm.org/fossil/subscribe
Your help in finding creative ways of breaking the new system is appreciated.

This is already touched on in your email design document, but I'm just
adding another point here.

I really like the idea of the subscriberCode, because passwords are
not needed to be stored or remembered to alter the subscription.
However, the subscriberCode doesn't have to be stolen for the
subscription to be altered. If I inadvertently forward my email along
to someone/group without modifying the footer, the person/group would
be able to alter my subscription.

But as you point out, only the email address is available for the
miscreant and no username/password, etc.

[Richard Hipp]

Post by jungle Boogie
If I inadvertently forward my email along
to someone/group without modifying the footer, the person/group would
be able to alter my subscription.

How can I fix that?

[jungle Boogie]

Post by Richard Hipp

Post by jungle Boogie
If I inadvertently forward my email along
to someone/group without modifying the footer, the person/group would
be able to alter my subscription.

How can I fix that?

I don't know, but maybe that idea of randomizing them every 12-24
hours is a good stop gap?

Perhaps the system be enhanced to randomize the subscriberCodes
periodically - say just before each daily digest is sent out?

Post by Richard Hipp
--
D. Richard Hipp

[j. van den hoff]

On Mon, 25 Jun 2018 23:57:35 +0200, jungle Boogie

Post by Richard Hipp

Post by jungle Boogie
If I inadvertently forward my email along
to someone/group without modifying the footer, the person/group would
be able to alter my subscription.

How can I fix that?

it seems the only way would be to _not_ include the link in each and every
alert, no? maybe automated separate notification mail once every (3?)
months ("your subscription details are here:") would be feasible? many
mailing lists do the same (mailing subscription password reminders once a
month or every 3 months). o.t.o.h.: it is of course not really a big deal
to leave it as is (but the unintentional dissemination of the subscription
links via forwarding the alert mails will then happen regularly, of
course).

[Olivier R.]

Post by j. van den hoff
On Mon, 25 Jun 2018 23:57:35 +0200, jungle Boogie

Post by Richard Hipp

Post by jungle Boogie
If I inadvertently forward my email along
to someone/group without modifying the footer, the person/group would
be able to alter my subscription.

How can I fix that?

it seems the only way would be to _not_ include the link in each and
every alert, no? maybe automated separate notification mail once every
(3?) months ("your subscription details are here:") would be feasible?
many mailing lists do the same (mailing subscription password reminders
once a month or every 3 months). o.t.o.h.: it is of course not really a
big deal to leave it as is (but the unintentional dissemination of the
subscription links via forwarding the alert mails will then happen
regularly, of course).

Why not just include the unsubscribe link (*)?

Users enter their e-mail and get via e-mail a link to unsubscribe or
change the subscriptions.

Olivier

* https://fossil-scm.org/fossil/unsubscribe

[Sam Putman]

Post by Richard Hipp

Post by jungle Boogie
If I inadvertently forward my email along
to someone/group without modifying the footer, the person/group would
be able to alter my subscription.

How can I fix that?
--
D. Richard Hipp

This is a pretty normal problem with mailing lists, the standard response
is
to send a confirmation email for subscription and an alert email for
unsubscription.

The alert email has a one-click resubscribe, so all our Mallory can do here
is
attempt to subscribe me to an email list, and unsubscribe me such that when
I check
my email, I find the unsubscription and fix the problem.

If someone develops a persistent enemy who finds this form of mild
harassment satisfying,
you could add a user option "send a confirmation email before
unsubscription/any change".

For most users that's one step too many, but with that engaged, all that's
left is sending
spam requesting various changes.

[Andreas Kupries]

Post by Richard Hipp

Post by Jungle Boogie
no such table: subscriber SELECT 1 FROM subscriber WHERE suname='jungle'

The email notification tables are created on-demand. Apparently I
have missed a call to "email_schema()" someplace in the code. Fix
fossil email reset

And this (mentioning the schema) reminds me, go to any of the .fossil
repositories on core (/home/www/fossil/ and look at their fx_...
tables. `fx_aku_watch_tktfield`, and config entries `fx-...`.

Also `fx note --help`.

The fx system might go beyond what you are doing, I have not checked
your work yet. The main config is for tickets, to tell the system
which fields to look at for additional email addresses beyond the type
specific destinations.
--
See you,
Andreas Kupries <***@shaw.ca>
<http://core.tcl.tk/akupries/>
Developer @ SUSE (MicroFocus Canada LLC)
<***@suse.com>

EuroTcl 2018, Jul 7-8, Munich/DE, http://eurotcl.eu/
Tcl'2018, Oct 15-19, Houston, TX, USA. https://www.tcl.tk/community/tcl2018/
-------------------------------------------------------------------------------