PostgreSQL support

[Ingo Theiss]

Hi,

it would be great if mailpiler would support more database engines than just MySQL. Are the any toughts on this in the near future?

Regards

Ingo

[Janos SUTO]

well, mysql is pretty much wired to piler. Anyway it's possible to add postgresql support, however it requires rewriting several things. I think the next stable release (0.1.23) will be released in a few weeks, and then we can work on postgresql support.

BTW may I know why do you need a different sql server?

[Ingo Theiss]

wow, damn fast response :-)

Sure I could simply install and run a MySQL server. But in my case I have PostgreSQL server up running and it´s used by more than 10 different applications (CMS, Webmail, Dspam, etc.). It´s not really reasonable for people to run two database servers.

I think mailpiler is a great project (I came across by an article in a german IT-magazine) and the project would definitly benefit from a broader audience if it will not be limited to one specific database.

My C programming skills a very limited otherwise I would contribute to this. Maybe I can contribute to the PHP part of mailpiler.

Probably you already know, but here is a link http://libdbi.sourceforge.net/ to a database-independent abstraction layer in C.

[Janos SUTO]

Indeed, it doesn't make sense to run 2 different SQL servers, even though ideally piler runs on its own archiving box where nothing interferes with it.

Anyway I'll take a look at the libdbi project, and see where to go next. Btw. could you please provide a link to a IT-magazine where you saw piler? Thank you.

[Ingo Theiss]

Piler was part of a comparison publish in the the german IT-magazine called Linux Magazin". To bad the article isn´t public available. This is the Link to the article Mailarchivierung but there is nothing to see or read about Piler (you have to buy the article).

[Janos SUTO]

Thanks, I visited their website, and inquired them whether they can make an English translation. I've checked sphinx, and luckily it supports pgsql, so it's possible to rewrite piler to support it.

Here's the deal: let me finish 0.1.23, and then I'll see how to add pgsql support to piler.

[Ingo Theiss]

thanks and don´t put yourself into to much trouble porting piler to support postgresql. I am just a lonely citizen :-)

[Janos SUTO]

OK :-) I've checked libdbi, and it looks usable, however there are some issues:

• libdbi doesn't support prepared statements
• it seems that it hasn't been updated for a while. I hope that it's not abandoned

Anyway these issues above are not showstoppers for pgsql support, perhaps I'll pick another idea. I'll let you know when a pgsql enabled prototype is available.

[Janos SUTO]

Just an update on my progress. I rewrote the sql manipulating code in piler to use some sort of abstraction. Now I'm testing the code for a while. If it works as expected, then I'll add the pgsql specific code. It would be great if then you could test it.

[Ingo Theiss]

Hi. Great news! Just drop me a note when you are ready and I am happy to test the new code.

[John Jay]

Hi Guys,

i would also love to test the PostgreSQL Code. Thanks!

Kind Regards from Germany, john

[LinuxMagic]

Just thought let you know, with 1.2.4, when specifying database=psql, configure is successful, but make fails ..

In file included from ./piler.h:20:0, from dirs.c:13: ./sql.h:11:55: error: unknown type name ‘MYSQL_STMT’ ./sql.h:13:46: error: unknown type name ‘MYSQL_STMT’ ./sql.h:14:49: error: unknown type name ‘MYSQL_STMT’ ./sql.h:15:21: error: unknown type name ‘MYSQL_STMT’ ./sql.h:16:21: error: unknown type name ‘MYSQL_STMT’ ./sql.h:18:24: error: unknown type name ‘MYSQL_STMT’ ./sql.h:19:25: error: unknown type name ‘MYSQL_STMT’ ./sql.h:20:31: error: unknown type name ‘MYSQL_STMT’ dirs.c: In function ‘createdir’: dirs.c:59:18: warning: ignoring return value of ‘chown’, declared with attribute warn_unused_result [-Wunused-result] make[1]: *** [dirs.o] Error 1

It appears at first glance that the are defined only (defs.h) if specifying MYSQL It (sql.h) is included in piler.h, (and others)

Are you calling psql supported yet?

[Janos SUTO]

No, I don't call it supported. Before releasing 0.1.24 I was working on an sql wrapper function to support both mysql and psql. The mysql part is ready, however I encountered problems with psql queries, so this project is currently stalling, and I'm focusing on other features. Perhaps later will continue.

[Janos SUTO]

I apologize to have this issue open for so long. However currently I don't plan to add postgresql support, so I set this issue to "on hold".

