postfix - mysql debugging question

Post by Nick Ellson
used for this last try
https://help.ubuntu.com/community/PostfixCompleteVirtualMailSystemHowto
for my howto and initial mysql database. (if there is a better one, I am
OK scrapping the whole thing and starting over :-D )

Can you login as posfix into mysql, if so can your read the alias table
as postfix?
When the email arrives, do you see a connection mysql?
If so turn on full logging in mysql, so you can see the query from
postfix, to check if that query works.

cheers

Nick Ellson

2015-01-18 02:41:55 UTC

OK, so logging in mysql, I'll look at that now.

..and I gotta recompile mysql with the "debug" USE flag so log queries will work. On it! Thanks, will update soon!

Nick Ellson

2015-01-18 03:02:52 UTC

OK, so logging in mysql, I'll look at that now.
..and I gotta recompile mysql with the "debug" USE flag so log queries will work. On it! Thanks, will update soon!

Ugh... reemerged the mysql package with the "debug" USE flag, it won't start with the "log = /tmp/mysql.log" directive in the my.cf file.. calls it Ambiguous.. gotta go track that down now.

But to your first question, looks OK yes?

boink mysql # mysql -u postfix -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use postfix
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SELECT * FROM alias;
+----------------------+----------------------+----------------+---------------------+---------------------+--------+
| address | goto | domain | created | modified | active |
+----------------------+----------------------+----------------+---------------------+---------------------+--------+
| ***@nickellson.com | ***@nickellson.com | nickellson.com | 2015-01-16 11:57:29 | 2015-01-16 11:57:29 | 1 |
+----------------------+----------------------+----------------+---------------------+---------------------+--------+
1 row in set (0.00 sec)

Nick Ellson

2015-01-18 03:05:39 UTC

OK, so logging in mysql, I'll look at that now.
..and I gotta recompile mysql with the "debug" USE flag so log queries will work. On it! Thanks, will update soon!

Ugh... reemerged the mysql package with the "debug" USE flag, it won't start with the "log = /tmp/mysql.log" directive in the my.cf file.. calls it Ambiguous.. gotta go track that down now.
But to your first question, looks OK yes?
boink mysql # mysql -u postfix -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22-log Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use postfix
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM alias;
+----------------------+----------------------+----------------+---------------------+---------------------+--------+
| address | goto | domain | created | modified | active |
+----------------------+----------------------+----------------+---------------------+---------------------+--------+
+----------------------+----------------------+----------------+---------------------+---------------------+--------+
1 row in set (0.00 sec)

And I think that is supposed to go to the mailbox table

mysql> SELECT * FROM mailbox;
+----------------------+------------------------------------+-------------+-----------------------+-------+------------+----------------+---------------------+---------------------+--------+
| username | password | name | maildir | quota | local_part | domain | created | modified | active |
+----------------------+------------------------------------+-------------+-----------------------+-------+------------+----------------+---------------------+---------------------+--------+
| ***@nickellson.com | $1$a5d26c66$HNXxJqBHgHnpckwwQ0Hch/ | Nick Ellson | nickellson.com/grimm/ | 0 | grimm | nickellson.com | 2015-01-16 11:57:29 | 2015-01-16 11:57:29 | 1 |
+----------------------+------------------------------------+-------------+-----------------------+-------+------------+----------------+---------------------+---------------------+--------+
1 row in set (0.00 sec)

so I would expect my mail in /home/vmail/nickellson.com/grimm/.maildir/NEW given the base dir of /home/vmail and the maildir variable of .maildir, yes?

Burkhard Ott

2015-01-18 04:13:36 UTC

Post by Nick Ellson
used for this last try
https://help.ubuntu.com/community/

PostfixCompleteVirtualMailSystemHowto

Post by Nick Ellson
for my howto and initial mysql database. (if there is a better
one, I am OK scrapping the whole thing and starting over :-D )

Can you login as posfix into mysql, if so can your read the alias
table as postfix?
When the email arrives, do you see a connection mysql?
If so turn on full logging in mysql, so you can see the query from
postfix, to check if that query works.
cheers

OK, so logging in mysql, I'll look at that now.
..and I gotta recompile mysql with the "debug" USE flag so log
queries will work. On it! Thanks, will update soon!

