Dovecot-1.0 and Postgres authentication

Discussion in 'Server Operation' started by Youngblood000, Jul 28, 2006.

  1. Youngblood000

    Youngblood000 New Member

    I am trying to set up Dovecot to use a Postgres database to read in passwords. I can get it to work when it reads in the passwords from a flat file, but my boss wants everything to work through a database.

    Fedora Core 5
    Dovecot 1.0
    PostgreSQL 8.1.4
    Postfix 2.2.10

    maildb=# select * from vpasswd ;
    userid | password
    [email protected] | joe

    userdb static {
    args = uid=506 gid=507 mail=/var/mail/vhosts/%d/%n/
    passdb sql {
    args = /etc/dovecot/dovecot-sql.conf

    driver = pgsql
    connect = host=localhost dbname=maildb user=postgres password=******
    password_query = SELECT password FROM vpasswd WHERE userid='%u'

    I configured dovecot --with-pgsql and it still fails to authenticate. Can anyone find an error in there or recommend something to find out more information. I've checked /var/log/maillog and the postgres logfile. It seems as though dovecot connects to maildb, but perhaps the query is set up wrong. I've looked everywhere on the 'net, I would be SO pleased if someone could help me out. Thank you very much.
    Last edited: Jul 28, 2006
  2. falko

    falko Super Moderator ISPConfig Developer

    Can you post the relevant parts of your logs?
    The first thing that struck me is that you save your password as clear text in the database. Maybe dovecot expects an encrypted password (MD5 or something like that)?
  3. Youngblood000

    Youngblood000 New Member

    From /var/log/maillog after an unsuccessful login:

    Jul 31 08:35:15 test dovecot: pop3-login: Aborted login: user=<[email protected]>, method=PLAIN, rip=::ffff:, lip=::ffff:, secured

    Postgres's log says nothing about it. According to /var/log/maillog it is expecting a plain password. How do you encrypt data in postgres? I'll give that a try and see what happens. Plus, I'll want it encrypted when it starts working, also.

  4. Youngblood000

    Youngblood000 New Member

    ...Ok, then... Falko, you were right. In the database, I needed to precede the password with the type of encryption, in this case:

    userid | password
    [email protected] | {plain}kyle
    [email protected] | {plain}joe

    So, the authentication part is working now.
  5. Youngblood000

    Youngblood000 New Member

    How do I get the passwords to be stored in postgres as encrypted text and then get dovecot to read them using that encryption? I don't want people to be able to look into my database and know what the passwords for my users are, but I want Dovecot to be able to decrypt the text. Thanks.
  6. falko

    falko Super Moderator ISPConfig Developer

  7. Youngblood000

    Youngblood000 New Member

    Thanks for the link. I followed the tutorial exactly, but the password fields in the database are still human-readable. How do I encrypt them so that no one can read them except Dovecot (via PAM or SASL or whatever)? Thanks.
  8. falko

    falko Super Moderator ISPConfig Developer

    I can't really help you here because I've never worked with a combination of Dovecot and PostgreSQL... :eek:
  9. Youngblood000

    Youngblood000 New Member

    Ok. Well thank you very much for your help, Falko. I appreciate it.

    Does anyone else know? It seems like it should be a very plausible idea to encrypt data in a database. But I can't find how to do it anywhere.
  10. Youngblood000

    Youngblood000 New Member

    I finally found a helpful article. It pointed me in the right direction and I figured out the rest. Here's what I did:

    go to installation folder of postgres (in my case, /root/downloads/postgresql-8.1.4)
    cd contrib/pgcrypto
    make install
    make installcheck (ensure that the functions will work)
    psql dbname -f pgcrypto.sql (run this for each database you want encrypted)

    psql dbname
    update ... set password=crypt('passwd', gen_salt('md5')) where ...;

    And that will encrypt whatever field you desire to be encrypted and it can be decrypted by other programs, which makes it suitable for passwords, credit card numbers, or other sensitive information.
  11. falko

    falko Super Moderator ISPConfig Developer

    And you didn't have to configure Dovecot so that it can work with encrypted passwords?
  12. Youngblood000

    Youngblood000 New Member

    I'm not sure. I may have modified it earlier at some point (I've changed the configuration so often and foolishly didn't document the changes). But, it worked when I tried it. Perhaps the lines:

    auth default {
    mechanisms = digest-md5 plain

    are what made it read it properly. That's my best guess. It may have already been set up. I'm not sure.

Share This Page