Email data type

Gaetano Mendola wrote:

Hi all, this is the alpha version for the new email data type,
is not written as built in type but as plug in, Tome Lane and others
drove me in this direction.

The type is indexable and provide also conversion methods:

text <--> email
and the operator >>, is possible use it in select like:

select * from my_user where email >> 'hotmail.com';

this select will extract all records with an email inside
the domain 'hotmail.com'.

The validation routine is very simple, right now test only
the presence of character '@' (it's an alpha version), I'd like
to put it on pgfoundry but apparently my DNS are unable to resolve
www.pgfoundry.org. I'll put this version as soon the address become
available.

This is the first time that I wrote code for postgresql so please, if
you can, do a sort of code revision on it.

Comments are welcomed.

Forget the version attached and use the version on pgfoundry.

Regards
Gaetano Mendola

On Sun, May 16, 2004 at 04:36:55 +0200,
Gaetano Mendola <mendola@bigfoot.com> wrote:

The type is indexable and provide also conversion methods:

text <--> email
and the operator >>, is possible use it in select like:

When you are converting between text and email data, what format are the
text version of the address going to be? For example you might be using
rfc2821 encoding, rfc2822 encoding or concatenating the local part,
an @ sign and the domain name. Don't forget about domain literals.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bruno Wolff III wrote:

| On Sun, May 16, 2004 at 04:36:55 +0200,
| Gaetano Mendola <mendola@bigfoot.com> wrote:
|
|>The type is indexable and provide also conversion methods:
|>
|>text <--> email
|>and the operator >>, is possible use it in select like:
|
|
| When you are converting between text and email data, what format are the
| text version of the address going to be? For example you might be using
| rfc2821 encoding, rfc2822 encoding or concatenating the local part,
| an @ sign and the domain name. Don't forget about domain literals.

Actually I use <local_part>@<domain_name>

Also the validator will validate emails in this form, if you are thinking to
validate emails as:

"Gaetano M. Public"(junior)<gmendola@(new account)bigfoot.com>

that are perfectly valid I think that it's a valid option to consider.

About the domain literals, I think to validate it in the near future,
rejecting private subnet according to this list:

10.0.0.0 - 10.255.255.255
172.16.0.0 - 172.31.255.255
192.168.0.0 - 192.168.255.255
169.254.0.0 -169.254.255.255

I think I have to discard also the addresses with last octet equal to 256.

Any comments ?

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFAqNPP7UpzwH2SGd4RAgqxAJ9UCPOOTpPN6Tl9w1748inn7t4a/QCg7pl1
8O3lHDwEKsCuCOJYQAo0vHM=
=wFo+
-----END PGP SIGNATURE-----

-----Original Message-----
From: Gaetano Mendola [mailto:mendola@bigfoot.com]
Sent: 17 May 2004 16:02
To: Bruno Wolff III
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Email data type

About the domain literals, I think to validate it in the near
future, rejecting private subnet according to this list:

10.0.0.0 - 10.255.255.255
172.16.0.0 - 172.31.255.255
192.168.0.0 - 192.168.255.255
169.254.0.0 -169.254.255.255

Why? What's wrong with private addresses?

Regards, Dave.

On Mon, May 17, 2004 at 05:01:36PM +0200, Gaetano Mendola wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bruno Wolff III wrote:

| On Sun, May 16, 2004 at 04:36:55 +0200,
| Gaetano Mendola <mendola@bigfoot.com> wrote:
|
|>The type is indexable and provide also conversion methods:
|>
|>text <--> email
|>and the operator >>, is possible use it in select like:
|
|
| When you are converting between text and email data, what format are the
| text version of the address going to be? For example you might be using
| rfc2821 encoding, rfc2822 encoding or concatenating the local part,
| an @ sign and the domain name. Don't forget about domain literals.

Actually I use <local_part>@<domain_name>

Also the validator will validate emails in this form, if you are thinking to
validate emails as:

"Gaetano M. Public"(junior)<gmendola@(new account)bigfoot.com>

that are perfectly valid I think that it's a valid option to consider.

About the domain literals, I think to validate it in the near future,
rejecting private subnet according to this list:

10.0.0.0 - 10.255.255.255
172.16.0.0 - 172.31.255.255
192.168.0.0 - 192.168.255.255
169.254.0.0 -169.254.255.255

Bad idea. If I'm testing I'm likely to be testing with addresses on my
local network. My local network is in 10/8. The middleware or the
application should be making those decisions. But if you are going to
filter on IP space, also consider class D & E space.

Also, a@10.11.12.13 is a syntactically valid email address, in the .13
TLD. It does not deliver to 10.11.12.13, or anywhere else, as of
today, unless the MTA or local recursive resolver is broken (a common
case). a@[10.11.12.13] is a whole other thing. As is a@[::10.11.12.13]
and various other IPv6 variants.

a@foo.bar is syntactically valid. a@foo.invalid is syntactically valid, but
should be immediately rejected. a@example.com is valid, even if known to
be non-deliverable.

I think I have to discard also the addresses with last octet equal to 256.

Or equal to 4872014, come to that. Any reason you're looking at 256
in particular? If you mean .255 (or .0) then don't fall into that trap -
there are perfectly valid, routable addresses ending in both .0 and
.255, despite what some folks in Redmond would have you believe.

Any comments ?

Parsing email addresses is a significant part of my day job, and email
address validation is a lot harder than it looks at first sight.

Don't forget quoting, whitespace, escaping and nesting parenthetical
comments. Also, remember that A@b.com and a@b.com are different email
addresses, while a@b.com and a@B.com are the same email
address. POSTMASTER@b.com and postmaster@b.com are the same email
address. ABUse@b.com and abuse@b.com may be the same address or
different email addresses, depending on which religious faction you
belong to.

In some contexts the empty string is a valid email address. In some
contexts "Postmaster" is a valid email address.

Also, one persons definition of a valid email address will be very
different from another persons definition of such. Many of those
definitions require some DNS resolution to make the decision.

I'm not entirely convinced that an email address is a simple and
well-defined enough datatype to handle comprehensively within the
DB. The validation decisions are complex and vary from application to
application.

(I use two text columns - localpart and domainpart, with an index
on reverse(lower(domainpart)) and leave validation to the application,
myself).

Cheers,
Steve

From: Gaetano Mendola [mailto:mendola@bigfoot.com]

I think I have to discard also the addresses with last octet
equal to 256.

Any comments ?

Any octet that contains a number less than 0 or greater than 255 should be
suspect.

Assuming you really meant 255:

It would be perfectly legal for an entity on the internet to have a block of
addresses with a subnet mask of less than 24 bits, which leads to legal
addresses that end in 255.

For example, if your company/university/black helicopter squad needed about
500 servers with direct presence, you might be assigned a block like
123.45.6.0/23. So the network address would be 123.45.6.0, the broadcast
address would be 123.45.7.255, and everything in between, including
123.45.6.255 and 123.45.7.0, would be available for your servers.

HTH,

Paul

-----Original Message-----
From: Steve Atkins [mailto:steve@blighty.com]
Sent: 17 May 2004 16:46
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Email data type

a@foo.bar is syntactically valid. a@foo.invalid is
syntactically valid, but should be immediately rejected.

I disagree - just because the database server cannot verify the the
existence of a domain does not mean that it isn't valid. I think only
addresses that are syntactically incorrect should be rejected.

Regards, Dave.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Dave Page wrote:
|>-----Original Message-----
|>From: Gaetano Mendola [mailto:mendola@bigfoot.com]
|>Sent: 17 May 2004 16:02
|>To: Bruno Wolff III
|>Cc: pgsql-hackers@postgresql.org
|>Subject: Re: [HACKERS] Email data type
|>
|>About the domain literals, I think to validate it in the near
|>future, rejecting private subnet according to this list:
|>
|>10.0.0.0 - 10.255.255.255
|>172.16.0.0 - 172.31.255.255
|>192.168.0.0 - 192.168.255.255
|>169.254.0.0 -169.254.255.255
|
|
| Why? What's wrong with private addresses?

Well I think that accept an email like:

gmendola@192.168.212.2

is a risky.

It's true that any RFC forbid this kind of emails,
so I can accept it and leave to the final user the
opportunity to extract the domain an validate it.

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFAqOHm7UpzwH2SGd4RAp4dAJwNqDSws1cVwKr/QMXG/JvV/8M94QCbBfIS
alixKQYe/VoLpwXrBFHszDs=
=BAML
-----END PGP SIGNATURE-----

-----Original Message-----
From: Gaetano Mendola [mailto:mendola@bigfoot.com]
Sent: 17 May 2004 17:02
To: Dave Page
Cc: Bruno Wolff III; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Email data type

Well I think that accept an email like:

gmendola@192.168.212.2

is a risky.

It's true that any RFC forbid this kind of emails, so I can
accept it and leave to the final user the opportunity to
extract the domain an validate it.

Forbidden on the 'net with good reason, but useful internally on private
networks for testing, and probably various other reasons where you might
want to direct a message to a specific interface rather than a hostname
or MX.

Regards, Dave

On Monday 17 May 2004 8:45 am, Steve Atkins wrote:

Also, a@10.11.12.13 is a syntactically valid email address, in the
.13 TLD. It does not deliver to 10.11.12.13, or anywhere else, as
of today, unless the MTA or local recursive resolver is broken (a
common case). a@[10.11.12.13] is a whole other thing. As is
a@[::10.11.12.13] and various other IPv6 variants.

Along those lines a@foo.bar.com and a@[foo.bar.com] are valid but they
don't necessarily refer to the same mailbox (depends on the mx for
foo.bar.com).

