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. Using: Fedora Core 5 Dovecot 1.0 PostgreSQL 8.1.4 Postfix 2.2.10 maildb=# select * from vpasswd ; userid | password -----------------+---------- [email protected] | joe /etc/dovecot.conf: userdb static { args = uid=506 gid=507 mail=/var/mail/vhosts/%d/%n/ } passdb sql { args = /etc/dovecot/dovecot-sql.conf } /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.
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)?
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:127.0.0.1, lip=::ffff:127.0.0.1, 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. Thanks.
...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.
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.
This seems to be a nice tutorial about the topic: http://www.lxtreme.nl/index.pl/docs/linux/dovecot_postfix_pam
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.
I can't really help you here because I've never worked with a combination of Dovecot and PostgreSQL...
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.
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 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.
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.
