Hi!
I was lokking posted message about JDBCVirtualUsersTable.
Below is my steps to create virtuals domains with aliases one to many users.
In my organization is 5 domain, like 80 users and many aliases (over 500!).
All mails is checked by SpamAssassin mailet and ClamAV! Work super!!!
adomain.com
bdomain.com.pl
cdomain.com.pl
aadomain.pl
Every domain have many aliases:
[EMAIL PROTECTED] - is disposed for [EMAIL PROTECTED], [EMAIL PROTECTED]
[EMAIL PROTECTED] ...
...
In specification to jdbcvirtualsusersatable is MySQL. But I was change him to
PostgreSQL.
PostgreSQL have two small problems.
First: field username must be changed from 'user' to 'username'.
You must change SQL query in JDBCVirtualUserTable.java (line 154) to
(recompile sources after!):
query = "select target_address from VirtualUserTable where username like
? and domain like ?"
This query select one user with one domain: robert.skubij domain.com.
JDBCVirtualUserTable is:
| username | domain | target_address |
-------------- ----------- ----------------
| robert.skubij| poczta.fm | robert.skubij | <- last colum is a local user
created by telnet localhost 4555
If mail arrive to [EMAIL PROTECTED] JDBCVirtualUserTable mailet divide mail
address on two pieces: username (robert.skubij) and host (poczta.fm).
Execute query: "select target_address from VirtualUserTable where username
like '%robert.skubij%' and domain like '%poczta.fm%'"
Result is 1 record: robert.skubij. This is a local user. Mail is spolled to
mailbox.
Two: PostgreSQL not have function concatenate(). Should be create in PL/pgSQL
if you wan't change sql query to above.
CREATE FUNCTION concat_text (TEXT, TEXT, TEXT) RETURNS TEXT AS '
BEGIN
RETURN $1 || &2 || $3;
END;
' LANGUAGE 'plpgsql';
OK.
Next step. We create aliases for some users.
Exmaple:
alias.
[EMAIL PROTECTED] - mail should be dispatch to 3 users.
-- [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED]
or
[EMAIL PROTECTED] have 3 aliases: [EMAIL PROTECTED], [EMAIL PROTECTED],
[EMAIL PROTECTED]
How to create alias for him?
Use mailet JDBCAliases with smal patch. Patch allow create more that one
alias per user.
in config.xml place below text:
<mailet match="All" class="JDBCAlias">
<mappings>db://maildb/Aliases</mappings>
<source_column>source_email_address</source_column>
<target_column>target_email_address</target_column>
</mailet>
wher maildb is Your data-source.
change JDBCAlias.java
remark below lines (from 147 to 154)
/*
if (!mappingRS.next()) {
//This address was not found
continue;
}
*/
add while loop like this.
while(mappingRS.next()){
...
...
} <-- end
After recompile sources with ant.
export JAVA_HOME=/path/to/java
in src catalog run build.sh (or build.bat)
WARNING! Only with JDK 1.4 do not use 1.5!
copy james.sar from build/lib directory to james/apps
Place too JDBC.Driver for PostgreSQL database in lib directory.
Create table aliases.
pgsql>CREATE TABLE ALIASES (source_email_address TEXT, target_email_address
TEXT);
insert into aliases values ('[EMAIL PROTECTED]','[EMAIL PROTECTED]');
insert into aliases values ('[EMAIL PROTECTED]','[EMAIL PROTECTED]');
insert into aliases values ('[EMAIL PROTECTED]','[EMAIL PROTECTED]');
A target email address MUST BE real addres with real user created by
manager!!!!
Tareget email addres not work with others alias!
In my future plans is write mailet to send notify by sms with Kannel SMS-C
(NOKIA) gateway.
Kannel is most popular, GPL source product. Wrok with many protocols.
Best regards,
Robert SKUBIJ
----------------------------------------------------------------------
PHP, cgi i MySQL w standardzie >>> http://link.interia.pl/f1878
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
