Re: Postfix(chroot) and Postgresql

[Bengt Frost]

*Addition* to above: In pg_hba.conf (PosgreSQL):
vmail(user)  access to datab with md5 password
local(and host)

--bfrost

Bengt Frost wrote:

Hi,

I am trying to use PostgreSQL as a backend for my Postfix virtual mail system and dovecot(psql) for smtp-auth. 'Postfix' is chrooted - most of it - and with MySQL socket there is no problem to auth users and use Postfix transport_maps and virtual_*_maps. I have problem with postgresql socket(.s.PGSQL.5432). Neither dovecot(auth) or Postfix(processes) can connect to PostgreSQL. I have tried to google, read OpenBSD misc and ports maillinglists with no success. Here is some files with related 'stuff':

### rc - system
/etc/rc.local:
# Postfix - PostgreSQL
if [ -x /usr/local/bin/pg_ctl ]; then
   echo -n ' postgresql'
   su -l _postgresql -c "nohup /usr/local/bin/pg_ctl start \
   -D /var/postgresql/data -l /var/postgresql/logfile \
   -o '-D /var/postgresql/data' >/dev/null"
   su -l _postgresql -c "ln -s /var/spool/postfix/tmp/.s.PGSQL.5432 /tmp"

su -l _postgresql -c "ln -s /var/spool/postfix/tmp/.s.PGSQL.5432.lock /tmp"

fi

/etc/rc.shutdown:
# Posfix - PostgreSQL
if [ -f /var/postgresql/data/postmaster.pid ]; then
    su -l _postgresql -c "/usr/local/bin/pg_ctl stop -m fast \
       -D /var/postgresql/data"
   rm -f /var/postgresql/data/postmaster.pid \
         /var/spool/postfix/tmp/.s.PGSQL.5432 \
         /var/spool/postfix/tmp/.s.PGSQL.5432.lock \
         /tmp/.s.PGSQL.5432 \
         /tmp/.s.PGSQL.5432.lock
fi

### Dovecot:
/etc/dovecot.conf:
 passdb sql {
   args = /etc/dovecot-pgsql.conf
 }
...
userdb sql {
   args = /etc/dovecot-pgsql.conf
... socket listen {
   client {
       path = /var/spool/postfix/private/auth
       mode = 0660
       user = _postfix
       group = _postfix
   }
/etc/dovecot-pgsql:

# Currently supported schemes include PLAIN, PLAIN-MD5, DIGEST-MD5, and CRYPT.

default_pass_scheme = CRYPT  # also above schemes

# Database options
# UNIX socket - see host
connect = host=/tmp/.s.PGSQL.5432 dbname=xxxx user=vmail password=xxxxx

### Postfix - referensed from main.conf(pgsql: - no proxymap used):
/etc/postfix/pgsql_transport:
# UNIX socket - PostgreSQL - relative path(chroot)
hosts = unix:/tmp/.s.PGSQL.5432
# inet: for TCP connections (default)
#hosts = localhost
##hosts = 127.0.0.1

### PostgreSQL
/var/postgresql/postgresql.conf:
unix_socket_directory = '/var/spool/postfix/tmp'

# tmp directory in Postfix root : rwxrwxr-t permission and 'owned' by _postfix _postgresql

Someone out there have any suggestions how use Postfix (and Dovecot) with PostgreSQL?

Thanks!

--bfrost