OT? Courier + PgSQL problem

[Madison Kelly <linux@xxxxxxxxxxx>]

Hi all,

  I fully acknowledge that this may be off topic, but hopefully not too 
much. :) I am hoping some of you have used PgSQL this way and can help 
as I am not on any courier mail lists.

  I have a problem I can't seem to figure out. I am trying to get
Courier to read email over POP3 using a PgSQL database as the backend.
It's close, but not quite functional.

  The problem is, it doesn't seem to be looking for the mail file
properly. It's not concatenating 'PGSQL_HOME_FIELD' and
'PGSQL_MAILDIR_FIELD' fields.

  In my case, email is stored as: '/email/<domain>/<user>' so the user
'mkelly@xxxxxxxxxx' should end up looking for the mail file at
'/email/feneon.com/mkelly'. The query being generated is:

SELECT
	usr_email,
	'',
	usr_password,
	1001,
	1001,
	'/email',
	substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM '(.*)@'),
	'',
	usr_name,
	''
FROM
	users
WHERE
	usr_email = 'mkelly@xxxxxxxxxx';

  This returns:

     usr_email     | ?column? | usr_password | ?column? | ?column? |
?column? |     ?column?      | ?column? |   usr_name    | ?column?
-------------------+----------+--------------+----------+----------+----------+-------------------+----------+---------------+----------
 mkelly@xxxxxxxxxx |          | foo          |     1001 |     1001 |
/email   | feneon.com/mkelly |          | Madison Kelly |

  The important columns are:

 ?column? |     ?column?
----------+-------------------
 /email   | feneon.com/mkelly

  But when I try to connect over telnet to port 25 I get this error:

$ telnet cluster 110
Trying 192.168.2.10...
Connected to cluster.
Escape character is '^]'.
+OK Hello there.
USER mkelly@xxxxxxxxxx
+OK Password required.
PASS secret
-ERR chdir feneon.com/mkelly failed
Connection closed by foreign host.

  And in '/var/log/mail.info':

Aug 28 11:59:13 nicole authdaemond: LOG:  duration: 1.765 ms  statement:
SELECT usr_email, '', usr_password, 1001, 1001, '/email',
substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM
'(.*)@'), '', usr_name, '' FROM users WHERE usr_email = 'mkelly@xxxxxxxxxx'
Aug 28 11:59:13 nicole courierpop3login: chdir feneon.com/mkelly: Not a
directory
Aug 28 11:59:13 nicole authdaemond: Authenticated: sysusername=<null>,
sysuserid=1001, sysgroupid=1001, homedir=/email,
address=mkelly@xxxxxxxxxx, fullname=Madison Kelly,
maildir=feneon.com/mkelly, quota=<null>, options=<null>
Aug 28 11:59:13 nicole authdaemond: Authenticated: clearpasswd=secret,
passwd=<null>

  Nothing relevant is printed in 'mail.err'. From what I see in
'/etc/courier/authpgsqlrc' I have:

##NAME: PGSQL_HOME_FIELD:0
#

PGSQL_HOME_FIELD        '/email'

##NAME: PGSQL_NAME_FIELD:0
#
# The user's name (optional)

PGSQL_NAME_FIELD        usr_name

##NAME: PGSQL_MAILDIR_FIELD:0
#
# This is an optional field, and can be used to specify an arbitrary
# location of the maildir for the account, which normally defaults to
# $HOME/Maildir (where $HOME is read from PGSQL_HOME_FIELD).
#
# You still need to provide a PGSQL_HOME_FIELD, even if you uncomment this
# out.
#
PGSQL_MAILDIR_FIELD     substring(usr_email FROM
'@(.*)')||'/'||substring(usr_email FROM '(.*)@')

  Should this not work?

Thanks all!

Madison

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match