Mailing List Archive

A few weeks ago, while Ilja and I were working on integrating my LMTP and Sorting patch, I
asked Ilja what he thought about reserving user id numbers 1-10 or 1-100 (or 1-9 or 1-99, ie
all single or double digits, whatever) for system accounts. There are some interesting uses for
reserved accounts, particularly with foreign key constraints. Lost mail, unowned shared
mailboxes, stuff like that. At the time we figured that minimal rocking of the boat was in order
while my patch was going in, since we wanted people to begin testing it right away.

So I'll share the idea with the mailing list: shall reserve a range of user id numbers? Will this be
too difficult to migrate? Would you be comfortable using a script that renumbered some of your
lower-numbered of users?

Aaron


""Christian G. Warden"" <xndbmail@xerus.org> said:

> On Sat, Jan 31, 2004 at 09:32:29PM -0000, Aaron Stone wrote:
> > The new delivery chain requires a temporary account with the fixed user id
> > number 0. The next release candidate should probably include an appropriate
> > insert/update to add this user (note that it needs to be an insert and then an
> > update, because the insert alone with '0' triggers the auto_increment.
> >
> > INSERT INTO users (user_idnr, userid, passwd) VALUES (0, '*', '*');
> > UPDATE userid SET user_idnr = 0 WHERE username = '*';
>
> We should consider using something else besides user_idnr = 0, for this
> special account. Perhaps, username = 'delivery' (or whatever) and
> client_id = 0, or even a special boolean column just for this user.
>
> Using user_idnr=0 has some unfortanate side-effects with mysql. If
> you're joining between aliases and users on deliver_to = user_indr,
> mysql considers 0 equal to any string, e.g.
> SELECT
> IF (u.user_idnr IS NULL, a.deliver_to, u.userid) AS recipient
> FROM
> aliases a
> LEFT JOIN
> users u
> ON
> u.user_idnr = a.deliver_to
> WHERE
> a.alias = "xn@postica.com"
>
> Also, when altering the table, you can run into key violations as mysql
> helpfully tries to replace the 0.
>
> xn
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>



--

To me, the cleanest way to solve this is by adding a boolean column to
the users table, which holds 1 if the user is a 'system' user, and 0 if
it is a normal user. System users can be used for special stuff like the
delivery chain, and cannot log in using IMAP or POP.

Functions like auth_user_exists() will then still return the user_idnr
of a user, but functions like auth_validate() will always return 0 (not
validated), when called with this user as an argument.

This way, we do not have to reserve a range of numbers for special users.

Any thoughts on this?

Ilja

Aaron Stone wrote:

> A few weeks ago, while Ilja and I were working on integrating my LMTP and Sorting patch, I
> asked Ilja what he thought about reserving user id numbers 1-10 or 1-100 (or 1-9 or 1-99, ie
> all single or double digits, whatever) for system accounts. There are some interesting uses for
> reserved accounts, particularly with foreign key constraints. Lost mail, unowned shared
> mailboxes, stuff like that. At the time we figured that minimal rocking of the boat was in order
> while my patch was going in, since we wanted people to begin testing it right away.
>
> So I'll share the idea with the mailing list: shall reserve a range of user id numbers? Will this be
> too difficult to migrate? Would you be comfortable using a script that renumbered some of your
> lower-numbered of users?
>
> Aaron
>
>
> ""Christian G. Warden"" <xndbmail@xerus.org> said:
>
>
>>On Sat, Jan 31, 2004 at 09:32:29PM -0000, Aaron Stone wrote:
>>
>>>The new delivery chain requires a temporary account with the fixed user id
>>>number 0. The next release candidate should probably include an appropriate
>>>insert/update to add this user (note that it needs to be an insert and then an
>>>update, because the insert alone with '0' triggers the auto_increment.
>>>
>>>INSERT INTO users (user_idnr, userid, passwd) VALUES (0, '*', '*');
>>>UPDATE userid SET user_idnr = 0 WHERE username = '*';
>>
>>We should consider using something else besides user_idnr = 0, for this
>>special account. Perhaps, username = 'delivery' (or whatever) and
>>client_id = 0, or even a special boolean column just for this user.
>>
>>Using user_idnr=0 has some unfortanate side-effects with mysql. If
>>you're joining between aliases and users on deliver_to = user_indr,
>>mysql considers 0 equal to any string, e.g.
>>SELECT
>> IF (u.user_idnr IS NULL, a.deliver_to, u.userid) AS recipient
>>FROM
>> aliases a
>>LEFT JOIN
>> users u
>>ON
>> u.user_idnr = a.deliver_to
>>WHERE
>> a.alias = "xn@postica.com"
>>
>>Also, when altering the table, you can run into key violations as mysql
>>helpfully tries to replace the 0.
>>
>>xn
>>_______________________________________________
>>Dbmail-dev mailing list
>>Dbmail-dev@dbmail.org
>>http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>>
>
>
>
>

--
IC&S
Stadhouderslaan 57
3583 JD Utrecht

PGP-key:
http://www.ic-s.nl/keys/ilja.txt

Aaron Stone wrote:
> A few weeks ago, while Ilja and I were working on integrating my LMTP and Sorting patch, I
> asked Ilja what he thought about reserving user id numbers 1-10 or 1-100 (or 1-9 or 1-99, ie
> all single or double digits, whatever) for system accounts. There are some interesting uses for
> reserved accounts, particularly with foreign key constraints. Lost mail, unowned shared
> mailboxes, stuff like that. At the time we figured that minimal rocking of the boat was in order
> while my patch was going in, since we wanted people to begin testing it right away.
>
> So I'll share the idea with the mailing list: shall reserve a range of user id numbers? Will this be
> too difficult to migrate? Would you be comfortable using a script that renumbered some of your
> lower-numbered of users?
>
> Aaron

Well, this is probably wrong for some reason I can't understand,
but isn't the user_idnr field a signed integer?
What about reserving negative numbers for system accounts?

Magnus


>
>
> ""Christian G. Warden"" <xndbmail@xerus.org> said:
>
>
>>On Sat, Jan 31, 2004 at 09:32:29PM -0000, Aaron Stone wrote:
>>
>>>The new delivery chain requires a temporary account with the fixed user id
>>>number 0. The next release candidate should probably include an appropriate
>>>insert/update to add this user (note that it needs to be an insert and then an
>>>update, because the insert alone with '0' triggers the auto_increment.
>>>
>>>INSERT INTO users (user_idnr, userid, passwd) VALUES (0, '*', '*');
>>>UPDATE userid SET user_idnr = 0 WHERE username = '*';
>>
>>We should consider using something else besides user_idnr = 0, for this
>>special account. Perhaps, username = 'delivery' (or whatever) and
>>client_id = 0, or even a special boolean column just for this user.
>>
>>Using user_idnr=0 has some unfortanate side-effects with mysql. If
>>you're joining between aliases and users on deliver_to = user_indr,
>>mysql considers 0 equal to any string, e.g.
>>SELECT
>> IF (u.user_idnr IS NULL, a.deliver_to, u.userid) AS recipient
>>FROM
>> aliases a
>>LEFT JOIN
>> users u
>>ON
>> u.user_idnr = a.deliver_to
>>WHERE
>> a.alias = "xn@postica.com"
>>
>>Also, when altering the table, you can run into key violations as mysql
>>helpfully tries to replace the 0.
>>
>>xn
>>_______________________________________________
>>Dbmail-dev mailing list
>>Dbmail-dev@dbmail.org
>>http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>>
>
>
>
>

Magnus Sundberg wrote:
>
> Well, this is probably wrong for some reason I can't understand, but
> isn't the user_idnr field a signed integer?
> What about reserving negative numbers for system accounts?
DBMail uses unsigned 64 integers internally, so using negative numbers
here will not work.

Ilja

--
IC&S
Stadhouderslaan 57
3583 JD Utrecht

PGP-key:
http://www.ic-s.nl/keys/ilja.txt

OK,

What I've done now is used a constant username for the dbmail delivery user:
#define DBMAIL_DELIVERY_USERNAME "__@!internal_delivery_user!@__"

On delivery, dbmail looks up the user_idnr of this user, and inserts
the message in it's mailbox.

this user is added by both create tables scripts and migration scripts.

Using this scheme, we do not have to use a user_idnr of 0.

Ilja

Ilja Booij wrote:

> To me, the cleanest way to solve this is by adding a boolean column to
> the users table, which holds 1 if the user is a 'system' user, and 0 if
> it is a normal user. System users can be used for special stuff like the
> delivery chain, and cannot log in using IMAP or POP.
>
> Functions like auth_user_exists() will then still return the user_idnr
> of a user, but functions like auth_validate() will always return 0 (not
> validated), when called with this user as an argument.
>
> This way, we do not have to reserve a range of numbers for special users.
>
> Any thoughts on this?
>
> Ilja
>
> Aaron Stone wrote:
>
>> A few weeks ago, while Ilja and I were working on integrating my LMTP
>> and Sorting patch, I asked Ilja what he thought about reserving user
>> id numbers 1-10 or 1-100 (or 1-9 or 1-99, ie all single or double
>> digits, whatever) for system accounts. There are some interesting uses
>> for reserved accounts, particularly with foreign key constraints. Lost
>> mail, unowned shared mailboxes, stuff like that. At the time we
>> figured that minimal rocking of the boat was in order while my patch
>> was going in, since we wanted people to begin testing it right away.
>>
>> So I'll share the idea with the mailing list: shall reserve a range of
>> user id numbers? Will this be too difficult to migrate? Would you be
>> comfortable using a script that renumbered some of your lower-numbered
>> of users?
>>
>> Aaron
>>
>>
>> ""Christian G. Warden"" <xndbmail@xerus.org> said:
>>
>>
>>> On Sat, Jan 31, 2004 at 09:32:29PM -0000, Aaron Stone wrote:
>>>
>>>> The new delivery chain requires a temporary account with the fixed
>>>> user id
>>>> number 0. The next release candidate should probably include an
>>>> appropriate
>>>> insert/update to add this user (note that it needs to be an insert
>>>> and then an
>>>> update, because the insert alone with '0' triggers the auto_increment.
>>>>
>>>> INSERT INTO users (user_idnr, userid, passwd) VALUES (0, '*', '*');
>>>> UPDATE userid SET user_idnr = 0 WHERE username = '*';
>>>
>>>
>>> We should consider using something else besides user_idnr = 0, for this
>>> special account. Perhaps, username = 'delivery' (or whatever) and
>>> client_id = 0, or even a special boolean column just for this user.
>>>
>>> Using user_idnr=0 has some unfortanate side-effects with mysql. If
>>> you're joining between aliases and users on deliver_to = user_indr,
>>> mysql considers 0 equal to any string, e.g.
>>> SELECT
>>> IF (u.user_idnr IS NULL, a.deliver_to, u.userid) AS recipient
>>> FROM
>>> aliases a
>>> LEFT JOIN
>>> users u
>>> ON
>>> u.user_idnr = a.deliver_to
>>> WHERE
>>> a.alias = "xn@postica.com"
>>>
>>> Also, when altering the table, you can run into key violations as mysql
>>> helpfully tries to replace the 0.
>>>
>>> xn
>>> _______________________________________________
>>> Dbmail-dev mailing list
>>> Dbmail-dev@dbmail.org
>>> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>>>
>>
>>
>>
>>
>

--
IC&S
Stadhouderslaan 57
3583 JD Utrecht

PGP-key:
http://www.ic-s.nl/keys/ilja.txt

Sure, that works. It's probably the easiest way to retrofit onto an existing
system, too. Should also work fine with LDAP, just as long as we mention that
an LDAP account needs to be created with this name. Reserved numbers would
have been an LDAP nightmare, now that I think about it.

In the future we can have things like...

#define DBMAIL_LOST_MAILBOX_USERNAME "__@!internal_lostmailbox_user!@__"
#define DBMAIL_LOST_MESSAGE_USERNAME "__@!internal_lostmessage_user!@__"

And we'll only need to insert those accounts as the code is written.

Now, we just need make sure that these users cannot log in...

Aaron


Ilja Booij <ilja@ic-s.nl> said:

> OK,
>
> What I've done now is used a constant username for the dbmail delivery user:
> #define DBMAIL_DELIVERY_USERNAME "__@!internal_delivery_user!@__"
>
> On delivery, dbmail looks up the user_idnr of this user, and inserts
> the message in it's mailbox.
>
> this user is added by both create tables scripts and migration scripts.
>
> Using this scheme, we do not have to use a user_idnr of 0.
>
> Ilja
>
> Ilja Booij wrote:
>
> > To me, the cleanest way to solve this is by adding a boolean column to
> > the users table, which holds 1 if the user is a 'system' user, and 0 if
> > it is a normal user. System users can be used for special stuff like the
> > delivery chain, and cannot log in using IMAP or POP.
> >
> > Functions like auth_user_exists() will then still return the user_idnr
> > of a user, but functions like auth_validate() will always return 0 (not
> > validated), when called with this user as an argument.
> >
> > This way, we do not have to reserve a range of numbers for special users.
> >
> > Any thoughts on this?
> >
> > Ilja
> >
> > Aaron Stone wrote:
> >
> >> A few weeks ago, while Ilja and I were working on integrating my LMTP
> >> and Sorting patch, I asked Ilja what he thought about reserving user
> >> id numbers 1-10 or 1-100 (or 1-9 or 1-99, ie all single or double
> >> digits, whatever) for system accounts. There are some interesting uses
> >> for reserved accounts, particularly with foreign key constraints. Lost
> >> mail, unowned shared mailboxes, stuff like that. At the time we
> >> figured that minimal rocking of the boat was in order while my patch
> >> was going in, since we wanted people to begin testing it right away.
> >>
> >> So I'll share the idea with the mailing list: shall reserve a range of
> >> user id numbers? Will this be too difficult to migrate? Would you be
> >> comfortable using a script that renumbered some of your lower-numbered
> >> of users?
> >>
> >> Aaron
> >>
> >>
> >> ""Christian G. Warden"" <xndbmail@xerus.org> said:
> >>
> >>
> >>> On Sat, Jan 31, 2004 at 09:32:29PM -0000, Aaron Stone wrote:
> >>>
> >>>> The new delivery chain requires a temporary account with the fixed
> >>>> user id
> >>>> number 0. The next release candidate should probably include an
> >>>> appropriate
> >>>> insert/update to add this user (note that it needs to be an insert
> >>>> and then an
> >>>> update, because the insert alone with '0' triggers the auto_increment.
> >>>>
> >>>> INSERT INTO users (user_idnr, userid, passwd) VALUES (0, '*', '*');
> >>>> UPDATE userid SET user_idnr = 0 WHERE username = '*';
> >>>
> >>>
> >>> We should consider using something else besides user_idnr = 0, for this
> >>> special account. Perhaps, username = 'delivery' (or whatever) and
> >>> client_id = 0, or even a special boolean column just for this user.
> >>>
> >>> Using user_idnr=0 has some unfortanate side-effects with mysql. If
> >>> you're joining between aliases and users on deliver_to = user_indr,
> >>> mysql considers 0 equal to any string, e.g.
> >>> SELECT
> >>> IF (u.user_idnr IS NULL, a.deliver_to, u.userid) AS recipient
> >>> FROM
> >>> aliases a
> >>> LEFT JOIN
> >>> users u
> >>> ON
> >>> u.user_idnr = a.deliver_to
> >>> WHERE
> >>> a.alias = "xn@postica.com"
> >>>
> >>> Also, when altering the table, you can run into key violations as mysql
> >>> helpfully tries to replace the 0.
> >>>
> >>> xn
> >>> _______________________________________________
> >>> Dbmail-dev mailing list
> >>> Dbmail-dev@dbmail.org
> >>> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> >>>
> >>
> >>
> >>
> >>
> >
>
> --
> IC&S
> Stadhouderslaan 57
> 3583 JD Utrecht
>
> PGP-key:
> http://www.ic-s.nl/keys/ilja.txt
>
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>



--

Hi Aaron,

> Sure, that works. It's probably the easiest way to retrofit onto an existing
> system, too. Should also work fine with LDAP, just as long as we mention that
> an LDAP account needs to be created with this name. Reserved numbers would
> have been an LDAP nightmare, now that I think about it.
>
> In the future we can have things like...
>
> #define DBMAIL_LOST_MAILBOX_USERNAME "__@!internal_lostmailbox_user!@__"
> #define DBMAIL_LOST_MESSAGE_USERNAME "__@!internal_lostmessage_user!@__"
>
> And we'll only need to insert those accounts as the code is written.
>
> Now, we just need make sure that these users cannot log in...

Ilja's idea of adding an additional boolean column if it is a system
account would make everything much cleaner though.

External programs could select all non-system users much easier than
with names (ignore all __@!internal_*_user!@__ user?).

So I'd propose to keep the __@!internal_delivery_user!@__ user but add
an additional column too. What do you think?


--
MfG Thomas Mueller - http://www.tmueller.com for pgp key (95702B3B)

That's a really good point. Refencing my posts about a clients table, we could
also hardwire clientid 1 as the "internal client" and have only internal users
part of that client group. This makes me happier than a boolean column, which
I don't really like because we'd have to change every auth query to add "...
where users.system = 0" at the end.

But there's also the interaction with LDAP and the proposals for configurable
SQL authentication queries (see pam_mysql / nss_mysql as an example of this
idea) and how we're going to ensure that the internal user account is properly
represented by the authentication provider.

In any event, we need to come up with something good during the 2.1 series.
Clearly IC&S wants to get 2.0 out the door, and Ilja's solution to my hack
should work well enough despite the potential side effects like running a
per-user billing script and trying to send bill's to dbmail_internal -- which
might be a good thing, really. Just put IC&S' address in and hope that their
billing department doesn't pay close attention to invoices... :-P

Aaron


Thomas Mueller <dbmail@tmueller.com> said:

> Hi Aaron,
>
> > Sure, that works. It's probably the easiest way to retrofit onto an
> > existing system, too. Should also work fine with LDAP, just as long
> > as we mention that an LDAP account needs to be created with this name.
> > Reserved numbers would have been an LDAP nightmare, now that I think
> > about it.
> >
> > In the future we can have things like...
> >
> > #define DBMAIL_LOST_MAILBOX_USERNAME "__@!internal_lostmailbox_user!@__"
> > #define DBMAIL_LOST_MESSAGE_USERNAME "__@!internal_lostmessage_user!@__"
> >
> > And we'll only need to insert those accounts as the code is written.
> >
> > Now, we just need make sure that these users cannot log in...
>
> Ilja's idea of adding an additional boolean column if it is a system
> account would make everything much cleaner though.
>
> External programs could select all non-system users much easier than
> with names (ignore all __@!internal_*_user!@__ user?).
>
> So I'd propose to keep the __@!internal_delivery_user!@__ user but add
> an additional column too. What do you think?
>
> MfG Thomas Mueller - http://www.tmueller.com for pgp key (95702B3B)
--

Hi Aaron,

> That's a really good point. Refencing my posts about a clients table, we could
> also hardwire clientid 1 as the "internal client" and have only internal users
> part of that client group. This makes me happier than a boolean column, which
> I don't really like because we'd have to change every auth query to add "...
> where users.system = 0" at the end.

I don't see the difference for auth queries? Then you have to add "...
where users.clientid <> 1" at the end?

But an extra table would have other advantages. A groups table could look
like:
group_idnr | groupid | maxmail_size

users.client_idnr could be renamed to users.group_idnr and reference
groups.group_idnr. A special group groups.group_idnr = 1 could hold all
system accounts.

That way group quotas could be implemented easily too in the future.

> But there's also the interaction with LDAP and the proposals for configurable
> SQL authentication queries (see pam_mysql / nss_mysql as an example of this
> idea)

That shouldn't be too hard. pam_pgsql, exim and PowerDNS have configurable
queries too (they all share the same database on my system - would be nice
if dbmail had tablename prefixes ;-) ).

> and how we're going to ensure that the internal user account is properly
> represented by the authentication provider.

That's harder.

> In any event, we need to come up with something good during the 2.1 series.
> Clearly IC&S wants to get 2.0 out the door, and Ilja's solution to my hack
> should work well enough despite the potential side effects like running a
> per-user billing script and trying to send bill's to dbmail_internal -- which
> might be a good thing, really. Just put IC&S' address in and hope that their
> billing department doesn't pay close attention to invoices... :-P

I'll try that and add 'Aaron Stone' as contact for billing questions to
the bill ;-)


--
MfG Thomas Mueller - http://www.tmueller.com for pgp key (95702B3B)

Comments inline...

Thomas Mueller <dbmail@tmueller.com> said:

> Hi Aaron,
>
> > That's a really good point. Refencing my posts about a clients table, we
> > could also hardwire clientid 1 as the "internal client" and have only
> > internal users part of that client group. This makes me happier than a
> > boolean column, which I don't really like because we'd have to change
> > every auth query to add "...
> > where users.system = 0" at the end.
>
> I don't see the difference for auth queries? Then you have to add "...
> where users.clientid <> 1" at the end?

Oh, duh.

But I guess that's OK, because think about it from the other direction: if
there were a clients table and a boolean flag in the users table, we'd either
have to join any queries of the clients table against the users table to make
sure that we don't include system users, or just make a client to hold the
system accounts anyway. Might as well go for it.

>
> But an extra table would have other advantages. A groups table could look
> like:
> group_idnr | groupid | maxmail_size
>
> users.client_idnr could be renamed to users.group_idnr and reference
> groups.group_idnr. A special group groups.group_idnr = 1 could hold all
> system accounts.
>
> That way group quotas could be implemented easily too in the future.

I am very strongly in favor of this. IMHO, a top priority for 2.1.

>
> > But there's also the interaction with LDAP and the proposals for
> > configurable SQL authentication queries (see pam_mysql / nss_mysql
> > as an example of this idea)
>
> That shouldn't be too hard. pam_pgsql, exim and PowerDNS have configurable
> queries too (they all share the same database on my system - would be nice
> if dbmail had tablename prefixes ;-) ).

I am very strongly in favor of this, too!

>
> > and how we're going to ensure that the internal user account is properly
> > represented by the authentication provider.
>
> That's harder.

Yeah, something tells me that we'll just have to play it by ear for LDAP.
We're sort of in a transition period right now on that front; we really need
to be using dlopen() for the database and authentication providers. If we
were, we could just use authsql.so for system accounts and either authsql.so
or authldap.so for user accounts.

>
> > In any event, we need to come up with something good during the 2.1
> > series. Clearly IC&S wants to get 2.0 out the door, and Ilja's solution
> > to my hack should work well enough despite the potential side effects
> > like running a per-user billing script and trying to send bill's to
> > dbmail_internal -- which might be a good thing, really. Just put IC&S'
> > address in and hope that their billing department doesn't pay close
> > attention to invoices... :-P
>
> I'll try that and add 'Aaron Stone' as contact for billing questions to
> the bill ;-)
>
> --
> MfG Thomas Mueller - http://www.tmueller.com for pgp key (95702B3B)
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>

--