postfixadmin-devel — Postfix Admin development talk - users are also welcome

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[Tanstaafl <tan...@li...>]

On 2012-01-22 9:11 AM, Sonam Penjor <s_p...@ru...> wrote:
> I have been trying to upload my bulk mail source code for the review for
> last one week but without any success.
>
> I would appreciate if  you can provide me some guidelines.

Please do not hijack threads...

Post a *new* message (do NOT reply to an existing one and just change 
the subject), and use a *meaningful* subject...

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[Sonam P. <s_p...@ru...>]

Hello,

I have been trying to upload my bulk mail source code for the review for 
last one week but without any success.

I would appreciate if  you can provide me some guidelines.

Thank you

Sonam

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

[Postfixadmin-devel] final request for Creating Bulk email ID

[Sonam P. <s_p...@ru...>]

Hello,

This is my last and final part of my bulk email uploading. I was just 
thinking whether it is really important to change character set 
encoding  as I am using data to be uploaded from CSV file. If so then, 
should I update column wise or table wise.

I was also wondering how to upload the my source code for the review.

I would appreciate for your comments, advice and recommendations on my 
last and final part of the activities.

Thank You

Sonam Penjor



-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[Christian B. <pos...@cb...>]

Hello,

Am Montag, 16. Januar 2012 schrieb David Goodwin:
> >> And BTW: MySQL automatically converts the charset, so you can
> >> give it utf8 as input and it will store it as latin1 if the
> >> field is defined as such.
> 
> Indeed. But that's fine. As effectively you are passing it a load of
> bytes in, and it's just returning them as is.

No, it's returning them in the charset of your connection - which you 
can influence with SET NAMES ;-)

(And you really want to read
http://mysqldump.azundris.com/archives/60-Handling-character-sets.html
which I already mentioned yesterday.)
	
> >> In other words: never change the charset in a mysqldump file.
> >> If you really want to change a column to uft8, do it directly in
> >> MySQL ("ALTER TABLE foo CHANGE bar bar VARCHAR(255) CHARACTER
> >> SET utf8 NOT NULL").
> 
> Wouldn't this still require a dump of the database and reinsertion in
> order to get the stuff encoded correctly)?
> 
> I don't think MySQL can tell what is, and isn't a UTF8 character (can
> it)?
> 
> I think if you just change the table charset it'll still contain the
> invalid encoding as is.

MySQL _does_ a charset conversion when you ALTER a column from one 
charset from another (if the latin1 column contained a latin1-"ö", it 
will contain a utf8-"ö" after changing the column to utf8).

This of course only works if the data in the original encoding is 
correct - you can't expect valid output data if you provide broken 
input... ;-)

(Actually, it's a bit tricky to _avoid_ the automatic charset 
conversion, for example if you accidently stored utf8-encoded stuff in a 
latin1 column. IIRC the only way is to change the column to a "binary" 
varchar and then to utf8. Or of course the hard way of replacing the 
correct[tm] file in /var/lib/mysql/ - but you shouldn't do this at 
home ;-))

> I assume that changing the charset would just make a difference for
> future queries (e.g. it'll realise that £ or ö is actually one
> character and not 2 if you're doing a strlen operation within MySQL).

The client will not even notice a difference if the column charset is 
changed. (Except if it tries to store a character that is not available 
in latin1, of course.)

> >>> Last... what is *recommended* for postfixadmin?
> >> 
> >> MyISAM for most tables, as created by setup.php/upgrade.php.
> 
> InnoDB is better :-)
> 
> <round name="one">fight</round>

<round name="two" type="shoutmatch">Even if I assume your statement is 
true: we don't use the features that makes it better, so there is no 
difference for us ;-) And MyISAM supports fulltext search, which InnoDB 
doesn't...</round>

<additional_punch>If you implement transaction support everywhere (with 
the things from my last mail in mind), I'll happily switch all tables to 
InnoDB ;-) </additional_punch>


Regards,

Christian Boltz
-- 
Früher mußte man den Müll heimlich im Wald verbuddeln;
Heute gibt es EBAY :-)    [Axel Lindlau in suse-linux]

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[David G. <da...@co...>]

>> 
>> And BTW: MySQL automatically converts the charset, so you can give it
>> utf8 as input and it will store it as latin1 if the field is defined as
>> such.

Indeed. But that's fine. As effectively you are passing it a load of bytes in, and it's just returning them as is.


>>>> No. Any utf-8 characters already in there will be stored in a
>>>> weird
>>>> encoding. If you have no existing UTF8 characters in your data
>>>> then it would work.
>>> 
>>> Ok, thanks... is there a way to test for existing utf8 characters? I'm
>>> assuming I don't, since this database has apparently always been in
>>> latin1 format...
>> 
>> Only ASCII characters (0..127) are the same in all charsets. If you are
>> using other characters (like the german umlauts äöü) which are in the
>> 128..255 area, you'll get invalid utf8.


Yes - but when querying the database for that data it'll probably end up coming out right - as it'll be returned to you the same as what you entered it as (99% of the time) - as long as no one has done e.g. addslashes on the data and the unicode character doesn't contain the Byte 0x5a or whatever it is. (').

>> In other words: never change the charset in a mysqldump file.
>> If you really want to change a column to uft8, do it directly in MySQL
>> ("ALTER TABLE foo CHANGE bar bar VARCHAR(255) CHARACTER SET utf8 NOT
>> NULL").

Wouldn't this still require a dump of the database and reinsertion in order to get the stuff encoded correctly)? 

I don't think MySQL can tell what is, and isn't a UTF8 character (can it)?

I think if you just change the table charset it'll still contain the invalid encoding as is.

I assume that changing the charset would just make a difference for future queries (e.g. it'll realise that £ or ö is actually one character and not 2 if you're doing a strlen operation within MySQL).

>> 
>>> Last... what is *recommended* for postfixadmin?
>> 
>> MyISAM for most tables, as created by setup.php/upgrade.php.
>> 

InnoDB is better :-)

<round name="one">fight</round>

David.

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[Tanstaafl <tan...@li...>]

Thanks very much for taking the time to explain this in such detail 
Christian!

I understand much better now why things are as they are...

Now I just have to decide if I want to move out of my comfort zone in 
mysql and switch to postgreSQL...

