PostgreSQL support for reporting

[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.

[ferz]

A few design notes on the PostgreSQL backend, from looking at PR #251 and the nestihacky fork.

Both translate the MySQL schema almost 1:1. That works, but a few points seem worth raising before a PG schema gets settled:

1. Partition messages by range on date (e.g. monthly), instead of relying on DELETE-based expiry. Retention then becomes DETACH/DROP PARTITION — instant, no table bloat, no autovacuum pressure on the hottest table. opendmarc-expire would only need plain DELETE on the small tables (requests, etc.).
2. Restore the secondary indexes. The existing PG translations keep only PK/UNIQUE and drop the non-unique KEY(date) / KEY(lastsent). Report queries filter on those ranges, so the indexes matter at volume.
3. Replace the INSERT + SELECT lastval() pattern. For the get-or-create dimension lookups (domains, ipaddr, reporters), use INSERT ... ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name RETURNING id — one round-trip, returns the id on both insert and conflict, and is concurrency-safe by design. For the messages → signatures chain, a writable CTE (WITH m AS (INSERT ... RETURNING id) INSERT INTO signatures SELECT m.id, ...) collapses the insert-then-loop into one statement. Note: ON CONFLICT, not MERGE — MERGE only gained RETURNING in PG 17, so on PG 16 it can't return the id without an extra SELECT, and it isn't a concurrency-safe upsert. ON CONFLICT works unchanged across 16–18.
4. Base the PG schema on the current MySQL schema, ARC tables included. The existing PG attempts predate ARC, so they'd ship incomplete against today's schema.mysql.
5. Minor: prefer GENERATED ALWAYS AS IDENTITY over SERIAL (current PG idiom), and note that the nestihacky expire has AND NOT lastsent = NULL, which is always false — it should be IS NOT NULL.

I'm happy for whoever is already working on the backend (PR #251, the develop work) to take whatever is useful here — these are design notes, not a claim on the implementation.

[thegushi]

Okay, at this point I've pushed most of the changes I've made up to the actual official "develop" branch. There's a CHANGES-2026-05.md (or very similar) file in the root of that dir that covers what we've done so far.

But as RFC9989/9990/9991 are out, I want to make sure that we're in compliance with that (fixing the bugs and crashes felt more urgent)? This may mean that new tables show up in the base version that you'll have to work around. So it might be good to wait until I land those. I'd hate for you to say "cool, done" and me to say "Well, actually...."