JdbcVirtualUserTable

[ro...@poczta.fm]

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:

 info@adomain.com - is disposed for user.name1@adomain.com, username@adomain.com 
 service@adomain.com ...
 ...



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 robert.skubij@poczta.fm 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.
    info@domain.com - mail should be dispatch to 3 users.
          -- user1@domain.com, user2@domain.com, user3@domain.com
 or 
    user.name@domain.com have 3 aliases: uname@domain.com, name@domain.com, nick@domain.com

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 ('info@domain.com','user.name@domain.com');
    insert into aliases values ('uname@domain.com','user.name@domain.com');
    insert into aliases values ('name@domain.com','user.name@domain.com');

    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: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org