Parsing email addresses is a significant part of my day job, and
email address validation is a lot harder than it looks at first
sight.

Yes, indeed.

Don't forget quoting, whitespace, escaping and nesting
parenthetical comments....

The "just looking for an @" breaks pretty quickly, say with
"joe@"some.dom or a myriad of other variations.

In some contexts the empty string is a valid email address. In some
contexts "Postmaster" is a valid email address.

As are postmaster and pOsTmaSTeR and POSTmaster and they are all the
same address.

I'm not entirely convinced that an email address is a simple and
well-defined enough datatype to handle comprehensively within the
DB. The validation decisions are complex and vary from application
to application.

(I use two text columns - localpart and domainpart, with an index
on reverse(lower(domainpart)) and leave validation to the
application, myself).

Indeed. A problem with the "email address" datatype is that it hinders
normalization:

"Joe User" <joe@user.dom> is valid but a database designer would
probably prefer columns for name and email, or if the addresses were
all people, firstname, middlename, lastname, email.

As you mentioned, the email can be broken into localpart and
domainpart but if the app requires it, the domainpart could be
further rendered into toplevel (so you could find all the .gov or
.edu), secondlevel (at least corresponds to a registrant) and
subdomain(s) as necessary.

Cheers,
Steve

On Mon, May 17, 2004 at 05:01:05PM +0100, Dave Page wrote:

a@foo.bar is syntactically valid. a@foo.invalid is
syntactically valid, but should be immediately rejected.

I disagree - just because the database server cannot verify the the
existence of a domain does not mean that it isn't valid. I think only
addresses that are syntactically incorrect should be rejected.

I think you're missing the point. Go and take a glance at RFC 2606 -
it's a BCP which defines the .test, .invalid, .example and .localhost
TLDs.

Of course, some users may want to use .invalid email addresses in
their database, despite their being, by definition, invalid. It's
something that will vary depending on their needs.

Cheers,
Steve

On Mon, May 17, 2004 at 09:21:54AM -0700, Steve Crawford wrote:

Along those lines a@foo.bar.com and a@[foo.bar.com] are valid but they
don't necessarily refer to the same mailbox (depends on the mx for
foo.bar.com).

I don't believe the latter is actually valid, as it has to be an
address literal inside the square brackets. Until that's extended
by RFC that means IPv4 or IPv6 literals.

It's another example of "this is neither trivial nor well-defined",
though.

I'm not entirely convinced that an email address is a simple and
well-defined enough datatype to handle comprehensively within the
DB. The validation decisions are complex and vary from application
to application.

(I use two text columns - localpart and domainpart, with an index
on reverse(lower(domainpart)) and leave validation to the
application, myself).

