1 Topic by ciordia9

  • ciordia9
  • Member
  • Offline
  • Registered: 2013-07-24
  • Posts: 7

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.

Mail.err:

Jul 23 17:51:56 nexus roundcube: IMAP Error: Login failed for andy@ciordia.info from 96.10.189.173. 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)

logs/dovecot.log:

Jul 23 17:51:56 auth: Info: pgsql(127.0.0.1): 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,127.0.0.1): 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=127.0.0.1, lip=127.0.0.1, 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.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

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

  • alberto
  • Member
  • Offline
  • Registered: 2013-07-24
  • Posts: 4

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.

3 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,091

Re: Dovecot Error

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

# cat /etc/lsb-release 
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=12.04
DISTRIB_CODENAME=precise
DISTRIB_DESCRIPTION="Ubuntu 12.04.2 LTS"

# apt-cache policy postgresql
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.

4 Reply by ciordia9

  • ciordia9
  • Member
  • Offline
  • Registered: 2013-07-24
  • Posts: 7

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!

5 Reply by ciordia9

  • ciordia9
  • Member
  • Offline
  • Registered: 2013-07-24
  • Posts: 7

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!