How can I configure CipherMail to use an external DB server?
We are looking to replicate PSQL using Postgres-XC on our database servers, and offload that duty from the mail server. Any experience with this?
~ Laz Peterson
Paravis, LLC
Ohh, nevermind. I exported the database using the djigzo user, then restored it on the remote server. After that, I edited the conf/hibernate.cfg.xml file and changed the remote database there.
All sorted out!
But if anyone has experience using Postgres-XC and CipherMail, that would be good info to have.
~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201
···
On Jun 29, 2015, at 1:48 PM, Laz C. Peterson <laz(a)paravis.net> wrote:
How can I configure CipherMail to use an external DB server?
We are looking to replicate PSQL using Postgres-XC on our database servers, and offload that duty from the mail server. Any experience with this?
~ Laz Peterson
Paravis, LLC
_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users
Zitat von "Laz C. Peterson" <laz(a)paravis.net>:
Ohh, nevermind. I exported the database using the djigzo user, then
restored it on the remote server. After that, I edited the
conf/hibernate.cfg.xml file and changed the remote database there.All sorted out!
But if anyone has experience using Postgres-XC and CipherMail, that
would be good info to have.
No experience with Postgres-XC but be sure to use a encrypted
connection if using a remote database as the database contains
sensitive information.
Regards
Andreas
Yes, all communication goes through encrypted tunnels.
Our goal is to have high availability for all services running on databases, but PostgreSQL seems to be a tough nut to crack.
~ Laz Peterson
Paravis, LLC
···
On Jun 30, 2015, at 2:48 AM, lst_hoe02(a)kwsoft.de wrote:
Zitat von "Laz C. Peterson" <laz(a)paravis.net>:
Ohh, nevermind. I exported the database using the djigzo user, then restored it on the remote server. After that, I edited the conf/hibernate.cfg.xml file and changed the remote database there.
All sorted out!
But if anyone has experience using Postgres-XC and CipherMail, that would be good info to have.
No experience with Postgres-XC but be sure to use a encrypted connection if using a remote database as the database contains sensitive information.
Regards
Andreas
_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users
Zitat von "Laz C. Peterson" <laz(a)paravis.net>:
Yes, all communication goes through encrypted tunnels.
Our goal is to have high availability for all services running on
databases, but PostgreSQL seems to be a tough nut to crack.
Why?
https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
Never used it because we are not really 24x7 but it looks like there
are a lot of options to do hot/warm/cold standby/replication whatever.
Regards
Andreas
It turned out be a lot more difficult than I had expected. Beyond my intelligence level. Ha ha ha … I am so much more familiar with MySQL and have years of experience with replication, so that definitely is a better option for me.
~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201
···
On Jun 30, 2015, at 7:51 AM, lst_hoe02(a)kwsoft.de wrote:
Zitat von "Laz C. Peterson" <laz(a)paravis.net>:
Yes, all communication goes through encrypted tunnels.
Our goal is to have high availability for all services running on databases, but PostgreSQL seems to be a tough nut to crack.
Why?
Replication, Clustering, and Connection Pooling - PostgreSQL wikiNever used it because we are not really 24x7 but it looks like there are a lot of options to do hot/warm/cold standby/replication whatever.
Regards
Andreas
_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users
Heyho Laz!
Quoting "Laz C. Peterson" <laz(a)paravis.net>:
It turned out be a lot more difficult than I had expected.
me2! 
May you send me your MaraDB database scheme?
ttyl
Django
Supporting MySQL (MariaDB) requires a few minor changes to the code. The
database schema generated by Hibernate (when using the MySQL dialect)
contains a number of statements which MySQL does not like. For example
the database name 'blob' and the column name "index" are reserved names
for MySQL (not for Postgres). Also MySQL does not support long strings
in indexes (see for more info
sql - MySQL error: key specification without a key length - Stack Overflow).
Hibernate also generates TINYBLOB for byte arrays but these are too
small for most binary data (certificates etc.) so these must be changed
to LONGBLOB. Because Hibernate generates a faulty database scheme, all
unit tests that are using databases will fail because the unit tests
will for each test clear the database and generate a fresh database
using the automatically generated database scheme. With MySQL this will
therefore not work because the scheme is not correct.
Changing the Blob table name, quoting the index column name, replacing
text with varchar and removing some indexes fixes the issues for MySQL.
However this results in a database schema change which then impacts all
the existing CipherMail databases. Another option would be to use a
different database naming strategy only for MySQL but this requires
extensive testing. The tests can be changed to directly import sql
instead of using the Hibernate tools but since there are a lot of tests
it might be a lot of work.
The backup/restore functionality depends on Postgres so the
backup/restore will not work unless the backup/restore script is
modified to support MySQL. Even though in principle MySQL (and other
databases) can be supported, it's probably not something we are going to
support unless there is a good business reason to do so. Changing the
code to support MySQL is not the biggest problem. Maintaining it is
something else. Testing the gateway on all supported flavours of Linux
is already a lot of work. If we add MySQL to the mix, testing and
maintaining becomes even harder.
Similar to other products we have a "black-box" version of our gateway.
The virtual appliance is a fully tested solution and what kind of
database it uses should in principle be just an implementation detail.
Because we standardize on a number of products for the virtual appliance
we can maintain a high level of reliability.
To sum, we have it working on MySQL and in principle it's doable. But
unless there is a good business reason to support MySQL we will not
officially support it.
I can give you the information on the changes to be made to get it
working with MySQL.
Kind regards,
Martijn Brinkers
···
On 07/01/2015 10:51 AM, django(a)nausch.org wrote:
Quoting "Laz C. Peterson" <laz(a)paravis.net>:
It turned out be a lot more difficult than I had expected.
me2!
May you send me your MaraDB database scheme?
--
CipherMail email encryption
Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.
Twitter: http://twitter.com/CipherMail
HI Laz!
may you can take a look into my mysql-database-structure (attached file).
ttyl
Django
djigzo_mysql_table_structure.sql (11.8 KB)
Yes Martijn, I definitely understand about the solid reasons not to take on the major task of maintaining something that has no valid purpose in your business.
Let me ask you this … (Because this is the reason behind my interest.) Do you have any clients or CipherMail/Djigzo installations that are either clustered, configured for high availability or possibly replicating configurations/certificates across multiple sites in a live-live configuration? (Not a live-standby.)
Since all of our mail goes through your gateway, it does become a single point of failure. I guess we could set a mapping in Postfix to filter out the subject line tag for outgoing and incoming email, but that is a little beyond my capabilities and doesn’t necessary guarantee a CipherMail-based correspondence will be properly processed. Or maybe I am just overcomplicating things too much!
I do currently have two CipherMail installations now configured to share the same PostgreSQL database. The original goal was to have a bidirectionally replicated PostgreSQL, but I’ve got to do a lot more testing and that’s at least a half-dozen VM’s down the road (ha ha). MySQL replication is very well done for databases that aren’t extremely active with hundreds of thousands or millions of transactions going on. So that was my thought …
Again, I do agree with you that it is a lot of work to maintain something new that is outside of the given standard (ex, CipherMail appliance). And with any modifications to the existing version to support MySQL, the same would have to be done on my end to support MySQL in new versions. But yes I would love to see your notes if you don’t mind.
I looked at the datatype conversion tables supplied by MySQL for PostgreSQL migration. That was giving me a little bit of an issue at first, with the datatypes you mentioned.
All in all, this was simply my approach to build a HA cluster of CipherMail. I have no problem with PostreSQL, just my poor little brain likes to live in its comfort zone. 
~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201
···
On Jul 1, 2015, at 4:49 AM, Martijn Brinkers <martijn(a)ciphermail.com> wrote:
On 07/01/2015 10:51 AM, django(a)nausch.org wrote:
Quoting "Laz C. Peterson" <laz(a)paravis.net>:
It turned out be a lot more difficult than I had expected.
me2!
May you send me your MaraDB database scheme?
Supporting MySQL (MariaDB) requires a few minor changes to the code. The
database schema generated by Hibernate (when using the MySQL dialect)
contains a number of statements which MySQL does not like. For example
the database name 'blob' and the column name "index" are reserved names
for MySQL (not for Postgres). Also MySQL does not support long strings
in indexes (see for more info
sql - MySQL error: key specification without a key length - Stack Overflow).
Hibernate also generates TINYBLOB for byte arrays but these are too
small for most binary data (certificates etc.) so these must be changed
to LONGBLOB. Because Hibernate generates a faulty database scheme, all
unit tests that are using databases will fail because the unit tests
will for each test clear the database and generate a fresh database
using the automatically generated database scheme. With MySQL this will
therefore not work because the scheme is not correct.Changing the Blob table name, quoting the index column name, replacing
text with varchar and removing some indexes fixes the issues for MySQL.
However this results in a database schema change which then impacts all
the existing CipherMail databases. Another option would be to use a
different database naming strategy only for MySQL but this requires
extensive testing. The tests can be changed to directly import sql
instead of using the Hibernate tools but since there are a lot of tests
it might be a lot of work.The backup/restore functionality depends on Postgres so the
backup/restore will not work unless the backup/restore script is
modified to support MySQL. Even though in principle MySQL (and other
databases) can be supported, it's probably not something we are going to
support unless there is a good business reason to do so. Changing the
code to support MySQL is not the biggest problem. Maintaining it is
something else. Testing the gateway on all supported flavours of Linux
is already a lot of work. If we add MySQL to the mix, testing and
maintaining becomes even harder.Similar to other products we have a "black-box" version of our gateway.
The virtual appliance is a fully tested solution and what kind of
database it uses should in principle be just an implementation detail.
Because we standardize on a number of products for the virtual appliance
we can maintain a high level of reliability.To sum, we have it working on MySQL and in principle it's doable. But
unless there is a good business reason to support MySQL we will not
officially support it.I can give you the information on the changes to be made to get it
working with MySQL.Kind regards,
Martijn Brinkers
--
CipherMail email encryptionOpen source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.Twitter: http://twitter.com/CipherMail
_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users
HI Laz,
Quoting "Laz C. Peterson" <laz(a)paravis.net>:
Since all of our mail goes through your gateway, it does become a
single point of failure.
That's right!
I do currently have two CipherMail installations now configured to
share the same PostgreSQL database.
Me 2. 
Here we have 4 fronted MX and will setup two
ciphermail-gateways on different location. They should use our
redundant MariaDB cluster.
All in all, this was simply my approach to build a HA cluster of CipherMail.
yes!
I have no problem with PostreSQL,
I have a major theme with postgresql, 'cause it's not allowed, only
oracle or MariaDB.
ttyl
Django