On 2012-01-14 12:08 PM, Christian Boltz <pos...@cb...> wrote:
> Hello,
>
> I'm a bit undecided which mail in this thread I should answer - I'll
> just take this one ;-)
>
> And somebody should write a summary in the postfixadmin wiki for this
> (more or less) FAQ. I'll happily give you write permissions if you
> a) login to sourceforge and then visit the postfixadmin wiki (this will
>     create a wiki user for you)
> b) tell me your sourceforge username
>
> Am Freitag, 13. Januar 2012 schrieb Tanstaafl:
>> On 2012-01-13 7:47 AM, David Goodwin<da...@co...>  wrote:
>>> On 13 Jan 2012, at 12:37, Tanstaafl wrote:
>>>> Can I simply edit the dump file prior to restoring it? Ie,
>>>> change the Engine in each of the table definitions from MyISAM
>>>> to InnoDB?>
>>> Yes. We don't use any full text indexes so there is no dependency
>>> on MyISAM.
>> Hmmm... I didn't know of that limitation with InnoDB, I just read
>> somewhere that InooDB was preferred...
>>
>> So... maybe I should stick with MyISAM?
>
> At the moment, postfixadmin uses MyISAM for most tables (except vacation
> and vacation_notification because they have a foreign key [1]). There is
> no specific reason for that, except the historic one - "nobody changed
> it".
>
> If you want, you can easily switch a table from MyISAM to InnoDB with
> "ALTER TABLE foo ENGINE=InnoDB" - however I don't see a reason to do
> that at the moment ;-)
>
> Using transactions [2] would be a reason to switch to InnoDB - however
> we would first need someone who implements transaction usage. It's
> probably not as easy as it looks - for example, creating a mailbox also
> creates an alias for it, so we can't just do a "begin transaction" at
> the beginning of every *Handler. And the more interesting question is
> about rollback: creating a mailbox will send a welcome mail and (if
> configured) create subfolders using IMAP. Now imagine creating the
> subfolders fails - would you do a rollback or not? The welcome mail was
> sent at that point, and there's no way to undo that...
>
>>>> As for changing the character set from latin1 to utf8... can I
>>>> do
>>>> it the same way as described above (simply edit the dump file
>>>> before restoring)?
>
> utf8 is a different beast in MySQL ;-)
>
> utf8 can take up to 3 byte per character, which means a VARCHAR(255) to
> store a mail address [3] would need 3*255 bytes on disk. But that's not
> the main problem - even if you have lots of mail addresses, you will
> most probably be more concerned about the disk space for your mail
> storage than the disk space for the database ;-)
>
> The main problem is that an index in MySQL is limited to 1024 bytes (not
> characters). In other words: It's impossible to have an index over two
> VARCHAR(255) columns in utf8 - and that's something we need (see
> vacation_notification table - fortunately this table stores only mail
> addresses, so latin1 is enough).
>
> If you are interested in more technical details,
> http://mysqldump.azundris.com/archives/60-Handling-character-sets.html
> might be an interesting reading. Or even better: read everything on
> http://mysqldump.azundris.com/ - the author worked as MySQL consultant
> for some years.
>
> That all said: The postfixadmin database uses utf8 where it is needed
> (name, description etc.) and uses latin1 in all fields that are not
> allowed to contain non-ascii characters (mail address, domain [4] etc.)
>
> I see no reason to change all fields to utf8, and in some cases utf8
> would even make some serious trouble (the two-column index in
> vacation_notification).
>
> And BTW: MySQL automatically converts the charset, so you can give it
> utf8 as input and it will store it as latin1 if the field is defined as
> such.
>
>>> No. Any utf-8 characters already in there will be stored in a
>>> weird
>>> encoding. If you have no existing UTF8 characters in your data
>>> then it would work.
>>
>> Ok, thanks... is there a way to test for existing utf8 characters? I'm
>> assuming I don't, since this database has apparently always been in
>> latin1 format...
>
> Only ASCII characters (0..127) are the same in all charsets. If you are
> using other characters (like the german umlauts äöü) which are in the
> 128..255 area, you'll get invalid utf8.
>
> In other words: never change the charset in a mysqldump file.
> If you really want to change a column to uft8, do it directly in MySQL
> ("ALTER TABLE foo CHANGE bar bar VARCHAR(255) CHARACTER SET utf8 NOT
> NULL").
>
>> Last... what is *recommended* for postfixadmin?
>
> MyISAM for most tables, as created by setup.php/upgrade.php.
>
>
> Regards,
>
> Christian Boltz
>
> [1] this foreign key made some database changes quite funny[tm], and
>      from that I learned that it might be better to avoid foreign keys
>      enforced by the database ;-)
>
> [2] we already use transactions in some rare cases for postgresql
>
> [3] making the fields shorter is not an option - we even got a bugreport
>      saying that RFC allows up to 320 (IIRC) characters in a mail address
>      and that we should make our fields longer...
>      While that is technically a valid request, it would make our tables
>      incompatible with older MySQL versions which are limited to 255
>      characters in VARCHAR (not sure about postgresql length limits).
>      And seriously, do you really want to have a mail address that is
>      longer than 255 characters? If yes, I'd like to see your business
>      card ;-)
>
> [4] I'm aware of unicode domains - but those are encoded as
>      "xn--something.tld" by clients (browsers etc.)
>

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[Christian B. <pos...@cb...>]

Hello,

I'm a bit undecided which mail in this thread I should answer - I'll 
just take this one ;-)

And somebody should write a summary in the postfixadmin wiki for this 
(more or less) FAQ. I'll happily give you write permissions if you
a) login to sourceforge and then visit the postfixadmin wiki (this will 
   create a wiki user for you)
b) tell me your sourceforge username

Am Freitag, 13. Januar 2012 schrieb Tanstaafl:
> On 2012-01-13 7:47 AM, David Goodwin <da...@co...> wrote:
> > On 13 Jan 2012, at 12:37, Tanstaafl wrote:
> >> Can I simply edit the dump file prior to restoring it? Ie,
> >> change the Engine in each of the table definitions from MyISAM
> >> to InnoDB?> 
> > Yes. We don't use any full text indexes so there is no dependency
> > on MyISAM.
> Hmmm... I didn't know of that limitation with InnoDB, I just read
> somewhere that InooDB was preferred...
> 
> So... maybe I should stick with MyISAM?

At the moment, postfixadmin uses MyISAM for most tables (except vacation 
and vacation_notification because they have a foreign key [1]). There is 
no specific reason for that, except the historic one - "nobody changed 
it".

If you want, you can easily switch a table from MyISAM to InnoDB with 
"ALTER TABLE foo ENGINE=InnoDB" - however I don't see a reason to do 
that at the moment ;-)

