Changing maildir/mailbox filesystem layout
Brought to you by:
christian_boltz,
gingerdog
Menu
▾
▴
Reposting with more appropriate subject, since there have been no replies yet...
Hello,
Please check me...
When I originally set up my system, I was very new to everything, so used the defaults for:
(from config.inc.php)
$CONF['domain_path'] = 'NO';
$CONF['domain_in_mailbox'] = 'YES';
which results in everything stored as:
/var/virtual/username@domain.tld
I now want to change the layout of my system so that everything is stored under:
/var/virtual/mail/domain.tld/username
Am I correct that this setting is only used when returning the maildir/mailbox location to postfix in the mysql query? So, all I need to do is stop postfix, reorganize the actual maildir layout on the filesystem, change the base for both postfix to /var/virtual/home, reverse the above settings in config.inc.php (change them to YES and NO respectively), then start postfix, and it will immediately pick up the new maildir locations? Or do I need to restart mysql too (I can't see why)?
Or, maybe do I have to also change the mysql query, currently:
query = SELECT maildir FROM mailbox WHERE username='%s'
Thanks...
I think if you look in the mailbox database table you'll see that the path is hard coded in a field (i.e. domain.tld/username or username@domain.tld).
Thats what I was afraid of: these config.inc.php settings are only used for populating this field when the user account is created.
Ouch. This makes it very problematic for anyone who might decide to change the mailbox layout (like I want to do now).
Before I actually create one, I'd like to hear what you/everyone else thinks of a Feature Request to make this much more flexible.
I'm thinking the most flexible way to do this would be to simply create one additional field to store the local part of the email address. Then all you have to do is modify the SQL query used by your smtp and pop/imap servers accordingly.
What do you think? Doing it this way would allow the flexibility to 'assemble' the maildir/mailbox location using the SQL query, and at the same time not break existing installations.
Hmmm... or how about a 'calculated' field that calculates the mailbox location dynamically, depending on the config setting? Change the config setting, and the mailboxes are automatically changed...
I'm just thinking out loud...
Anyway...
What would be the easiest way to change the mailbox/maildir location for existing accounts? Just manually edit the fields? If so, what is the best tool for someone who is not an 'SQL guy' or a programmer/scripting guru?
I can't imagine having to do something like this where thousands of accounts are involved... <shudder>
No one likes the idea? No one cares?
This is very problematic for anyone in this situation (wanting to change the filesystem layout of maildirs).
It really seems to me that adding a single new field that contains the local part of the users email/userid won't break anything, and will provide all the added flexibility needed for anyone who needs to change this - I can then use the CONCAT function in the mysql query to return whatever I want for the mailbox location.
Also, what about adding the 'maildir' field to the Account Details page, so it can be edited directly from postfixadmin... that would make it easier, at least for smaller installs (like mine). Obviously, changing something like this in a hard coded field is not practical for large organizations, nor would they be likely to WANT this value to be hard-coded, and would benefit greatly from the flexibility offered by having the local part of the username stored separately.
So, just one new field - 'local' - right before the 'domain' field in the mailbox table, that is automatically populated with *only* the local part (the part before the '@' sign) of the username - is all I'm asking for.
The defaults could all work the same and thus wouldn't affect any existing installations - but existing/new users would then have the flexibility of calculating the mailbox location dynamically using the SQL query...
I hope the only reason no one has commented on this with understanding/approval is because you're busy... ;)
Errm....
Firstly, I'm sure it would be possible to write SQL which would perform a textual change on the value of a field.
e.g. update table set x = replace('a', 'b', x);
Would replace instances of a with b in the field x.
(hopefully).
(See it's not just you who've replied to this thread!)
David.
> Firstly, I'm sure it would be possible to write SQL which would
> perform a textual change on the value of a field.
Well... sure... ;) but that doesn't really relate much to the point of this thread...
I'm asking if it makes sense to the devs to simply add this new field ('local' part of the email address in its own field), so that this is a non-issue for anyone else in the future...
> (See it's not just you who've replied to this thread!)
Oh, I knew you'd get around to it... I'm just really glad that you guys started hacking away at postfixadmin again, after that multi-year long hiatus...
:)
As another point - it makes no sense to allow an admin to change the maildir path from the web ui, as the actual path (where mails are) and the new path (where new stuff will go to) will be out of sync. Remember, Postfixadmin itself doesn't (normally) have permission to write/access /var/spool/vmail (or wherever).
My opinion is that if you want to change where your mailboxes are, it would need to be a manual (although hopefully scripted) process as you have to update the database and filesystem at the same time.
David.
> As another point - it makes no sense to allow an admin to change the maildir
> path from the web ui, as the actual path (where mails are) and the new path
> (where new stuff will go to) will be out of sync. Remember, Postfixadmin itself
> doesn't (normally) have permission to write/access /var/spool/vmail (or
> wherever).
Ok, I'll buy that, as to making this field editable in the GUI...
> My opinion is that if you want to change where your mailboxes are, it would
> need to be a manual (although hopefully scripted) process as you have to
> update the database and filesystem at the same time.
True... but if the uers local part of their email address was contained in a separate field, changing the filesystem layout of the maildirs would be a simple matter of:
a) rearranging the filesystem, and
b) making one simple change to the sql query being used
This, imnsho, is far better than having to manually (or even scriptually) changing the maildir field in the table...
I really am having a hard time understanding why there is resistance to this idea... it is such a very minor/simple change, and breaks NOTHING for existing installations, but adds tremendous flexibility for both existing and future installations...
Can you please explain why you don't like the idea of adding one simple field to the table? The domain part of the userid/email address is already in a separate field, why no the local part?
ping...
So, is this a dead issue? No chance of adding this field? :(
Sorry; it wasn't intentional (any ignoring).
How would you see the field being added for existing mailboxes? I'm not sure how an upgrade could be done in a painless manner...
The update isn't very difficult. In MySQL you do the following two queries:
ALTER TABLE `mailbox` ADD `test_localpart` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL AFTER `quota` ;
update mailbox set `test_localpart` = SUBSTRING_INDEX(username,'@',1);
(note that I'm using test_localpart as fieldname here to avoid problems with future database updates)
GingerDog, can you find out how the PgSQL queries look like?
On a general note: There's nothing wrong with adding the localpart field, but we should also keep the maildir field. IMHO especially changing the maildir layout is a case where the maildir field might be useful - it allows to change the disk layout for mailboxes created in the future without having to move around all existing mailboxes. (And yes, I know that libertytrek might see this different - but he could just use the new localpart field in his queries.)
Hi Gingerdog,
I knew you weren't ignoring me on purpose... ;)
I was assuming some kind of script that someone could use to populate the local part for existing fields - and I see that Christian is way ahead of me...
I actually agree with you Christian... the main goal should always be to never break existing installs, so keeping the maildir field is a must...
Imo, allowing someone the flexibility to either just use the maildir field, OR be more precise by using the sql query, is the best of both worlds...
Oh... and thanks so much for considering this! I hate having to maintain private modifications to softwares, especially such mission critical ones as postfixadmin.. :)
(cboltz: I'm too stupid to quickly work out the PostgreSQL substring type thing... make the change for MySQL and that'll get me to figure it out)
Ok, pinging to make sure this isn't forgotten...
Has the initial code Christian proposed been added to the main repository yet?
Also, any chance of a new official release soon? Maybe a 2.2.2, since this will (hopefully) add a new field to the mailbox Table structure?
Thanks again guys!
Hi,
Christian and I discussed a new release the other night on IRC, so it's not far away.
He thought bugs 2073379 and 1980062 needed fixing first.
No worries...
But you didn't answer my first question... has the code to add the new field to the mailbox Table made it into cvs (and will make it into the next release) yet?
Thanks again guys for all your hard work!
No; I've not seen any code to do your mailbox renaming etc.
No, no, no...
I'm talking about the code to simply add one new field to the mailbox table... 'local', or whatever (Christian had named it 'test_localpart' in his initial code that was for mysql)...
Like I said, I'm even happier with this, becasue then I have full control by simply changing the sql query, and don't have to change the maildir field at all...
Did you understand what I'm asking now gingerdog? Just want to make sure before a new release is made so that hopefully this small but very useful change can be included...
Thanks!
Yes; but it's not just a case of running a one off query - it'll also be necessary to modify the add/edit mailbox table to add the appropriate field.
Ok... well, your other response was about 'mailbox renaming', which is not what this is about.
So, as long as we are all on the same page as what is being discussed... the simple addition of one field to the mailbox table - 'localpart' - that will contain just the localpart of the email address, just like the domain field contains just the domain part.
As for the code necessary to make this work fully, that is beyond me... but I thought Christian had provided example code that would do the job for Mysql above... although he did ask for help for the postgresql code...
Anyway, thanks very much for considering it, and I hope it is a simple enough change that it can make it into the next release...