LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Distributions > Debian
User Name
Password
Debian This forum is for the discussion of Debian Linux.

Notices



Reply
 
Search this Thread
Old 01-13-2009, 06:25 PM   #1
bluethundr
Member
 
Registered: Jun 2003
Location: Summit, NJ
Distribution: CentOS 5.4
Posts: 122

Rep: Reputation: 15
Mail Disappears into a black hole using postfix, mysql


Hello linux guys,

Could really use your help with this one.

Well, first off MUCH THANKS to flurdy for his brilliant tutorial which I followed to the best of my ability here:

http://flurdy.com/docs/postfix/

Truly excellent work and an excellent tutorial.

Nevertheless I am having some problems that I can sure use some help on.

Let me state before going any further that I _need_ to mysqlize my postfix backend, any user limitations on my end be damned!

Currently, my postfix mail setup can send to the universe, but cannot receive.

Mail sent to my account gets this bounce message:

http://paste.debian.net/25859/

Mail sent to my domain apparently disappears into a black hole for 7 days as my mail system delays bouncing it. I'd love to know why.

In this and in all of my further pastebins I have sanitized vital information in order to protect my domain and accounts from abuse. But you should still be able to tell what's going on from the info presented.

Here's one thing I would like to note before we proceed. This is how my virtual_mailbox_base is set in main.cf:

virtual_mailbox_base = /var/spool/mail/virtual

Yet though I have accounts setup in my mysql database, when I cd to that location the directory is completely empty.

Also, another odd thing is that I keep seeing references to user@foo.com. No where in my main.cf is foo.com mentioned so I am at a loss to understand this.

This you might not be able to help with though, because 'foo' is how I obscure my domain in posts to pastebin for security purposes.

Here's my logs (obscured of course using foo and other obfuscations including the nonsensical 666.666.666.666 representing my public IP).

http://paste.debian.net/25861/

As you can see, my logs are rife with errors that I am unclear on how to address.

Here is my my main.cf file (again obfuscated):

http://paste.debian.net/25863/

Here's my master.cf file for good measure (not sure if this helps or not):

http://paste.debian.net/25864/

This is my virtual_mailbox_maps = mysql:/etc/postfix/mysql_mailbox.cf

http://paste.debian.net/25867/

This is my virtual_uid_maps = mysql:/etc/postfix/mysql_uid.cf

http://paste.debian.net/25869/

This is my virtual_gid_maps = mysql:/etc/postfix/mysql_gid.cf

http://paste.debian.net/25870/

This is my virtual_alias_maps = mysql:/etc/postfix/mysql_alias.cf

http://paste.debian.net/25871/

And my final mysql interaction in my main.cf,

virtual_mailbox_domains = mysql:/etc/postfix/mysql_domains.cf

http://paste.debian.net/25872/

This is how my mail database is setup:

http://paste.debian.net/25873/

This is my aliases table in mysql:

http://paste.debian.net/25874/

This is my domains table:

http://paste.debian.net/25875/

and finally this is my users table:

http://paste.debian.net/25876/


And lastly, POP and IMAP refuse to authenticate valid user data.

Here is my /etc/courier/authmysqlrc:

http://paste.debian.net/25877/

This is my /etc/courier/imapd:

http://paste.debian.net/25879/

And finally, finally here's my /etc/courier/pop3d


That's all I can think of for now.

ANY insight into my plight at all will save a poor sysadmin's sanity.

Thank you all very much.
 
Old 01-13-2009, 07:05 PM   #2
billymayday
Guru
 
Registered: Mar 2006
Location: Sydney, Australia
Distribution: Fedora, CentOS, OpenSuse, Slack, Gentoo, Debian, Arch, PCBSD
Posts: 6,678

Rep: Reputation: 122Reputation: 122
A couple of thoughts

1) I home "myhostname = mail. foo.com" is a typo when you sunbtituted foo, since that space shouldn't be there.

