I'm running OpenTAXII with a PostgresQL backend in the same VM. The
Contos 7 VM was configured with Europe/Amsterdam (as of writing
CEST/UTC +2) as timezone.
After I pushed some Content Blocks to the TAXII server, I found that
the timestamps in the database were consistently two hours off.
The problem:
Some researching later, I think I found the cause of this.
If the TAXII content does not supply any timestamp,
opentaxii.persistence.sqldb.models.py uses datetime.utcnow as default
value for the timestamps. As per https://docs.python.org/3/library/datetime.html#datetime.datetime.utcnow, utcnow does not have any timezone
information.
The UTC timestamp was send to the database. Because there is no
timezone information, postgres interprets the timestamp as local time
(the database was also configured for Europe/Amsterdam), which causes
the two hour offset.
Steps to reproduce:
1) Start a postgres database with non-UTC timezone (e.g. $ docker run --rm --env TZ=Europe/Amsterdam postgres:latest)
2) Configure opentaxii to use that database
3) Run opentaxii
4) Push some STIX content to the TAXII server
5) Check for the TAXII ContentBlocks in the content_blocks table of the
database. At the very least, the date_created column should have a
timestamp that is not correct.
Proposed solution:
Include timezone information in the timestamps send to postgres so that
postgres won't interpret the timestamps as local time.
Workaround:
For now, I have changed the timezone of the database to UTC. For now,
the timestamps are inserted correctly, but it would be nice if this can
be fixed.
Context:
I'm running OpenTAXII with a PostgresQL backend in the same VM. The Contos 7 VM was configured with Europe/Amsterdam (as of writing CEST/UTC +2) as timezone.
After I pushed some Content Blocks to the TAXII server, I found that the timestamps in the database were consistently two hours off.
The problem:
Some researching later, I think I found the cause of this. If the TAXII content does not supply any timestamp, opentaxii.persistence.sqldb.models.py uses datetime.utcnow as default value for the timestamps. As per https://docs.python.org/3/library/datetime.html#datetime.datetime.utcnow, utcnow does not have any timezone information.
The UTC timestamp was send to the database. Because there is no timezone information, postgres interprets the timestamp as local time (the database was also configured for Europe/Amsterdam), which causes the two hour offset.
Steps to reproduce:
1) Start a postgres database with non-UTC timezone (e.g.
$ docker run --rm --env TZ=Europe/Amsterdam postgres:latest) 2) Configure opentaxii to use that database 3) Run opentaxii 4) Push some STIX content to the TAXII server 5) Check for the TAXII ContentBlocks in the content_blocks table of the database. At the very least, the date_created column should have a timestamp that is not correct.Proposed solution:
Include timezone information in the timestamps send to postgres so that postgres won't interpret the timestamps as local time.
Workaround:
For now, I have changed the timezone of the database to UTC. For now, the timestamps are inserted correctly, but it would be nice if this can be fixed.