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
'[EMAIL PROTECTED]' 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 = '[EMAIL PROTECTED]';
This returns:
usr_email | ?column? | usr_password | ?column? | ?column? |
?column? | ?column? | ?column? | usr_name | ?column?
-------------------+----------+--------------+----------+----------+----------+-------------------+----------+---------------+----------
[EMAIL PROTECTED] | | 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 [EMAIL PROTECTED]
+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 = '[EMAIL PROTECTED]'
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,
[EMAIL PROTECTED], 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
