Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > ISPConfig 3 > General

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 18th August 2009, 01:33
klonos klonos is offline
Member
 
Join Date: Apr 2007
Posts: 78
Thanks: 5
Thanked 3 Times in 3 Posts
Question MySQL query to add/delete/edit mail account?

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:

Code:
// 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)';
__________________
You can support Howtoforge and all the people behind it too. Consider becoming a supporter. It only costs a few and has to offer so much more than it already does. Take a look here
Reply With Quote
Sponsored Links
  #2  
Old 18th August 2009, 11:44
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 35,676
Thanks: 819
Thanked 5,317 Times in 4,170 Posts
Default

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/
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
The Following User Says Thank You to till For This Useful Post:
klonos (18th August 2009)
  #3  
Old 20th August 2009, 17:26
bajodel bajodel is offline
Member
 
Join Date: Jul 2007
Location: Italy
Posts: 63
Thanks: 26
Thanked 12 Times in 11 Posts
Default ..some little hints :) ..please

Quote:
Originally Posted by till View Post
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.
Reply With Quote
  #4  
Old 21st August 2009, 13:37
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 35,676
Thanks: 819
Thanked 5,317 Times in 4,170 Posts
Default

ISPConfig 3.0.1.4 will contain a remoting framework API for this.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
The Following User Says Thank You to till For This Useful Post:
bajodel (21st August 2009)
  #5  
Old 21st August 2009, 15:31
bajodel bajodel is offline
Member
 
Join Date: Jul 2007
Location: Italy
Posts: 63
Thanks: 26
Thanked 12 Times in 11 Posts
 
Default

Quote:
Originally Posted by till View Post
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.
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
procmail can't write Hellbound General 27 8th June 2009 21:51
Help configure Postfix to use alt port 465 or 587 BoloMarkIII Installation/Configuration 10 16th March 2009 17:57
just the last step...and it works. Postfix...need help config. ubuntusr Installation/Configuration 1 5th January 2009 09:50
Filtering emails using Spamassassin and Clamav for Exchange 2000 Server Rocky Server Operation 19 11th March 2008 08:27
Multiple MySQL Queries - with Postfix-mysql, courier-authmysql, maildrop-mysql... snowfly HOWTO-Related Questions 4 7th July 2006 04:19


All times are GMT +2. The time now is 20:50.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.