Using transactions [2] would be a reason to switch to InnoDB - however 
we would first need someone who implements transaction usage. It's 
probably not as easy as it looks - for example, creating a mailbox also 
creates an alias for it, so we can't just do a "begin transaction" at 
the beginning of every *Handler. And the more interesting question is 
about rollback: creating a mailbox will send a welcome mail and (if 
configured) create subfolders using IMAP. Now imagine creating the 
subfolders fails - would you do a rollback or not? The welcome mail was 
sent at that point, and there's no way to undo that...

> >> As for changing the character set from latin1 to utf8... can I
> >> do
> >> it the same way as described above (simply edit the dump file
> >> before restoring)?

utf8 is a different beast in MySQL ;-)

utf8 can take up to 3 byte per character, which means a VARCHAR(255) to 
store a mail address [3] would need 3*255 bytes on disk. But that's not 
the main problem - even if you have lots of mail addresses, you will 
most probably be more concerned about the disk space for your mail 
storage than the disk space for the database ;-)

The main problem is that an index in MySQL is limited to 1024 bytes (not 
characters). In other words: It's impossible to have an index over two 
VARCHAR(255) columns in utf8 - and that's something we need (see 
vacation_notification table - fortunately this table stores only mail 
addresses, so latin1 is enough).

If you are interested in more technical details, 
http://mysqldump.azundris.com/archives/60-Handling-character-sets.html
might be an interesting reading. Or even better: read everything on 
http://mysqldump.azundris.com/ - the author worked as MySQL consultant 
for some years.

That all said: The postfixadmin database uses utf8 where it is needed 
(name, description etc.) and uses latin1 in all fields that are not 
allowed to contain non-ascii characters (mail address, domain [4] etc.)

I see no reason to change all fields to utf8, and in some cases utf8 
would even make some serious trouble (the two-column index in 
vacation_notification).

And BTW: MySQL automatically converts the charset, so you can give it 
utf8 as input and it will store it as latin1 if the field is defined as 
such.

> > No. Any utf-8 characters already in there will be stored in a
> > weird
> > encoding. If you have no existing UTF8 characters in your data
> > then it would work.
> 
> Ok, thanks... is there a way to test for existing utf8 characters? I'm
> assuming I don't, since this database has apparently always been in
> latin1 format...

Only ASCII characters (0..127) are the same in all charsets. If you are 
using other characters (like the german umlauts äöü) which are in the 
128..255 area, you'll get invalid utf8.

In other words: never change the charset in a mysqldump file.
If you really want to change a column to uft8, do it directly in MySQL 
("ALTER TABLE foo CHANGE bar bar VARCHAR(255) CHARACTER SET utf8 NOT 
NULL").

> Last... what is *recommended* for postfixadmin?

MyISAM for most tables, as created by setup.php/upgrade.php.


Regards,

Christian Boltz

[1] this foreign key made some database changes quite funny[tm], and
    from that I learned that it might be better to avoid foreign keys
    enforced by the database ;-)

[2] we already use transactions in some rare cases for postgresql

[3] making the fields shorter is not an option - we even got a bugreport
    saying that RFC allows up to 320 (IIRC) characters in a mail address
    and that we should make our fields longer...
    While that is technically a valid request, it would make our tables
    incompatible with older MySQL versions which are limited to 255
    characters in VARCHAR (not sure about postgresql length limits).
    And seriously, do you really want to have a mail address that is
    longer than 255 characters? If yes, I'd like to see your business 
    card ;-)

[4] I'm aware of unicode domains - but those are encoded as 
    "xn--something.tld" by clients (browsers etc.)

-- 
Ich verlas mich. Die Dokumentation ist devel und nicht unstable, daher
kann wohl nur ein kyrillischer Zeichensatz oder gar ein inhaltlicher
Fehler vorkommen. Obwohl... Man könnte sie unter Windows 95 lesen, damit
sie abstürzt. Das wäre aber OT.   [Ferdinand Ihringer in suse-linux]

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[David G. <da...@co...>]

> But, I'm thinking/hoping it is more my thought processes than it is a 
> matter of postgresql being *that* much more complicated.
> 
> I am about as far from an 'expert' at anything sql as anyone could be, 
> but I have found managing my mysql to be very painless and easy, and I 
> don't really have the time to become a postgres guru just to run my 
> postfixadmin database.
> 
> So, if I can get some help with doing the following things the postgres 
> way (these are about the only things I ever do in mysql, up to now that 
> is), then maybe I can give switching another look...
> 
> Following is my documented procedure for installing and/or updating my 
> postfixadmin in mySQL - what I need help with is how to do the 
> equivalent in postgresql. I appreciate any tips you can provide...
> 
> *************************************************
> 
> Updating PostfixAdmin after emerging the new version and installing it 
> with webapp-config:
> 
> 1. dump the current/production DB to an sql file:
> 
> From the root commandline (NOT the mysql command line):
> # mysqldump -u root -p -v pfa_currentver > 
> /home/user/pfa_currentver_preupdate.sql
> 
> pgsql equivalent:
> 

As the system user postgres :


pg_dump -D postfix > postgres.sql

(from memory). 

> 
> 2. create the admin user (if already exists, skip this step):
> 
> mysql> CREATE USER 'pfa_admin'@'localhost' IDENTIFIED BY 'password';
> 
> pgsql equivalent (maybe?):
> pgsql> CREATE USER pfa_admin WITH NOSUPERUSER NOCREATEDB NOCREATEROLE 
> INHERIT PASSWORD 'password';
> 

Postgres has a different permissions structure. 

Create user postgres with password 'fish'

(I think)

> 3. create the new DB:
> 
> # mysql -u root -p password
> 
> mysql> CREATE DATABASE pfa_newver;
> 

> pgsql equivalent (maybe?):
> pgsql> CREATE DATABASE pfa_newver OWNER pfa_admin; ?
> 

Yep. 

> 4. grant required access to the pfa_newver DB to the pfa_admin user:
> 
> mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
> INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE 
> ROUTINE, ALTER ROUTINE ON `pfa_newver`.* TO 'pfa_admin'@'localhost';
> 
> pgsql equivalent?
> 

No need. Ownership fixes that. 


> 
> 
> 
> 6. create a read-only user (if already exists, skip the CREATE command) 
> and grant only select privileges for querying the database:
> 
> mysql> CREATE USER 'pfa'@'localhost' IDENTIFIED BY 'password';
> 
> pgsql equivalent (maybe?):
> CREATE USER pfa WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT 
> PASSWORD 'password';
> 

Not sure. Will have to research / read docs. 
> 
> 7. import the dump from the production DB into the new DB:
> 
> # mysql -u root -p password pfa_newver < 
> /home/user/pfa_currentver_preupdate.sql
> 
> pgsql equivalent?
> 


