[Dovecot] PostgreSQL connection bug

[Daniel Howard]

Hello,

There seems to be a bug which can affect systems using PostgreSQL as a
user or password database.

Usually there is just one postgres connection, but if the query fails for
any reason, the failed connection is left open and dovecot will keep
opening new connections for each auth attempt.

I discovered this with a setup using PAM, AND a database to authenticate. 
Most users login with an account number (integer) which is looked up in
the postgres database.  Some users have a unix system account, and use
their system username to log in (string).  Dovecot was set up to check the
database first, then try PAM.

When a system user logged in, the SQL query failed because it couldn't
coerce the system user's username into an integer.

In these cases, Dovecot would still fall back to PAM authentication, and
the user would log in, but the postgres connection would remain stuck. 
Gradually the number of connections would increase until postgres failed.


I worked around this by converting the account numbers in the database
from integers to strings.  Now the query never fails and there's only ever
1 dovecot connection.  Hopefully this will help someone.



dovecot --version
1.0.15


dovecot -n
# 1.0.15: /etc/dovecot/dovecot.conf
log_timestamp: %Y-%m-%d %H:%M:%S
login_dir: /var/run/dovecot/login
login_executable: /usr/lib/dovecot/imap-login
mail_privileged_group: mail
mail_location: maildir:~/Maildir
auth default:
  passdb:
    driver: sql
    args: /etc/dovecot/dovecot-sql.conf
  passdb:
    driver: pam
  userdb:
    driver: passwd
  userdb:
    driver: prefetch





cat dovecot-sql.conf | egrep -v "^#"

driver = pgsql
connect = host=localhost dbname=rapidgroup user=dovecot password=*********
default_pass_scheme = PLAIN
password_query = SELECT userid as user, password, userdb_mail, userdb_uid,
userdb_gid FROM dovecotview WHERE userid='%u'




Cheers, Dan