Cyberbe Notes from Blackcat

Postfix check_recipient_access with MySQL

2016-12-18T23:24:07+03:00

Для предотвращения повторной отправки писем "несуществующим" пользователям добавляем в правило postfix:

smtpd_recipient_restrictions = mysql:/etc/postfix/mysql/mysql-virtual-recipient-access.cf,
permit_mynetworks,
permit_sasl_authenticated,

Содержимое /etc/postfix/mysql/mysql-virtual-recipient-access.cf:

user = <username>
password = <password>
hosts = <host>
dbname = <dbname>
query = select ifnull( (select (case `notificationType` when  'bounced' then 'REJECT' END) as access  FROM `EmailStatuses` WHERE `recipients` = '%s' limit 1),'OK') as access

Запрос к базе формируется по вкусу, у меня статусы отправки пишутся в базу отдельным скриптом

Не забыть перезапустить postfix

Сделано на основе - https://kitt.hodsden.org/blog/2014/06/postfix_check_recipient_access_mysql

Postfix disable Delivery Status notification

2016-12-18T23:16:14+03:00

Для полной блокировки DSN добавьте в main,cf:

smtpd_discard_ehlo_keywords = silent-discard, dsn

Балансировка отправки почты с нескольких IP (Postfix)

2016-12-09T11:17:34+03:00

Install the perl module List::util::WeightedRoundRobin:
cpan install /List::util::WeightedRoundRobin/

Create the following perl script and make it executable:
vi /etc/postfix/random.pl

#!/usr/bin/perl -w
# author: Hari Hendaryanto <hari.h -at- csmcom.com>
use strict;
use warnings;
use Sys::Syslog qw(:DEFAULT setlogsock);
use List::Util::WeightedRoundRobin;
use Storable;

my $hashfile="/tmp/file.hash";
store {}, $hashfile unless -r $hashfile;

#
# our transports lists, we will define this in master.cf as transport services
# Queued using Weighted Round-Robin Scheduling
#
my $list = [
{
name => 'smtp1:',
weight => 1,
},
{
name => 'smtp2:',
weight => 1,
},
{
name => 'smtp3:',
weight => 1,
},
{
name => 'smtp4:',
weight => 1,
},
{
name => 'smtp5:',
weight => 1,
},
{
name => 'smtp6:',
weight => 1,
},
{
name => 'smtp7:',
weight => 1,
},
];

my $WeightedList = List::Util::WeightedRoundRobin->new();
my $weighted_list = $WeightedList->create_weighted_list( $list );

# $maxinqueue max number of queue in smtp list
my $maxinqueue = scalar(@{$weighted_list});

#
# Initalize and open syslog.
#
openlog('postfix/randomizer','pid','mail');
#
# Autoflush standard output.
#
select STDOUT; $|++;

while (<>) {
chomp;
my $count;
my $hash=retrieve($hashfile);

if (!defined $hash->{"index"})
{
$count = 0;
} else {
$count = $hash->{"index"};
}

if ($count >= $maxinqueue)
{
$hash->{"index"} = 0;
$count = 0;
}

$hash->{"index"}++;
store $hash, $hashfile;
my $random_smtp = ${$weighted_list}[$count];
if (/^get\s(.+)$/i) {
print "200 $random_smtp\n";
syslog("info","Using: %s Transport Service", $random_smtp);
next;
}
print "200 smtp:\n";
}

Execute the script to make sure it`s working and no errors are encountered:
/etc/postfix/random.pl

Configure postfix to use the random generated smtp transport.

Edit /etc/postfix/master.cf, by appending following lines:

## Round-robin outgoing smtp
127.0.0.1:23000 inet n n n - 0 spawn
user=nobody argv=/etc/postfix/random.pl
# random smtp
smtp1 unix - - n - - smtp
-o syslog_name=postfix-smtp1
-o smtp_helo_name=FQDN
-o smtp_bind_address=IP

smtp2 unix - - n - - smtp
-o syslog_name=postfix-smtp2
-o smtp_helo_name= FQDN
-o smtp_bind_address=IP

smtp3 unix - - n - - smtp
-o syslog_name=postfix-smtp3
-o smtp_helo_name=FQDN
-o smtp_bind_address=IP

smtp4 unix - - n - - smtp
-o syslog_name=postfix-smtp4
-o smtp_helo_name=FQDN
-o smtp_bind_address=IP

smtp5 unix - - n - - smtp
-o syslog_name=postfix-smtp5
-o smtp_helo_name=FQDN
-o smtp_bind_address=IP

smtp6 unix - - n - - smtp
-o syslog_name=postfix-smtp6
-o smtp_helo_name= FQDN
-o smtp_bind_address=IP

smtp7 unix - - n - - smtp
-o syslog_name=postfix-smtp7
-o smtp_helo_name= FQDN
-o smtp_bind_address=IP

Replace FQDN with desired hostname and IP with the list of the IP addresses you`d like to use.