Indeed. A problem with the "email address" datatype is that it hinders
normalization:

Yup. That's a bigger concern than the overall vagueness of the problem.

"Joe User" <joe@user.dom> is valid but a database designer would
probably prefer columns for name and email, or if the addresses were
all people, firstname, middlename, lastname, email.

As you mentioned, the email can be broken into localpart and
domainpart but if the app requires it, the domainpart could be
further rendered into toplevel (so you could find all the .gov or
.edu), secondlevel (at least corresponds to a registrant) and
subdomain(s) as necessary.

That's why I index the domainpart on reverse(lower()) - that way I
can say WHERE reverse(lower(domainpart)) LIKE reverse('%.com')

Cheers,
Steve

It's rumoured that Steve Atkins once said:

On Mon, May 17, 2004 at 05:01:05PM +0100, Dave Page wrote:

a@foo.bar is syntactically valid. a@foo.invalid is
syntactically valid, but should be immediately rejected.

I disagree - just because the database server cannot verify the the
existence of a domain does not mean that it isn't valid. I think only
addresses that are syntactically incorrect should be rejected.

I think you're missing the point. Go and take a glance at RFC 2606 -
it's a BCP which defines the .test, .invalid, .example and .localhost
TLDs.

Ahh, yes - clearly I was missing the point :-)

Regards, Dave.

On Mon, May 17, 2004 at 17:01:36 +0200,
Gaetano Mendola <mendola@bigfoot.com> wrote:

Also the validator will validate emails in this form, if you are thinking to
validate emails as:

"Gaetano M. Public"(junior)<gmendola@(new account)bigfoot.com>

That appears to be an rfc 2822 address. RFC 2821 addresses are more limited
in that they can't have comments but still escape special characters.

that are perfectly valid I think that it's a valid option to consider.

What you want to store depends on what you are going to do with the address.
If you don't need the comments, storing it as a local part and a domain
is probably best.

About the domain literals, I think to validate it in the near future,
rejecting private subnet according to this list:

10.0.0.0 - 10.255.255.255
172.16.0.0 - 172.31.255.255
192.168.0.0 - 192.168.255.255
169.254.0.0 -169.254.255.255

That would depend on whether or not sending to those addresses would make
sense in your case.

I think I have to discard also the addresses with last octet equal to 256.

Email addresses are in ascii and hence bytes should only contain characters
with the codes 0 through 127.

On Mon, May 17, 2004 at 18:01:43 +0200,
Gaetano Mendola <mendola@bigfoot.com> wrote:

Well I think that accept an email like:

gmendola@192.168.212.2

Be careful about this. gmendola@192.168.212.2 is not the same as
gmendola@[192.168.212.2] .

Bruno Wolff III wrote:

On Mon, May 17, 2004 at 18:01:43 +0200,
Gaetano Mendola <mendola@bigfoot.com> wrote:

Well I think that accept an email like:

gmendola@192.168.212.2

Be careful about this. gmendola@192.168.212.2 is not the same as
gmendola@[192.168.212.2] .

Not wanting to rain on this fascinating parade, but shouldn't this
discussion be carried out on a mailing list for the pgfoundry emailadt
project? (pgfoundry mailing lists were a bit broken but I think they are
fixed now).

cheers

andrew