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

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^
-1

You must define the mailreader in pg_hba.conf.

  • 1
    This needs more information, e.g. how to exactly do this. – Sven Jun 29 '17 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.