[vchkpw] mysql auth, dir_control, pw_uid/pw_gid and maildrop
Hi, This is regarding vpopmail-5.2.1 and the mysql auth module: A year ago, or more, I converted all my domains from cdb to mysql using the convert script in contrib. I noticed all users in the vpopmail table contain '1' in the pw_uid and '0' in the pw_gid. Now this is the primary issue. Also, the dir_control table contained rows (domains) that had cur_users set to a big number, like around 2 billion. Others didn't reflect the exact users in the domain. However, newly added domains reflect the number of users and it increments as expected. The dir_control table also always contained a last row that contains 'dom_500' in the domain field (the first field in the dir_control table). I guess this is always set to dom_vpopmailuid ? I noticed the cur_users field in the row that begins with 'dom_500' as the domain gets incremented when I add a new domain. But again, since the conversion, the number was not correct and didn't reflect the number of domains managed by vpopmail. So I manually updated the cur_users rows to reflect the actual number of users in each domain row, and the numbers of domains managed by vpopmail in the dom_500 row. Is that OK? Now I mentioned after the cdb-mysql conversion, the mysql row for each of the users always reflected 1 and 0 for the pw_uid and pw_gid fields, respectively. When I added a new domain, new users (and postmaster) have 0 and 0 for the pw_uid and pw_gid fields. Although /var/qmail/users/assign shows the correct uid/gid of vpopmail (500/500) for all the domains. Shouldn't the pw_uid and pw_gid fields reflect 500/500 just like in assign? The reason why I am concerned is I am looking in using maildrop for delivery and since it supports mysql, I can have it read the users home directly from the vpopmail database instead of running `vuserinfo -d [EMAIL PROTECTED] from a mailfilter script. From maildropmysql.conf : # UIDNUMBER_FIELD - MySQL attribute which contains the system uid to deliver # mail as uidnumber_field uidnumber # GIDNUMBER_FIELD - MySQL attribute which contains the system gid to deliver # mail as gidnumber_field gidnumber Any information, clarifications, suggestions, experiences, or ideas would be greatly appreciated. Respectfully, Tim Hasson
Re: [vchkpw] mysql auth, dir_control, pw_uid/pw_gid and maildrop
Quoting Erik Bourget [EMAIL PROTECTED]: Sorry to not answer your question, but you raise a pet peeve of mine wrt vpopmail - Tim Hasson [EMAIL PROTECTED] writes: So I manually updated the cur_users rows to reflect the actual number of users in each domain row, and the numbers of domains managed by vpopmail in the dom_500 row. Is that OK? dir_control is a horrible black art and one should never mess around with it. It decided where new directories are made, i.e. /domains/foo.bar.net/8/K/z/erik. I know that. That's why I only changed the cur_users because it was already messed up since the migration for the domains migrated. Newly added domains showed the expected increment/decrement behavior when domains/users were added/deleted. So I just basically updated the cur_users for the ones that were off (or way off), period. I guess if it worked for my primary domain with cur_users set to _2 BILLION_ , it'll work when it's only like 15 (15 users in that domain). What in the universe is the point of a __SEVENTEEN COLUMN__ table to represent this information? I agree. No, vpopmail (and I'm really sorry to rant here but I hate vpopmail's mysql database layout) uses the pw_uid for nothing at all and pw_gid to store FLAGS ON AN ACCOUNT. A gid of 1024 means something, like 'don't allow pop access' or 'bounce all mail to this account'. That sucks. What if you want to add a domain with a unique uid that's different from vpopmail's uid ? (for os quota enforcement/security etc..) Do you have to always consult assign/users to get the uid for a domain or user? Then what is the point of the mysql auth module? Also, this would be a perfect place to store the uid/gid of the user, if you wanted to have an option -u username/uid to vadduser just like vadddomain (I guess you would also need a -d ~userhome option so it would work) or even if you have multiple domains that run under a different uid/gid each. For the above mentioned reasons, by intiuition, I would think that the pw_uid and pw_gid would at least reflect the same uid/gid in assign/users - if not even to offer a unique uid per user option. The reason why I am concerned is I am looking in using maildrop for delivery and since it supports mysql, I can have it read the users home directly from the vpopmail database instead of running `vuserinfo -d [EMAIL PROTECTED] from a mailfilter script. From maildropmysql.conf : # UIDNUMBER_FIELD - MySQL attribute which contains the system uid to deliver # mail as uidnumber_field uidnumber # GIDNUMBER_FIELD - MySQL attribute which contains the system gid to deliver # mail as gidnumber_field gidnumber Maildrop will become very upset if the uid and gid are not the same as the 500:500 that you have in qmail-local. Maildrop's MySQL support is not one-to-one with vpopmail's MySQL support. Do as you remarked before and set default_uidnumber and default_gidnumber to 500. From my pervious comments above, you should now understand why I wanted to link the uidnumber_field and gidnumber_field from maildrop with vpopmail's pw_uid and pw_gid fields. Any information, clarifications, suggestions, experiences, or ideas would be greatly appreciated. Use my maildrop MySQL rule filtering patch! vdelivermail is a relic! :) I am not up to speed with you on offering delivery-level-filtering with maildrop to end users quite yet. So for now, they'll have to stick with IMP's webmail filters for moving messages around at login. However, I do use maildrop filters to invoke spamc and scan mail for spam then look for SPAM keyword in the message header, and if it's marked as spam, deliver it to user/Maildir/.BulkMail , else deliver to user/Maildir This is enabled for only one domain from it's .qmail-default file. However, since you mentioned in your earlier email that you have the maildrop maildir autocreation patch seperated, please direct me to it. I guess for now as a workaround for vpopmail's table pw_name and pw_domain incompatibility with maildrop's single uid_field, I can create a new column, and call it 'pw_email' for example and on mysql do: UPDATE vpopmail SET pw_email = CONCAT(pw_name, '@', pw_domain); and set uid_field in maildropmysql.conf to 'pw_email' And also set the other stuff (like the userhome_field etc.) should be ok. - Erik Bourget Respectfully, Tim Hasson
Re: [vchkpw] mysql auth, dir_control, pw_uid/pw_gid and maildrop
Erik, Thank you very much for all your help. I sent a message to chris and hopefully he'll reply. I just have a couple more questions/points to confirm: 1. If using maildrop, bounce all messages flag for a user probablly wouldn't work because it's proprietary to vdelivermail, correct? 2. How does the maildrop maildir auto creation handle inexistent- [EMAIL PROTECTED] ? Does it confirm that the userhome is set in the database before creating dirs? I recall having a problem when using maildrop with a simple mailfilter for delivery (called from .qmail-default) when someone sends a message to any [EMAIL PROTECTED], which would cause messages to be queued and later bounced. So as a temp workaround I added a little if clause to my mailfilter to check if the user's maildir exists first, and if the returncode == 1, then exitcode 100, and echo Sorry, no user here by that name or something like that. Sorry if I am not making much sense or being redundant, but I am getting really tired with this whole vdelivermail/maildrop thing :| Thanks, Tim Quoting Erik Bourget [EMAIL PROTECTED]: Tim Hasson [EMAIL PROTECTED] writes: Quoting Erik Bourget [EMAIL PROTECTED]: [ what do pw_uid and pw_gid do? ] No, vpopmail (and I'm really sorry to rant here but I hate vpopmail's mysql database layout) uses the pw_uid for nothing at all and pw_gid to store FLAGS ON AN ACCOUNT. A gid of 1024 means something, like 'don't allow pop access' or 'bounce all mail to this account'. That sucks. Hell yes! What if you want to add a domain with a unique uid that's different from vpopmail's uid ? (for os quota enforcement/security etc..) Do you have to always consult assign/users to get the uid for a domain or user? Then what is the point of the mysql auth module? The way qmail works, qmail-local is actually run AS THE UID that it finds in the users/cdb file. It's not vpopmail's fault that it can't escape this limitation, it's a (rather reasonable) behavior of qmail. The point of the mysql auth module is to be not used solely in a vpopmail environment. This configuration option does not apply to vpopmail. For the above mentioned reasons, by intiuition, I would think that the pw_uid and pw_gid would at least reflect the same uid/gid in assign/users - if not even to offer a unique uid per user option. You'll find that the intersection of vpopmail thinking and intuition is very small. From my pervious comments above, you should now understand why I wanted to link the uidnumber_field and gidnumber_field from maildrop with vpopmail's pw_uid and pw_gid fields. Well, here's another way you can do it - ALTER TABLE the vpopmail table and add a uidnumber and gidnumber column. vpopmail doesn't do anything as braindead as SELECT * so it won't break, and maildrop can look up in these new fields. You'll have to make sure to update them after every vadduser though (or just modify the vadduser source if you're c-inclined). Any information, clarifications, suggestions, experiences, or ideas would be greatly appreciated. Use my maildrop MySQL rule filtering patch! vdelivermail is a relic! :) However, I do use maildrop filters to invoke spamc and scan mail for spam then look for SPAM keyword in the message header, and if it's marked as spam, deliver it to user/Maildir/.BulkMail , else deliver to user/Maildir This is enabled for only one domain from it's .qmail-default file. However, since you mentioned in your earlier email that you have the maildrop maildir autocreation patch seperated, please direct me to it. I guess for now as a workaround for vpopmail's table pw_name and pw_domain incompatibility with maildrop's single uid_field, I can create a new column, and call it 'pw_email' for example and on mysql do: UPDATE vpopmail SET pw_email = CONCAT(pw_name, '@', pw_domain); and set uid_field in maildropmysql.conf to 'pw_email' You know, you might actually be able to just say CONCAT(pw_name, '@', pw_domain) AS pw_email in maildropmysql.conf itself. I think that it's just sprintf'd into the string in maildrop (which would mean that you can screw yourself over pretty bad with some creative SQL injection...). And also set the other stuff (like the userhome_field etc.) should be ok. You should get in touch with Chris Masters, rotis 23 at yahoo dot com (no space between the rotis and the 23). He seems to have the same motives as you and has created a maildrop patch for himself. It includes the maildir creation and some other minor mysql-ish niceness. - Erik Bourget
