qmail & greylisting spam control

[funat120mph]

Originally posted by rvdmeer
I created a database with username/password thru plesk. Then it suddenltly started working...

Hi rvdmeer,

After installing a complier I was able to install qmail successfully (I think.) I have the /tmp/greylist_dbg.txt file and it has the same entries as yours did.

Originally posted by rvdmeer
protocol = notneeded4qmail ronald@q-works.net
--------
protocol = notneeded4qmail
--------
protocol = notneeded4qmail
--------
protocol = notneeded4qmail ronald@ab.com

How did you create your database through plesk. I am running a VPS and it seems that I can only administer my databases for each site individually.

Thanks for everyone's help.

Adam

 

[rvdmeer]

Accessing databases is not linked to an domain outside plesk. So what i did was creating an DB thru the plesk configuration for a primary domain.

 

[funat120mph]

Originally posted by rvdmeer
Accessing databases is not linked to an domain outside plesk. So what i did was creating an DB thru the plesk configuration for a primary domain.

Thanks, did you delete the "qmail" db you initially created through the command prompt?

 

[rvdmeer]

yes i did

 

[funat120mph]

Thank you everyone for your replies. It seems that I have everything up and running.

Part of my problem was that my password in PHPMyAdmin was too long and I didn't realize I was typing more that the field would allow. Then in the config file I typed the entire password. Obviously it was too long then.

Thanks again. I'll know more in the morning if the SPAM is cut down.

Adam

 

[erhnam]

I also installed this piece of software this morning. Really looks great!

 

[pretender]

I installed in Debian both packages libmysqlclient10-dev and libssl-dev, changed the Makefile and recompiled qmail. But still nothing
I created the database via phpmyadmin but also tried it through plesk, still nothing.
Is there a possibility to activate a debug mode or similar anything that extend logging etc. ?
If this is still a mysql access problem how can I check if the user is allowed to write in the database?

Thanks!

 

[bmeshier]

Originally posted by pretender
I installed in Debian both packages libmysqlclient10-dev and libssl-dev, changed the Makefile and recompiled qmail. But still nothing

Can you give some detail on what the problem is? Are you having problems with the compile?

 

[pretender]

First I had some problems with compiling but it was only because I had not all the necessary libraries in the "include" dir, so I installed the sources of mysql and openssl (I think that was the reason why masterkain told me to install the two deb packages). After that no problems with compiling just the /tmp/greylist_dbg.txt file with:

protocol = notneeded4qmail email@email.com
---------
protocol = notneeded4qmail email2@email.com

I created the database with phpmyadmin and also once through plesk and gave the user all privileges (at least phpmyadmin shows me that.)
I changed the user in the local_scan.c to greylist and also tried greylisting because I have no clue at all why it is not working.

Thanks!

 

[bmeshier]

