[vchkpw] mysql auth
Dear all, At first i installed qmail from LWQ on Debian.Then i installed courier-imap courier-pop their core. Then i installed vpopmail with mysql auth module.Even i tested it it read write to mysql. Now, When i use following command,i give error: -- localhost:/home/mohsen# telnet 0 110 Trying 0.0.0.0... Connected to 0. Escape character is '^]'. +OK Hello there. user postmas...@mohsenjan +OK Password required. pass 123456 -ERR Login failed. Connection closed by foreign host. localhost:/home/mohsen# cd vpopmail-5.4.27 -- mohsenjan is my domain that i inserted to my db. When i saw my /var/log/mail.log, i see follow line: Feb 25 19:17:20 localhost authdaemond: SQL query: SELECT pw_name, pw_passwd, pw_clear_passwd, pw_uid, pw_gid, pw_dir , , , , FROM vpopmail WHERE pw_name = 'postmas...@mohsenjan' Feb 25 19:17:20 localhost authdaemond: zero rows returned Feb 25 19:17:20 localhost authdaemond: no password available to compare Feb 25 19:17:20 localhost authdaemond: authmysql: REJECT - try next module Feb 25 19:17:20 localhost authdaemond: FAIL, all modules rejected Feb 25 19:18:01 localhost pop3d: LOGOUT, ip=[:::127.0.0.1] Feb 25 19:18:01 localhost pop3d: Disconnected, ip=[:::127.0.0.1] --- My question: How i tell to courier to pw_name is just is my username from email?.It doesn't contain domain name I have attached my /etc/courier/authmysqlrc file. Yours, Mohsen !DSPAM:49a575d132681124118433! ##VERSION: $Id: authmysqlrc,v 1.20 2007/10/07 02:50:45 mrsam Exp $ # # Copyright 2000-2007 Double Precision, Inc. See COPYING for # distribution information. # # Do not alter lines that begin with ##, they are used when upgrading # this configuration. # # authmysqlrc created from authmysqlrc.dist by sysconftool # # DO NOT INSTALL THIS FILE with world read permissions. This file # might contain the MySQL admin password! # # Each line in this file must follow the following format: # # field[spaces|tabs]value # # That is, the name of the field, followed by spaces or tabs, followed by # field value. Trailing spaces are prohibited. ##NAME: LOCATION:0 # # The server name, userid, and password used to log in. MYSQL_SERVERlocalhost MYSQL_USERNAME vpopmailuser MYSQL_PASSWORD vpoppasswd ##NAME: SSLINFO:0 # # The SSL information. # # To use SSL-encrypted connections, define the following variables (available # in MySQL 4.0, or higher): # # # MYSQL_SSL_KEY/path/to/file # MYSQL_SSL_CERT /path/to/file # MYSQL_SSL_CACERT /path/to/file # MYSQL_SSL_CAPATH /path/to/file # MYSQL_SSL_CIPHERSALL:!DES ##NAME: MYSQL_SOCKET:0 # # MYSQL_SOCKET can be used with MySQL version 3.22 or later, it specifies the # filesystem pipe used for the connection # MYSQL_SOCKET /var/run/mysqld/mysqld.sock ##NAME: MYSQL_PORT:0 # # MYSQL_PORT can be used with MySQL version 3.22 or later to specify a port to # connect to. MYSQL_PORT 3306 ##NAME: MYSQL_OPT:0 # # Leave MYSQL_OPT as 0, unless you know what you're doing. MYSQL_OPT 0 ##NAME: MYSQL_DATABASE:0 # # The name of the MySQL database we will open: MYSQL_DATABASE vpopmail #NAME: MYSQL_CHARACTER_SET:0 # # This is optional. MYSQL_CHARACTER_SET installs a character set. This option # can be used with MySQL version 4.1 or later. MySQL supports 70+ collations # for 30+ character sets. See MySQL documentations for more detalis. # # MYSQL_CHARACTER_SET latin1 ##NAME: MYSQL_USER_TABLE:0 # # The name of the table containing your user data. See README.authmysqlrc # for the required fields in this table. MYSQL_USER_TABLEvpopmail ##NAME: MYSQL_CRYPT_PWFIELD:0 # # Either MYSQL_CRYPT_PWFIELD or MYSQL_CLEAR_PWFIELD must be defined. Both # are OK too. crypted passwords go into MYSQL_CRYPT_PWFIELD, cleartext # passwords go into MYSQL_CLEAR_PWFIELD. Cleartext passwords allow # CRAM-MD5 authentication to be implemented. MYSQL_CRYPT_PWFIELD pw_passwd ##NAME: MYSQL_CLEAR_PWFIELD:0 # # MYSQL_CLEAR_PWFIELD pw_clear_passwd ##NAME: MYSQL_DEFAULT_DOMAIN:0 # # If DEFAULT_DOMAIN is defined, and someone tries to log in as 'user', # we will look up 'u...@default_domain' instead. # # # DEFAULT_DOMAINexample.com ##NAME: MYSQL_UID_FIELD:0 # # Other fields in the mysql table: # # MYSQL_UID_FIELD - contains the numerical userid of the account # MYSQL_UID_FIELD pw_uid ##NAME: MYSQL_GID_FIELD:0 # # Numerical groupid of the account MYSQL_GID_FIELD pw_gid ##NAME: MYSQL_LOGIN_FIELD:0 # # The login id, default is id. Basically the query is: # # SELECT MYSQL_UID_FIELD, MYSQL_GID_FIELD, ... WHERE id='loginid' # MYSQL_LOGIN_FIELD pw_name ##NAME: MYSQL_HOME_FIELD:0 # MYSQL_HOME_FIELD pw_dir ##NAME: MYSQL_NAME_FIELD:0 # # The user's name (optional) #MYSQL_NAME_FIELD pw_gecos
Re: [vchkpw] mysql auth
2nd graffitiist: Why? Use bill shupp toaster you will have not problem with this. Just my 2 cents. Remo From: Mohsen Pahlevanzadeh moh...@pahlevanzadeh.org Reply-To: vchkpw@inter7.com Date: Wed, 25 Feb 2009 20:14:27 +0330 To: vchkpw@inter7.com Subject: [vchkpw] mysql auth Dear all, At first i installed qmail from LWQ on Debian.Then i installed courier-imap courier-pop their core. Then i installed vpopmail with mysql auth module.Even i tested it it read write to mysql. Now, When i use following command,i give error: -- localhost:/home/mohsen# telnet 0 110 Trying 0.0.0.0... Connected to 0. Escape character is '^]'. +OK Hello there. user postmas...@mohsenjan +OK Password required. pass 123456 -ERR Login failed. Connection closed by foreign host. localhost:/home/mohsen# cd vpopmail-5.4.27 -- mohsenjan is my domain that i inserted to my db. When i saw my /var/log/mail.log, i see follow line: Feb 25 19:17:20 localhost authdaemond: SQL query: SELECT pw_name, pw_passwd, pw_clear_passwd, pw_uid, pw_gid, pw_dir , , , , FROM vpopmail WHERE pw_name = 'postmas...@mohsenjan' Feb 25 19:17:20 localhost authdaemond: zero rows returned Feb 25 19:17:20 localhost authdaemond: no password available to compare Feb 25 19:17:20 localhost authdaemond: authmysql: REJECT - try next module Feb 25 19:17:20 localhost authdaemond: FAIL, all modules rejected Feb 25 19:18:01 localhost pop3d: LOGOUT, ip=[:::127.0.0.1] Feb 25 19:18:01 localhost pop3d: Disconnected, ip=[:::127.0.0.1] --- My question: How i tell to courier to pw_name is just is my username from email?.It doesn't contain domain name I have attached my /etc/courier/authmysqlrc file. Yours, Mohsen !DSPAM:49a5792132687773844569!
Re: [vchkpw] mysql-auth replicated databases
Hi Rick: Thanks for the help. We managed to get replication running and it seems to working. We stayed with MySQL 3.23.58 because we were unable to get the upgrade to MySQL version 4 to work. So now we have chkuser-mysql and virus scanning off-loaded to the front-end server. Next step is to offload the spamassassin but that should be easy since the mysql userprefs tables are being replicated also. At 01:21 PM 4/21/2004, you wrote: On Wed, 2004-04-21 at 12:08, Jeff Koch wrote: In order to relieve the load from our main mailserver we offloaded chkuser and virus scanning to a frontend mailserver. However, the front-end server is not getting a fast enough response on the chkuser query from the mysql server on the main mailserver and, at peaks loads, has started bouncing mails. We would like to have a slave copy of the mysql vpopmail DB on the front-end mailserver to service chkuser queries directly. We need to keep the slave database in sync with the master DB and would like to use mysql replication. Can we get some opinions on how best to keep the DB's in sync? Does replication work OK? Do we need to upgrade to MySQL version 4? Or can we get away with rsync? I just did this in Feb. Upgrade both systems to MySQL version 4. Then follow this: http://dev.mysql.com/doc/mysql/en/Replication.html I personally did the LOAD DATA FROM MASTER to get the initial data set over to the slave. I also put some settings into the local /etc/my.cnf - I kept typing commands wrong within MySQL ;) Master: [mysqld] log-bin server-id=1 Slave:[mysqld] server-id=2 master-host=master.host.com master-user=user master-password=pass max_connections=800 I had a hell of a time trying to get a Master 3.x server, and a slave 4.x. I strongly recommend going with 4.x on both boxes. Once I upgraded the master, everything went smoothly. Rick Thanks in advance for the advice and counsel. Jeff Koch Best Regards, Jeff Koch, Intersessions
Re: [vchkpw] mysql-auth replicated databases
On Wed, 2004-04-21 at 12:21, Rick Romero wrote: On Wed, 2004-04-21 at 12:08, Jeff Koch wrote: snip The replication works like a charm. I have found the occasional problem if the vlog table is replicated, for some reason its very prone to duplicate records. I myself just stopped using this table and my replication issues are all gone. I just did this in Feb. Upgrade both systems to MySQL version 4. Then follow this: http://dev.mysql.com/doc/mysql/en/Replication.html I personally did the LOAD DATA FROM MASTER to get the initial data set over to the slave. I also put some settings into the local /etc/my.cnf - I kept typing commands wrong within MySQL ;) snip I had a hell of a time trying to get a Master 3.x server, and a slave 4.x. I strongly recommend going with 4.x on both boxes. Once I upgraded the master, everything went smoothly. What problems did you have? I ran a 3.x master with 4.x slaves for a while during a migration I did on my infrastructure. Obviously load data from master isn't supported, so I had to go with the less than ideal tar'ing under a read lock of the database files, however it was do-able. Rick Thanks in advance for the advice and counsel. Jeff Koch Also, if anybody here also uses big brother to monitor their servers, I've got a perl extension I wrote for monitoring mysql replication that works for MySQL 4.x (monitors both the IO thread and the sql thread). Hope that helps, Nick
[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
[vchkpw] MySQL auth
Well i'm in trouble with MySQL POP authentication. Messages from internet arrive and are correctly stored into user mailbox, but no way to retrieve them. I'm running qmail+vpopmail+mysql on Slack 8.1, so my conf files may differ (moreover no softlimit in the distro). rc.qmail: bash -cf '/var/qmail/rc ' QMAILUID=`id -u qmaild` NOFILESGID=`id -g qmaild` /usr/local/bin/tcpserver -H -R -l dns1.cpbo.it - x /etc/tcp.smtp.cdb -u$QMAILUID -g$NOFILESGID 0 smtp /var/qmail/bin/qmail-smtpd /dev/null 2/dev/null #Pop3 /usr/local/bin/tcpserver \ -v -H -lhost.domain -R 0 pop3 \ /var/qmail/bin/qmail-popup host.domain \ /home/vpopmail/bin/vchkpwd /var/qmail/bin/qmail-pop3d Maildir Compilation options ./configure \ --enable-roaming-users=y \ --enable-defaultquota=1000S,1000C \ --enable-default-domain=cpbo.it \ --enable-ip-alias-domains=n \ --enable-relay-clear-minutes=60 \ --enable-mysql=y \ --enable-incdir=/usr/local/mysql/include \ --enable-libdir=/usr/local/mysql/lib \ --enable-logging=p \ --enable-passwd=n \ --enable-clear-passwd=y \ --enable-many-domains=n\ --enable-auth-logging=y \ --enable-mysql-logging=y \ --enable-valias=y Any help? TIA, Max