Ugh... reemerged the mysql package with the "debug" USE flag, it won't
start with the "log = /tmp/mysql.log" directive in the my.cf file..
calls it Ambiguous.. gotta go track that down now.
But to your first question, looks OK yes?
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 Server version: 5.6.22-log Source
distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use postfix Reading table information for completion of table
and column names You can turn off this feature to get a quicker startup
with -A
Database changed
mysql> SELECT * FROM alias;
+----------------------+----------------------+----------------

+---------------------+---------------------+--------+

+---------------------+---------------------+--------+

Post by Nick Ellson
2015-01-16 11:57:29 | 2015-01-16 11:57:29 | 1 |
+----------------------+----------------------+----------------

+---------------------+---------------------+--------+

Post by Nick Ellson
1 row in set (0.00 sec)

And I think that is supposed to go to the mailbox table
mysql> SELECT * FROM mailbox;
+----------------------+------------------------------------

+-------------+-----------------------+-------+------------
+----------------+---------------------+---------------------+--------+

+-------------+-----------------------+-------+------------
+----------------+---------------------+---------------------+--------+

Post by Nick Ellson
Ellson | nickellson.com/grimm/ | 0 | grimm | nickellson.com |
2015-01-16 11:57:29 | 2015-01-16 11:57:29 | 1 |
+----------------------+------------------------------------

+-------------+-----------------------+-------+------------
+----------------+---------------------+---------------------+--------+

Post by Nick Ellson
1 row in set (0.00 sec)
so I would expect my mail in
/home/vmail/nickellson.com/grimm/.maildir/NEW given the base dir of
/home/vmail and the maildir variable of .maildir, yes?

The postfix entries look about right, let's focus on that first. The
temporary lookup error you have seen first, comes from postfix. Postfix
obviously knows it's responsible for the domain nickellson.com, the next
step is to check the user(***@domain), which looks to me is the part
which fails.
I setup many mysql virtual systems (postfix/cyrus), you have to ensure
that postfix finds the information (useraccount) it needs to deliverthe
mail into an mailbox. The 4xx error means it misses anything to find that
mailbox/user, which would be your backend (I think you used courier).
What did you set as 'mailbox_transport' in your main.cf?

Nick Ellson

2015-01-18 06:21:35 UTC

Post by Burkhard Ott
The postfix entries look about right, let's focus on that first. The
temporary lookup error you have seen first, comes from postfix. Postfix
obviously knows it's responsible for the domain nickellson.com, the next
which fails.
I setup many mysql virtual systems (postfix/cyrus), you have to ensure
that postfix finds the information (useraccount) it needs to deliverthe
mail into an mailbox. The 4xx error means it misses anything to find that
mailbox/user, which would be your backend (I think you used courier).
What did you set as 'mailbox_transport' in your main.cf?

Here is my main.cf, I don't even SEE that directive.. just a virtual one.

boink mysql # cat /etc/postfix/main.cf
queue_directory = /var/spool/postfix
command_directory = /usr/sbin
daemon_directory = /usr/libexec/postfix
data_directory = /var/lib/postfix
mail_owner = postfix
myhostname = boink.nickellson.com
mydomain = nickellson.com
inet_interfaces = all
mydestination =
relayhost =
unknown_local_recipient_reject_code = 450
mynetworks = 10.0.0.0/24, 127.0.0.0/8
home_mailbox = .maildir/
local_destination_concurrency_limit = 2
default_destination_concurrency_limit = 20
debug_peer_level = 2
debugger_command =
PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
ddd $daemon_directory/$process_name $process_id & sleep 5
sendmail_path = /usr/sbin/sendmail
newaliases_path = /usr/bin/newaliases
mailq_path = /usr/bin/mailq
setgid_group = postdrop
html_directory = no
manpage_directory = /usr/share/man
sample_directory = /etc/postfix
readme_directory = no
inet_protocols = ipv4
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
relocated_maps = mysql:/etc/postfix/mysql-relocated.cf
local_transport = local
local_recipient_maps = $alias_maps $virtual_mailbox_maps unix:passwd.byname
## (The domains listed by the mydestination should not be listed in
## the virtual_mailbox_domains parameter)
mailbox_size_limit = 0
recipient_delimiter = +

virtual_mailbox_domains = nickellson.com, boink.nickellson.com

