CiviCRM Community Forums (archive)

If you remember I was previously participating in the forums and documentation for the setup of CiviCRM on Windows XP to be used as intranet. This was determined as 'unworkable' due to the restrictions on outgoing mail numbers.

I have since moved to a Virtual Private Server with 1and1. This VPS has CentOS 5.5 (custom kernel) running Plesk 9.5.2. The system has been upgraded fully with PHP 5.2.14 (all extensions), MySQL 5.1.49 and PRCE Unicode installed. I am running a domain where all the settings have been correctly configured (Apache, PHP, MySQL, DNS, iptables) after hundreds of hours 'learning' Linux.

Drupal 6.19 is installed on the root of a subdomain, with an install of the latest CiviCRM 3.2.3 correctly installed using the data migrated from the windows box. Postfix 2.3.3 is installed and configured for the basics of email with Plesk (bugs an' all) completing most of the config. I am using Courier-IMAP 3.0.8 for POP3 and IMAP using MailDir for storage.

Problem:
I recently set the email account information in CiviMail and Outbound Mail and successfully sent an email to current users (after creating the Mail Account in Plesk). Part of the issue is the relaxed SMTP configuration to allow PLAIN authentication rather than SSL; researching how this is completed using Plesk.
Immediately after the email sent from CiviCRM, no users receive any email. Although the maillog shows logs identifying delivery and receipt of some emails, no information is received into the relevant MailDir, even a Save and Test Email failed to be delivered! I get the following in the logs, that I have identified as part of the problem:


2010-09-10T15:35:01.908140+01:00 xxxx postfix/smtpd[28642]: sql_sqlite3 plugin prepare SQL statement for query 'SELECT `cmusaslsecretDIGEST-MD5` FROM domains d, users u WHERE u.name='civicrm' and d.name='mydomain.com' and d.status=0 and u.status=0 and u.dom_id=d.id;' error: no such column: cmusaslsecretDIGEST-MD5
2010-09-10T15:35:01.908140+01:00 xxxx postfix/smtpd[28642]: sql_sqlite3 plugin prepare SQL statement for query 'SELECT `cmusaslsecretDIGEST-MD5` FROM domains d, users u WHERE u.name='civicrm' and d.name='mydomain.com' and d.status=0 and u.status=0 and u.dom_id=d.id;' error: no such column: cmusaslsecretDIGEST-MD5
2010-09-10T15:35:05.114392+01:00 xxxx postfix/smtpd[28642]: sql_sqlite3 plugin prepare SQL statement for query 'SELECT `cmusaslsecretDIGEST-MD5` FROM domains d, users u WHERE u.name='civicrm' and d.name='mydomain.com' and d.status=0 and u.status=0 and u.dom_id=d.id;' error: no such column: cmusaslsecretDIGEST-MD5
2010-09-10T15:35:05.114392+01:00 xxxx postfix/smtpd[28642]: sql_sqlite3 plugin prepare SQL statement for query 'SELECT `cmusaslsecretDIGEST-MD5` FROM domains d, users u WHERE u.name='civicrm' and d.name='mydomain.com' and d.status=0 and u.status=0 and u.dom_id=d.id;' error: no such column: cmusaslsecretDIGEST-MD5
2010-09-10T15:35:11.244494+01:00 xxxx postfix/smtpd[28642]: sql_sqlite3 plugin prepare SQL statement for query 'SELECT `cmusaslsecretDIGEST-MD5` FROM domains d, users u WHERE u.name='civicrm' and d.name='mydomain.com' and d.status=0 and u.status=0 and u.dom_id=d.id;' error: no such column: cmusaslsecretDIGEST-MD5


I execute CiviCRM on the same VPS as the mail server, therefore do I/CiviCRM require the extra security provided by sasl that has obviously caused the problem. Can this be turned off? Or if not recommended, after I get the SSL certificate into Plesk, will I get back email functionality?

Postfix main.cf
queue_directory = /var/spool/postfix
command_directory = /usr/sbin
daemon_directory = /usr/libexec/postfix
mail_owner = postfix
inet_interfaces = all
mydestination = localhost.$mydomain, localhost, localhost.localdomain
unknown_local_recipient_reject_code = 550
alias_maps = hash:/etc/aliases, hash:/var/spool/postfix/plesk/aliases
alias_database = hash:/etc/aliases
debug_peer_level = 2
command =
PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
xxgdb $daemon_directory/$process_name $process_id & sleep 5
sendmail_path = /usr/sbin/sendmail.postfix
newaliases_path = /usr/bin/newaliases.postfix
mailq_path = /usr/bin/mailq.postfix
setgid_group = postdrop
html_directory = no
manpage_directory = /usr/share/man
sample_directory = /usr/share/doc/postfix-2.3.3/samples
readme_directory = /usr/share/doc/postfix-2.3.3/README_FILES
virtual_mailbox_domains = $virtual_mailbox_maps, hash:/var/spool/postfix/plesk/virtual_domains
virtual_alias_maps = $virtual_maps, hash:/var/spool/postfix/plesk/virtual
virtual_mailbox_maps = hash:/var/spool/postfix/plesk/vmailbox
transport_maps = hash:/var/spool/postfix/plesk/transport
smtpd_tls_cert_file = /etc/postfix/postfix_default.pem
smtpd_tls_key_file = $smtpd_tls_cert_file
smtpd_tls_security_level = may
smtpd_use_tls = yes
smtp_tls_security_level = may
smtp_use_tls = no
smtpd_sender_restrictions = check_sender_access hash:/var/spool/postfix/plesk/blacklists
smtp_send_xforward_command = yes
smtpd_authorized_xforward_hosts = 127.0.0.0/8
smtpd_sasl_auth_enable = yes
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
virtual_mailbox_base = /var/qmail/mailnames
virtual_uid_maps = static:110
virtual_gid_maps = static:31
virtual_transport = plesk_virtual
plesk_virtual_destination_recipient_limit = 1
# Changed smtpd client restrictions template (small bug in Plesk)
#smtpd_client_restrictions = reject_rbl_client
smtpd_client_restrictions = permit_sasl_authenticated, permit_mynetworks, reject_non_fqdn_sender, reject_unknown_sender_domain, permit
smtp_host_lookup = dns, native
myhostname = mail.mydomain.com
message_size_limit = 51200000
mynetworks = 127.0.0.0/8, [IP]


I understand that CiviMail does not use sqlite, however the SMTP server I use does . As I stated in the initial post, all my email services were working perfectly before I sent an email from within CiviCRM to civicrm@mydomain.com with the aforementioned info logged.

However, my question above seems to be premature as the postfix configuration has been compiled by Plesk with only a small amount of modifications by me.

I did get it working again after checking the CiviCRM source file; /packages/Net/SMTP.php and determining how CiviCRM identifies what level of authentication to use when sending SMTP mail. Although I should NOT change the level of security to be used for SMTP, I discovered that to prevent CiviCRM from using DIGEST-MD5 therefore preventing problems with Postfix on this configuration I could limit the methods of authentication available to LOGIN and PLAIN.

I modified not the source of CiviCRM, but my installed SMTP configuration as above and fixed the errors to get email back up and running until a planned modification for SSL is completed.

Sorry about the delay in replying.
I did think that since my mail server is on the same server as civicrm, that I didn't need to use the SMTP; just the mail(). I was only attempting to repeat the same options I had when running on a Windows server (no luck with that!).

as i was suggesting, could you switch in civi from smtp to mail() for the outbound ? should solve all your problems,

I changed the settings to mail() and it worked .

What do you mean by planned modification? smtp authentication is better in md5 than in plain, but smtp auth from localhost doesn;t make much sense in the first place

I am currently, (completed) configuring the site and mail server to use SSL.

The extract from my logs above have identified a problem in my system related to Plesk that occurs with md5 and the sqlite package. Seems to be a known bug. Apologies for being 'premature' and not researching the root of the problem before posting .

Thanks for the pointer on mail() though.