Anders,
> >> However, I do have the MySQL database, and postfix reads its domain data
> >> from it. It would be great to have amavis read from the DB as well, but
> >> I can't find any references on how to set up sql access to read in from
> >> sql to @local_domains_maps.
...
> Postfix reads the database via pam_mysql, which probably isn't optimal,
> but it works fine, and fast.
>...
> Anyway, the database table that carries the domains has this format;
>
> CREATE TABLE `domain` (
> `domain_name` varchar(255) NOT NULL default '',
> `prefix` varchar(50) NOT NULL default '',
> `maxaccounts` int(11) NOT NULL default '20',
> `quota` int(10) NOT NULL default '20000',
> `transport` varchar(255) NOT NULL default 'cyrus',
> `freenames` enum('YES','NO') NOT NULL default 'NO',
> `freeaddress` enum('YES','NO') NOT NULL default 'NO',
> PRIMARY KEY (`domain_name`),
> UNIQUE KEY `prefix` (`prefix`)
> ) TYPE=MyISAM;
>
> Where a post whould carry this info;
>
> INSERT INTO `domain` VALUES ('example.net', 'ex', 20, 10240, 'cyrus',
> 'NO', 'NO');
>
> So, the domains are cleanly presented in the DB, so I think it would be
> possible to use it, if I only knew how...
The basic idea is to extend the set of resulting matches from table
'users' by a set of fabricated entries from another table like your
table 'domain'. If table 'users' is not even used, then 'extending it'
boils down to just preparing a fabricated table from the actual
table 'domain', no need for a union.
If the only purpose for SQL lookup is to find out whether a user is local
or not (but not to select other settings from SQL, leaving them at static
lookups), one could set $sql_select_policy to a:
SELECT "Y" AS local, 1 AS id
FROM domain WHERE CONCAT("@",domain_name) IN (%k)
which will return local="Y",id=1 if recipient domain is listed, otherwise
no match; missing policy fields are all interpreted as NULL, falling back
to static lookups. Field 'id' is only needed if one wants to use SQL-based
global white/blacklisting, otherwise it can be omitted.
Just making a record appear to exist, relying of default logic
for the value of a field 'local', achieves the same (assuming
id is not needed):
SELECT 1 FROM domain WHERE CONCAT("@",domain_name) IN (%k);
is similar to previous, but no field 'local' is present - it defaults
to 'Y' by the mere presence of a record, unless email is '@.',
which in this case it is not (field not even exist);
see README.lookups: "Special handling of optional SQL field 'users.local'"
If obtaining other settings from SQL is desired, one can assing a hard-coded
(or fetched) policy.id to be associated with records from table 'domain',
e.g. policy 99, and let settings for such users be taken from this policy
(and not from static lookups):
SELECT * FROM policy, domain
WHERE policy.id=99 AND CONCAT('@',domain_name) IN (%k);
If some users are in a classical amavis table 'users', and their associated
policies in 'policy', but want to extend it to include fabricated entries
made up from domain names in table 'domain', giving these some fixed
policy_id like 99, a UNION of two selects is needed - either making an union
of the final policy entries (which is a bit clumsy, as both arguments to UNION
need to have the same number of elements, and table 'policy' has many fields)
or alternatively, probably better, to make a union of real and fabricated
'users' tables as a subselect, and applying the result to fetching a 'policy'
record.
SELECT *, extuser.id
FROM ((SELECT id,policy_id,priority,email FROM users) UNION
(SELECT 1,99,1,CONCAT("@",domain_name) FROM domain)) AS extuser
LEFT JOIN policy ON policy_id=policy.id
WHERE email IN (%k) ORDER BY priority DESC
So in short, one of the following settings would do the job
(and I'm sure there are other ways to write it in SQL):
$sql_select_policy =
'SELECT 1 FROM domain WHERE CONCAT("@",domain_name) IN (%k)';
or, if policy settings need to be taken from SQL:
$sql_select_policy =
'SELECT *, extuser.id'.
' FROM ((SELECT id,policy_id,priority,email FROM users) UNION'.
' (SELECT 1,99,1,CONCAT("@",domain_name) FROM domain)) AS extuser'.
' LEFT JOIN policy ON policy_id=policy.id'.
' WHERE email IN (%k) ORDER BY priority DESC';
and, same thing, if existing table 'users' is empty/not in use,
and only a fabricated table is provided:
$sql_select_policy =
'SELECT *, user.id'.
' FROM (SELECT 1 as id, 99 as policy_id, 1 as priority,'.
' CONCAT("@",domain_name) as email FROM domain) AS user'.
' LEFT JOIN policy ON policy_id=policy.id'.
' WHERE email IN (%k) ORDER BY priority DESC';
or rewritten, probably more optimally, moving the WHERE and ORDER
to the inner select:
$sql_select_policy =
'SELECT *, user.id'.
' FROM (SELECT 1 as id, 99 as policy_id, 1 as priority'.
' FROM domain WHERE CONCAT("@",domain_name) IN (%k)'.
' ORDER BY priority DESC) AS user'.
' LEFT JOIN policy ON policy_id=policy.id';
Mark
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