#virtual_mailbox_limit =
broken_sasl_auth_clients = yes
smtpd_sasl_auth_enable = yes
smtpd_sasl_security_options = noanonymous
smtpd_sasl_local_domain =
smtpd_sasl_authenticated_header = yes
smtpd_recipient_restrictions = reject_unauth_pipelining, permit_sasl_authenticated, permit_mynetworks, reject_non_fqdn_recipient, reject_rbl_client zen.spamhaus.org, reject_rbl_client bl.spamcop.net
smtpd_sender_restrictions = permit_sasl_authenticated, permit_mynetworks, reject_non_fqdn_sender, reject_unauth_pipelining, permit

virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias_maps.cf
virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-domains_maps.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
virtual_mailbox_limit = 51200000
virtual_minimum_uid = 5000
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000
virtual_mailbox_base = /home/vmail
virtual_transport = virtual
virtual_create_maildirsize = yes
virtual_mailbox_extended = yes
virtual_mailbox_limit_maps = mysql:/etc/postfix/mysql-virtual-mailbox-limit_maps.cf
virtual_mailbox_limit_override = yes
virtual_maildir_limit_message = Sorry, the your maildir has overdrawn your diskspace quota, please free up some of spaces of your mailbox try again.
virtual_overquota_bounce = yes

Nick Ellson

2015-01-18 06:33:02 UTC

Post by Burkhard Ott
I setup many mysql virtual systems (postfix/cyrus), you have to ensure
that postfix finds the information (useraccount) it needs to deliverthe
mail into an mailbox. The 4xx error means it misses anything to find that
mailbox/user, which would be your backend (I think you used courier).

So, I am not sure what you mean by back-end? In the last howto I followed, It was Postfix -> MySql for inbound, Courier-Imap -> MySql for retrieval. Courier has courier auth for the auth wedge. I has postfix set to use SASL2 to Courier Auth, but this one has me going straight to MySQL. If I try to use "AUTH LOGIN" from outside my LAN, it finds my ***@nickellson.com virtual user and password successfully.

The previous howto "http://wiki.gentoo.org/wiki/Complete_Virtual_Mail_Server" would accept mail and deliver but it went to my local Unix account's home directory which was not what I was looking for. I checked, that did not specify a transport either.

Burkhard Ott

2015-01-18 17:13:22 UTC

Post by Burkhard Ott
I setup many mysql virtual systems (postfix/cyrus), you have to ensure
that postfix finds the information (useraccount) it needs to deliverthe
mail into an mailbox. The 4xx error means it misses anything to find
that mailbox/user, which would be your backend (I think you used
courier).

So, I am not sure what you mean by back-end? In the last howto I
followed, It was Postfix -> MySql for inbound, Courier-Imap -> MySql for
retrieval. Courier has courier auth for the auth wedge. I has postfix
set to use SASL2 to Courier Auth, but this one has me going straight to
MySQL. If I try to use "AUTH LOGIN" from outside my LAN, it finds my
The previous howto
"http://wiki.gentoo.org/wiki/Complete_Virtual_Mail_Server" would accept
mail and deliver but it went to my local Unix account's home directory
which was not what I was looking for. I checked, that did not specify a
transport either.

By backend I mean you courier.
mydestination = should be localhost, in case you send local email like
from cronjobs to ***@localhost which would be then locally stored, or
you have it in your domain maps.
Other than that your main.cf looks good so far.
I think postfix finds the account but can't store the email in your
databases (which is the backend in your case).
So something goes wrong in that section when postfix tries to save the
email in the DB.
cheers

Nick Ellson

2015-01-18 17:36:19 UTC

Post by Burkhard Ott
I setup many mysql virtual systems (postfix/cyrus), you have to ensure
that postfix finds the information (useraccount) it needs to deliverthe
mail into an mailbox. The 4xx error means it misses anything to find
that mailbox/user, which would be your backend (I think you used
courier).

So, I am not sure what you mean by back-end? In the last howto I
followed, It was Postfix -> MySql for inbound, Courier-Imap -> MySql for
retrieval. Courier has courier auth for the auth wedge. I has postfix
set to use SASL2 to Courier Auth, but this one has me going straight to
MySQL. If I try to use "AUTH LOGIN" from outside my LAN, it finds my
The previous howto
"http://wiki.gentoo.org/wiki/Complete_Virtual_Mail_Server" would accept
mail and deliver but it went to my local Unix account's home directory
which was not what I was looking for. I checked, that did not specify a
transport either.

By backend I mean you courier.
mydestination = should be localhost, in case you send local email like
you have it in your domain maps.
Other than that your main.cf looks good so far.
I think postfix finds the account but can't store the email in your
databases (which is the backend in your case).
So something goes wrong in that section when postfix tries to save the
email in the DB.
cheers