Append the following lines to your /etc/postfix/main.cf:
transport_maps = tcp:127.0.0.1:23000
127.0.0.1:23000_time_limit = 3600s

Restart/reload postfix and verify everything is working correctly:
# postmap -q "dummy" tcp:127.0.0.1:23000
smtp4:
# postmap -q "dummy" tcp:127.0.0.1:23000
smtp5:
# postmap -q "dummy" tcp:127.0.0.1:23000
smtp6:

In your logs you should see the different smtp transport beeing used when mails are sent:
Sep 2 12:50:05 myserver postfix-smtp2/smtp[2016]: 3CA964C0004: to=<example@domain.org>, relay=domain.org[XXXXXXXXX]:25, delay=2, delays=0.03/0.01/0.18/1.8, dsn=2.0.0, status=sent (250 OK id=1T86oe-0005tU-E9)
Sep 2 12:50:05 myserver postfix-smtp5/smtp[2014]: 4EE244C0012: to=<example@domain.org>, relay=domain.org[XXXXXXXXX]:25, delay=2, delays=0.01/0/0.21/1.8, dsn=2.0.0, status=sent (250 OK id=1T86oe-0005tx-Gn)
Sep 2 12:50:05 myserver postfix-smtp3/smtp[2045]: 69305680035: to=<example@domain.org>, relay=domain.org[XXXXXXXXX]:25, delay=2, delays=0.05/0/0.11/1.9, dsn=2.0.0, status=sent (250 OK id=1T86oe-0005uJ-Ih)
Sep 2 12:50:05 myserver postfix-smtp3/smtp[2034]: 444B94C0006: to=<example@domain.org>, relay=domain.org[XXXXXXXXX]:25, delay=2.2, delays=0.04/0/0.21/1.9, dsn=2.0.0, status=sent (250 OK id=1T86oe-0005tW-G9)
Sep 2 12:50:05 myserver postfix-smtp1/smtp[2039]: 585A6583CFE: to=<example@domain.org>, relay=domain.org[XXXXXXXXX]:25, delay=2.2, delays=0.01/0/0.15/2, dsn=2.0.0, status=sent (250 OK id=1T86oe-0005u0-GO)

How to Create Scheduled Events in MySQL

2016-10-06T18:55:58+03:00

you can start the scheduler from the MySQL command line:

SET GLOBAL event_scheduler = ON;

Creating an Event

We require a scheduled event which:

Copies posts from `blog` to `blog_archive` when the deleted flag is set to 1.
Copies the associated audit entries for those posts from `audit` to `audit_archive`.
Physically deletes archived posts from the `blog` table. Referential integrity has been defined with a foreign key so all associated audit entries for those posts will also be removed.

Assuming you have MySQL rights to create events, the basic syntax is:

CREATE EVENT `event_name`
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
DO BEGIN
-- event body
END;

The schedule can be assigned various settings, e.g.

An event is normally dropped once its schedule has expired (ON COMPLETION NOT PRESERVE). Set ON COMPLETION PRESERVE to prevent that behavior. The MySQL CREATE EVENT Syntax documentation provides further details.

We can now define our event (remembering to set the DELIMITER first). We’ll set it to run every week starting on a Sunday morning:

DELIMITER $$

CREATE
EVENT `archive_blogs`
ON SCHEDULE EVERY 1 WEEK STARTS '2011-07-24 03:00:00'
DO BEGIN

-- copy deleted posts
INSERT INTO blog_archive (id, title, content)
SELECT id, title, content
FROM blog
WHERE deleted = 1;

-- copy associated audit records
INSERT INTO audit_archive (id, blog_id, changetype, changetime)
SELECT audit.id, audit.blog_id, audit.changetype, audit.changetime
FROM audit
JOIN blog ON audit.blog_id = blog.id
WHERE blog.deleted = 1;

-- remove deleted blogs and audit entries
DELETE FROM blog WHERE deleted = 1;

END */$$

DELIMITER ;

https://www.sitepoint.com/how-to-create-mysql-events/