psql postfix < postfix.dump

You might need to specify user etc when connecting. 

> 


Somewhere on my website (codepoets.co.UK) there should be a tutorial covering Installation with pgsql. 

David. 

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[Tanstaafl <tan...@li...>]

On 2012-01-13 11:57 AM, Tanstaafl <tan...@li...> wrote:
> On 2012-01-13 8:53 AM, David Goodwin<da...@co...>  wrote:
>>> Out of curiosity, is that what you use? Or do you use postgresql?

>> For Postfix I use PostgreSQL …
>>
>> For nearly all our development work we use MySQL, and within that
>> generally always InnoDB.

> Ok, well, since you use both, would you mind if I picked your brain
> on a few things? My apologies for hoe long this is, and if you don't
> have time to answer, that is ok...

I should have added... I am in no hurry at all to get this switched 
over, so if you are willing to take a few minutes here and there over 
the next few weeks or months to answer these question, that would be 
absolutely fine with me...

Thanks again for all your hard work on the premiere tool for managing a 
linux based mail system!

Charles

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[Tanstaafl <tan...@li...>]

On 2012-01-13 8:53 AM, David Goodwin <da...@co...> wrote:
>> Out of curiosity, is that what you use? Or do you use postgresql?

> For Postfix I use PostgreSQL …
>
> For nearly all our development work we use MySQL, and within that generally always InnoDB.

Ok, well, since you use both, would you mind if I picked your brain on a 
few things? My apologies for hoe long this is, and if you don't have 
time to answer, that is ok...

Ever since Oracle acquired mysql, I've been toying with the idea of 
switching to postgresql. I installed it and messed around a bit, but am 
thoroughly confused on some fairly simple things, which gave me reason 
to pause - if I'm so confused about the simple things, maybe I shouldn't 
switch...

But, I'm thinking/hoping it is more my thought processes than it is a 
matter of postgresql being *that* much more complicated.

I am about as far from an 'expert' at anything sql as anyone could be, 
but I have found managing my mysql to be very painless and easy, and I 
don't really have the time to become a postgres guru just to run my 
postfixadmin database.

So, if I can get some help with doing the following things the postgres 
way (these are about the only things I ever do in mysql, up to now that 
is), then maybe I can give switching another look...

Following is my documented procedure for installing and/or updating my 
postfixadmin in mySQL - what I need help with is how to do the 
equivalent in postgresql. I appreciate any tips you can provide...

*************************************************

Updating PostfixAdmin after emerging the new version and installing it 
with webapp-config:

1. dump the current/production DB to an sql file:

 From the root commandline (NOT the mysql command line):
# mysqldump -u root -p -v pfa_currentver > 
/home/user/pfa_currentver_preupdate.sql

pgsql equivalent:
?

2. create the admin user (if already exists, skip this step):

mysql> CREATE USER 'pfa_admin'@'localhost' IDENTIFIED BY 'password';

pgsql equivalent (maybe?):
pgsql> CREATE USER pfa_admin WITH NOSUPERUSER NOCREATEDB NOCREATEROLE 
INHERIT PASSWORD 'password';

3. create the new DB:

# mysql -u root -p password

mysql> CREATE DATABASE pfa_newver;

pgsql equivalent (maybe?):
pgsql> CREATE DATABASE pfa_newver OWNER pfa_admin; ?

4. grant required access to the pfa_newver DB to the pfa_admin user:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE 
ROUTINE, ALTER ROUTINE ON `pfa_newver`.* TO 'pfa_admin'@'localhost';

pgsql equivalent?

5. create the pfa_vacation user (if already exists, skip the CREATE 
command) and grant it minimal rights necessary to interact with the 
vacation databases:

mysql> CREATE USER 'pfa_vacation'@'localhost' IDENTIFIED BY 'password';

pgsql equivalent (maybe?):
pgsql> CREATE USER pfa_vacation WITH NOSUPERUSER NOCREATEDB NOCREATEROLE 
INHERIT PASSWORD 'password';

mysql> GRANT SELECT, UPDATE ON `pfa_newver`.`vacation` TO 
'pfa_vacation'@'localhost';

pgsql equivalent?


mysql> GRANT SELECT ON `pfa_newver`.`alias` TO 'pfa_vacation'@'localhost';

pgsql equivalent?


mysql> GRANT SELECT ON `pfa_newver`.`alias_domain` TO 
'pfa_vacation'@'localhost'

pgsql equivalent?


mysql> GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON 
`pfa_newver`.`vacation_notification` TO 'pfa_vacation'@'localhost';

pgsql equivalent?


6. create a read-only user (if already exists, skip the CREATE command) 
and grant only select privileges for querying the database:

mysql> CREATE USER 'pfa'@'localhost' IDENTIFIED BY 'password';

pgsql equivalent (maybe?):
CREATE USER pfa WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT 
PASSWORD 'password';

mysql> GRANT SELECT, EXECUTE ON `pfa_newver`.* TO 'pfa'@'localhost';

pgsql equivalent (maybe?):


7. import the dump from the production DB into the new DB:

# mysql -u root -p password pfa_newver < 
/home/user/pfa_currentver_preupdate.sql

pgsql equivalent?


8. stop courier-imap then postfix

9. modify the following config files to reference the new db_name:

/etc/postfix/maps/mysql/all
/etc/courier/authlib/authmysqlrc

10. restart courier-imap and then postfix

done...

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[David G. <da...@co...>]

On 13 Jan 2012, at 13:26, Tanstaafl wrote:

> On 2012-01-13 8:14 AM, David Goodwin <da...@co...> wrote:
>> On 13 Jan 2012, at 13:05, Tanstaafl wrote:
>>> Hmmm... I didn't know of that limitation with InnoDB, I just read
>>> somewhere that InooDB was preferred…
> 
>> InnoDB is good for tables which have frequent updates and reads. When
>> you update a MyISAM table the entire table is locked - so on a
>> high traffic site reads are delayed.
>> 
>> I'd be surprised if there were many Postfixadmin installations which
>> have enough write and read traffic for it to matter which they use.
>> 
>> InnoDB is the way forward - MySQL 6(?) will (perhaps does already?)
>> effectively default to using it. I think it recovers better from crashes
>> and supports transactions etc. Postfixadmin doesn't use transactions.
> 
> Ok, sounds like switching to InnoDB would be the way to go then.
> 
> Out of curiosity, is that what you use? Or do you use postgresql?

For Postfix I use PostgreSQL … 

For nearly all our development work we use MySQL, and within that generally always InnoDB.

