CockroachDB time calculations depend on zoneinfo format

A database binary does not contain every input to its time-zone calculations. CockroachDB's October 29 advisory A-56116 identifies incorrect local date-time calculations when the host supplies slim-format zoneinfo files. The defect is in the Go runtime's interpretation of daylight-saving information, and the advisory applies to any CockroachDB version running with the affected operating-system data.

Deployment packaging therefore matters more than the SQL server version alone. The advisory says official CockroachDB Docker images, the reference Kubernetes configurations and CockroachCloud are unaffected. An independently assembled operating-system image can have a different zoneinfo source even when it runs the same database release. That makes the runtime's data-loading environment part of the correctness investigation.

I accept the costs of distributed consistency in CockroachDB because they have a defined purpose. Time-zone conversion presents a different correctness dependency. Agreement about a stored instant does not ensure that a host's civil-time rules are interpreted correctly. A database can preserve its transaction guarantees while a conversion expression computes the wrong local representation.

The Go report identifies a concrete environment

The Go issue linked by the advisory was opened on October 22. Its report describes a compiled Go program running in an Alpine image with tzdata installed. Europe/Berlin calculations could use daylight-saving information inappropriate for dates after the October transition. The report contrasts that environment with use of embedded zoneinfo when the operating-system package was absent.

This is useful evidence about the dependency without implying that every Alpine image or every named zone fails identically. The advisory supplies the CockroachDB assessment and remediation. The runtime report explains why changing a packaging input can alter calculations without an application SQL change.

It also explains why a process can produce a wrong result without reporting that its zone data is unreadable. The problem is interpretation of available data, rather than necessarily a missing file. A successful location lookup is therefore weaker evidence than a calculation that checks the expected offset for an affected time period.

Named locations contain transition rules

The Go 1.15.3 location implementation represents a location using zone information and transition times. It also permits an extension string describing daylight-saving transitions beyond the explicitly recorded transitions. Location lookup can consult a cached interval, search recorded transitions or use that extension information.

Those mechanisms explain why a named zone is more than a fixed numeric offset. The applicable offset depends on the instant being interpreted and on the available transition rules. Substituting one constant offset for a geographical zone would change the question the application asks, rather than generally repairing the interpretation of that zone's rules.

The zoneinfo reader in the same runtime reads transition arrays, offsets and daylight-saving flags. It also loads an extension string from the data and initializes a cache for the current time. The database's dependency is consequently on both those data structures and the runtime logic that interprets them.

For an operational explanation, it is enough to identify that dependency accurately. The advisory does not claim that stored database rows all changed when the host loaded a slim file. It warns about calculations involving affected time zones and periods. An investigation should distinguish an incorrect conversion result from a claim about every stored timestamp.

The SQL probe fixes the session environment

The advisory supplies a SQL probe that first selects UTC as the session time zone:

SET TIME ZONE UTC;
SELECT '2020-10-29 15:30'::TIMESTAMPTZ
    AT TIME ZONE 'Europe/Berlin' AT TIME ZONE 'UTC';

Its expected output is 2020-10-29 16:30:00+00:00. The advisory says another value indicates exposure to this runtime bug. Specifying the session setting is part of the probe, since interpreting the initial literal must use the same environment as the published check.

The CockroachDB timestamp documentation available at disclosure distinguishes TIMESTAMP and TIMESTAMPTZ. The latter uses the session time zone for presentation and interpretation where applicable, but does not store the original zone name. AT TIME ZONE converts between the timestamp forms using a specified zone.

That distinction explains why the probe uses a particular sequence of conversions. It exercises the named zone's rules and returns the result in a fixed frame for comparison. Changing the input session zone or removing one conversion would produce a different test. Retaining the published expression makes the observed output comparable with the advisory.

The workaround supplies a known archive

The remediation supplies zoneinfo.zip 2020a to every node, points the ZONEINFO environment variable at it and performs a rolling restart of the CockroachDB processes. The advisory then repeats the SQL probe to confirm the resulting calculation. It is a process environment change, not a schema migration or an update to a table containing application timestamps.

The runtime's LoadLocation documentation gives the mechanism. It first searches the directory or uncompressed zip named by ZONEINFO, then known Unix installation locations, with the Go installation archive as a further source. An explicit archive can therefore take precedence over the host files implicated by the advisory.

I prefer making that dependency explicit in the deployment definition. Recording only the database version leaves out the source of civil-time rules the process will actually load. The workaround identifies an archive and a process environment that another operator can inspect. It also needs to apply across nodes so query placement does not select different underlying data sources for the same requested zone.

The final probe establishes that the restarted process produces the advisory's expected result. It is evidence about that calculation under the new environment. It does not identify every application result previously derived using the affected rules. Any review of earlier derived values needs the application's actual expressions and time periods, because the advisory describes calculation behaviour rather than an automatic rewrite of those values. The operational record can then identify both the binary and the selected zoneinfo archive, which explains a part of date-time behaviour that the SQL schema alone cannot specify.