0

I am trying to use Postfix to authenticate into a postgresql database on an experimental debian 8 server. My postgresql configuration is the following:

The /etc/postgresql/9.4/main/pg_hba.conf file:

# Database administrative login by Unix domain socket
local   all             postgres                                peer 
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
#local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.\
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5
local    mail        all                 ident map=mailmap
local   all              all                                     peer

The /etc/postgresql/9.4/main/pg_ident.conf file:

 mailmap         dovecot                 mailreader
 mailmap         postfix                 mailreader
 mailmap         root                    mailreader

My Postfix configuration contains these files:

The /etc/postfix/main.cf has the following configuration:

smtpd_banner = $myhostname ESMTP $mail_name (Debian/GNU)
biff = no

# appending .domain is the MUA's job.
append_dot_mydomain = no

# Uncomment the next line to generate "delayed mail" warnings
#delay_warning_time = 4h

readme_directory = no

# TLS parameters
smtpd_tls_cert_file=/etc/letsencrypt/live/pcmagas.tk/fullchain.pem
smtpd_tls_key_file=/etc/letsencrypt/live/pcmagas.tk/privkey.pem

smtpd_use_tls=yes
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache

smtpd_sasl_auth_enable = yes
smtpd_sasl_type = dovecot
# The path is relative to $queue_directory:
#   # postconf |grep queue_directory
#   queue_directory = /var/spool/postfix
smtpd_sasl_path = private/auth
# Do not accept SASL authentication over unencrypted connections
smtpd_tls_auth_only = yes

# See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for
# information on enabling SSL in the smtp client.

smtpd_relay_restrictions = permit_mynetworks permit_sasl_authenticated defer_unauth_destination
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases


myhostname = mail.pcmagas.tk
myorigin = /etc/mailname
mydestination = mail.pcmagas.tk,localhost

relayhost = [mail.smtp2go.com]:2525
smtp_sasl_auth_enable = yes
smtp_sasl_security_options = noanonymous
smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd

mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128 10.0.0.0/24
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all

#virtual mailbox
virtual_transport = lmtp:unix:private/dovecot-lmtp
virtual_mailbox_domains = pcmagas.tk  
virtual_mailbox_maps = pgsql:/etc/postfix/pgsql-boxes.cf
virtual_alias_maps = pgsql:/etc/postfix/pgsql-aliases.cf

The /etc/postfix/pgsql-boxes.cf has the following content:

user=mailreader
dbname=mail
table=users
select_field=email
where_field=email
hosts=unix:/var/run/postgresql

The /etc/postfix/pgsql-aliases.cf has this content:

user=mailreader
dbname=mail
table=aliases
select_field=alias
where_field=email
hosts=unix:/var/run/postgresql

Also the ls -l /var/run/postgresql/ gives the following:

drwxr-s--- 2 postgres postgres 120 Ιούν 29 18:06 9.4-main.pg_stat_tmp
-rw-r--r-- 1 postgres postgres   4 Ιούν 29 17:20 9.4-main.pid

When I try to send a local mail via sendemail command it outputs the following message:

Jun 29 18:08:42 mailservertest sendemail[1189]: NOTICE => Authentication not > supported by the remote SMTP server! Jun 29 18:08:42 mailservertest sendemail[1189]: WARNING => The recipient was rejected by the mail server, error follows: Jun 29 18:08:42 mailservertest sendemail[1189]: WARNING => Received: 451 4.3.0 : Temporary lookup failure Jun 29 18:08:42 mailservertest sendemail[1189]: ERROR => Exiting. No recipients were accepted for delivery by the mail server.

And the /var/log/mail.log file has the following logs:

Jun 29 18:10:38 MailserverTest postfix/postscreen[1202]: CONNECT from [::1]:32932 to [::1]:25 Jun 29 18:10:38 MailserverTest postfix/postscreen[1202]: WHITELISTED [::1]:32932 Jun 29 18:10:38 MailserverTest postfix/smtpd[1203]: connect from localhost[::1] Jun 29 18:10:38 MailserverTest postfix/trivial-rewrite[1205]: warning: connect to pgsql server unix:/var/run/postgresql: could not connect to server: No such file or directory??Is the server running locally and accepting??connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?? Jun 29 18:10:38 MailserverTest postfix/trivial-rewrite[1205]: warning: pgsql:/etc/postfix/pgsql-aliases.cf: table lookup problem Jun 29 18:10:38 MailserverTest postfix/trivial-rewrite[1205]: warning: virtual_alias_domains lookup failure Jun 29 18:10:38 MailserverTest postfix/trivial-rewrite[1205]: warning: pgsql:/etc/postfix/pgsql-aliases.cf: table lookup problem Jun 29 18:10:38 MailserverTest postfix/trivial-rewrite[1205]: warning: virtual_alias_domains lookup failure Jun 29 18:10:38 MailserverTest postfix/smtpd[1203]: NOQUEUE: reject: RCPT from localhost[::1]: 451 4.3.0 : Temporary lookup failure; from= to= proto=ESMTP helo= Jun 29 18:10:38 MailserverTest postfix/smtpd[1203]: lost connection after RCPT from localhost[::1] Jun 29 18:10:38 MailserverTest postfix/smtpd[1203]: disconnect from localhost[::1]

Also the database that I use has the following tables:

 Schema |  Name   | Type  |   Owner    
--------+---------+-------+------------
 public | aliases | table | mailreader
 public | users   | table | mailreader

And each table has the following chema:

mail=> \d users
                Table "public.users"
  Column  |           Type           |   Modifiers   
----------+--------------------------+---------------
 email    | text                     | not null
 password | text                     | not null
 maildir  | text                     | not null
 created  | timestamp with time zone | default now()

mail=> \d aliases
  Table "public.aliases"
 Column | Type | Modifiers 
--------+------+-----------
 alias  | text | not null
 email  | text | not null

Also postgresql listent to a unix socket:

netstat -xlnp | grep postgresql
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
unix  2      [ ACC ]     STREAM     LISTENING     11651    -                   /var/run/postgresql/.s.PGSQL.5432

Do you fellows have any Idea why I get this errors and how I will fix them? A

Dimitrios Desyllas
  • 563
  • 2
  • 11
  • 30

3 Answers3

0

Clearly, postgres was not listening on the socket:

Also the ls -l /var/run/postgresql/ gives the following:

drwxr-s--- 2 postgres postgres 120 Ιούν 29 18:06 9.4-main.pg_stat_tmp -rw-r--r-- 1 postgres postgres 4 Ιούν 29 17:20 9.4-main.pid

To enable the socket endpoint, configure unix_socket_directories in postgres conf (and make sure that the server is actually running).

Robert Cutajar
  • 113
  • 6
0

In the end I needed to switch into host authentication that worked like a charm:

In the pg_hba.conf change the line into: host mail mailreader 127.0.0.1/32 ident map=mailmap

Also in postgresql via sudo -u postgres psql (usually) execute the following command to set password for the user mailreader:

ALTER USER mailreader WITH PASSWORD '^password_here^'

Then both on /etc/postfix/pgsql-boxes.cf and /etc/postfix/pgsql-aliases.cf change the 

hosts=unix:/var/run/postgresql

line into 

hosts=localhost

Also set the password field on both files mentioned above like that:

password=^put the password here^
Dimitrios Desyllas
  • 563
  • 2
  • 11
  • 30
-1

You must define the mailreader in pg_hba.conf.

Theodotos Andreou
  • 1
  • 1