OT? Courier + PgSQL problem - Mailing list pgsql-general

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@feneon.com' 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@feneon.com';

   This returns:

      usr_email     | ?column? | usr_password | ?column? | ?column? |
?column? |     ?column?      | ?column? |   usr_name    | ?column?

-------------------+----------+--------------+----------+----------+----------+-------------------+----------+---------------+----------
  mkelly@feneon.com |          | 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@feneon.com
+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@feneon.com'
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@feneon.com, 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