# Dovecot seems unable to perform pgsql queries



## mariourk (Sep 20, 2017)

Hi,

I'm trying to setup a mailserver using Postfix, Dovecot and PostgreSQL. And I've ran into a problem that's driving me mad, since I can't figure out what's wrong. I've build multiple servers like this already and the current setup should work.

The problem is that I can't login via IMAP or IMAP-SSL. The logs show this:


```
Sep 20 14:22:32 imap-login: Info: Disconnected (disconnected before auth was ready, waited 0 secs): user=<>, rip=123.123.123.123, lip=321.321.321.321, TLS handshaking: SSL_accept() failed: error:1408A0C1:SSL routines:ssl3_get_client_hello:no shared cipher, session=<Wd21B55ZGtBcRCyZ>
```

Notice this little part: *user=<>*
That should be: *user=<me@domain.com>*

I started digging a little deeper and started logging all the PostgreSQL queries. And to my surpise, no query whatsoever from Dovecot shows up in the logs. I do see queries from Postfix. And incoming email are delivered as expected. So that seems to work. I also can login to PostgreSQL from the commandline. But Dovecot refuses to even touch PostgreSQL. I don't even see any errors in the logs.

The only difference with any setup I've done so far is that PostgreSQL runs in a seperate jail. I have *jail_sysvipc_allow* enabled and all connections to PostgreSQL work fine. Except for Dovecot. I installed Dovecot from ports (/usr/ports/mail/dovecot - dovecot-2.2.31_2) with pgsql enabled.

dovecot.conf


```
auth_debug=yes
auth_verbose=yes
mail_debug=yes

protocols = imap lmtp
listen = *

auth_mechanisms = plain login
mail_max_userip_connections = 40
disable_plaintext_auth = no

log_path = /var/log/dovecot.log
info_log_path = /var/log/dovecot_info.log
debug_log_path = /var/log/dovecot_debug.log

first_valid_uid = 1002
last_valid_uid = 1002
first_valid_gid = 1002
last_valid_gid = 1002

mail_home = /usr/local/virtual/%d/%u
mail_location = maildir:~


passdb {
        driver = sql
        args = /usr/local/etc/dovecot/dovecot-sql.conf
}

userdb {
        driver = sql
        args = /usr/local/etc/dovecot/dovecot-sql.conf
}

service imap-login {
        inet_listener imap {
                port = 143
                ssl = no
        }

        inet_listener imaps {
                port = 993
                ssl = yes
        }

        type = login
        process_limit = 250
        process_min_avail = 5
}

namespace inbox {
        separator = .
        prefix = INBOX.
        inbox = yes
}

service auth {
        inet_listener {
                port = 12345
        }
}

plugin {
        sieve = ~/sieve/.dovecot.sieve
        sieve_global_path = /usr/local/etc/dovecot/sieve/default.sieve
        sieve_dir = ~/sieve
        sieve_global_dir = /usr/local/etc/dovecot/sieve/
}

verbose_ssl = yes
ssl_cert = </etc/ssl/dovecot/cert.pem
ssl_key = </etc/ssl/dovecot/key.pem
ssl_ca = </etc/ssl/dovecot/ca_x509.pem
```

dovecot-sql.conf


```
driver = pgsql
connect = host=123.123.123.123 dbname=postfix user=postfix password=SuperSecret
default_pass_scheme = MD5-CRYPT

# Get the mailbox
user_query = SELECT '/usr/local/virtual/%d/%u' as home, 'maildir:/usr/local/virtual/%d/%u' as mail, 1002 AS uid, 1002 AS gid, concat('dirsize:storage=',  quota) AS quota FROM mailbox WHERE username = '%u' AND active = '1'

# Get the password
password_query = SELECT username as user, password, '/usr/local/virtual/%d/%u' as userdb_home, 'maildir:/usr/local/virtual/%d/%u' as userdb_mail, 1002 as  userdb_uid, 1002 as userdb_gid FROM mailbox WHERE username = '%u' AND active = '1'
```

Any help will be most appreciated.


----------



## SirDice (Sep 20, 2017)

mariourk said:


> ```
> connect = host=123.123.123.123 dbname=postfix user=postfix password=SuperSecret
> ```


Verify if you can actually login on PostgreSQL with those credentials. Then verify if the SQL queries are correct and return something useful. Using the command line tool make sure to replace the %d and %u in the queries with actual (existing) data.


----------



## mariourk (Sep 20, 2017)

I did. And it works.

The odd thing is that Dovecot logs no complaint whatsoever, about not being able to connect to the PostgreSQL server. Even when I change the host or the password to something stupid.


----------



## SirDice (Sep 20, 2017)

You're probably missing something. The default dovecot.conf normally loads a bunch of configuration files from /usr/local/etc/dovecot/conf.d/. If I recall correctly I had to modify 10-auth.conf a bit in order to get the database access working (I have Dovecot running with MySQL). The only changes I made to dovecot.conf itself involved the protocols and IP address.


----------



