dovecot - Jul 2008 - Dovecot failed with a correct sql user_query

Dovecot (1.1.1) criticises a sql user_query that's working on the sql  
console and thats correct.

DOVECOT-LOG:
"dovecot: Jul 06 22:58:29 Error: auth(default): sql(nti): User query  
failed: ERROR:  syntax error at or near "mbox"
dovecot: Jul 06 22:58:29 Error: auth(default): LINE 1: ...gid, home,  
(CASE WHEN smtp like '/var/pop3/ THEN 'mbox:' || ..."

QUERY:
user_query = SELECT '%u' AS user, uid, gid, home, (CASE WHEN home like  
'/var/pop3/%' THEN 'mbox:' || home || '/mail/:INDEX=' ||
home || '/
indexes/' WHEN home like '/var/maildir/%' THEN 'maildir:' ||
home || '/
mail/:INDEX=' || home || '/indexes/' END) AS mail FROM users WHERE  
username = '%u'  AND users.enabled = '1' AND
users.type='local'

PGSQL-PROMPT:
mail=# SELECT 'nti' AS user, uid, gid, home, (CASE WHEN home like '/
var/pop3/%' THEN 'mbox:' || home || '/mail/:INDEX=' || home
|| '/
indexes/' WHEN home like '/var/maildir/%' THEN 'maildir:' ||
home || '/
mail/:INDEX=' || home || '/indexes/' END) AS mail FROM users WHERE  
username = 'nti' AND users.enabled = '1' AND
users.type='local';
  user | uid | gid |       home       |                               
mail
------+-----+-----+------------------ 
+----------------------------------------------------------------
  nti  |  99 |  99 | /var/maildir/nti | maildir:/var/maildir/nti/ 
mail/:INDEX=/var/maildir/nti/indexes/

Hi Nicola,

I'm guessing Dovecot is failing at your % character in "....like 
'var/pop3/%' THEN....." - notice in the log, it's missing the
final
apostrophe from that string ("....like '/var/pop3/ THEN.....".)

Dovecot is trying to take %' as a substitution character (like %u) and 
converting it to nothing (I think.)

I'm not 100% certain if this will work, but try changing your % in your 
SQL query to %% (i.e. make them double %'s instead of single.)

Regards,

Andy

Nicola Tiling wrote:
>
> Dovecot (1.1.1) criticises a sql user_query that's working on the sql 
> console and thats correct.
>
> DOVECOT-LOG:
> "dovecot: Jul 06 22:58:29 Error: auth(default): sql(nti): User query 
> failed: ERROR:  syntax error at or near "mbox"
> dovecot: Jul 06 22:58:29 Error: auth(default): LINE 1: ...gid, home, 
> (CASE WHEN smtp like '/var/pop3/ THEN 'mbox:' || ..."
>
> QUERY:
> user_query = SELECT '%u' AS user, uid, gid, home, (CASE WHEN home
like
> '/var/pop3/%' THEN 'mbox:' || home ||
'/mail/:INDEX=' || home ||
> '/indexes/' WHEN home like '/var/maildir/%' THEN
'maildir:' || home ||
> '/mail/:INDEX=' || home || '/indexes/' END) AS mail FROM
users WHERE
> username = '%u'  AND users.enabled = '1' AND
users.type='local'
>
> PGSQL-PROMPT:
> mail=# SELECT 'nti' AS user, uid, gid, home, (CASE WHEN home like 
> '/var/pop3/%' THEN 'mbox:' || home ||
'/mail/:INDEX=' || home ||
> '/indexes/' WHEN home like '/var/maildir/%' THEN
'maildir:' || home ||
> '/mail/:INDEX=' || home || '/indexes/' END) AS mail FROM
users WHERE
> username = 'nti' AND users.enabled = '1' AND
users.type='local';
>  user | uid | gid |       home       |                              mail
>
------+-----+-----+------------------+----------------------------------------------------------------
>
>  nti  |  99 |  99 | /var/maildir/nti | 
> maildir:/var/maildir/nti/mail/:INDEX=/var/maildir/nti/indexes/
>