2)According to your logs, you have a virtual domain listed in mydestination, but according to your main.cf, no value for mydestination. Does this mean your logs precede changes to main.cf - in which case they're not too useful.

3) is mysqld running, and if so, where is it's socket? This looks like the major problem.

4) did you try the setup before adding the mysql part, or did you just follow a the guied and cross your fingers? The usual method for setting up postfix (and other things) is to get a basic setup working and add bits at a time.


See how you go with that.
 
Old 01-13-2009, 10:34 PM   #3
bluethundr
Member
 
Registered: Jun 2003
Location: Summit, NJ
Distribution: CentOS 5.4
Posts: 122

Original Poster
Rep: Reputation: 15
Thanks for the input!

1) To answer your question, the space in mail. foo.com is actually a slight bug in the perl script I ran to scrub the file of information that would reveal too much detail to the net and possibly expose my config to intruders.

In the actual config file there's no space. It's like "myhostname = mail.foo.com". I'm sorry, I should have corrected that in the pastebin or at least pointed that out.

And yes! My config was working perfectly before my mysqlization. I actually backed up my config files by copying them to main.cf.pre-flurdy should I ever desire to go back and have a working config again.

As to 2) I will correct that and have a go. Thank you for the lucid observation.

3) mysqld is running I can connect to it on the command line and by phpmyadmin. It is running on port 3306, but I don't know how to determine the socket or make it connect via a socket.

4) see above. Yes my postfix was running fine prior to the mysqlization. I do really need to get mysqlization of the backend to postfix running as soon as reasonably possible.

Thanks again for your input.




Quote:
Originally Posted by billymayday View Post
A couple of thoughts

1) I home "myhostname = mail. foo.com" is a typo when you sunbtituted foo, since that space shouldn't be there.

2)According to your logs, you have a virtual domain listed in mydestination, but according to your main.cf, no value for mydestination. Does this mean your logs precede changes to main.cf - in which case they're not too useful.

3) is mysqld running, and if so, where is it's socket? This looks like the major problem.

4) did you try the setup before adding the mysql part, or did you just follow a the guied and cross your fingers? The usual method for setting up postfix (and other things) is to get a basic setup working and add bits at a time.


See how you go with that.
 
Old 01-13-2009, 11:58 PM   #4
billymayday
Guru
 
Registered: Mar 2006
Location: Sydney, Australia
Distribution: Fedora, CentOS, OpenSuse, Slack, Gentoo, Debian, Arch, PCBSD
Posts: 6,678

Rep: Reputation: 122Reputation: 122
3) look in /etc/my.cnf
 
Old 01-14-2009, 02:17 AM   #5
bluethundr
Member
 
Registered: Jun 2003
Location: Summit, NJ
Distribution: CentOS 5.4
Posts: 122

Original Poster
Rep: Reputation: 15
Thanks. I looked in /etc/mysql/my.cnf and this is what I found regarding the socket:

"socket = /var/run/mysqld/mysqld.sock"

Yet, I still keep getting things like this turn up in my logs:

"Jan 11 08:40:47 mail postfix/cleanup[21753]: warning: connect to mysql server localhost: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)"

And when I ls -l /var/run/mysqld/mysqld.sock this is what I get:

"srwxrwxrwx 1 mysql mysql 0 2009-01-11 22:02 /var/run/mysqld/mysqld.sock"

Any suggestions on how to proceed with this info?
Thanks







Quote:
Originally Posted by billymayday View Post
3) look in /etc/my.cnf
 
Old 01-14-2009, 04:37 AM   #6
billymayday
Guru
 
Registered: Mar 2006
Location: Sydney, Australia
Distribution: Fedora, CentOS, OpenSuse, Slack, Gentoo, Debian, Arch, PCBSD
Posts: 6,678

Rep: Reputation: 122Reputation: 122
My guess would be a permissions problem with mysql. Is there anything in the mysql logs when postfix tries to connect?
 
Old 01-14-2009, 06:30 AM   #7
bluethundr
Member
 
Registered: Jun 2003
Location: Summit, NJ
Distribution: CentOS 5.4
Posts: 122