## mariourk (Sep 21, 2017)

There is no /usr/local/etc/dovecot/conf.d/ 

And `dovecot -n` dumps the same config as I put into /usr/local/etc/dovecot/dovecot.conf. So that's what it seems to be using.


----------



## SirDice (Sep 21, 2017)

I see they moved them, `pkg info -l dovecot`:

```
...snip...
        /usr/local/etc/dovecot/example-config/conf.d/10-auth.conf
        /usr/local/etc/dovecot/example-config/conf.d/10-director.conf
        /usr/local/etc/dovecot/example-config/conf.d/10-logging.conf
        /usr/local/etc/dovecot/example-config/conf.d/10-mail.conf
        /usr/local/etc/dovecot/example-config/conf.d/10-master.conf
        /usr/local/etc/dovecot/example-config/conf.d/10-ssl.conf
        /usr/local/etc/dovecot/example-config/conf.d/15-lda.conf
        /usr/local/etc/dovecot/example-config/conf.d/15-mailboxes.conf
        /usr/local/etc/dovecot/example-config/conf.d/20-imap.conf
        /usr/local/etc/dovecot/example-config/conf.d/20-lmtp.conf
        /usr/local/etc/dovecot/example-config/conf.d/20-pop3.conf
        /usr/local/etc/dovecot/example-config/conf.d/90-acl.conf
        /usr/local/etc/dovecot/example-config/conf.d/90-plugin.conf
        /usr/local/etc/dovecot/example-config/conf.d/90-quota.conf
        /usr/local/etc/dovecot/example-config/conf.d/auth-checkpassword.conf.ext
        /usr/local/etc/dovecot/example-config/conf.d/auth-deny.conf.ext
        /usr/local/etc/dovecot/example-config/conf.d/auth-dict.conf.ext
        /usr/local/etc/dovecot/example-config/conf.d/auth-ldap.conf.ext
        /usr/local/etc/dovecot/example-config/conf.d/auth-master.conf.ext
        /usr/local/etc/dovecot/example-config/conf.d/auth-passwdfile.conf.ext
        /usr/local/etc/dovecot/example-config/conf.d/auth-sql.conf.ext
        /usr/local/etc/dovecot/example-config/conf.d/auth-static.conf.ext
        /usr/local/etc/dovecot/example-config/conf.d/auth-system.conf.ext
        /usr/local/etc/dovecot/example-config/conf.d/auth-vpopmail.conf.ext
        /usr/local/etc/dovecot/example-config/dovecot-dict-auth.conf.ext
        /usr/local/etc/dovecot/example-config/dovecot-dict-sql.conf.ext
        /usr/local/etc/dovecot/example-config/dovecot-ldap.conf.ext
        /usr/local/etc/dovecot/example-config/dovecot-sql.conf.ext
        /usr/local/etc/dovecot/example-config/dovecot.conf
...snip...
```
I've set this up a couple of years ago, the port may have changed a bit in the mean time.


----------



## mariourk (Sep 21, 2017)

The port did change. It used to be split into Dovecot and Dovecot2. Now it's just Dovecot. And all the setups I've done so far just use a sinlge file, /usr/local/etc/dovecot/dovecot.conf.

As far as I can see is all I need is in /usr/local/etc/dovecot/dovecot.conf. And that is also what's being loaded, accordin to `dovecot -n`. So, it should work. What surprises me the most, is that Dovecot doesn't even log any errors about not being able to connect to PostgreSQL.


----------



## gkontos (Sep 21, 2017)

How are you trying to connect? It looks like the client does not accept the certificate.


```
SSL_accept() failed: error:1408A0C1:SSL routines:ssl3_get_client_hello:no shared cipher
```


----------



## mariourk (Sep 21, 2017)

I experience the same problem when I try this over an unexcrypted IMAP-connection on port 143.


```
Sep 21 13:34:34 imap-login: Info: Disconnected: Auth process broken (disconnected before auth was ready, waited 14 secs): user=<>, rip=123.123.123.123, lip=321.321.321.321, session=<1Zq8ebFZ6stcRCyZ>
```

Notice how *user=<>* is still empty. And I still have zero hits on the database. So my guess is, this is not an SSL-issue, but a DB-issue.


----------



## SirDice (Sep 21, 2017)

mariourk said:


> It used to be split into Dovecot and Dovecot2.


It wasn't split up, the 'old' dovecot port was a 1.x version, dovecot2 was 2.x. As the old 1.x versions have now been deprecated the 'old' port got removed and the 'old' dovecot2 port was renamed to dovecot.



> And all the setups I've done so far just use a sinlge file, /usr/local/etc/dovecot/dovecot.conf.


Yes, but that was common with Dovecot 1.x, for 2.x the configuration got split up into various smaller files.


----------



## mariourk (Oct 2, 2017)

Well, I finally bit the bullet, deleted the entire jail and started from scratch. This time everything worked as expected.

So, I'm glad everything works now and I can move on. But it still bugs me that I never figured out the actual cause of this problem


----------