> 
>>> Last... what is *recommended* for postfixadmin?
> 
>> If it's working, don't "fix" it…..
> 
> Normally I'd agree, but my reason for considering doing this switch is 
> to 'future-proof' my postfixadmin system, and everything I've read says 
> that utf8 is the future... gentoo has switched to utf8 for their entire 
> systems.
> 

Ubuntu switched to UTF8 by default ages ago (when it was released I think).

> On the wiki page I linked in my last email, it says:
> 
> "Be careful when switching to UTF-8. Once you have converted your data, 
> any program/webapp that uses the database will have to check that the 
> data they are sending to the database is valid UTF-8. If it isn't then 
> MySQL will silently truncate the data after the invalid part, which can 
> cause all sorts of problems. If your program/webapp doesn't specifically 
> say that it supports unicode then you may want to stick with latin1 
> instead."
> 
> So, does postfixadmin validate input for utf8 if it is used? Or is there 
> any other reason it might be a problem?

I have memory of there being some UTF8 patches submitted/merged into PFA over a year ago. Some parts can't support UTF8 (e.g. domain names) while others can (e.g. vacation message, someone's name etc).
I think these have all been dealt with, and I thought a new installation of PFA would default to InnoDB and UTF-8 charset encoded tables. I'm too lazy to go into the code and check though.


David.

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[Tanstaafl <tan...@li...>]

On 2012-01-13 8:14 AM, David Goodwin <da...@co...> wrote:
> On 13 Jan 2012, at 13:05, Tanstaafl wrote:
>> Hmmm... I didn't know of that limitation with InnoDB, I just read
>> somewhere that InooDB was preferred…

> InnoDB is good for tables which have frequent updates and reads. When
> you update a MyISAM table the entire table is locked - so on a
> high traffic site reads are delayed.
>
> I'd be surprised if there were many Postfixadmin installations which
> have enough write and read traffic for it to matter which they use.
>
> InnoDB is the way forward - MySQL 6(?) will (perhaps does already?)
> effectively default to using it. I think it recovers better from crashes
> and supports transactions etc. Postfixadmin doesn't use transactions.

Ok, sounds like switching to InnoDB would be the way to go then.

Out of curiosity, is that what you use? Or do you use postgresql?

>> Last... what is *recommended* for postfixadmin?

> If it's working, don't "fix" it…..

Normally I'd agree, but my reason for considering doing this switch is 
to 'future-proof' my postfixadmin system, and everything I've read says 
that utf8 is the future... gentoo has switched to utf8 for their entire 
systems.

On the wiki page I linked in my last email, it says:

"Be careful when switching to UTF-8. Once you have converted your data, 
any program/webapp that uses the database will have to check that the 
data they are sending to the database is valid UTF-8. If it isn't then 
MySQL will silently truncate the data after the invalid part, which can 
cause all sorts of problems. If your program/webapp doesn't specifically 
say that it supports unicode then you may want to stick with latin1 
instead."

So, does postfixadmin validate input for utf8 if it is used? Or is there 
any other reason it might be a problem?

Thanks again David...

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[David G. <da...@co...>]

On 13 Jan 2012, at 13:05, Tanstaafl wrote:

> On 2012-01-13 7:47 AM, David Goodwin <da...@co...> wrote:
>> On 13 Jan 2012, at 12:37, Tanstaafl wrote:
>>> Can I simply edit the dump file prior to restoring it? Ie, change the
>>> Engine in each of the table definitions from MyISAM to InnoDB?
> 
>> Yes. We don't use any full text indexes so there is no dependency on MyISAM.
> 
> Hmmm... I didn't know of that limitation with InnoDB, I just read 
> somewhere that InooDB was preferred…
> 

InnoDB is good for tables which have frequent updates and reads.
When you update a MyISAM table the entire table is locked - so on a high traffic site reads are delayed.

I'd be surprised if there were many Postfixadmin installations which have enough write and read traffic for it to matter which they use.

InnoDB is the way forward - MySQL 6(?) will (perhaps does already?) effectively default to using it. I think it recovers better from crashes and supports transactions etc. Postfixadmin doesn't use transactions.


> So... maybe I should stick with MyISAM?
> 
>>> As for changing the character set from latin1 to utf8... can I do
>>> it the same way as described above (simply edit the dump file
>>> before restoring)?
> 
>> No. Any utf-8 characters already in there will be stored in a weird
>> encoding. If you have no existing UTF8 characters in your data then it
>> would work.
> 
> Ok, thanks... is there a way to test for existing utf8 characters? I'm 
> assuming I don't, since this database has apparently always been in 
> latin1 format…
> 

Not that I know of.


> Last... what is *recommended* for postfixadmin?
> 

If it's working, don't "fix" it…..

David.

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[Tanstaafl <tan...@li...>]

On 2012-01-13 8:05 AM, Tanstaafl <tan...@li...> wrote:
> is there a way to test for existing utf8 characters? I'm
> assuming I don't, since this database has apparently always been in
> latin1 format...

I found something online that said to run the following command, but I 
don't know how to interpret the result:

# file pfa_234.sql
pfa_234.sql: ASCII English text, with very long lines

?

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[Tanstaafl <tan...@li...>]

On 2012-01-13 7:47 AM, David Goodwin <da...@co...> wrote:
> On 13 Jan 2012, at 12:37, Tanstaafl wrote:
>> Can I simply edit the dump file prior to restoring it? Ie, change the
>> Engine in each of the table definitions from MyISAM to InnoDB?

> Yes. We don't use any full text indexes so there is no dependency on MyISAM.

Hmmm... I didn't know of that limitation with InnoDB, I just read 
somewhere that InooDB was preferred...

So... maybe I should stick with MyISAM?

>> As for changing the character set from latin1 to utf8... can I do
>> it the same way as described above (simply edit the dump file
>> before restoring)?

> No. Any utf-8 characters already in there will be stored in a weird
> encoding. If you have no existing UTF8 characters in your data then it
> would work.

Ok, thanks... is there a way to test for existing utf8 characters? I'm 
assuming I don't, since this database has apparently always been in 
latin1 format...

Last... what is *recommended* for postfixadmin?

Thanks as always!

Charles

Re: [Postfixadmin-devel] Question re: changing db engine and character set...

[David G. <da...@co...>]

On 13 Jan 2012, at 12:37, Tanstaafl wrote:

> Hello all,
> 
> I have a question regarding converting the database engine used to 
> InnoDB (currently MyISAM) and the character set to utk8 (currently 
> latin1)...
> 
> Can I simply edit the dump file prior to restoring it? Ie, change the 
> Engine in each of the table definitions from MyISAM to InnoDB?


