MySQL query to add/delete/edit mail account?

[klonos]

Hello,

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)';

[till]

You can not add or delete mail users with a simple mysql query as you will have to add also a correct object representation of the old and new record into the sys_datalog table.

Changing the password is possible without that as long as you do not use a multiserver system.

Also take a look here:

http://www.faqforge.com/linux/contro...-his-password/

[bajodel]

Originally Posted by till

You can not add or delete mail users with a simple mysql query as you will have to add also a correct object representation of the old and new record into the sys_datalog table.

Hi till, i'm just trying to 'understand' the gears moving behind ISPConfig3 ..at 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 ..so simpler). In my test i stoped cron on "/.../server.sh" 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 /../server.sh is stopped)

I can use /../server.sh 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 (/../server.sh) 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 (/../server.sh)
# 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 server.sh script delete the domain folder but not the /var/vmail/mailfilters/$DOMAIN one.

Bye..

bajodel.

[till]

ISPConfig 3.0.1.4 will contain a remoting framework API for this.

[bajodel]

Originally Posted by till

ISPConfig 3.0.1.4 will contain a remoting framework API for this.

.. i notice that you are really in a hurry

Thanx till, i think i cannot wait for 3.0.1.4 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 ) but i'm a good 'fu**ing manual reader' .. if i can help in some way tell me!

Bye..

bajodel.