[Pa Wa]

Hello,

some time has passed since. The development of PostgreSQL has been very extensive in between.

Is there any possibility to get mailpiler working together with PostgreSQL?

[eXtremeSHOK]

Why?

PgSQL will not offer any major benefits over mysql besides halving the development and testing time available.

[Pa Wa]

Why? Basically the same reason as the original issue reporter has stated, that running two dbs on one system makes no sense.

The other main reason, albeit somewhat unusual, is SEPostgreSQL/sepgsql. Running SELinux with mls, using the security labels, the reach of 'security' has to go as far as it needs to be. That is, if you will, to fulfil administrative regulations - apart from mandatory retention of emails, the whole security aspect of it has to be tighten up as restrictive as possible - and since this scenario is already in place (sepgsql), switching to MySQL/MariaDB breaks the whole point of a multi level security system. The security aspect of the administrative regulations, at least in Germany, asks for greater measures to be put in place in regard of business communication, especial the digital one.

One can go further and haul out the whole PgSQL vs MySQL/MariaDB thing - at least in 2016 it can make sense.

And to avoid 'vendor' (db) lock-in.

[Janos SUTO]

Sorry, the conclusion is that pgsql won't be supported. Though I see your point in terms of increased security, I still believe that mysql (and it's derivatives) are excellent rdbms for piler. Moreover extremeshok also has a point on development and testing. However, if you provided some clues about German regulations what specific greater measures they require, then perhaps I could come up with something acceptable. Surely, those regulations must allow other rdbms without selinux support as well.

[Pa Wa]

Hello Janos,

thank you for your reply.

Since 2015 previous regulation is renewed. Anything concerning the business communication (as well as bookkeeping) has to be retained in an audit safe manner for audit purposes - that itself is not new.

What seems to be rather new, are requirements of security features. Since now bookkeeping has to be archived the same ways as emails, according to previous regulation (prior 2015).

It is now required to have

• access controls, based on according access concepts,
• logging (error messages, validity checks)
• safety measures against intended and not intended manipulation of programs, data and documents.

The regulations do not state specific measures, or how this has to be achieved (some aspect aren't realistic at all). Although it skirts a little bit the program retaining data, it seems to place great emphasis on the security architecture as a whole.

However, the scenario I presented (SELinux multi level security + PostgreSQL Security labels) allows me to set up according access controls with extensive auditing capabilities (i.e. logging). I surely can confine MySQL/MariaDB in SELinux, the auditing will however stop and go not as deep as it would be the case with PgSQL with sepgsql.

I understand your position - that an additional implementation of PgSQL simply costs time and work while manpower and time is short.

But maybe after some two years since the last activity on this topic things might have changed, I had to ask about it.

In a previous answer by you on this issue, you've written, that you have encountered problems with psql queries. Do you remember what those problems were?

[Janos SUTO]

Thank you for the clarification. Well, I when this topic was opened I wanted to create an SQL wrapper to support both mysql and pgsql. The wrapper itself has been created, and supports mysql, however the way pgsql works is very different from mysql API, so I had a hard time to make it, and then I gave it up. I was searching for such an sql wrapper library, and there are some, however none of them was for C (note that piler uses prepared statements, so it must be supported by any viable solution). So the bottomline is that I simply don't have the resources to start it again in the forseeable future.

However, I also understand your position, and perhaps you know someone who can do the heavy lifting, and donate the code to the project. So if it were available, then I'd use it with piler. In a nutshell: it must support recent versions of mysql (and it's derivatives), and postgresql. It must support both prepared statements and "ordinary" sql queries. Finally, it must be usable from a C code.

[Pa Wa]

Unfortunately, I am not a developer.

I see libdbi was mentioned (that is dead). Have you came across libzdb, perhaps?

http://tildeslash.com/libzdb https://bitbucket.org/tildeslash/libzdb

[Janos SUTO]

No, not yet. However, it looks promising. I'll give the libzdb project a try. I'll keep you posted.

[Pa Wa]

There is also OpenDBX:

https://www.linuxnetworks.de/doc/index.php?title=OpenDBX

However, Prepared Statements Interface is scheduled for the 1.5 release, while the latest stable is 1.4.6.

[eXtremeSHOK]

OpenDBX is a dead project... and has issues with MySQL.

Last code commit was 2010-03-27

As to your audit requirements, enable the mysql transaction log and keep those records.