Yes. We don't use any full text indexes so there is no dependency on MyISAM.

You probably can't just change the encoding type though (i.e. edit the dump file and push it back in).


> 
> Also - why does the engine keep getting reset to MyISAM? I have changed 
> the tables to InnoDB before, but updates always change it back…
> 

> As for changing the character set from latin1 to utf8... can I do it the 
> same way as described above (simply edit the dump file before restoring)?
> 

No. Any utf-8 characters already in there will be stored in a weird encoding. If you have no existing UTF8 characters in your data then it would work.

> Lastly, how to make sure that from now on, all databases default to 
> InnoDB and utf8?
> 

Unknown. If you created your database from being empty then I could see it possibly being set to MyISAM. The normal upgrade.php routine should not change an existing table format - unless (for instance) we added functionality in which required a specific table format.

David.

[Postfixadmin-devel] Question re: changing db engine and character set...

[Tanstaafl <tan...@li...>]

Hello all,

I have a question regarding converting the database engine used to 
InnoDB (currently MyISAM) and the character set to utk8 (currently 
latin1)...

Can I simply edit the dump file prior to restoring it? Ie, change the 
Engine in each of the table definitions from MyISAM to InnoDB?

Also - why does the engine keep getting reset to MyISAM? I have changed 
the tables to InnoDB before, but updates always change it back...

As for changing the character set from latin1 to utf8... can I do it the 
same way as described above (simply edit the dump file before restoring)?

Lastly, how to make sure that from now on, all databases default to 
InnoDB and utf8?

Thanks...

Re: [Postfixadmin-devel] Request for help to creating bulk email address

[Christian B. <pos...@cb...>]

Hello,

Am Mittwoch, 11. Januar 2012 schrieb Sonam Penjor:
> Creation of bulk email address is almost complete. Procedure of  email
> creation are the following:
> 
> 1. Uploading and reading CSV file as usual
> 2. Printing the status of CSV file
> 3. creating the email address by taking care of maximum_execution time
> 
> Regrading number three:
> 
> I self-redirect after creating certain mailboxes with
> ?csvfile=uploaded_file.csv&continue_with_line=51 

I'd store the filename in the session instead of passing it around in 
the redirect. Besides that, it looks good.

> before 5 seconds of
> maximum execution time as recommended by Mr. Christian Boltz (Thank
> You very much). For this simple re-direction I am using simple header
> php function, I don't know whether this method is correct??. Your
> help or recommendation would be helpful.

Yes,   header ("Location: [...]")   is correct.

> As I am coding directly base on SVN trunk, I using the MailBoxHandler
> class.  The following piece of code is used to create email address
> using the separate class:
...
> As you can notice from the above piece of code that I am creating
> multiple instances / constructor ( $MailHandler = new
> MailboxHandler($usernam[$i]["username"]) within the loop, Do I need to
> call destructor method after creating every mail box (If so then need
> to create destructor function in the MailboxHandler class) or leave
> it as it is or other suggestions.

You can leave it as is - PHP should automatically destruct the "old" 
MailboxHandler instance when you overwrite the $MailHandler variable 
with a new MailboxHandler instance.

> I would appreciate if you could provide recommendations or suggestions
> or advice to complete the above task.

Everything looks good to me :-) and I'm looking forward to see your 
patch ;-)


Regards,

Christian Boltz
-- 
>cat `rpm -qpil *.rpm` > packtete.txt
*KREISCH*
Erst 7. Januar und schon ist der "Useless use of cat"-Award 2004
vergeben.   [> Markus Wunder und David Haller in suse-linux]

Re: [Postfixadmin-devel] Request for help to creating bulk email address

[Sonam P. <s_p...@ru...>]

Hello,

Creation of bulk email address is almost complete. Procedure of  email 
creation are the following:

1. Uploading and reading CSV file as usual
2. Printing the status of CSV file
3. creating the email address by taking care of maximum_execution time

Regrading number three:

I self-redirect after creating certain mailboxes with 
?csvfile=uploaded_file.csv&continue_with_line=51 before 5 seconds of 
maximum execution time as recommended by Mr. Christian Boltz (Thank You 
very much). For this simple re-direction I am using simple header php 
function, I don't know whether this method is correct??. Your help or 
recommendation would be helpful.

As I am coding directly base on SVN trunk, I using the MailBoxHandler 
class.  The following piece of code is used to create email address 
using the separate class:

