search

gutmensch / docker-dmarc-report

244 stars 27 forks source link

Document / automate database creation #1

Closed elonen closed 4 years ago

elonen commented 6 years ago

The techsneeze/dmarcts-report-parser script seems to contain code for creating tables if they are missing, but I always see Query failed: Table 'dmarc.report' doesn't exist (Error #1146) when browsing the Docker service address.

Is there something that needs to be done to make it create the tables, and if it simply doesn't work, maybe document the required table schema?

gutmensch commented 6 years ago

Well, yes, the parser creates the tables automatically, but the db host, db user, db password you have to create beforehand manually and then inject it into the env of the container as described in the README - did you do that? of course you could fork this image and just create one which includes an "empty root pw" mysql db and go from there, but at least I do already have a mysql running in another container where I setup the db, user, pw accordingly.

gutmensch commented 6 years ago 
mysql> show grants for 'dmarc_report'@'%';
+----------------------------------------------------------------+
| Grants for dmarc_report@%                                      |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dmarc_report'@'%'                       |
| GRANT ALL PRIVILEGES ON `dmarc_report`.* TO 'dmarc_report'@'%' |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)
elonen commented 6 years ago

Yes, before I made sure it creates the database and the user, error message showed something like "no permission", instead of Table 'dmarc.report' doesn't exist. If it's supposed to auto-create the tables, guess I'll need to debug further.

dabide commented 5 years ago

Apparently, the tables aren't created until the first DMARC report arrives.

lgg42 commented 4 years ago

Apparently, the tables aren't created until the first DMARC report arrives.

Yep, can confirm. I had the same error, manually running the cron command created the table and everything.

gurumark commented 4 years ago

@dabide @lgg42 @gutmensch @elonen Any update on this issue?

gutmensch commented 4 years ago

So, I don't exactly know how I could fix this.

  1. If we create the database with an init step in Docker beforehand, we possibly diverge from the original source code (parser and viewer), which I do not prefer.
  2. The doc already states to create the DMARC dns entry first, it might take up to 24h until you get your first report (or not even then if no mail volume has happened), which would then create the database tables as proposed.

It would be probably the easiest to contribute an "init" command to the original parser source code (link is also in the README.md) or if somebody contributes a patch for the perl file here, which we can use in the Dockerfile build, so that we would see if upstream changes and build fails.

Personally I'm really bad at Perl, so I would leave this up to a contributor. I'll gladly add someone to the project with write permissions.

gurumark commented 4 years ago

I was able to run the cron and create all the missing tables after tweaking MySql a little. So I would not worry about this issue too much. After all the tables are created, it started to pull the xml files from gmail. Everything is looking good now.