Original Poster
Rep: Reputation: 15
Thumbs down Pastebinning my head off

I sent a new email to my user account to generate a new mysql event and to generate a new event in the mail logs.


Here ya go, here's what mysql does:

http://paste.debian.net/25977/

Some of what you see there is laconica stuff which seems to be working smoothly. Look all at the end for the attempted postfix interaction.

Here's the same event in the mail log:

http://paste.debian.net/25978/

Again, the most recent mail event is all the way at the end.

Good luck!

Quote:
Originally Posted by billymayday View Post
My guess would be a permissions problem with mysql. Is there anything in the mysql logs when postfix tries to connect?

Last edited by bluethundr; 01-14-2009 at 09:44 AM.
 
Old 01-14-2009, 02:07 PM   #8
billymayday
Guru
 
Registered: Mar 2006
Location: Sydney, Australia
Distribution: Fedora, CentOS, OpenSuse, Slack, Gentoo, Debian, Arch, PCBSD
Posts: 6,678

Rep: Reputation: 122Reputation: 122
Next time you post logs, etc., can you post them in the thread, relevant parts only and surrounded by code tags please - these links are impossible to follow, and there's topo much irrelevant stuf there.

Your problem seems to revolve around
Code:
2278 Query       SELECT 'virtual' FROM domains WHERE domain=' foo.com'
which seems to generate
Code:
Jan 14 09:34:17 mail postfix/virtual[30062]: warning: mysql query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM users WHERE email='amavis@ foo.com'' at line 1
Jan 14 09:34:17 mail postfix/virtual[30062]: warning: table virtual_mailbox_maps: lookup amavis@ foo.com: Success
What is the relevant query that's causing this? I can't immediately identify it from those I've seen.
 
Old 01-14-2009, 06:01 PM   #9
bluethundr
Member
 
Registered: Jun 2003
Location: Summit, NJ
Distribution: CentOS 5.4
Posts: 122

Original Poster
Rep: Reputation: 15
Okay, thanks for the tip.



The problem, I agree seems to be stemming from the line that you pointed out.


That entry to the log seems to point to this line in main.cf:

Code:
virtual_mailbox_maps = mysql:/etc/postfix/mysql_mailbox.cf

I looked at the contents of that file and all seems well. I can't figure out why that would be the source of the problem. Here are the contents of the file:

Code:
user=fake_db_user
password=fake_passwd
dbname=mail
table=users
select_field=maildir
where_field=id
hosts=127.0.0.1
additional_conditions = and enabled = 1
MySQL seems to build the query out of the contents of this file. But I am hard pressed to find an error in the file as I have it here.

And this is the users table in the mail database:

Code:
mysql> DESCRIBE users;
+-----------------+----------------------+------+-----+-------------------------+-------+
| Field           | Type                 | Null | Key | Default                 | Extra |
+-----------------+----------------------+------+-----+-------------------------+-------+
| id              | varchar(128)         | NO   | PRI |                         |       | 
| name            | varchar(128)         | NO   |     |                         |       | 
| uid             | smallint(5) unsigned | NO   |     | 5000                    |       | 
| gid             | smallint(5) unsigned | NO   |     | 5000                    |       | 
| home            | varchar(255)         | NO   |     | /var/spool/mail/virtual |       | 
| maildir         | varchar(255)         | NO   |     | blah/#this is what it is| 
| enabled         | tinyint(3) unsigned  | NO   |     | 1                       |       | 
| change_password | tinyint(3) unsigned  | NO   |     | 1                       |       | 
| clear           | varchar(128)         | NO   |     | defaultPass             |       | 
| crypt           | varchar(128)         | NO   |     | gobbledyGook            |       | 
| quota           | varchar(255)         | NO   |     |                         |       | 
| procmailrc      | varchar(128)         | NO   |     |                         |       | 
| spamassassinrc  | varchar(128)         | NO   |     |                         |       | 
+-----------------+----------------------+------+-----+-------------------------+-------+
13 rows in set (0.00 sec)

