feature: PostgreSQL support

[r10r]

Hi there,

we want to run OpenDMARC with PostgreSQL instead of MySQL because of licensing issues.
Is anyone out there who has done this already ?

Thanks
Ruben

[tuxmaster5000]

It looks like the source code can support multiple back end's, but I only find an schema definition for MySQL/MariaDB.

[sparked435]

I also would prefer something other than MySQL. I'm already running Postgres; SQLite would be more than enough for a small site like mine (just a few hundred inbound emails/day.)

Converting the schema file to Postgres looks near trivial, I could probably do that within an hour or so. The bigger problem is the utilities: despite using the database-agnostic DBI library, they appear to stick exclusively to the MySQL dialect for the SQL calls themselves. Sorting this out is a lot heavier lift and my Perl is rusty.

[randolf]

I also would prefer something other than MySQL. I'm already running Postgres; SQLite would be more than enough for a small site like mine (just a few hundred inbound emails/day.)

Converting the schema file to Postgres looks near trivial, I could probably do that within an hour or so. The bigger problem is the utilities: despite using the database-agnostic DBI library, they appear to stick exclusively to the MySQL dialect for the SQL calls themselves. Sorting this out is a lot heavier lift and my Perl is rusty.

I may be able to help with this. I work with Perl's DBI and PostgreSQL almost every day, so please let me know what you've got so far so that I can just focus on what's left without re-inventing what you've already done.

[sparked435]

so please let me know what you've got so far so that I can just focus on what's left without re-inventing what you've already done.

I've done nothing but look at the code, don't let me stop you from proceeding.

[randolf]

I've done nothing but look at the code, don't let me stop you from proceeding.

Thanks! I just finished writing my PostgreSQL code, and I submitted it as a pull request: #251

Since you've been looking at the code, if you could take a look at what I've written I'd appreciate it -- I tried my best to get everything the way it needs to be, but I won't be surprised if improvements will be contributed by others. Also, a few columns are in need of documentation.

[randolf]

Many Pull Requests over the past 12+ months have been submitted by many different contributors, but it appears they haven't been dealt with, and I'm feeling concerned. Does anyone know if the original organizers are intending to continue work on this project?

I recently started an issue regarding my concern: #253

Hopefully further development can continue in the near future. (Thanks.)

[thegushi]

FreeBSD just pushed a commit into their ports tree to make opendmarc work with mariaDB, and I just approved a pull request to support mariadb (the perl modules are slightly different). I believe I commented on your postgres pull request as well, but I think we'd need to modify the scripts as well, as they presently use DBD::Mysql, and might need to modularize them (or perhaps, just ship a different set of scripts).

I would consider adding postgresql support to be something that would happen in a 1.5.0 branch versus a 1.4.2, but putting schema files in for people to test against is not a terrible idea.

[thegushi]

As a follow-up to my earlier comment: I've done a more detailed audit of the SQL portability now that I'm actively working on the reporting scripts. The DBI layer is fine but the SQL itself has several MySQL-specific dependencies that would need addressing for PostgreSQL support:

• FROM_UNIXTIME() and UNIX_TIMESTAMP() -- PostgreSQL equivalents are to_timestamp() and EXTRACT(EPOCH FROM ...)
• LAST_INSERT_ID() -- would need restructuring to use RETURNING id, since it's not a drop-in swap
• DATE_SUB(..., INTERVAL ? SECOND) -- slightly different interval syntax
• Schema: AUTO_INCREMENT -> SERIAL, TINYINT -> SMALLINT, INT UNSIGNED -> INT

The schema conversion is straightforward. The scripts are the heavier lift, primarily because of the insert-then-fetch-id pattern used throughout -- that's the one that requires more than a function name substitution.

I've had a look at randolf's PR #251 and it's a solid starting point. PostgreSQL support is a post-next-release target but it's on the radar. Patches welcome in the meantime.

[thegushi]

In all honesty, the more likely answer would be to fork the reporting scripts into postgresql and maria/mysql variants, which if you want to do that, is an easy pull-in for me.

[thegushi]

Okay, I've got some fun answers here for you, after our conversations in email. I'm reworking a lot of the reporting stuff for various reasons. Some of it is just incomplete fields and rfc compliance stuff, but also, I really feel like reporting was tacked on at the last minute here.

1. I'm making it so that opendmarc-reports can also send the forensic reports. What that means is that all the other enhancements I'm adding for sending aggregates (ability to use an alternate SMTP server and port, ability to do SMTP auth, ability to look up domains that bounce, ability to do VERP, etc are all shared by both report-types. (This is DB agnostic, the DB isn't used in forensic mode).

2. Instead of having to staple three different cron jobs together (and guess about when they'll complete), I'm shipping an opendmarc-run wrapper script, which not only runs the log rotation/history-to-db-import/send-reports, but has a -suffix knob in it so that by default opendmarc-reports speaks mysql (as it always has) but if you set the suffix to -pg it'll call opendmarc-reports-pg, which means anyone porting to a different engine can just work in parallel.

3. I'm making it so that both opendmarc-run (a shell script) and opendmarc-reports (perl) can read in a config file (not the same one), so if you've installed this via your favorite package manager, you don't have to hard-edit the scripts.

4. I've gone through the code and commented anything mysql specific (as above) with what I think the approprate pg answer might be.

5. It occurs to me that if you're running a milter, you maybe REALLY don't want to spin up a whole DBMS for what's basically a temporary database, so there's one more thing I think is worth supporting: SQLite. These abstractions would make that easy to do. (And the SQLite code could init the DB on its own, versus needing an initial setup step).

[thegushi]

If you want to see how it works, go clone https://github.com/thegushi/OpenDMARC/tree/integration/fixes -- things should be fairly well documented there.