7 messages in net.sourceforge.lists.courier-usersAW: [courier-users] authmysqlrc + MYS...
FromSent OnAttachments
Rodrigo SeveroNov 5, 2003 2:14 pm 
"Bürkle, Roman"Nov 5, 2003 2:16 pm 
"Bürkle, Roman"Nov 6, 2003 3:55 am 
David LubowaNov 6, 2003 4:49 am 
Rodrigo SeveroNov 6, 2003 6:44 am 
"Bürkle, Roman"Nov 6, 2003 8:45 am 
Rodrigo SeveroNov 7, 2003 4:21 am 
Actions with this message:
Paste this link in email or IM:
Paste this link in email or IM:
Atom feed for this thread
Paste this URL into your reader:
Subject:AW: [courier-users] authmysqlrc + MYSQL_SELECT_CLAUSEActions...
From:"Bürkle, Roman" (buer@stimme.net)
Date:Nov 6, 2003 3:55:46 am
List:net.sourceforge.lists.courier-users

MYSQL_SELECT_CLAUSE SELECT id, crypt, passwd, uid, gid, home, '', quota_size, '' from user where id = '$(local_part)'

I believe the field 'username' must contain the domain also (us@example.com). I have a debate with Mr. Sam some time ago about this but I can't remember the details.

Mr. Sam, can you say something to that matter, please?

BTW, why are you using MYSQL_SELECT_CLAUSE?

Well i'm just about to implement a different table-structure than the current one, so i wanted to test that feature before i switch. In future (when i get this thing working ;-) ) i'll have a more complicated one, like yours, Rodrigo.

OKOK, so now i tried some more combinations:

MYSQL_SELECT_CLAUSE SELECT CONCAT(id, "@", mbox_host), "", passwd, uid, gid, home, "", quota_size, "" FROM user WHERE id = "$(local_part)" AND mbox_host = "$(domain)" or MYSQL_SELECT_CLAUSE SELECT CONCAT(id, '@', mbox_host), '', passwd, uid, gid, home, '', quota_size, '' FROM user WHERE id = '$(local_part)' and so on...

BUT: whatever i tried it didnt work, even a very simple sql-statement didnt pass.

So, i checked the mysql.log for details, and not one QUERY of the test is in the log. For comparison:

---------------------- A) with "MYSQL_SELECT_CLAUSE" deactivated, a incoming mail produces in the mysql.log:

14 Connect courier@localhost on 14 Init DB mailuser 14 Query SELECT id, "", passwd, uid, gid, home, "", concat(quota_size * 1024 ,"S"), "" FROM user WHERE id = "robtest"

All daemons (pop, imap, mta) -authentifications work fine and very stable with this.

---------------------- B) with my current "MYSQL_SELECT_CLAUSE" activated, a incoming mail produces in the mysql.log:

15 Connect courier@localhost on 15 Init DB mailuser

You see, theres no query... but WHY..??

----------------------

For your information, here's my current authmysqlrc:

tank:/etc/courier# cat authmysqlrc ##VERSION: $Id: authmysqlrc,v 1.14 2003/05/09 18:15:15 mrsam Exp $ # MYSQL_SERVER localhost MYSQL_USERNAME courier MYSQL_PASSWORD ****** MYSQL_SOCKET /var/run/mysqld/mysqld.sock MYSQL_PORT 3306 MYSQL_OPT 0 MYSQL_DATABASE mailuser MYSQL_USER_TABLE user # MYSQL_CRYPT_PWFIELD crypt MYSQL_CLEAR_PWFIELD passwd # DEFAULT_DOMAIN example.com MYSQL_UID_FIELD uid MYSQL_GID_FIELD gid MYSQL_LOGIN_FIELD id MYSQL_HOME_FIELD home # MYSQL_NAME_FIELD name # MYSQL_MAILDIR_FIELD maildir # MYSQL_DEFAULTDELIVERY defaultdelivery MYSQL_QUOTA_FIELD concat(quota_size * 1024 ,"S") # MYSQL_WHERE_CLAUSE server='mailhost.example.com'

# in order specified bellow: # username, cryptpw, clearpw, uid, gid, home, maildir, quota, fullname

# MYSQL_SELECT_CLAUSE SELECT CONCAT(id, "@", mbox_host), "", passwd, uid, gid, home, "", concat(quota_size * 1024 ,"S"), "" FROM user WHERE id = "$(local_part)" AND mbox_host = "$(domain)"

and my current user-table:

Server: mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686)

mysql> desc user; +-------------+------------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+------------+-------+ | id | char(32) | | MUL | | | | crypt | char(32) binary | | | | | | passwd | char(32) binary | | | | | | uid | int(10) unsigned | | | 888 | | | gid | int(10) unsigned | | | 888 | | | shell | char(100) binary | | MUL | /bin/false | | | home | char(100) binary | | MUL | | | | mbox_host | char(64) | | | my.net | | | start_date | date | | MUL | 0000-00-00 | | | expire_date | date | | MUL | 2030-01-01 | | | active | enum('Y','N') | | MUL | Y | | | quota_size | int(10) unsigned | | | 0 | | +-------------+------------------+------+-----+------------+-------+ 12 rows in set (0.00 sec)

Need more input. Suggestions: Change the DEBUG_LOGIN option in pop3d (and/or imapd) config file from DEBUG_LOGIN=0 to DEBUG_LOGIN=1 Take a look at /var/log/maillog (or similar for your system). Turn on mySQL's General Query Log starting it with |--log[=file] or including log[=file] in your my.cnf. This log will have all queries sent to the server.

I activated the debuggings:

a POP-login produces:

----------------------------- mail.log: Nov 6 12:39:32 tank courierpop3login: Connection, ip=[::ffff:192.168.0.1] Nov 6 12:39:37 tank courierpop3login: LOGIN FAILED, ip=[::ffff:192.168.0.1]

mysql.log: 031106 12:39:32 24 Connect courier@localhost on 24 Init DB mailuser

-----------------------------

a IMAP-login produces:

mail.log: Nov 6 12:43:13 tank imaplogin: Connection, ip=[::ffff:192.168.0.1] Nov 6 12:43:18 tank imaplogin: LOGIN: DEBUG: ip=[::ffff:192.168.0.1], command=LOGIN Nov 6 12:43:18 tank imaplogin: LOGIN: DEBUG: ip=[::ffff:192.168.0.1], username=robtest Nov 6 12:43:23 tank imaplogin: LOGIN FAILED, ip=[::ffff:192.168.0.1] Nov 6 12:43:25 tank imaplogin: DISCONNECTED, ip=[::ffff:192.168.0.1], headers=0, body=0

mysql.log: 031106 12:43:18 25 Connect courier@localhost on 25 Init DB mailuser

-----------------------------

I bet that looking at both logs you will find the problem yourself but you can also send us some pieces of them. Easier to help knowing what's happening ;)|

Would have been nice if that happened, but it didnt :-(

I dont know why there are no QUERYs from the authclient, when i deactivate MYSQL_SELECT_CLAUSE everything works so nice.

I also tried to analyse the C-source, but I'm not confident with C-programs, i didnt succeed to find something in there.

MANY MANY thanx to all of you guys, who can help me with that!!!

Roman Bürkle