improving dbmail dbpgsql.c

I have this log:
dbpgsql.c,db_query(+279): [SELECT deliver_to FROM
dbmail_aliases WHERE 
lower(alias) = lower('userdomain.xx') AND
lower(alias) <> 
lower(deliver_to)]

couldn't you write ....'AND alias <> deliver_to'? I
mean, skip 
the "lower()' twice? Just force entries in that table
to be lower() 
already. I heard in some RFC they say that upper/lowercase
should be a 
difference, but I never saw that in live use.

mfg zmi
-- 
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0676/846 914 666                     
.network.your.ideas.
// PGP Key:        "curl -s http://zmi.at/zmi4.asc |
gpg --import"
// Fingerprint: EA39 8918 EDFF 0A68 ACFB  11B7 BA2D 060F
1C6F E6B0
// Keyserver: www.keyserver.net                   Key-ID:
1C6FE6B0
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Michael Monnerie wrote:
> I have this log:
> dbpgsql.c,db_query(+279): [SELECT deliver_to FROM
dbmail_aliases WHERE 
> lower(alias) = lower('userdomain.xx') AND
lower(alias) <> 
> lower(deliver_to)]
> 
> couldn't you write ....'AND alias <> deliver_to'?
I mean, skip 
> the "lower()' twice? Just force entries in that
table to be lower() 
> already. I heard in some RFC they say that
upper/lowercase should be a 
> difference, but I never saw that in live use.

That's incorrect. Email addresses are case-insensitive. And
no, we
cannot be sure the values entered are indeed lower-case,
unless we setup
a trigger.

Postgres allows you to 'create index lower(column)' which
will resolve
the performance issues here.


-- 
 
____________________________________________________________
____
  Paul Stevens                                      paul at
nfg.nl
  NET FACILITIES GROUP                     GPG/PGP:
1024D/11F8CD31
  The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

On Thu, Dec 28, 2006, jake anderson <jakevapourforge.com> said:

> Paul J Stevens wrote:
>> Michael Monnerie wrote:
>>   
>>> I have this log:
>>> dbpgsql.c,db_query(+279): [SELECT deliver_to
FROM dbmail_aliases WHERE 
>>> lower(alias) = lower('userdomain.xx') AND lower(alias) <> 
>>> lower(deliver_to)]
>>>
>>> couldn't you write ....'AND alias <>
deliver_to'? I mean, skip 
>>> the "lower()' twice? Just force entries in
that table to be lower() 
>>> already. I heard in some RFC they say that
upper/lowercase should be a 
>>> difference, but I never saw that in live use.
>>>     
>>
>> That's incorrect. Email addresses are
case-insensitive. And no, we
>> cannot be sure the values entered are indeed
lower-case, unless we setup
>> a trigger.
>>
>> Postgres allows you to 'create index lower(column)'
which will resolve
>> the performance issues here.
>>
>>
>>   
> I think the OP was saying to programatically insert
into the table in 
> lower, then postgres and mysql (and any other db you
care to name) can 
> play with it without conversion, yes there is some data
"lost" but its 
> insignifigant.

You're reading the OP correctly, but I would still do the
lookups with
forced lower case because the data might have been inserted
by someone
other than the dbmail-users program. I would prefer not to
have to specify
"if you write a third party tool, always make sure to
insert lowercase
data into this column" if we can just as easily enforce
that in the
queries. Making sure to build the  index with the lowercased
values is the
big issue I think.

Aaron
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

On Thu, Dec 28, 2006, Paul J Stevens wrote:

> Michael Monnerie wrote:
> > already. I heard in some RFC they say that
upper/lowercase should be a 
> > difference, but I never saw that in live use.
> 
> That's incorrect. Email addresses are case-insensitive.

Am I missing something? RFC 2821 clearly states ...

| The local-part of a mailbox MUST BE treated as case
sensitive.
| Therefore, SMTP implementations MUST take care to preserve
the case
| of mailbox local-parts. Mailbox domains are not case
sensitive. In
| particular, for some hosts the user "smith" is
different from the
| user "Smith". However, exploiting the case
sensitivity of mailbox
| local-parts impedes interoperability and is discouraged.

I would rather expect, that this behaviour is maintained
even in some
backend. Even if it's not common case to have two different
email
addresses smithexample.com annesstoexample.com, IMHO any
serious
email software should be able to behave RFC compliant by
default.

-cs
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

On 29 Dec 2006, at 03:38, Rod K wrote:

> That's still not an elegant solution.  The best
solution would be  
> be to write a constraint or domain in the DB that would
prevent  
> uppercase characters being inserted at all no matter
where the  
> insertion is coming from.

That's what I would call a brute-force approach. The case of
e-mail  
addresses should be preserved. Furthermore, it's only the
domain part  
of the address that's case insensitive. The local part is
actually  
case sensitive.

Robert