[/tmp/greylist_dbg.txt file with:

protocol = notneeded4qmail email@email.com
---------
protocol = notneeded4qmail email2@email.com

If thats all your seeing in that file, the problem is with your mysql permissions. Check to make sure the user has select/insert privs on the table.

 

[pretender]

I think so too. But when I look into phpmyadmin I see that the user:
greylist has the following rights on localhost/DB:qmail

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, FILE, INDEX, ALTER, CREATE TEMPORARY TABLES
What else should I do? That´s more than necessary I think.

Regards

 

[rvdmeer]

My advise; create the db thru the plesk configuration.
I'm almost 100% sure that this has something to do with the permissions.

 

[bmeshier]

What else should I do? That´s more than necessary I think.

In MySQL did you 'flush privileges' after adding the user?

 

[pretender]

Yes I used the flush privileges command. The hint with plesk did also not work out. Now I created everything from scratch again, the database (through plesk) the privileges and the tables. Guess what happened nothing!

Addition:

I found in the phpmyadmin process list:
ID User Host DB Command Last Status
20482 greylisting localhost mysql Query 0 ---

What does this mean now? Because the command only lasts 0 seconds it seems like it is not really being processed. And it also looks like it is accessing the mysql DB and not the QMAIL DB! Why that?

I´m going to recompile also the qmail binary this evening, maybe this helps, if you guys have more ideas please feel free

Maybe it has also something to do with the CHARACTER SET and COLLATE settings. Because my database uses utf8 but the table is created with latin1_swedish_ci although I think they should be compatible.

 

[bmeshier]

I´m going to recompile also the qmail binary this evening, maybe this helps, if you guys have more ideas please feel free

Just a thought... double check that the username/password/database match what you have in local_scan.c

 

[funat120mph]

Originally posted by pretender
Now I created everything from scratch again, the database (through plesk) the privileges and the tables. Guess what happened nothing!

Make sure you keep the quotes around the parameters in the local_scan.c file. I had a few problems too and each time I would start back over I would rename the qmail-1.03 folder and start fresh with install, change of the parameters and compile.

Do you have any errors when you compile?

 

[pretender]

I installed everything new! And it is the same problem as before. I created the database through plesk and flushed the privileges. Everything exactly like you described. I’m really going mad right now.
But I discovered something! bmeshier posted once a sample of his greylist_dbg.txt and there where three sql commands:
1. SELECT id, block_expires > NOW(), block_expires < NOW() FROM relaytofrom WHERE record_expires > NOW() AND mail_from IS NULL AND rcpt_to IS NULL AND (relay_ip = '69.64.46.29' OR relay_ip = '69.64.46' OR relay_ip = '69.64' OR relay_ip = '69') ORDER BY length(relay_ip)

2. SELECT id, block_expires > NOW(), block_expires < NOW() FROM relaytofrom WHERE record_expires > NOW() AND mail_from IS NULL AND relay_ip IS NULL AND rcpt_to = 'meshier.com'

3. SELECT id, NOW() > block_expires FROM relaytofrom WHERE record_expires > NOW() AND mail_from = 'anonymous@plesk.com' AND rcpt_to = 'brent@meshier.com' AND relay_ip like '69.64.46%' order by block_expires desc

and 4.
update relaytofrom set record_expires = NOW() + INTERVAL 36 DAY, passed_count = passed_count + 1 where id ='259503'

I get always this as result:
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0003 sec)

Is this right? Seems mysql is doing nothing at all, or is it a complete different command that qmail uses instead of the one in the .txt file?

My local_scan.c file looks like this:
#define MYSQLHOST "localhost"
#define MYSQLUSER "greylisting"
#define MYSQLPASS "greylistqmail"
#define MYSQLDB "qmail"
#define BLOCK_EXPIRE 5 /* minutes until email is accepted */
#define RECORD_EXPIRE 600 /* minutes until record expires */
#define RECORD_EXPIRE_GOOD 36 /* days until record expires after accepting email */


Any suggestions?

 

[rvdmeer]

I'm just wondering... some users are using greylisting at this moment. My question to those is; are you experiencing problems with gmail.com email traffic. I have currently 2 customers who are saying that mail is not delivered to them. I tell them that the first mailing will take a lill longer.. but they did wait several times.. isnt gmail queuing messages?

 

[bmeshier]

I wanted to mention that you should run a nightly cron job to clean out old entries. Its not unusual for the table to grow over 1GB in size, especially if your the unlucky recipient of a joe-job or dictionary attack.

Create a quick perl script like this and place in /etc/cron.daily

#!/usr/bin/perl
use strict;
use warnings;

use constant DBD => 'DBI:mysql:qmail:localhost:3306';
use constant DBUSER => 'milter';
use constant DBPASS => 'greylist';

use DBI;

system ("cat /dev/null > /tmp/greylist_dbg.txt");

my $dbh = DBI->connect(DBD,DBUSER,DBPASS) or die "can't connect to db ", $DBI::errstr, ":$!";

$dbh->do("DELETE FROM relaytofrom WHERE record_expires < NOW() - INTERVAL 1 HOUR AND origin_type = 'AUTO'");
$dbh->do("OPTIMIZE TABLE relaytofrom");

$dbh->disconnect;

exit;

 

[SecondPhase]

compatible with qscanq?

Hello, this looks like another great tool. Is this compatible with qscanq/clamAV hostbird setup.. Are you running that as well rvdmeer?

Thanks, would like to give this a try..