MySql check_sender_access
Jeff Slone
"White-listed Domain", and "OK", as below.
testdomain.com would be a domain I would offer filtering for.
mysql> select * from domain ;
+--------------+-----------------+------+------------------+
| domain | wl_domain | wl | amavisd_domain |
+--------------+-----------------+------+------------------+
| testdomain.com | wltest.com | ok | @wltest.com |
My goal is to have check_sender_access reference the table in the
following
way.
mysql> SELECT wl FROM domain WHERE wl_domain="wltest.com" AND
domain="testdomain.com" ;
+------+
| wl |
+------+
| OK |
+------+
1 row in set (0.00 sec)
This would allow me to have one table that could hold all whitelist for
all
of my domain's we offer filtering for. The part I don't understand how
to
accomplish is the (AND domain="testdomain.com") part of the mysql
statement.
I can accomplish the rest by the following.
main.cf
check_client_access = mysql:/etc/postfix/mysql_check_client_access.cf
mysql_check_client_access.cf
user = username
password = password
dbname = dbname
table = domain
select_field = wl
where_field = wl_domain
However this doesn't make it domain specific, which is why I need the
(AND
domain="testdomain.com"). Is there a way to pass the domain from RCPT TO
through to the mysql lookup? If so I could add the following to
mysql_check_client_access.cf
additional_conditions = AND domain = 'domain variable from RCPT TO'
A workaround would be to create seperate tables for domains I wish to
provide a private whitelist for and create seperate mysql access files
where
the AND domain = statement would have the domain hard coded versus being
able to pass a variable, but I'd like to be able to perform the query
"mysql> SELECT wl FROM domain WHERE wl_domain="wltest.com" AND
domain="testdomain.com" ; " for check_client_access.cf
Thanks
JEFF
/dev/rob0
> This would allow me to have one table that could hold all whitelist
> for all of my domain's we offer filtering for. The part I don't
> understand how to accomplish is the (AND domain="testdomain.com")
> part of the mysql statement.
Another part you don't seem to understand is the effect of using
whitelists based on sender domain. Did you know that anyone can pretend
to be a user in one of your whitelisted domains? No matter what kludge
is used, SMTP cannot prevent such forgery in mail transport.
> I can accomplish the rest by the following.
>
> main.cf
> check_client_access = mysql:/etc/postfix/mysql_check_client_access.cf
Nor this. check_client_access has to be specified under one of the
smtpd_*_restrictions stages. See
http://www.postfix.org/SMTPD_ACCESS_README.html for an overview.
I cannot comment on the mysql_table(5) issues except to refer you to the
manual: http://www.postfix.org/mysql_table.5.html
--
mail to this address is discarded unless "/dev/rob0"
or "not-spam" is in Subject: header
mouss
You don't say what you are trying to accomplish. you just say "pass the
domain". which domain?
if your intenet is to whitelist senders or clients but have the
whitelist depend on the recipient domain, then say it in english,
instead of sql:)
if so, well, you can't do it in a general way. all check_*_access are
stateless and self-contained. they only pass their own argument. so you
can't have a check_recipient_access that passes the sender or client.
you can use restriction classes if you only have few domains, but even
then, this is hardly maintainable...
what you really need is a policy server that implements such controls.
To repeat /dev/rob0, whitelist senders with caution (and avoid as you can).
Jeff Slone
-----Original Message-----
From: owner-pos...@postfix.org
[mailto:owner-pos...@postfix.org] On Behalf Of /dev/rob0
Sent: Friday, July 29, 2005 12:26 AM
To: Postfix users
Subject: Re: MySql check_sender_access
Jeff Slone wrote:
> This would allow me to have one table that could hold all whitelist
> for all of my domain's we offer filtering for. The part I don't
> understand how to accomplish is the (AND domain="testdomain.com")
> part of the mysql statement.
Another part you don't seem to understand is the effect of using
whitelists based on sender domain. Did you know that anyone can pretend
to be a user in one of your whitelisted domains? No matter what kludge
is used, SMTP cannot prevent such forgery in mail transport.
> I can accomplish the rest by the following.
>
> main.cf
> check_client_access = mysql:/etc/postfix/mysql_check_client_access.cf
Nor this. check_client_access has to be specified under one of the
smtpd_*_restrictions stages. See
http://www.postfix.org/SMTPD_ACCESS_README.html for an overview.
I cannot comment on the mysql_table(5) issues except to refer you to the
manual: http://www.postfix.org/mysql_table.5.html
--
mail to this address is discarded unless "/dev/rob0"
or "not-spam" is in Subject: header
Thanks for the information. I'm aware of the issues with white-listing
domains for senders, so white-listing by IP will be a better option,
what are you opinions on this? My goal in the end is to have a web
front end that will allow customers to add entries to their personal
white-list without affecting the white-list for other customers on the
same server. So I'm thinking I will need to create different classes
under smtpd_*_restrictions for each hosted client that will need a
personal white-list and have that class point to its own
mysql:/etc/postfix/class_for_domain.cf, this will enable me to use
additional_conditions = AND domain = 'doman-class-is-for'
/dev/rob0
would be preferable overall if you used a MUA which understood usual
quoting and threading standards, but when you leave in my signature
delimiter (regexp "^-- $") any standard-compliant MUA will see all
following text as being part of YOUR signature, and it's more difficult
to quote your text. Thank you.
Jeff Slone wrote:
> Thanks for the information. I'm aware of the issues with white-listing
> domains for senders, so white-listing by IP will be a better option,
> what are you opinions on this? My goal in the end is to have a web
> front end that will allow customers to add entries to their personal
> white-list without affecting the white-list for other customers on the
My opinion and experience is that end users don't know enough about SMTP
to safely manage their own whitelists. Of course it is their mail and
bandwidth and spam if they choose to do it anyway.
I've mostly managed to sidestep whitelisting issues. At one time I was
pressured at one site to provide a user whitelist (check_sender_access)
interface. I delayed on it and ultimately refused to do it. They quit
asking, since they've not had issues with missing legitimate mail. (That
was what I asked in the first place: "Are you missing mail?" No. "Then
why do you think you need this?")
A few times I have lost legitimate mail from sites with horribly
misconfigured DNS, or similar problems. I've read the logs myself,
whois'ed the sending IP address, and added it or the netblock to the
whitelist, check_client_access cidr:whitelist_cidr.
This is rare! Rare enough that the customers don't see it as a serious
problem. And they know they can tell anyone to use any major free
Webmail provider if they need to get mail through in a pinch.
What you are wanting to do is quite possible. The hard part is in
explaining how to use it. I recommend keeping control of it yourself;
don't turn it over to end users. But that might not be politically feasible.
> same server. So I'm thinking I will need to create different classes
> under smtpd_*_restrictions for each hosted client that will need a
> personal white-list and have that class point to its own
This part is a good idea. I use restriction classes extensively.
Basically I break generic restrictions down into types, grouping where
appropriate and using "friendly" names which explain what they do. At
multi-domain sites I have umbrella classes for each domain which lists a
custom set of the smaller restriction classes designed to fit each
domain owner's goals (and spam tolerance.)
> mysql:/etc/postfix/class_for_domain.cf, this will enable me to use
>
> additional_conditions = AND domain = 'doman-class-is-for'
And still, I don't know much about mysql_table(5) use.
mouss
>
> My opinion and experience is that end users don't know enough about SMTP
> to safely manage their own whitelists. Of course it is their mail and
> bandwidth and spam if they choose to do it anyway.
>
that's true, but if the "user" is a domain admin (case of hosting), then
it may be good to delegate part of the work to them:)
> I've mostly managed to sidestep whitelisting issues. At one time I was
> pressured at one site to provide a user whitelist (check_sender_access)
> interface. I delayed on it and ultimately refused to do it. They quit
> asking, since they've not had issues with missing legitimate mail. (That
> was what I asked in the first place: "Are you missing mail?" No. "Then
> why do you think you need this?")
in general, if you say "I am going to setup a mail gw/server that blocks
spam", they will say "not until you prove we can't lose mail".
but if you just say "I am going to setup a mail gw/server" (for good
routing, perf, etc), they say nothing, even if you implement anti-spam
measures...
>
> This part is a good idea. I use restriction classes extensively.
> Basically I break generic restrictions down into types, grouping where
> appropriate and using "friendly" names which explain what they do. At
> multi-domain sites I have umbrella classes for each domain which lists a
> custom set of the smaller restriction classes designed to fit each
> domain owner's goals (and spam tolerance.)
>
>> mysql:/etc/postfix/class_for_domain.cf, this will enable me to use
>>
>> additional_conditions = AND domain = 'doman-class-is-for'
>
>
> And still, I don't know much about mysql_table(5) use.
smtpd_recipient_restrictions =
...
check_recipient_access mysql:$path/recipient_policy
...
policy_1 =
...
check_client_access mysql:$path/client_wl_1
...
policy_2 =
...
check_client_access mysql:$path/client_wl_2
...
policy_3 =
...
check_client_access mysql:$path/client_wl_3
....
=== recipient_policy
query = select policy from domain_policies where '%s' = domain
=== policy_N
query = select 'OK' from rcptdomain where '%s' = client and domain =
'type_N'
this assumes 2 tables. one that defines domain "policy classes". for
each domain (column domain in the example), you assign a policy. here it
is called "policy_N". this must be a restriction class (so you can use
other names, such as "spamlover", "spamhater", "lazy", "nicepeople", ...).
the second table contains the whitelisted clients, in pairs (domain,
client), where domain is the recipient domain and client is the client
to whitelist if mail is sent to the said recipient domain. so the table
has two columns: domain and client.
use your imagination to rename the columns, to build different tables,
... etc.
what you can't do is get rid of the N restriction classes, and of the N
map files (mysql:$path/client_wl_k).
an alternative is to whitelist the "union" (that is, anyone can add a
client to the postfix whitelist), and then use amavisd policy banks on a
per-domain basis. the only caveat is that you can't reject/bounce since
you already accepted the message.
one thing that I didn't try is to use an amavisd in before-the-queue
mode to do just that (no spam/virus filtering). in other words, use
amavisd as a policy "controller" (I'm avoiding "server" so that it is
not confused with policy service delegation).