_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Christoph Schug wrote:
> On Thu, Dec 28, 2006, Paul J Stevens wrote:
> 
>> Michael Monnerie wrote:
>>> already. I heard in some RFC they say that
upper/lowercase should be a 
>>> difference, but I never saw that in live use.
>> That's incorrect. Email addresses are
case-insensitive.
> 
> Am I missing something? RFC 2821 clearly states ...
> 
> | The local-part of a mailbox MUST BE treated as case
sensitive.
> | Therefore, SMTP implementations MUST take care to
preserve the case
> | of mailbox local-parts. Mailbox domains are not case
sensitive. In
> | particular, for some hosts the user "smith"
is different from the
> | user "Smith". However, exploiting the case
sensitivity of mailbox
> | local-parts impedes interoperability and is
discouraged.
> 
> I would rather expect, that this behaviour is
maintained even in some
> backend. Even if it's not common case to have two
different email
> addresses smithexample.com annesstoexample.com, IMHO any
serious
> email software should be able to behave RFC compliant
by default.

I disagree. Dbmail is *not* an SMTP server so we don't have
to follow
smtp rules. I'm sure that if dbmail starts bouncing mail
because of
strict case sensitive LHS matching, that would upset a lot
of people.





-- 
 
____________________________________________________________
____
  Paul Stevens                                      paul at
nfg.nl
  NET FACILITIES GROUP                     GPG/PGP:
1024D/11F8CD31
  The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

On Fri, Dec 29, 2006, Paul J Stevens wrote:

> Christoph Schug wrote:
> > On Thu, Dec 28, 2006, Paul J Stevens wrote:
> > 
> >> That's incorrect. Email addresses are
case-insensitive.
> > 
> > Am I missing something? RFC 2821 clearly states
...
[...]
> > I would rather expect, that this behaviour is
maintained even in some
> > backend. Even if it's not common case to have two
different email
> > addresses smithexample.com annesstoexample.com, IMHO any serious
> > email software should be able to behave RFC
compliant by default.
> 
> I disagree. Dbmail is *not* an SMTP server so we don't
have to follow
> smtp rules. I'm sure that if dbmail starts bouncing
mail because of
> strict case sensitive LHS matching, that would upset a
lot of people.

On the otherhand it doesn't make any sense to separate
information
during the SMTP transport just to intermix it finally at the
DBMail
backend.

Then it should be clearly stated in the README file, that
RFC conformant
setups like mentioned above cannot be implemented with
DBMail.
Furthermore man pages need to be fixed up, especially
dbmail-lmtpd(8)
quoting

| dbmail-lmtpd - receive messages from an MTA supporting the
Lightweight
| Mail Transport Protocol, as specified in RFC 2033.

as in return RFC 2033 states (without any further exception
regarding
the local part of the address)

| The LMTP protocol is identical to the SMTP protocol SMTP
[SMTP]
| [HOST-REQ] with its service extensions [ESMTP], except as
modified by
| this document.

referencing RFC 821, which has been obsoleted by RFC 2821.

I agree with you that this is not a very common setup,
nevertheless it
is explicitly defined by the standard. Thus, it's more a
question of
ruling principle here. Violating standards intentionally
just to make
ignorant people happy might not pay out on the long term.

-cs
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

I wasn't debating whether the names SHOULD be case-sensitive
or not, 
rather the method for enforcing insensitivity in the DB. 
Coercing 
strings to lowercase for comparison on each query and
therefore 
requiring a separate index is brute force.  Enforcing the
integrity of 
the data at insertion is just good design.

Now, that said, while RFC 2821 states that SMTP
implementations MUST 
treat the local part as case-sensitive, this does NOT mean
that any 
particular provider MUST have case-sensitive local names. 
Since some 
providers MAY offer case-sensitive names, each SMTP server
in the route 
must maintain the case integrity.

There are many characters that are allowable by RFC in a
local name, 
that does NOT mean that every provider has to allow every
character in 
THEIR local names.  A tilde is a perfectly valid character
to use in a 
local name, however, very few administrators would allow it
as a valid 
name character to avoid confusion.  I can also choose, as
the 
administrator for email services I provide, NOT to offer
case-sensitive 
names for the same reason.  An email address such as
mrbillexample.com 
could be written in all lowercase, all uppercase or mixed
case 
(MrBillexample.com) and still be delivered.  As a corporate
policy this 
just makes sense.

So when person A sends an email to "mrbillexample.com" and person B 
sends an email to "MrBillexample.com" the case
is preserved until 
delivery so that if we DO have case-sensitive names the
correct user 
will receive the email.  However, there's nothing that says
(even RFCs) 
that the local system cannot treat those as the same
mailbox.

Criticizing dbmail as not RFC compliant because it does not
allow 
case-sensitive usernames is a straw horse.  Criticizing
dbmail because 
it doesn't offer case-sensitive usernames when YOU want to
allow them is 
valid.

Robert Claeson wrote:
>
> On 29 Dec 2006, at 03:38, Rod K wrote:
>
>> That's still not an elegant solution.  The best
solution would be be 
>> to write a constraint or domain in the DB that
would prevent 
>> uppercase characters being inserted at all no
matter where the 
>> insertion is coming from.
>
> That's what I would call a brute-force approach. The
case of e-mail 
> addresses should be preserved. Furthermore, it's only
the domain part 
> of the address that's case insensitive. The local part
is actually 
> case sensitive.
>
> Robert
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail