Re: local maps

> From: Jo Rhett <jrhettnetconsonance.com>
> Date: Thu, 3 Jan 2008 17:21:02 -0800
> To: Mark Martinec <Mark.Martinec+amavisijs.si>
> Cc: <amavis-userlists.sourceforge.net>
> Subject: [AMaViS-user] local maps
> 
> (seperated topics because the issue is different)
> 
> The problem is that I'm hosting a bunch of friends, and
I in no way
> really want to be editing/restarting everything any
time they host a
> new domain.  (same set of problems for other hosting
providers too)
> 
> What are my choices to resolve this?   I need some sort
of db lookup
> for what domains are local.

Yes, sql based lookups, see readme's or restart amavisd
every time a new
domain comes on.
If this happens a lot, maybe you want to use one of the
amavisd/sa web based
front ends?  They must support what you are doing

(also, don't you also have to make sure your MTA knows about
the new domains
so it will relay for them?)

-- 
Michael Scheidell, CTO
>|SECNAP Network Security

____________________________________________________________
_____________
This email has been scanned and certified safe by
SpammerTrap(tm). 
For Information please see http://www.spammertrap.com

____________________________________________________________
_____________

------------------------------------------------------------
-------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

Michael Scheidell wrote:
>> From: Jo Rhett <jrhettnetconsonance.com>
>> Date: Thu, 3 Jan 2008 17:21:02 -0800
>> To: Mark Martinec <Mark.Martinec+amavisijs.si>
>> Cc: <amavis-userlists.sourceforge.net>
>> Subject: [AMaViS-user] local maps
>>
>> (seperated topics because the issue is different)
>>
>> The problem is that I'm hosting a bunch of friends,
and I in no way
>> really want to be editing/restarting everything any
time they host a
>> new domain.  (same set of problems for other
hosting providers too)
>>
>> What are my choices to resolve this?   I need some
sort of db lookup
>> for what domains are local.
> 
> Yes, sql based lookups, see readme's or restart amavisd
every time a new
> domain comes on.

I have seen the readme, and discussed this on the list in
the past. 
Nothing came of the discussions in the past, and this topic
is not 
addressed in the readmes.

> If this happens a lot, maybe you want to use one of the
amavisd/sa web based
> front ends?  They must support what you are doing

I've evaluated a lot of them, and most of them require more
work than 
using vi and editing the files yourself.  The only one I
like is the 
Squirrelmail plugin, but that is only for users to edit
preferences.  If 
you know of something better (not listed on the related
projects page) 
please let me know.

> (also, don't you also have to make sure your MTA knows
about the new domains
> so it will relay for them?)

Yes, that's easily done with edits to the virtusertable db
hash.


------------------------------------------------------------
-------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://ad.doubleclick.net/clk;164216
239;13503038;w?http://sf.net/marketplace
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

Jo,

> >> The problem is that I'm hosting a bunch of
friends, and I in no way
> >> really want to be editing/restarting
everything any time they host a
> >> new domain.  (same set of problems for other
hosting providers too)
> >>
> >> What are my choices to resolve this?   I need
some sort of db lookup
> >> for what domains are local.
> >
> > Yes, sql based lookups, see readme's or restart
amavisd every time a new
> > domain comes on.

If you want to avoid a restart, the only options are SQL or
LDAP -based
lookup tables. This needn't be a full-featured SQL server,
it would
suffice to use a SQLite database (embedded, no SQL server).

> > (also, don't you also have to make sure your MTA
knows about the new
> > domains so it will relay for them?)
>
> Yes, that's easily done with edits to the virtusertable
db hash.

Using read_hash() function in amavisd.conf, it can read the
text
source file that you use to construct the virtusertable db
hash.
The format is mostly compatible between Postfix and what is
understood
by read_hash, a notable difference is that 'example.com'
does not
include subdomains in amavisd, and '.example.com' includes
domains
AS WELL AS the top domain itself. And amavisd does not
understand
continuation lines. A restart is still needed after changing
the file.

  Mark

------------------------------------------------------------
-------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://ad.doubleclick.net/clk;164216
239;13503038;w?http://sf.net/marketplace
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

------------------------------------------------------------
-------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://ad.doubleclick.net/clk;164216
239;13503038;w?http://sf.net/marketplace
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

On Jan 11, 2008, at 9:27 AM, Mark Martinec wrote:
>>>> The problem is that I'm hosting a bunch of
friends, and I in no way
>>>> really want to be editing/restarting
everything any time they  
>>>> host a
>>>> new domain.  (same set of problems for
other hosting providers too)
>>>>
>>>> What are my choices to resolve this?   I
need some sort of db  
>>>> lookup
>>>> for what domains are local.
>>>
>>> Yes, sql based lookups, see readme's or restart
amavisd every  
>>> time a new
>>> domain comes on.
>
> If you want to avoid a restart, the only options are
SQL or LDAP - 
> based
> lookup tables. This needn't be a full-featured SQL
server, it would
> suffice to use a SQLite database (embedded, no SQL
server).

I am honestly trying to find documentation on how to do
this, and  
coming up blank.

I have an existing SQL table with all of my mail users, if I
can  
query that it would be perfect.

-- 
Jo Rhett
Net Consonance : consonant endings by net philanthropy, open
source  
and other randomness



------------------------------------------------------------
-------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://ad.doubleclick.net/clk;164216
239;13503038;w?http://sf.net/marketplace
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

On 1/14/08, Jo Rhett <jrhettnetconsonance.com>
wrote:
>
> On Jan 11, 2008, at 9:27 AM, Mark Martinec wrote:
> >>>> The problem is that I'm hosting a
bunch of friends, and I in no way
> >>>> really want to be editing/restarting
everything any time they
> >>>> host a
> >>>> new domain.  (same set of problems for
other hosting providers too)
> >>>>
> >>>> What are my choices to resolve this?  
I need some sort of db
> >>>> lookup
> >>>> for what domains are local.
> >>>
> >>> Yes, sql based lookups, see readme's or
restart amavisd every
> >>> time a new
> >>> domain comes on.
> >
> > If you want to avoid a restart, the only options
are SQL or LDAP -
> > based
> > lookup tables. This needn't be a full-featured SQL
server, it would
> > suffice to use a SQLite database (embedded, no SQL
server).
>
> I am honestly trying to find documentation on how to do
this, and
> coming up blank.
>
> I have an existing SQL table with all of my mail users,
if I can
> query that it would be perfect.
>
> --
> Jo Rhett
> Net Consonance : consonant endings by net philanthropy,
open source
> and other randomness
>

This thread (in which I think you will find a solution):

http://marc.info/?l=amavis-user&m=115672419610264

Led to this note in amavisd.conf-sample:

# Does a database mail address field with no ''
character represent a
# local username or a domain name?  By default it implies a
username in
# SQL and LDAP lookups (but represents a domain in hash and
acl lookups),
# so domain names in SQL and LDAP should be specified as
'domain'.
# Setting these to true will cause 'xxx' to be interpreted
as a domain
# name, just like in hash or acl lookups.
#
# $sql_lookups_no_at_means_domain  = 0;  # default is 0
# $ldap_lookups_no_at_means_domain = 0;  # default is 0

# Here is an example of a SELECT clause that fabricates an
artificial 'users'
# table from actual table 'postfix_domains' containing a
field 'domain_name'.
# The effect is that domains listed in the 'postfix_domains'
table will be
# treated as local by amavisd, and be given settings from a
policy id 99
# if such a policy id exists, or just fall back to static
lookups.
# The user.id (with a value 1) is there only to provide a
user id (same id
# for all listed domains) when global SQL-based
white/blacklisting is used.
#
# $sql_lookups_no_at_means_domain = 1;
# $sql_select_policy =
#   'SELECT *, user.id'.
#   ' FROM (SELECT 1 as id, 99 as policy_id, "Y"
AS local'.
#         ' FROM postfix_domains WHERE domain_name IN (%k))
AS user'.
#   ' LEFT JOIN policy ON policy_id=policy.id';

This assumes you are not currently using SQL in amavisd-new
(if you
were, and you were using the amavisd-new schema, you
probably would
not need to do this). If you are, then changing the default
$sql_select_policy in this manner should break the way
amavisd-new
normally queries SQL data.

Don't forget that you also need to configure the lookup_sql_dsn. For example:

lookup_sql_dsn = ( ['DBI:mysql:database:localhost',
'user', 'password'] );

This is from amavisd.conf-sample:

# Where to find SQL server(s) and database to support SQL
lookups?
# A list of triples: (dsn,user,passw).   (dsn = data source
name)
# More than one entry may be specified for multiple (backup)
SQL servers.
# See 'man DBI', 'man DBD::mysql', 'man DBD::Pg', ... for
details.
# When chroot-ed, accessing SQL server over inet socket may
be more convenient.
#
# lookup_sql_dsn =
#   ( ['DBI:mysql:database=mail;host=127.0.0.1;port=3306',
'user1', 'passwd1'],
#     ['DBI:mysql:database=mail;host=host2', 'username2',
'password2'],
#    
["DBI:SQLite:dbname=$MYHOME/sql/mail_prefs.sqlite"
, '', ''] );

Gary V

------------------------------------------------------------
-------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://ad.doubleclick.net/clk;164216
239;13503038;w?http://sf.net/marketplace
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

On 1/15/08, Jo Rhett wrote:
> Thank you, Gary for the detailed response (quoted
below).  However it
> appears that these queries will break the existing
amavisd tables used
> for kill score, etc.
>
> Is there a way I can use (different) SQL tables for
determining if an
> address is a local user?
>

Right, in that case it would break what you have. If you are
already
using the amavis schema, but only have a limited number of
users or
domains in the database, consider that you could add a new
policy that
contains nothing more than the policy.id, then, then add
any
domains.tld that are not currently in the database
(domains you want
considered local) and assign them to that policy. What this
does is
makes those domains local, but lets all lookups fall through
to static
lookups.

Sorry, I'm not schooled enough in SQL to provide any further
suggestion.

-- 
Gary V

------------------------------------------------------------
-------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

On 1/15/08, Gary V <mr88talentgmail.com> wrote:
> On 1/15/08, Jo Rhett wrote:
> > Thank you, Gary for the detailed response (quoted
below).  However it
> > appears that these queries will break the existing
amavisd tables used
> > for kill score, etc.
> >
> > Is there a way I can use (different) SQL tables
for determining if an
> > address is a local user?
> >
>
> Right, in that case it would break what you have. If
you are already
> using the amavis schema, but only have a limited number
of users or
> domains in the database, consider that you could add a
new policy that
> contains nothing more than the policy.id, then, then
add any
> domains.tld that are not currently in the database
(domains you want
> considered local) and assign them to that policy. What
this does is
> makes those domains local, but lets all lookups fall
through to static
> lookups.
>
> Sorry, I'm not schooled enough in SQL to provide any
further suggestion.
>
> --
> Gary V
>

But I can tell you that it's not hard to dump your domains
into a flat
file that amavis can use:

mysql -uusername -psome_password database -B -N -e
"select
concat('.',domain) from domain"
>/var/amavis/local_domains

Then in amavisd.conf:

local_domains_maps = (
read_hash("$MYHOME/local_domains") );



-- 
Gary V

------------------------------------------------------------
-------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

On Jan 15, 2008, at 3:28 PM, Gary V wrote:
> But I can tell you that it's not hard to dump your
domains into a flat
> file that amavis can use:
>
> mysql -uusername -psome_password database -B -N -e
"select
> concat('.',domain) from domain"
>/var/amavis/local_domains
>
> Then in amavisd.conf:
>
> local_domains_maps = (
read_hash("$MYHOME/local_domains") );

That seems so silly when "select distinct Domain from
Email" will  
give the exact answer.  Flushing every minute or so to text
tables  
and then having to restart amavisd to reread it is...
wasteful

-- 
Jo Rhett
Net Consonance : consonant endings by net philanthropy, open
source  
and other randomness



------------------------------------------------------------
-------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

On 1/15/08, Jo Rhett wrote:
> On Jan 15, 2008, at 3:28 PM, Gary V wrote:
> > But I can tell you that it's not hard to dump your
domains into a flat
> > file that amavis can use:
> >
> > mysql -uusername -psome_password database -B -N -e
"select
> > concat('.',domain) from domain"
>/var/amavis/local_domains
> >
> > Then in amavisd.conf:
> >
> > local_domains_maps = (
read_hash("$MYHOME/local_domains") );
>
> That seems so silly when "select distinct Domain
from Email" will
> give the exact answer.  Flushing every minute or so to
text tables
> and then having to restart amavisd to reread it is...
wasteful
>
> --
> Jo Rhett

Right. Prefixing the domain with a period is likely
unnecessary. It
wildcards all the subdomains of a given domain. Whether this
type of
thing is of any use depends on how often domains new domains
are
added. It would be disruptive if that occurs often. You
would only
need to do it when a new domain is added, which is not
likely to be
every minute or so. One might script this type of thing into
whatever
interface is used to add new domains.

-- 
Gary V

------------------------------------------------------------
-------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/