Topic: Dovecot Error

============ Required information ====
- iRedMail version: 0.8.5
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): pgsql
- Linux/BSD distribution name and version: Ubuntu 12.04.2 LTS
- Related log if you're reporting an issue: Login via Dovecot / RoundCube

Install went well, setup my first domain: ciordia.info, made my user. Attempted to login via Mail.app imap, failed so I logged in via roundcube.


Jul 23 17:51:56 nexus roundcube: IMAP Error: Login failed for andy@ciordia.info from LOGIN: Internal error occurred. Refer to server log for more information. in /usr/share/apache2/roundcubemail-0.9.2/program/lib/Roundcube/rcube_imap.php on line 184 (POST /mail/?_task=login&_action=login)


Jul 23 17:51:56 auth: Info: pgsql( Connected to database vmail
Jul 23 17:51:56 auth: Error: pgsql: Query failed, aborting: SELECT 'andy@ciordia.info' AS master_user, CONCAT(mailbox.storagebasedirectory, '/', mailbox.storagenode, '/', mailbox.maildir) AS home, CONCAT('*:bytes=', mailbox.quota*1048576) AS quota_rule FROM mailbox,domain WHERE mailbox.username='andy@ciordia.info' AND mailbox.domain='ciordia.info' AND mailbox."enableimapsecured"=1 AND mailbox.domain=domain.domain AND domain.backupmx=0 AND domain.active=1 AND mailbox.active=1
Jul 23 17:51:56 auth: Error: sql(andy@ciordia.info, User query failed: ERROR:  function concat(character varying, unknown, character varying, unknown, character varying) does not exist
Jul 23 17:51:56 auth: Error: LINE 1: SELECT 'andy@ciordia.info' AS master_user, CONCAT(mailbox.st...
Jul 23 17:51:56 auth: Error:                                                    ^
Jul 23 17:51:56 auth: Error: HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
Jul 23 17:51:56 imap: Error: Internal auth failure (client-pid=4630 client-id=1)
Jul 23 17:51:56 imap-login: Info: Internal login failure (pid=4630 id=1) (auth failed, 1 attempts): user=<andy@ciordia.info>, method=PLAIN, rip=, lip=, mpid=4631, secured

While I've run postfix many times outside of databases this is my first psql install so I'm a bit out of my neighborhood.

Advance thanks for input.

2 (edited by alberto 2013-07-24 23:23:09)

Re: Dovecot Error

I think PostgreSQL 8 and that query aren't going to work along.
PostgreSQL different versions in the 8 series handle CONCAT in a something random way. This version, it seems, doesn't allow CONCAT of string literals (in this case '/'). In version 9 it works fine, at least in my experience.
In order to make the code PostgreSQL 8-compatible, it should read

SELECT 'andy@ciordia.info' AS master_user,
mailbox.storagebasedirectory || '/' || mailbox.storagenode || '/' || mailbox.maildir AS home (etc...)

Actually, I think CONCAT() is not a standard SQL function.


Re: Dovecot Error

Ubuntu 12.04 ships PostgreSQL-9.1, why yours is 8.x?

# cat /etc/lsb-release 

# apt-cache policy postgresql
  Installed: (none)
  Candidate: 9.1+129ubuntu1
  Version table:
     9.1+129ubuntu1 0
        500 http://mirrors.163.com/ubuntu/ precise-updates/main amd64 Packages
     9.1+129 0
        500 http://mirrors.163.com/ubuntu/ precise/main amd64 Packages

Anyway, you can make it work by following alberto's reply.


Re: Dovecot Error

I bet this is a legacy from when this install was a part of the 10.x branch.

I decided to be a good boy and remove all the pieces that might be in the way for this to work so I've removed everything, configs, etc, and will do a fresh install with fresh packages. Who knows what other conflicts might rise up, so lets see if a fresh install makes everything happy.

Thanks for seeing the versioning problem!


Re: Dovecot Error

All clear!

I thought I was smart in my uninstalls but I introduced a number of duplicate configs that had to be hunted down and either removed or edited and cleaned up.

Otherwise I think I've got all the pieces talking nicely to each other.. (now that I've said that....)

Thanks again!