Page 1 of 1

[Solved] SQLgrey DB connection error in maillog - greylisting is not working

Posted: 03 Jan 2018 16:49
by dreniarb
I'm seeing these errors in maillog:

Code: Select all

Dec 31 03:42:27 efaVM postfix/smtpd[9719]: warning: hostname 70-98-78-40.nca.lanset.com does not resolve to address 70.98.78.40
Dec 31 03:42:27 efaVM postfix/smtpd[9719]: connect from unknown[70.98.78.40]
Dec 31 03:42:28 efaVM sqlgrey: dbaccess: can't connect to DB: Access denied for user 'sqlgrey'@'localhost' (using password: YES)
Dec 31 03:42:28 efaVM sqlgrey: dbaccess: error: couldn't access optout_domain table: Access denied for user 'sqlgrey'@'localhost' (using password: YES)
Dec 31 03:42:28 efaVM sqlgrey: dbaccess: can't connect to DB: Access denied for user 'sqlgrey'@'localhost' (using password: YES)
Dec 31 03:42:28 efaVM sqlgrey: dbaccess: error: couldn't access optin_email table: Access denied for user 'sqlgrey'@'localhost' (using password: YES)
Dec 31 03:42:28 efaVM postfix/smtpd[9719]: AE98C200093: client=unknown[70.98.78.40]
I grabbed the sqlgrey password from /etc/efa-config and was successful in logging into mysql via webmin. So I am assuming that the username and password are configured correctly.

I also disabled and re-enabled greylisting but still see the error. I've updated EFA, webmin, and all the software packages to their latest versions. Even rebooted EFA a few times.

Any suggestions?

Thanks

Re: SQLgrey DB connection error in maillog - greylisting is not working

Posted: 04 Jan 2018 06:46
by pdwalker
Yes, your mysql password for the sqlgrey@localhost account is incorrect.

Fix this, and you’ll solve your problem.

Run these commands from the command line and see if it behaves:

mysql -usqlgrey -hlocalhost -p<password>
mysql -usqlgrey -h<ip address of efa> -p<password>

Re: SQLgrey DB connection error in maillog - greylisting is not working

Posted: 04 Jan 2018 18:36
by dreniarb
Thanks for replying.

Are you sure about the password being wrong? Because when I grab the password that is in the /etc/efa-config file then go into MySQL via Webmin it accepts that password and lets me in:
Image_20180104133157644.png
Image_20180104133157644.png (7.31 KiB) Viewed 10503 times
Image_20180104133314059.png
Image_20180104133314059.png (29.1 KiB) Viewed 10503 times
I did go ahead and tried those commands. The first one with localhost just brought me to a MariaDB prompt.

The second one when using the ip address of efa itself (192.168.13.2) said "host efa.mydomain.com; is not allowed to connect". Which seemed odd since localhost worked.. but i'm not sure that matters.

Re: SQLgrey DB connection error in maillog - greylisting is not working

Posted: 04 Jan 2018 19:22
by pdwalker
It matters.

I’m away from my computer for a few hours so I cannot reply properly.

Let me verify a few settings on my system and get back to you.

Re: SQLgrey DB connection error in maillog - greylisting is not working

Posted: 05 Jan 2018 16:38
by dreniarb
Thanks. I do appreciate your assistance.

Re: SQLgrey DB connection error in maillog - greylisting is not working

Posted: 05 Jan 2018 18:21
by pdwalker
Sorry for the delay.

After checking my own system, the idea I had was a bust, so let's try something else

run these commands and report back to me:

1/ - is the ip address returned 127.0.0.1?

Code: Select all

ping localhost
2/ - success or failure?

Code: Select all

mysql -u sqlgrey -p`cat /etc/EFA-Config|awk -F: '/SQLGREYSQLPWD/ {print($2)}'`

Re: SQLgrey DB connection error in maillog - greylisting is not working

Posted: 05 Jan 2018 18:44
by dreniarb
I think I might have a typo in the mysql command line. All it does is open a blank prompt like it's waiting for more input:
Image_20180105134221301.png
Image_20180105134221301.png (10.5 KiB) Viewed 10480 times
I have to press ctrl-c to get back to the command prompt.

Re: SQLgrey DB connection error in maillog - greylisting is not working

Posted: 05 Jan 2018 19:03
by pdwalker
doh! I left off the trailing backpack

Code: Select all

mysql -u sqlgrey -p`cat /etc/EFA-Config|awk -F: '/SQLGREYSQLPWD/ {print($2)}'`
That's backtick, single quote, single quote, backtick

Re: SQLgrey DB connection error in maillog - greylisting is not working

Posted: 05 Jan 2018 19:17
by dreniarb
Neat trick - I should have caught that missing backtick as well.

Looks like it works:
Image_20180105141627481.png
Image_20180105141627481.png (6.89 KiB) Viewed 10476 times

Re: SQLgrey DB connection error in maillog - greylisting is not working

Posted: 05 Jan 2018 19:41
by pdwalker
huh. so that's not the problem either.

firewall rules? do you have firewall rules blocking connections to the database?

what does this report?

Code: Select all

sqlgrey --version
in /etc/sqlgrey/sqlgrey.conf - does "db_pass = ..." match the password in /etc/EFA-Config?

Re: SQLgrey DB connection error in maillog - greylisting is not working

Posted: 05 Jan 2018 20:58
by dreniarb
The password did not match! But now it does. And now greylisting is working. :)

Thank you!

Re: [Solved] SQLgrey DB connection error in maillog - greylisting is not working

Posted: 05 Jan 2018 21:38
by pdwalker
See? Told you the password was wrong, just not in the place I originally thought it was :lol:

Re: [Solved] SQLgrey DB connection error in maillog - greylisting is not working

Posted: 07 Jan 2018 03:09
by dreniarb
That you did. :)

Thanks again.

Re: [Solved] SQLgrey DB connection error in maillog - greylisting is not working

Posted: 09 Apr 2019 17:55
by nicolatiana
I experienced the same problem and solved according to pdwalker suggestion.

But ... why did it happen ? :think:

Re: [Solved] SQLgrey DB connection error in maillog - greylisting is not working

Posted: 18 Nov 2019 13:43
by pdwalker
no idea.