This is a quick readme for my mysql support in Qpopper 4.0.x Sample mysql config directives are stored in the mysql-popper.conf file in the main directory. Configure directives relative to mysql support are: --with-mysqllibpath=path Set the mysql library path [/usr/lib/mysql] This is where your libmysqlclient.so files are --with-mysqlincludepath=path Set the mysql include path [/usr/include/mysql] This is where your mysql.h files are --enable-mysql compile in mysql authentication --with-mysqlconfig=path Set the mysql-popper.conf file path [/etc/mysql-popper.conf] --enable-log-login-mysql Log successful user authentications into mysql database EXAMPLES -------- See file: example-mysql-configure.txt SETUP ----- * Configure, compile, and install qpopper normally with the desired options from above. * Copy the config file. Copy mysql-popper.conf into /etc/mysql-popper.conf or whatever you set --with-mysqlconfig= to Edit mysql-popper.conf with your mysql db info, table names, and fields you will use. * Create the mysql tables. This patch does NOT create mysql tables for you. For --enable-log-login-mysql, currently the table and field definitions are hardcoded. It should be defined/created in mysql as follows, where ip is the client IP and ts is the current timestamp in unix time: CREATE TABLE relay_ip ( ip char(15) NOT NULL default '', ts int(11) NOT NULL default '0', KEY ip(ip) ); The mysql authentication table should look something like this: CREATE TABLE email ( username char(128) NOT NULL, domain char(64), uid int(10), gid int(10), status int(2) DEFAULT '0', shell char(127), password char(64) NOT NULL, spool char(255), loginhosts char(255) NOT NULL ); username : The login name for the pop3 account domain : In a virtual domain setup, the domain the user is logging into. e.g. if the user is logging in as user@domain.com the username field should contain 'user' and the domain field should contain 'domain.com' uid : The UID the logged-in user's process will run as (if specified to check for in the .conf file). This should be a UID that has access to the user's mail spool. gid : The GID the logged-in user's process will run as (if specified to check for in the .conf file). This should be a GID that has access to the user's mail spool. status : The status of the user account. This determines if the user can login or not. Use '0' for disabled, '1' for enabled, '2' for suspended, '3' for on-hold. 1 is the only status that does NOT deny access. 2 and 3 are more of an 'informational' disabled status. shell : The shell a user will get when logged-in. This is mostly just for satisfying some requirements. You should set this to a non-active/non-existant shell. e.g. /bin/false password : The password for the user account, in one of the 4 types (as you designate in the .conf file). Namely, cleartext (just plain text in mysql), crypt (made with ENCRYPT('userpassword') in mysql), md5 (made with MD5('userpassword') in mysql), and mysql (made with PASSWORD('userpassword') in mysql). Set this to the actual password, not the TYPE of password! e.g. don't set this value to 'cleartext'. spool : The path to the location of the user's mail spool (if specified to check for in the .conf file, otherwise calculated). loginhosts : For multiple-pop3 server installations, a space delimited list of hosts the user can log into. To allow them to log into all hosts, specify a * (use this for single-server installations). To allow them to log into, say, host1.domain.com and host2.domain.com, you'd set this to: 'host1.domain.com host2.domain.com' * Run the qpopper daemon DO NOT specify the mysql-popper.conf file on the command line with the -f switch. Qpopper already knows where the file is, and to read it on startup. NOTES: The only REQUIRED fields are username and password. The other fields you only need to create if you query for said fields in your mysql-popper.conf configuration. * Populate the mysql table(s) with data. How to populate the auth. table with users is beyond the scope of this document. Reference your MySQL docs for the SQL commands to do this. But here's a quick example for an email of thelittleprince@asteroid-b612.org using ALL mysql fields. INSERT into email VALUES ( 'thelittleprince', 'asteroid-b612.org', '500', '100', '1', '/bin/false', ENCRYPT('userpassword'), '/var/spool/mail/asteroid-b612.org/thelittleprince', '*' ); DIRECTORY STRUCTURES (PLEASE READ!) ----------------------------------- When you use mysql, either with virtual or non-virtual set ups, spool directory or HOMEDIRMAIL set ups, some of the directory paths are constructed differently. Here is a run-down of how paths are constructed with this patch: In these examples POP_DROP_DIR is represented as /var/tmp/.pop POP_CACHE_DIR as /var/tmp/.cache POP_MAILDIR as /var/spool/mail and MYSQL_DEFAULT_HOMEDIR as /home NON-VIRTUAL (i.e. USER user) POP_MAILDIR SETUP: GNPH_PATH : /var/spool/mail GNPH_PATH (HASHED): /var/spool/mail/u/s GNPH_SPOOL : /var/spool/mail/user GNPH_SPOOL (HASHED): /var/spool/mail/u/s/user GNPH_OLDPOP : /var/spool/mail/.user.pop GNPH_OLDPOP (HASHED): /var/spool/mail/u/s/.user.pop GNPH_POP : /var/tmp/.pop/.user.pop GNPH_POP (HASHED): /var/tmp/.pop/u/s/.user.pop GNPH_CACHE : /var/tmp/.cache/.user.cache GNPH_CACHE (HASHED): /var/tmp/.cache/u/s/.user.cache HOMEDIRMAIL SETUP: GNPH_PATH : /home/user GNPH_PATH (HASHED): /home/u/s/user GNPH_SPOOL : /home/user/mbox GNPH_SPOOL (HASHED): /home/u/s/user/mbox GNPH_OLDPOP : /var/spool/mail/.user.pop GNPH_OLDPOP (HASHED): /var/spool/mail/u/s/.user.pop GNPH_POP : /var/tmp/.pop/.user.pop GNPH_POP (HASHED): /var/tmp/.pop/u/s/.user.pop GNPH_CACHE : /var/tmp/.cache/.user.cache GNPH_CACHE (HASHED): /var/tmp/.cache/u/s/.user.cache SPECIFIEDSPOOL SETUP (MysqlSpoolField/spool-file): In this setup hashed and non-hashed are the same for GNPH_PATH and GNPH_SPOOL, beacuse you are specifying in the db's table field the hashing structure yourself, so it is not calculated. GNPH_PATH : specifiedspool minus last /'d path arg GNPH_SPOOL : specifiedspool GNPH_OLDPOP : /var/spool/mail/.user.pop GNPH_OLDPOP (HASHED): /var/spool/mail/u/s/.user.pop GNPH_POP : /var/tmp/.pop/.user.pop GNPH_POP (HASHED): /var/tmp/.pop/u/s/.user.pop GNPH_CACHE : /var/tmp/.cache/.user.cache GNPH_CACHE (HASHED): /var/tmp/.cache/u/s/.user.cache VIRTUAL SETUP (i.e. USER user@domain.com, you set one of the virtual domain options in mysql-popper.conf) POP_MAILDIR SETUP: GNPH_PATH : /var/spool/mail/domain.com GNPH_PATH (HASHED): /var/spool/mail/domain.com/u/s GNPH_SPOOL : /var/spool/mail/domain.com/user GNPH_SPOOL (HASHED): /var/spool/mail/domain.com/u/s/user GNPH_OLDPOP : /var/spool/mail/domain.com/.user.pop GNPH_OLDPOP (HASHED): /var/spool/mail/domain.com/u/s/.user.pop GNPH_POP : /var/tmp/.pop/.user@domain.com.pop GNPH_POP (HASHED): /var/tmp/.pop/u/s/.user@domain.com.pop GNPH_CACHE : /var/tmp/.cache/.user@domain.com.cache GNPH_CACHE (HASHED): /var/tmp/.cache/u/s/.user@domain.com.cache HOMEDIRMAIL SETUP: GNPH_PATH : /home/domain.com/user GNPH_PATH (HASHED): /home/domain.com/u/s/user GNPH_SPOOL : /home/domain.com/user/mbox GNPH_SPOOL (HASHED): /home/domain.com/u/s/user/mbox GNPH_OLDPOP : /var/spool/mail/domain.com/.user.pop GNPH_OLDPOP (HASHED): /var/spool/mail/domain.com/u/s/.user.pop GNPH_POP : /var/tmp/.pop/.user@domain.com.pop GNPH_POP (HASHED): /var/tmp/.pop/u/s/.user@domain.com.pop GNPH_CACHE : /var/tmp/.cache/.user@domain.com.cache GNPH_CACHE (HASHED): /var/tmp/.cache/u/s/.user@domain.com.cache SPECIFIEDSPOOL SETUP (MysqlSpoolField/spool-file): In this setup hashed and non-hashed are the same for GNPH_PATH and GNPH_SPOOL, beacuse you are specifying in the db's table field the hashing structure yourself, so it is not calculated. GNPH_PATH : specifiedspool minus last /'d path arg GNPH_SPOOL : specifiedspool GNPH_OLDPOP : /var/spool/mail/domain.com/.user.pop GNPH_OLDPOP (HASHED): /var/spool/mail/domain.com/u/s/.user.pop GNPH_POP : /var/tmp/.pop/.user@domain.com.pop GNPH_POP (HASHED): /var/tmp/.pop/u/s/.user@domain.com.pop GNPH_CACHE : /var/tmp/.cache/.user@domain.com.cache GNPH_CACHE (HASHED): /var/tmp/.cache/u/s/.user@domain.com.cache USER CONFIG OPTIONS: POP_MAILDIR SETUP: NON-VIRTUAL: /var/spool/mail/.user.qpopper-options VIRTUAL: /var/spool/mail/.user@domain.com.qpopper-options HOMEDIRMAIL SETUP: NON-VIRTUAL: /home/user/.qpopper-options /home/u/s/user/.qpopper-options VIRTUAL: /home/domain.com/user/.qpopper-options /home/domain.com/u/s/user/.qpopper-options If you're using HOMEDIRMAIL, the MYSQL_DEFAULT_HOMEDIR variable in pop_user.c controls the base home directory for users. The default is "/home" HOW THE MYSQL AUTHENTICATION WORKS ---------------------------------- In pop_user() a mysql connection and query is made with the username entered as the search field (and the user's domain in a virtual setup). The resulting data is stored in the passwd structure that would normally be populated by getpwnam(). In pop_pass() the cached DB password in the passwd structure is compared against the entered password, using whatever method is specified in the MysqlAuthPasswordMethod config file option. On success and enabling of log-login-mysql, an additional mysql connection is made to the above table and appended/updated with the client's ip and the current unix timestamp. REPORTING A PROBLEM ------------------- Before reporting a problem to me, you should configure and run qpopper in debugging mode, and look through the generated logfile. This can potentially be VERY helpful solving your problem. To do this: 1. Pass the --enable-debugging option to the ./configure script 2. Compile qpopper as usual with make 3. Run qpopper with the -t switch, and the path to a logfile your want qpopper to write to. i.e. qpopper -t /var/log/qpopper.log 4. Connect to qpopper and cause the problem to happen. 5. Look through the log for debugging info. If reporting a problem to me, at thelittleprince@asteroid-b612.org make sure to: 1. Include a detailed description of the problem. 2. Your ./configure line 3. Your operating system/platform and version 4. Your version of qpopper 5. Your version of my patch 6. If you're using MySQL, the contents of your mysql-popper.conf file (be sure to mask out your mysql host and access info) 7. If you're using MySQL, your version of MySQL. 8. If NOT a configure/compiling problem, include all of, or relevant portions of your trace/debugging log file specified above, to me. 9. If IS a configure/compiling problem, include your config.log file EXTRA NOTES ----------- If using the log-login-mysql option in conjunction with your MTA for host relay checking, i'm told this is the proper relay config for Exim: host_accept_relay = "localhost:mysql;SELECT ip FROM relay_ip WHERE ip='${sender_host_address}'" or if you want to only select on ips less than 180 secs (3 min) old.. host_accept_relay = "localhost:mysql;SELECT ip FROM relay_ip WHERE ip='${sender_host_address}' AND ((UNIX_TIMESTAMP() - 180) < ts)" -Tony thelittleprince@asteroid-b612.org