1 Topic by mcaffee

  • mcaffee
  • Member
  • Offline
  • Registered: 2010-02-01
  • Posts: 15

Topic: Idear: More than one encryptions for mysql

I have a idear for more than one encryptions from mysql.

This should be an example, and it can be very cool for some imports like me, but i use ldap not mysql.

-- encryption table --

CREATE TABLE IF NOT EXISTS `encryption` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(20) NOT NULL,
  `value` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `encryption` (`id`, `name`, `value`) VALUES
(1, 'Default (PLAIN MD5)', '{PLAIN-MD5}'),
(2, 'MYSQL Encrypt', '{CRYPT}'),
(3, 'Shadow Passwort', '{MD5}');

-- view table --

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_users` AS select concat(`users`.`user`,_latin1'@',`domains`.`name`) AS `email`,concat(`encryption`.`value`,`users`.`password`) AS `password` from ((`users` left join `domains` on((`users`.`domain_id` = `domains`.`id`))) left join `encryption` on((`users`.`encryption_id` = `encryption`.`id`)));

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Idear: More than one encryptions for mysql

Storing multiple encryption in SQL is possible, the point is how other components can use/manage them, such as dovecot, postfix and postfixadmin (or other web-based admin panel).

3 Reply by mcaffee

  • mcaffee
  • Member
  • Offline
  • Registered: 2010-02-01
  • Posts: 15

Re: Idear: More than one encryptions for mysql

ZhangHuangbin wrote:

Storing multiple encryption in SQL is possible, the point is how other components can use/manage them, such as dovecot, postfix and postfixadmin (or other web-based admin panel).

With the view, every can use this, i have test it, is no problem, but it should be implementet in the iredadmin and postfixadmin.

4 Reply by mcaffee

  • mcaffee
  • Member
  • Offline
  • Registered: 2010-02-01
  • Posts: 15

Re: Idear: More than one encryptions for mysql

ZhangHuangbin wrote:

Storing multiple encryption in SQL is possible, the point is how other components can use/manage them, such as dovecot, postfix and postfixadmin (or other web-based admin panel).

View phpmyadmin:

email                 password
dddl@xyz.cc             {CRYPT}152c3Bq1WoBTY
ddw3na@dd.cc              {PLAIN-MD5}f3539c609de16d99900ebf7fb3b17557
dasdf3@dee33.dd            {MD5}$1$ZZFI7q8R$XTQKdl4u4elwAgH.yH4Tc.

Dovecot:
password_query = SELECT email as user, password FROM view_users WHERE email='%u';

Postfix:
virtual-mailbox.cf
query = SELECT email FROM view_users WHERE email='%s';

This works very well on a old system.

5 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Idear: More than one encryptions for mysql

Yes, i meant you have to make postfixadmin support this.

6 Reply by mcaffee

  • mcaffee
  • Member
  • Offline
  • Registered: 2010-02-01
  • Posts: 15

Re: Idear: More than one encryptions for mysql

ZhangHuangbin wrote:

Yes, i meant you have to make postfixadmin support this.

We can develope a patch for this and implement it in iredadmin.

Thanks.

7 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Idear: More than one encryptions for mysql

iRedAdmin-Pro for MySQL edition is not finished yet, but i will consider adding this feature in it, it's really useful.

My concern:

Patch PostfixAdmin should be OK, but we should maintain this patch or push it into PostfixAdmin official respository. If you can't maintain it in a long time, iRedMail users will go crazy because they don't have a FREE web-based admin panel after we change dovecot/postfix settings.

This is why i didn't change default SQL structure in iRedMail till now, it's based on PostfixAdmin. The default SQL structure is not so good (just IMO), and i will try to modify/improve it after iRedAdmin-Pro for MySQL edition is finished.

8 Reply by mcaffee

  • mcaffee
  • Member
  • Offline
  • Registered: 2010-02-01
  • Posts: 15

Re: Idear: More than one encryptions for mysql

ZhangHuangbin]iRedAdmin-Pro for MySQL edition is not finished yet, but i will consider adding this feature in it, it's really useful.

That sounds very good, if i can help you, please inform me.

My concern:

Patch PostfixAdmin should be OK, but we should maintain this patch or push it into PostfixAdmin official respository. If you can't maintain it in a long time, iRedMail users will go crazy because they don't have a FREE web-based admin panel after we change dovecot/postfix settings.

The main way should be the push into ProstfixAdmin repository, i can maintrain this the next years, this would not be a problem.

This is why i didn't change default SQL structure in iRedMail till now, it's based on PostfixAdmin. The default SQL structure is not so good (just IMO), and i will try to modify/improve it after iRedAdmin-Pro for MySQL edition is finished.

OK, i will push this up.

9 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Idear: More than one encryptions for mysql

mcaffee wrote:

The main way should be the push into ProstfixAdmin repository, i can maintrain this the next years, this would not be a problem.

I guess PostfixAdmin guys won't accept this patch, because all exist users should change their SQL structure, it's a big change.
The best way is improving iRedMail and patching postfixadmin, maintaining this patch yourself.

10 Reply by mcaffee

  • mcaffee
  • Member
  • Offline
  • Registered: 2010-02-01
  • Posts: 15

Re: Idear: More than one encryptions for mysql

ZhangHuangbin wrote:
mcaffee wrote:

The main way should be the push into ProstfixAdmin repository, i can maintrain this the next years, this would not be a problem.

I guess PostfixAdmin guys won't accept this patch, because all exist users should change their SQL structure, it's a big change.
The best way is improving iRedMail and patching postfixadmin, maintaining this patch yourself.

But, if some would use the patch can use this and others not, i will contact the guys, and try to find a way. If i have some informations, i will post this.