$i = $this -> continue_with_line;
$bulkMail = $_SESSION["user"];
foreach ($bulkMail["user"] as $mail => $username)
{

	if(is_array($username)) {
	        $MailHandler = new MailboxHandler($usernam[$i]["username"]);
    		if (!$MailHandler->add($usernam[$i]["password"], $name, $quota, $active, $mail)) {
        			die ('creating the mailbox failed - ' . $handler->errormsg);
         	}

                unset($_SESSION["user"][$i]);
                $i++;
         }

}
As you can notice from the above piece of code that I am creating 
multiple instances / constructor ( $MailHandler = new 
MailboxHandler($usernam[$i]["username"]) within the loop, Do I need to 
call destructor method after creating every mail box (If so then need to 
create destructor function in the MailboxHandler class) or leave it as 
it is or other suggestions.

I would appreciate if you could provide recommendations or suggestions 
or advice to complete the above task.

Thank You

Sonam Penjor


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Re: [Postfixadmin-devel] Vulnerabilities in postfixadmin 2.3.4

[David G. <da...@co...>]

<snip/>

backup.php :

Change :

-               foreach ($row as $key=>$val)
-               {
-                  $fields[] = $key;
-                  $values[] = $val;
-               }

To :

+                    $fields = array_keys($row);
+                    $values = array_values($row);
+                    $values = array_map('escape_string', $values);


See changeset/revision 1326.

thanks
David.



> Issue and fix confirmed (by testing with the old and new version).
> 
> We'll also need this fix in trunk. (Unfortunately your commit included 
> lots of whitespace changes, which makes the diff very hard to read.)
> 
>>> 3) Multiple XSS and lack of CSRF protection: I found several XSS
>>> in postfixadmin code. I noted from postfixadmin homepage that you
>>> planned to merge it  with Smarty wich could provide a good
>>> protection against XSS and CSRF. 
> 
> Yes, see SVN trunk if you want to test it. 
> 
> The move to Smarty is done (with a small exception in fetchmail.php).
> 
> We are moving lots of things to PHP classes which reduces the code size 
> and unifies the handling of forms, SQL queries etc. as much as possible. 
> (This part is not completed yet.)
> 
>>> Input passed via domain GET parameter to edit-vacation.php  is not
>>> properly sanitised before being returned to the user.
>>> http://127.0.0.1/postfixadmin-2.3.4/edit-vacation.php?domain=dontc
>>> are</script> <script>alert(1);</script>
> 
> Your fix urlencode()d $fCanceltarget, which was too secure ;-) - it 
> resulted in a 404 error when clicking the "Exit" button because the "?" 
> was also encoded. I changed it to only encode $fDomain.
> 
> After doing that: fix confirmed by testing.
> 
> vacation.php from trunk is not affected.
> 
>>> Input passed via fDomain POST parameter to create-domain.php  is
>>> not properly sanitised before being returned to the user.
>>> This is interesting because the fDomain variable is passed to
>>> strip_tags so something like on<a>click is trasformed to onclik.
>>> This allows to bypass browsers builtin XSS protection. To test
>>> this issue put the following string as Domain parameter in
>>> create-domain.php, submit the form and then click on Domain\'s
>>> input text..
>>> 
>>> dontcare\" oncli<a>ck=alert(document.cookie);//
> 
> Very interesting bug :-/
> 
> I commited a fix for it and also confirmed the issue and the fix by 
> testing.
> 
> edit.php from trunk (which replaces create-domain and many more) is not 
> affected.
> 
> 
> 
> Filippo, thanks for reporting these issues!
> 
> You can checkout the 2.3 branch with
>    svn co \  
>    https://postfixadmin.svn.sourceforge.net/svnroot/postfixadmin/branches/postfixadmin-2.3
> if you want to confirm the fixes yourself.
> 
> 
> 
> Regards,
> 
> Christian Boltz
> -- 
> my_hdr X-MSMail-Priority: Normal
> my_hdr X-Mailer: Microsoft Outlook Express 5.50.4133.2400
> my_hdr X-MimeOLE: Produced by Microsoft MimeOLE V5.50.4133.2400
> unset user_agent
> set attribution="----- Original Message -----\n\From: %n 
> <%a>\n\%t\n\Sent: %d\n\Subject: %s"
> ...und schon benutzt man OE. Mach das mal mit KMail. ;-))))
> [Andreas Kneib über mutt in suse-linux]
> 
> 
> ------------------------------------------------------------------------------
> Write once. Port to many.
> Get the SDK and tools to simplify cross-platform app development. Create 
> new or port existing apps to sell to consumers worldwide. Explore the 
> Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
> http://p.sf.net/sfu/intel-appdev
> _______________________________________________
> Postfixadmin-devel mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postfixadmin-devel

Re: [Postfixadmin-devel] Fwd: Vulnerabilities in postfixadmin 2.3.4

[Christian B. <pos...@cb...>]

Hello,

Am Dienstag, 10. Januar 2012 schrieb David Goodwin:
> I've fixed the problems in backup.php, edit-vacation.php (and
> template/smenu.php) and functions.inc.php
> 
> Can you confirm/test my changes please?

Done, see inline comments below.

We should release 2.3.5 in the next days to get those fixes out...

> Begin forwarded message:
> > From: Filippo Cavallarin <no...@so...>

> > 1) SQL injection in pacrypt function: if postfixadmin is
> > configured with \'mysql_encrypt\' the pacrypt function passes the
> > $pw parameter to sql query without santitzing it allowing non-
> > admin users to perform sql injection attacks.

Fix confirmed (by reading the diff)

We'll also need this fix in trunk.

> > 2) SQL injection in sql dump generated by backup.php: the
> > backup.php file generates sql queries without sanitizing values.
> > A non-admin user can inject arbitrary sql commands into backup
> > file that will be executed when an admin restores that backup. To
> > test this issue try to set the vacation message of any user to:

Issue and fix confirmed (by testing with the old and new version).

We'll also need this fix in trunk. (Unfortunately your commit included 
lots of whitespace changes, which makes the diff very hard to read.)

> > 3) Multiple XSS and lack of CSRF protection: I found several XSS
> > in postfixadmin code. I noted from postfixadmin homepage that you
> > planned to merge it  with Smarty wich could provide a good
> > protection against XSS and CSRF. 

Yes, see SVN trunk if you want to test it. 

The move to Smarty is done (with a small exception in fetchmail.php).

We are moving lots of things to PHP classes which reduces the code size 
and unifies the handling of forms, SQL queries etc. as much as possible. 
(This part is not completed yet.)

> > Input passed via domain GET parameter to edit-vacation.php  is not
> > properly sanitised before being returned to the user.
> > http://127.0.0.1/postfixadmin-2.3.4/edit-vacation.php?domain=dontc
> > are</script> <script>alert(1);</script>

Your fix urlencode()d $fCanceltarget, which was too secure ;-) - it 
resulted in a 404 error when clicking the "Exit" button because the "?" 
was also encoded. I changed it to only encode $fDomain.

After doing that: fix confirmed by testing.

vacation.php from trunk is not affected.

> > Input passed via fDomain POST parameter to create-domain.php  is
> > not properly sanitised before being returned to the user.
> > This is interesting because the fDomain variable is passed to
> > strip_tags so something like on<a>click is trasformed to onclik.
> > This allows to bypass browsers builtin XSS protection. To test
> > this issue put the following string as Domain parameter in
> > create-domain.php, submit the form and then click on Domain\'s
> > input text..
> > 
> > dontcare\" oncli<a>ck=alert(document.cookie);//

Very interesting bug :-/

I commited a fix for it and also confirmed the issue and the fix by 
testing.

edit.php from trunk (which replaces create-domain and many more) is not 
affected.



Filippo, thanks for reporting these issues!

You can checkout the 2.3 branch with
    svn co \  
    https://postfixadmin.svn.sourceforge.net/svnroot/postfixadmin/branches/postfixadmin-2.3
if you want to confirm the fixes yourself.



Regards,

Christian Boltz
-- 
my_hdr X-MSMail-Priority: Normal
my_hdr X-Mailer: Microsoft Outlook Express 5.50.4133.2400
my_hdr X-MimeOLE: Produced by Microsoft MimeOLE V5.50.4133.2400
unset user_agent
set attribution="----- Original Message -----\n\From: %n 
<%a>\n\%t\n\Sent: %d\n\Subject: %s"
...und schon benutzt man OE. Mach das mal mit KMail. ;-))))
[Andreas Kneib über mutt in suse-linux]

[Postfixadmin-devel] Fwd: Vulnerabilities in postfixadmin 2.3.4

[David G. <da...@co...>]

Hi,

I've fixed the problems in backup.php, edit-vacation.php (and template/smenu.php) and functions.inc.php

Can you confirm/test my changes please?