Still waiting on an answer to my MySQL logging issue, so kinda stuck debugging MySql. But is the mail itself stored in the database? I didn't see any tables that would indicate actual mail storage, just tables that link email addresses to the /home/vmail/ file structure. Which having MySql logs would be useful to see if postfix even tried looking up that information.

I though maybe using postmap to try to go through what postfix might do manually, but I don't quite get the same results that the postmap howto's suggest (a return of 1 for success) If I test ***@nickellson.com against the alias-maps config, it returns "***@nickellson.com", but if I misspell it on purpose, it returns nothing. I kind of like a nice solid error, ya know? ;-)

What I might do in the mean time (waiting on MySql to log right) is save off my main.cf, find another postfix-courier-mysql virtual mail howto, and add a second database using their schema and see if it behaves differently. Perhaps comparing different techniques might help.

Thanks again for the confirmations! :-D

Burkhard Ott

2015-01-18 18:46:57 UTC

Post by Nick Ellson
Still waiting on an answer to my MySQL logging issue, so kinda stuck
debugging MySql. But is the mail itself stored in the database? I didn't
see any tables that would indicate actual mail storage, just tables that
link email addresses to the /home/vmail/ file structure. Which having
MySql logs would be useful to see if postfix even tried looking up that
information.

SET global log_output = 'FILE';
SET global general_log_file='/path/to/your/logfile';
SET global general_log = 1;

Post by Nick Ellson
What I might do in the mean time (waiting on MySql to log right) is save
off my main.cf, find another postfix-courier-mysql virtual mail howto,
and add a second database using their schema and see if it behaves
differently. Perhaps comparing different techniques might help.

Techniques are pretty much the same, either you store the email within
the database or on the local filesystem, which can be a another host.
Lookup tables are only used by postfix to find the lcation where is
either has to send it (e.g. another MTA) or where to store it.
In your gentoo howto it stores it locally, postfix just looks up the
location.

cheers

Nick Ellson

2015-01-18 18:47:17 UTC

Post by Burkhard Ott
I setup many mysql virtual systems (postfix/cyrus), you have to ensure
that postfix finds the information (useraccount) it needs to deliverthe
mail into an mailbox. The 4xx error means it misses anything to find
that mailbox/user, which would be your backend (I think you used
courier).

So, I am not sure what you mean by back-end? In the last howto I
followed, It was Postfix -> MySql for inbound, Courier-Imap -> MySql for
retrieval. Courier has courier auth for the auth wedge. I has postfix
set to use SASL2 to Courier Auth, but this one has me going straight to
MySQL. If I try to use "AUTH LOGIN" from outside my LAN, it finds my
The previous howto
"http://wiki.gentoo.org/wiki/Complete_Virtual_Mail_Server" would accept
mail and deliver but it went to my local Unix account's home directory
which was not what I was looking for. I checked, that did not specify a
transport either.

Oh I feel dumb.....

my main.cf file did not get all the underscores changed to hyphens, so actually it was never using my vitual alieas maps at all.. corrected that and restarted postfix, mail delivered!! only it went to /home/vmail/nickellson.com/grimm/new instead of /home/vmail/nickellson.com/grimm/.maildir/new but as this is only for mail, I don't mind, just gotta teahc courier imap to pick up from there.

Thought I'd follow up. And then on to SSL/TLS ;-D

Burkhard Ott

2015-01-19 01:02:58 UTC

Post by Nick Ellson
Thanks again for the confirmations! :-D

Oh I feel dumb.....
my main.cf file did not get all the underscores changed to hyphens, so
actually it was never using my vitual alieas maps at all.. corrected
that and restarted postfix, mail delivered!! only it went to
/home/vmail/nickellson.com/grimm/new instead of
/home/vmail/nickellson.com/grimm/.maildir/new but as this is only for
mail, I don't mind, just gotta teahc courier imap to pick up from there.
Thought I'd follow up. And then on to SSL/TLS ;-D

Nice catch, I was more assuming postfix can't reach the local mysql file
socket, since postfix runs in a chroot :).
The tls stuff is then pretty straight forward, in times of global
surveillance I usually disable many algorythms and use GCM and DH and
kick RC4 etc. But that's another topic, however you may consider it as
well.

Nick Ellson

2015-01-24 21:01:07 UTC