Mail Disappears into a black hole using postfix, mysql

bluethundr · 01-13-2009, 06:25 PM

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.

billymayday · 01-13-2009, 07:05 PM

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.

bluethundr · 01-13-2009, 10:34 PM

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

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.

billymayday · 01-13-2009, 11:58 PM

3) look in /etc/my.cnf

bluethundr · 01-14-2009, 02:17 AM

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

3) look in /etc/my.cnf

billymayday · 01-14-2009, 04:37 AM

My guess would be a permissions problem with mysql. Is there anything in the mysql logs when postfix tries to connect?

bluethundr · 01-14-2009, 06:30 AM

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

My guess would be a permissions problem with mysql. Is there anything in the mysql logs when postfix tries to connect?

billymayday · 01-14-2009, 02:07 PM

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.

bluethundr · 01-14-2009, 06:01 PM

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

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.

billymayday · 01-14-2009, 09:08 PM

Doesn't look like the right query. Try

Code:

grep virtual /etc/postfix/*.cf

because I'd expect "select_field=virtual" somewhere

bluethundr · 01-14-2009, 11:07 PM

Quote:

Originally Posted by billymayday

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.

billymayday · 01-14-2009, 11:45 PM

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?

bluethundr · 01-15-2009, 08:34 AM

Quote:

Originally Posted by billymayday

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!