thanks
David.

Begin forwarded message:

> From: Filippo Cavallarin <no...@so...>
> Subject: Vulnerabilities in postfixadmin 2.3.4
> Date: 10 January 2012 15:22:09 GMT
> To: GingerDog <gin...@us...>
> 
> Hello,
> I would like to inform You about some vulnerabilities I discovered in postfixadmin 2.3.4. 
> 
> 1) SQL injection in pacrypt function: if postfixadmin is configured with \'mysql_encrypt\' the 
> pacrypt function passes the $pw parameter to sql query without santitzing it allowing non-
> admin users to perform sql injection attacks.
> 
> 
> 
> 2) SQL injection in sql dump generated by backup.php: the backup.php file generates sql 
> queries without sanitizing values. A non-admin user can inject arbitrary sql commands 
> into backup file that will be executed when an admin restores that backup. To test this 
> issue try to set the vacation message of any user to:
> 
> dontcare\',\'\',\'dominio.com\',\'2012-01-09 17:34:06\',\'1\');
> INSERT INTO admin (username,password,created,modified,active) 
> VALUES (\'so...@em...\',\'$1$2cab7a19$zIuOsr6PXksCu13883fVg/\',\'2012-01-08 
> 15:48:19\',\'2012-01-09 17:17:55\',\'1\'); #
> 
> then take a backup and restore it, the new admin so...@em... is added to admin 
> table.
> 
> 
> 
> 3) Multiple XSS and lack of CSRF protection: I found several XSS in postfixadmin code. I 
> noted from postfixadmin homepage that you planned to merge it  with Smarty wich could 
> provide a good protection against XSS and CSRF. BTW i report you some:
> 
> Input passed via domain GET parameter to edit-vacation.php  is not properly sanitised 
> before being returned to the user.
> http://127.0.0.1/postfixadmin-2.3.4/edit-vacation.php?domain=dontcare</script>
> <script>alert(1);</script>
> 
> Input passed via fDomain POST parameter to create-domain.php  is not properly 
> sanitised before being returned to the user.
> This is interesting because the fDomain variable is passed to strip_tags so something like 
> on<a>click is trasformed to onclik. This allows to bypass browsers builtin XSS protection.
> To test this issue put the following string as Domain parameter in create-domain.php, 
> submit the form and then click on Domain\'s input text..
> 
> dontcare\" oncli<a>ck=alert(document.cookie);//
> 
> 
> 
> Best,
> 
> Filippo Cavallarin
> 
> 
> --
> This message was sent to your SourceForge.net email alias via the web mail form.  You may reply to this message via https://sourceforge.net/sendmessage.php?touser=3692498
> To update your email alias preferences, please visit https://sourceforge.net/account

Re: [Postfixadmin-devel] Question re: changing maildir layout

[Tanstaafl <tan...@li...>]

On 2012-01-08 5:16 PM, Christian Boltz <pos...@cb...> wrote:
> Am Sonntag, 8. Januar 2012 schrieb Tanstaafl:
> Yes, you only have to change the maildir field (and rename the
> directories, of course).
>
> However I wouldn't edit the dump. Instead, I'd select username and
> maildir into a text file and use some sed magic to create update
> statements.
>
> But that's probably just a question of personal taste ;-)

Or skills... ;) but it only took a few minutes and its already done... 
thanks very much Christian for confirming this is all I need to change.

> BTW: The easiest way is to do nothing ;-)  The existing maildirs will
> keep their old naming (and still work), directories for new mailboxes
> will get the new naming scheme.

Yeah, but I'm a bit anal and like consistency... ;)

>> and changing the postfix/courier configs

> If you use the maildir field everywhere, you shouldn't have to change
> the config.

I don't have to change the query, no, but I neglected to mention, the 
previous guy had also set the maildir path to:

/var/virtual

and I prefer

/var/vmail

So I'll need to also change virtual_mailbox_base in postfix, and the 
MYSQL_HOME_FIELD in courier-imap after mv'ing the maildirs...

Anyway, many thanks, I should be able to pull the trigger on this tonight...

Re: [Postfixadmin-devel] Question re: changing maildir layout

[Christian B. <pos...@cb...>]

Hello,

Am Sonntag, 8. Januar 2012 schrieb Tanstaafl:
> What I was planning on doing was simply dumping the database, editing
> the .sql file to change the maildir field in the mailbox table is
> changed from 'us...@ex.../' to 'example.com/user/'...
> 
> Is that the *only* thing I need to change? I looked everywhere, and
> think that is the case, but would prefer to have confirmation before
> restoring this and then renaming the maildirs 

Yes, you only have to change the maildir field (and rename the 
directories, of course).

However I wouldn't edit the dump. Instead, I'd select username and 
maildir into a text file and use some sed magic to create update 
statements.

But that's probably just a question of personal taste ;-)


BTW: The easiest way is to do nothing ;-)  The existing maildirs will 
keep their old naming (and still work), directories for new mailboxes 
will get the new naming scheme.

> and changing the postfix/courier configs 

If you use the maildir field everywhere, you shouldn't have to change 
the config.

> (next on the list is changing couier-imap to
> dovecot, but one thing at a time)...

;-)


Regards,

Christian Boltz
-- 
>> Einmal im Jahr sorgen MS Produkte durch das Internet für
>> Milliardenschäden in der Wirtschaft
> 2000: Love Letter, 2001: Code Red und Nimda, 2003: Sapphire, 2002?
Windows XP.    [Eric Wick, Wolfgang Ewert und Urs Traenkner in dcsm]

[Postfixadmin-devel] Question re: changing maildir layout

[Tanstaafl <tan...@li...>]

Hi all,

I have inherited a system that has been in use for a long time. It had 
an old version of postfixadmin that I've already updated to the latest, 
but it also had these set:

$CONF['domain_path'] = 'NO';
$CONF['domain_in_mailbox'] = 'YES';

resulting in a maildir layout like:

/var/vmail/us...@ex.../

I want to change this to:

$CONF['domain_path'] = 'YES';
$CONF['domain_in_mailbox'] = 'NO';

to achieve a layout like:

/var/vmail/example.com/user/

What I was planning on doing was simply dumping the database, editing 
the .sql file to change the maildir field in the mailbox table is 
changed from 'us...@ex.../' to 'example.com/user/'...

Is that the *only* thing I need to change? I looked everywhere, and 
think that is the case, but would prefer to have confirmation before 
restoring this and then renaming the maildirs and changing the 
postfix/courier configs (next on the list is changing couier-imap to 
dovecot, but one thing at a time)...

Thanks,

Charles