MySQL query to add/delete/edit mail account?

Discussion in 'General' started by klonos, Aug 18, 2009.

  1. klonos

    klonos New Member


    Does any one know what a sql query to add a new user (mail account for a domain) in the ISPConfig database would be like?

    While at it, also need to know what the queries to delete or edit (change password) an account would be.

    Thanx in advance.

    PS: in case people wonder why I need these for, it is because I am trying to setup the 'password' plugin for the RoundCube webmail I've got working. Here is part of the plugin's config file:

    // The SQL query used to change the password.
    // The query can contain the following macros that will be expanded as follows:
    //      %p is replaced with the plaintext new password
    //      %c is replaced with the crypt version of the new password,
    //           MD5 if available, otherwise DES.
    //      %u is replaced with the username (from the session info)
    //      %o is replaced with the password before the change
    //      %h is replaced with the imap host (from the session info)
    // Escaping of macros is handled by this module.
    // Default: "SELECT update_passwd(%c, %u)"
    $rcmail_config['password_query'] = 'SELECT update_passwd(%c, %u)';
  2. till

    till Super Moderator Staff Member ISPConfig Developer

  3. bajodel

    bajodel New Member

    ..some little hints :) ..please

    Hi till, i'm just trying to 'understand' the gears moving behind ISPConfig3 least some of them :) ..because i'd love to reach two aims: to learn more and to find a solution to a 'apha' project ..see further (if you can) ..or jump to 'Questions Section' (if you are in a hurry) :)

    ==='Alpha' Project Section===

    Implement a server service for a school, the requirements are:

    1) php web app to:
    - see votes (students)
    - insert data (teachers)
    2) email comunication between students and teachers
    - teachers have real email (@domain.tld)
    - students have sub-domain mailbox (@students.domain.tld) and
    can only relay to sub-domain and parent-domain
    3) mailbox accounts have to be inserted from a Delphi Application
    (coded by third party to whom i'm the 'interface')

    I've decided to use ISPConfig3 as base onto develop:
    - point (1) is (almost) ready ..simple php web app
    - point (2) and point (3) are in "working progress" :)

    In order to satisfy points 2
    - i've decided to force webmail use to users (students and teachers)
    In this way i can easyly bypass the need to assist "config parameters"
    ..the user only need his login data.
    - i can control relay capabilities (for students) whith postfix headers
    check (i hope .. at least i can write a new little filter with bash)

    In order to satisfy points 3
    I'm trying to understand low level gears behind mailbox creation in ISPConfig (which well control postfix in db-driven way) in order to create an 'interface' to third party Delphi App.
    I was thinking about managing mailbox accounts managing mail_user table (this is a single server setup simpler). In my test i stoped cron on "/.../" and i noticed:
    - if i add a new mailbox (from ISPConfig interface) a new record appears in mail_user table (so postfix know).. then (somewhere) ISPConfig send the first wellcome mail (so 'mailfilter' script 'maildirmakes' the mailbox). So the creation could be easy .. i can add a record in mail_user and send a wellcome message.
    - if i want to remove a mailbox, i can remove record from mail_user table (so postfix know mailbox is not present) but obviously the mail folder is not cleared (remember that /../ is stopped)

    I can use /../ script to solve the problem .. but i need to understand what i have to insert in sys_datalog table (which is a little bit obscure to me). Otherwise .. i can store the mailbox deletion in another new table and write a new simple script to delete mailbox folder.

    I think i can satisfy all the requirements.. but my first aim is to better undestand ISPConfig3 because i think i'll use it frequently and i'll migrate lot of my production servers to it in a near future.

    ===Questions Section===

    1) is the mailbox creation process correct ?
    - mailbox record inserted in mail_user table
    - variation record inserted in sys_datalog table
    - welcome mail sent to mailbox to create mail folder (mailfilter script)
    - every min cron (/../ manages sys_datalog and makes multiserver config consistent
    ..mailbox is ready

    2) is the mailbox deletion process correct ?
    - mailbox record removed in mail_table
    - variation record inserted in sys_datalog table
    - every min cron (/../
    # manages sys_datalog and makes multiserver config consistent
    # deletes mailbox folder (on a correct server if multiserver config)

    3) is there a (framework) help on how to manage sys_datalog table ?
    I think i cannot easyly 'reverse engineer' that :)

    4) If i create in ISPConfig3 a mail domain (and mailboxes), a folder with the same name is created in mailfilters folder under /var/vmail/mailfilters/ .
    As i could see ..
    - Spamfilter 'Whitelist', 'Blacklist' and 'User / Domain' are stored in db
    - Global Filters 'Postfix Whitelist', 'Postfix Blacklist' and 'Content Filter'
    are stored on /etc/postfix

    So.. what kind of data are stored in /var/vmail/mailfilters/$DOMAIN folder ?

    Finally .. maybe a little bug .. if i delete a mail domain, the script delete the domain folder but not the /var/vmail/mailfilters/$DOMAIN one.


  4. till

    till Super Moderator Staff Member ISPConfig Developer

    ISPConfig will contain a remoting framework API for this.
  5. bajodel

    bajodel New Member

    .. i notice that you are really in a hurry :D

    Thanx till, i think i cannot wait for release to start my little project .. but i'll examine the remoting framework as soon as you release it!

    I'm not a "guru" ( ..just working on it :p ) but i'm a good 'fu**ing manual reader' :D .. if i can help in some way tell me!



Share This Page