greylist shows error

[tesme33]

Hi
when i click onto greylist i get the following error.

Code: Select all

Error in the query: 

The following SW is installed.

Code: Select all

MailWatch Version: 1.2.15

Operating System Version: CentOS Linux 7 (Core)

Postfix Version: 3.4.8

MailScanner Version: 5.3.4

ClamAV Version: 0.103.0

SpamAssassin Version: 3.4.4

PHP Version: 7.2.34

MySQL Version: 10.1.41-MariaDB

GeoIP Database Version: GeoLite2 Country database 2020-12-29 15:51:41

And the service is running.

Code: Select all

[root@efa4 ~]# systemctl status -l sqlgrey
● sqlgrey.service - SQLgrey Postfix Grey-listing Policy service
   Loaded: loaded (/usr/lib/systemd/system/sqlgrey.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/sqlgrey.service.d
           └─override.conf
   Active: active (running) since Tue 2021-01-05 16:06:09 CET; 15min ago
 Main PID: 13180 (sqlgrey)
   CGroup: /system.slice/sqlgrey.service
           └─13180 /usr/bin/perl -w /usr/sbin/sqlgrey -d

Jan 05 16:06:09 efa4.xx.de systemd[1]: Starting SQLgrey Postfix Grey-listing Policy service...
Jan 05 16:06:09 efa4.xx.de sqlgrey[13180]: Process Backgrounded
Jan 05 16:06:09 efa4.xx.de sqlgrey[13180]: 2021/01/05-16:06:09 sqlgrey (type Net::Server::Multiplex) starting! pid(13180)
Jan 05 16:06:09 efa4.xx.de systemd[1]: Started SQLgrey Postfix Grey-listing Policy service.
Jan 05 16:06:09 efa4.xx.de sqlgrey[13180]: Binding to TCP port 2501 on host 127.0.0.1 with IPv4
Jan 05 16:06:09 efa4.xx.de sqlgrey[13180]: Setting gid to "988 988"
Jan 05 16:06:09 efa4.xx.de sqlgrey[13180]: Setting uid to "991"

So for me it looks good.

I also already tried a "update_sqlgrey_config". and a service restart.
And /etc/sqlgrey/sqlgrey.conf also doesnt look strange.

Also mysql doesnt report an error:

Code: Select all

mysqlcheck -c -A -uroot -p`grep ROOT /etc/eFa/MySQL-Config | awk -F: '{print $2}'
mailscanner.audit_log                              OK
mailscanner.autorelease                            OK
mailscanner.blacklist                              OK
mailscanner.inq                                    OK
mailscanner.maillog                                OK
mailscanner.mcp_rules                              OK
mailscanner.mtalog                                 OK
mailscanner.mtalog_ids                             OK
mailscanner.outq                                   OK
mailscanner.sa_rules                               OK
mailscanner.saved_filters                          OK
mailscanner.user_filters                           OK
mailscanner.users                                  OK
mailscanner.whitelist                              OK
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
opendmarc.domains                                  OK
opendmarc.ipaddr                                   OK
opendmarc.messages                                 OK
opendmarc.reporters                                OK
opendmarc.requests                                 OK
opendmarc.signatures                               OK
sa_bayes.bayes_expire                              OK
sa_bayes.bayes_global_vars                         OK
sa_bayes.bayes_seen                                OK
sa_bayes.bayes_token                               OK
sa_bayes.bayes_vars                                OK
sa_bayes.txrep                                     OK
sqlgrey.config                                     OK
sqlgrey.connect                                    OK
sqlgrey.domain_awl                                 OK
sqlgrey.from_awl                                   OK
sqlgrey.optin_domain                               OK
sqlgrey.optin_email                                OK
sqlgrey.optout_domain                              OK
sqlgrey.optout_email                               OK

Any ideas ?

And which sql select on which table would i need to check if there is anything happening ?

I can connect to the mysql DB as user sqlgrey with the password mentioned in /etc/sqlgrey/sqlgrey.conf.


Thx

[pdwalker]

What do the log files say?

Your problem is here:
"Error in the query: "

Unfortunately, without the actual query and error message, it's hard to tell.

Is there any interesting error messages in /var/log/mariadb/mariadb.log?

[colinjack]

Just done a yum update to 4.0.4-9 following Shawn's Herculean efforts on resolving the PHP update errors - but still seeing this.
Arrived with yesterday's PHP update.

[yuljk]

Getting exactly the same issue. Running Centos 7.9 and EFA 4.0.4.

Can't see anything obvious in the mariadb logs.

Kind Regards

[shawniverson]

(suggestion didn't work)

[echo]

Hi everybody,
I've been following this topic and problems with the php update.
I just tried the charset/collation fix by shawn - no luck, still got "error in the query".

[colinjack]

Shawn's suggestion didn't fix it unfortunately.
This only appeared after the PHP update so not sure why the character set would change?
Might be something in mailscanner/sgwi/includes/functions.inc.php that has been deprecated?
Can't see anything helpful in the logs.

[shawniverson]

This may be too much to ask, but if anyone is willing to perform a dump on their sqlgrey database or allow a remote session, please let me know. I'm having a hard time reproducing the issue in my environment because I'm running on CentOS 8 and my CentOS 7 builds have an emtpy sqlgrey database. If you are willing, please PM me.

[elfranko]

This may be too much to ask, but if anyone is willing to perform a dump on their sqlgrey database or allow a remote session, please let me know. I'm having a hard time reproducing the issue in my environment because I'm running on CentOS 8 and my CentOS 7 builds have an emtpy sqlgrey database. If you are willing, please PM me.

Sent you a PM

[shawniverson]

Thanks to everyone's help, I have found the problem. The database itself is fine. A php include file was overwritten that pulls in the password for the web interface portion. Preparing a fix to go out.

In /var/www/html/sgwi/includes/config.inc.php:

Code: Select all

$efa_array = preg_grep('/^SQLGREYSQLPWD/', file('/etc/eFa/SQLGrey-Config'));
foreach($efa_array as $num => $line) {
  if ($line) {
    $db_pass = chop(preg_replace('/^SQLGREYSQLPWD:(.*)/','$1',$line));
  }
}

[colinjack]

Excellent!
Will this be a yum update or will we need to edit from CLI?

[shawniverson]

yum update

[colinjack]

Cool.

[jlau]

Hello Shawn,
Just checking in on this issue to see if you have an ETA for the bug fix update to be available through yum?

Thanks for your quick work on this issue!

[jlau]

Just saw the update available, greylist menu is back on!
Thanks!