Quote:
Originally Posted by billymayday View Post
Next time you post logs, etc., can you post them in the thread, relevant parts only and surrounded by code tags please - these links are impossible to follow, and there's topo much irrelevant stuf there.

Your problem seems to revolve around
Code:
2278 Query       SELECT 'virtual' FROM domains WHERE domain=' foo.com'
which seems to generate
Code:
Jan 14 09:34:17 mail postfix/virtual[30062]: warning: mysql query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM users WHERE email='amavis@ foo.com'' at line 1
Jan 14 09:34:17 mail postfix/virtual[30062]: warning: table virtual_mailbox_maps: lookup amavis@ foo.com: Success
What is the relevant query that's causing this? I can't immediately identify it from those I've seen.

Last edited by bluethundr; 01-14-2009 at 06:05 PM.
 
Old 01-14-2009, 09:08 PM   #10
billymayday
Guru
 
Registered: Mar 2006
Location: Sydney, Australia
Distribution: Fedora, CentOS, OpenSuse, Slack, Gentoo, Debian, Arch, PCBSD
Posts: 6,678

Rep: Reputation: 122Reputation: 122
Doesn't look like the right query. Try
Code:
grep virtual /etc/postfix/*.cf
because I'd expect "select_field=virtual" somewhere
 
Old 01-14-2009, 11:07 PM   #11
bluethundr
Member
 
Registered: Jun 2003
Location: Summit, NJ
Distribution: CentOS 5.4
Posts: 122

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by billymayday View Post
Doesn't look like the right query. Try
Code:
grep virtual /etc/postfix/*.cf
because I'd expect "select_field=virtual" somewhere
hmmm.. that grep sure produced a lot of output. What is it we're looking for?

Code:
/etc/postfix/main.cf:# this specifies where the virtual mailbox folders will be located 
/etc/postfix/main.cf:virtual_mailbox_base = /var/spool/mail/virtual
/etc/postfix/main.cf:virtual_mailbox_maps = mysql:/etc/postfix/mysql_mailbox.cf
/etc/postfix/main.cf:virtual_uid_maps = mysql:/etc/postfix/mysql_uid.cf
/etc/postfix/main.cf:virtual_gid_maps = mysql:/etc/postfix/mysql_gid.cf
/etc/postfix/main.cf:virtual_alias_maps = mysql:/etc/postfix/mysql_alias.cf 
/etc/postfix/main.cf:virtual_mailbox_domains = mysql:/etc/postfix/mysql_domains.cf 
/etc/postfix/main.cf:virtual_alias_domains = 
/etc/postfix/main.cf:virtual_alias_maps = mysql:/etc/postfix/mysql_alias.cf 
/etc/postfix/main.cf:virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf
/etc/postfix/main.cf:virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf
/etc/postfix/main.cf:virtual_mailbox_base = /home/vmail
/etc/postfix/main.cf:#virtual_uid_maps = static:5000
/etc/postfix/main.cf:#virtual_gid_maps = static:5000
/etc/postfix/main.cf:# this is how to connect to the domains (all virtual, but the option is there)
/etc/postfix/main.cf:virtual_create_maildirsize = yes
/etc/postfix/main.cf:virtual_mailbox_extended = yes
/etc/postfix/main.cf:virtual_mailbox_limit_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailbox_limit_maps.cf
/etc/postfix/main.cf:virtual_mailbox_limit_override = yes
/etc/postfix/main.cf:virtual_maildir_limit_message = "The user you are trying to reach is over quota."
/etc/postfix/main.cf:virtual_overquota_bounce = yes
/etc/postfix/main.cf: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
/etc/postfix/master.cf:virtual   unix  -       n       n       -       -       virtual
/etc/postfix/mysql-virtual_domains.cf:select_field = 'virtual'
/etc/postfix/mysql-virtual-mailbox-domains.cf:query = SELECT 1 FROM virtual_domains WHERE name='%s'

Because I believe that mysql query is automagiclly being built from that file,
Code:
/etc/postfix/mysql_mailbox.cf
isn't it?

It looks to me like the query is built from the elements of the file which for easy reference I will repeat here:

Code:
user=fake_db_user
password=fake_passwd
dbname=mail
table=users
select_field=maildir
where_field=id
hosts=127.0.0.1
additional_conditions = and enabled = 1
So, unfortunately and I hate to say it, I'm still confused as to why the mysql query is failing.
 
Old 01-14-2009, 11:45 PM   #12
billymayday
Guru
 
Registered: Mar 2006
Location: Sydney, Australia
Distribution: Fedora, CentOS, OpenSuse, Slack, Gentoo, Debian, Arch, PCBSD
Posts: 6,678

Rep: Reputation: 122Reputation: 122
See the last two lines of your grep output:
Code:
/etc/postfix/mysql-virtual_domains.cf:select_field = 'virtual'
/etc/postfix/mysql-virtual-mailbox-domains.cf:query = SELECT 1 FROM virtual_domains WHERE name='%s'
and from your main.cf you have
Code:
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf
but I don't see them being used in your howto. Where did these come from, and what is the conternts of the relevant .cf's?
 
Old 01-15-2009, 08:34 AM   #13
bluethundr
Member
 
Registered: Jun 2003
Location: Summit, NJ
Distribution: CentOS 5.4
Posts: 122

Original Poster
Rep: Reputation: 15
Wink

Quote:
Originally Posted by billymayday View Post
See the last two lines of your grep output:
Code:
/etc/postfix/mysql-virtual_domains.cf:select_field = 'virtual'
/etc/postfix/mysql-virtual-mailbox-domains.cf:query = SELECT 1 FROM virtual_domains WHERE name='%s'
and from your main.cf you have
Code:
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf
but I don't see them being used in your howto. Where did these come from, and what is the conternts of the relevant .cf's?

...hmmm. I didn't notice those. Thanks for pointing them out. I actually did try to follow one other how to before I attempted flurdy's (which is the best I've found so far). I thought I expunged everything from my main.cf that didn't pertain to flurdy's instructions.

Seems I may have missed a couple. These two lines seem like they may be detritus from the previous how-to. Again, thanks for spotting those.

At any rate, here is the output from those files:

Code:
/etc/postfix/mysql-virtual_domains.cf
Code:
user = fake_user
password = fake_pass
dbname = mail
table = domains
select_field = 'virtual'
where_field = domain
hosts = 127.0.0.1
Code:
/etc/postfix/mysql-virtual_mailboxes.cf
Code:
user = fake_user
password = fake_pass
dbname = mail
table = users
select_field = CONCAT(SUBSTRING_INDEX(email,'@',-1),
where_field = email
hosts = localhost

Ahhhhhhhh!!!!!!!! I think we have an anomaly here! Looking at the contents of these files I am beyond _certain_ that they are garbage left over from a previous attempt to put a mysql backend on my mail.

Notice the 'hosts' field is 'localhost' instead of 127.0.0.1 like my other .cf's. But most importantly, if you look at a description of my 'users' table you will notice that there is no 'email' field. Flurdy has that field represented as 'id' in his setup.

So, I am going to take 2 seconds to correct those anomalies. But I'm more interested in commenting those lines out and seeing if my mail setup comes to life!

Wish me luck!

Last edited by bluethundr; 01-15-2009 at 08:35 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
postfix mail to postfix +mysql mail btaylor1988 Linux - Server 0 05-29-2007 07:00 PM
FC5, the memory black hole seimour Fedora 3 04-17-2006 05:30 PM
postfix, mysql and mail routing demian Linux - Networking 0 09-09-2004 10:37 AM
Printing problems - strange black hole lynxgogo Linux - General 0 01-19-2004 03:43 PM
black hole proxy perl script thegeekster Linux - Newbie 5 12-18-2003 05:18 AM


All times are GMT -5. The time now is 03:47 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration