Mail server - Configuring Postfix to use MySQL - part 2

Now we have created the external files Postfix will use when connecting to the 'mail' database (see the previous article for details), we can carry on and edit the main Postfix configuration file.


main.cf

The main Postfix configuration file is know as 'main.cf'.

Let's go ahead and edit that:

sudo nano /etc/postfix/main.cf

The default entries include the hostname that was set during the Postfix installation.

As such, the bottom part of the file looks like this:

myhostname = mail.demoslice.com
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
myorigin = /etc/mailname
mydestination =
relayhost =
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all

You will find the 'myhostname' and 'mydestination' already have the hostname set - you will need to remove any entries for the 'mydestination' field.

Virtual Files

Now we need to add the details of the four files we created in the previous article so Postfix knows to refer to those in any mail execution.

At the bottom of the file add these lines:

virtual_alias_domains =
virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-forwards.cf, mysql:/etc/postfix/mysql-email.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-domains.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-mailboxes.cf
virtual_mailbox_base = /home/vmail
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000

proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $virtual_mailbox_limit_maps

You can see the references to the files we created and also the references to the 'vmail' user we created (where the mail is physically located).

The last line lets Postfix know what to search for in a mail domain to get the relevant details for the database query.

Done.

Summary

Now we have Postfix configured to query the database whenever needed.

The next article concentrates on configuring saslauthd for our secure connections.

PickledOnion

Article Comments:

Jesper Christiansen commented Sun Sep 07 19:19:25 UTC 2008:

After I have added the lines under "Virtual Files", then I can't use "mail" to send mail to external adresses anymore - if i remove those lines "mail" works again..

Is that how it should be?

PickledOnion commented Tue Sep 09 16:54:24 UTC 2008:

Hi,

That is not as it should be - we actually use mail later on to test the setup.

You should be able to send mail to external addresses via the 'mail' command.

PickledOnion

Jesper Christiansen commented Tue Sep 09 23:29:41 UTC 2008:

Ok! Great.. Then the error is somewhere there - either in the .cf files or somewhere else.. cause when I add the above lines to my main.cf, my mail stops working..

Thanks :)

Andrew Powell commented Sun Sep 28 11:37:55 UTC 2008:

Like Jasper, since adding the virtual mailbox stuff everything screws up for me. I can revert back to the original way, because I just commented out the original stuff. SMTP works when I revert, but IMAP never worked.

KFCSpike commented Wed Oct 08 20:25:35 UTC 2008:

I have the same problem.

I have tried twice to set this up,checking every entry, but same problem both times.

Checking /var/log/mail.log seems to show an error connecting to mysql.

I can connect to mysql using the mailadmin user in phpmyadmin, so can confirm that user is set up properly.

All I can think of is that I installed MySQL using an earlier article and maybe I have blocked a port needed by this article?

PickledOnion commented Thu Oct 09 10:11:15 UTC 2008:

Hi,

I am not sure what the issue may be. It is true that the article series is based on a fresh Slice but I have gone through it many times and have no issues.

I am not sure what to suggest to be honest.

PickledOnion

KFCSpike commented Thu Oct 09 18:36:52 UTC 2008:

I discovered (after a lot of googling) that the problem was mysql in my set-up was not listening on 127.0.0.1

My error was in /etc/mysql/my.cnf bind-address was set to my slice ip, it should be set to 127.0.0.1

I probably set this to my slice ip when setting up phpmyadmin, but it looks like I can still access phpmyadmin remotely after changing to it to 127.0.0.1 :)

I would suggest others having similar problems try:

sudo nano /etc/mysql/my.cnf

[...] bind-address = 127.0.0.1 [...]

/etc/init.d/mysql restart

Ben Wilson commented Sun Oct 12 16:50:46 UTC 2008:

Okay, I had the loop problem. But I added the following lines to main.cf, and things worked.

virtual_alias_domains =
virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-forwards.cf, mysql:/etc/postfix/mysql-email.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-domains.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-mailboxes.cf
virtual_mailbox_base = /home/vmail
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000

proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $virtual_mailbox_limit_maps

Jeremiah commented Wed Oct 29 18:41:31 UTC 2008:

For anyone else having the same problem connecting to mysql after the previous steps, where removing the last lines fixes it, issuing: sudo /etc/init.d/postfix restart fixed it for me. I'm not sure why it would, but it did.

Jeremiah commented Wed Oct 29 18:57:59 UTC 2008:

Sorry, my last comment was not as clear as it should have been (feel free to delete it).

I was receiving messages in /var/log/mail.log like the following:

Oct 29 18:35:38 disgruntledpeasants postfix/cleanup[5351]: warning: 6E52CA81A4:  virtual_alias_maps map lookup problem for ...

along with mysql authentication errors.

I could access mysql with the user in question via command-line, and could verify that the user had the correct privileges granted to it.

Issuing sudo /etc/init.d/postfix restart solved the issue, although I'm not sure why (guessing that config items conflicted or something).

Andrew B commented Sat Jan 17 23:07:23 UTC 2009:

I did some tests with postfix, and it seems if "virtualaliasmaps =" doesn't return any destination from mySQL it just deleivers the message to the original destination anyways

Abed commented Mon Jan 19 03:09:18 UTC 2009:

Err. I skipped the installation part because I had installed postfix and mysql in previous articles. When the mail stopped working I figured I had missed something by skipping the install line, quick compare/contrast from other articles: postfix + mysql (this article) sudo aptitude install postfix postfix-mysql mysql-server postfix-tls libsasl2-2 libsasl2-modules libsasl2-modules-sql sasl2-bin libpam-mysql openssl telnet mailx

postfix (previous article)
sudo aptitude install postfix telnet mailx

mysql (previous article)
sudo aptitude install mysql-server mysql-client libmysqlclient15-dev

the difference
install postfix-mysql postfix-tls libsasl2-2 libsasl2-modules libsasl2-modules-sql sasl2-bin libpam-mysql openssl

probably should have just copy/pasted the full installation line, i guess it would have resolved existing installations on its own? not sure what needs to be restarted either if at all asides from postfix.

IV commented Sun Jan 25 01:49:17 UTC 2009:

Anyone had this problem?: postfix/cleanup[4361]: warning: connect to mysql server 127.0.0.1: Access denied for user 'mailadmin'@'localhost'

thanks in advance

IV commented Sun Jan 25 10:39:39 UTC 2009:

Solved the problem, Checked the password in every configuration file. Validate if there aren't any spaces after the password

Keith Long commented Sat Apr 04 10:35:32 UTC 2009:

My issue was that my VMAIL User Id in /etc/passwd was not 5000 but 1000 I believe this may have been as originally I as using dovecot.

virtualuidmaps = static:5000 virtualgidmaps = static:5000

Again checking in /var/log sorted this out; these tutorials are brilliant my only critism is you don't have enough checkpoints to ensure that you have actually got this step working before moving on. Someone could be three articles past where they created the problem :(

Jeff Schwab commented Fri Apr 10 19:38:41 UTC 2009:

[Similar symptom, but from user error; posting so error-message searches by posterity will find something relevant.]

Messages in /var/log/mail.log:

fatal: dict_open: unsupported dictionary type: mysql: Is the postfix-mysql package installed? warning: private/proxymap socket: service dictproxyopen: Success warning: process /usr/lib/postfix/proxymap pid 7096 exit status 1 warning: /usr/lib/postfix/proxymap: bad command startup -- throttling

Fix:

sudo aptitude install postfix-mysql /etc/init.d/postfix restart

Messages in /var/log/mail.log:

fatal: /etc/postfix/mysql-email.cf: bad string length 0 < 1: dbname = warning: process /usr/lib/postfix/cleanup pid 7392 exit status 1 warning: /usr/lib/postfix/cleanup: bad command startup -- throttling

Fix: sudo vi /etc/postfix/*.cf

whoops, correct whatever you screwed up

/etc/init.d/postfix restart

TFM says postfix also has a reload command, but I haven't tried it.

Luke commented Tue Oct 20 21:46:31 UTC 2009:

After this setup, I can still send emails but cannot receive any. I had a look at /var/log/mail.log and found this:

NOQUEUE: reject: RCPT from fg-out-1718.google.com[72.14.220.154]: 554 5.7.1 demo@mail.demo.net: Relay access denied; from=test.inc@gmail.com to=demo@mail.demo.net proto=ESMTP helo=<fg-out-1718>

What is the problem?

lee commented Mon Jan 25 22:18:58 UTC 2010:

So basically I have the same problem as a lot of you, i.e. mail only works without the virtual host paths in main.cf. Does anybody have a difinitive fix for this as none of the one's posted have done anything for me.

Jason Schmidt commented Wed Oct 19 02:43:07 UTC 2011:

How come second value doesn't have proxy:mysql?

proxy:mysql:/etc/postfix/mysql-forwards.cf, mysql:/etc/postfix/mysql-email.cf

Last minute hotel deals / Guarrantee Low /Instant Booking / Free Cancellation commented Thu Jul 11 12:22:45 UTC 2013:

Magnificent beat ! I wish to apprentice while you amend your web site, how can i subscribe for a blog web site? The account helped me a appropriate deal. I had been tiny bit acquainted of this your broadcast provided vivid transparent idea

Last minute hotel deals / Guarrantee Low /Instant Booking / Free Cancellation commented Thu Jul 11 12:24:45 UTC 2013:

Magnificent beat ! I wish to apprentice while you amend your web site, how can i subscribe for a blog web site? The account helped me a appropriate deal. I had been tiny bit acquainted of this your broadcast provided vivid transparent idea

Alphonse commented Fri Jul 12 15:16:40 UTC 2013:

Hey just wanted to give you a quick heads up. The words in your content seem to be running off the screen in Ie. I'm not sure if this is a formatting issue or something to do with internet browser compatibility but I figured I'd post to let you know.

The style and design look great though! Hope you get the issue resolved soon. Cheers

xbox games shops in pontypridd commented Fri Jul 12 15:28:23 UTC 2013:

Excellent blog you have here.. It's difficult to find high-quality writing like yours these days. I honestly appreciate people like you! Take care!!

xbox games shops in pontypridd commented Fri Jul 12 15:28:24 UTC 2013:

Excellent blog you have here.. It's difficult to find high-quality writing like yours these days. I honestly appreciate people like you! Take care!!

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)