[PostGIS] #5666: Build reproducibility: timestamps in extension upgrade SQL scripts

[PostGIS]

#5666: Build reproducibility: timestamps in extension upgrade SQL scripts
---------------------------+---------------------
 Reporter:  James Addison  |      Owner:  pramsey
     Type:  defect         |     Status:  new
 Priority:  medium         |  Milestone:
Component:  postgis        |    Version:  3.4.x
 Keywords:                 |
---------------------------+---------------------
 == Problem
 When PostGIS builds extensions, the `upgrade-paths-rules.mk` makefile
 emits a few comments into templated SQL files, including a 'Built on' line
 that includes a timestamp.

 {{{#!sql
 -- Just tag extension testing version as "ANY"
 -- Installed by testing
 -- Built on 2024-02-12 12:47:27
 }}}

 The timestamp is generated by invoking the `date` command in a subshell
 ([https://trac.osgeo.org/postgis/browser/git/extensions/upgrade-paths-
 rules.mk?rev=c19ce56ca8982635026ba605f5b2fdce3622bb4a#L40 code ref]), and
 this currently causes the output to differ on each build of the extensions
 (even from the same source code), making the build non-reproducible.

 The expected/recommended behaviour is either to remove the timestamp
 completely (removing the source of divergence) or to derive a timestamp
 deterministically - typically by using the last-modified time of the
 source code or a timestamp of the release.

 As far as I am currently aware, this is the only source of non-determinism
 when building the PostGIS extensions from the v3.4.2 code as Debian-
 packaged.  I can't guarantee that there aren't others, but this is the
 only case I've encountered.

 == Additional Context
 Related to ticket: #4148 (introduction of reproducible build time handling
 in PostGIS)

 Originally reported downstream at: https://bugs.debian.org/cgi-
 bin/bugreport.cgi?bug=1063724

 More information about timestamps in relation to reproducible builds:
 https://reproducible-builds.org/docs/timestamps/

 == Notes about a similar solution
 There is an existing pattern in the `configure.ac` file that extracts a
 `POSTGIS_BUILD_DATE` string variable from a `SOURCE_DATE_EPOCH`
 environment variable (the [https://reproducible-builds.org/docs/source-
 date-epoch/ recommended] way to read a reproducible time value for use
 during builds).

 To handle the possibility that it could be confusing or disruptive to
 remove the 'Built on' line completely, I've attached a patch that applies
 this same pattern to the `upgrade-paths-rules.mk` file.  I've tested this
 using a checkout from v3.4.2 using:

 {{{
 postgis $ cd extensions
 postgis/extensions $ rm -rf sql;
 postgis/extensions $ mkdir -p sql;
 postgis/extensions $ SOURCE_DATE_EPOCH=0 EXTENSION=testing make -f
 upgrade-paths-rules.mk sql/testing--TEMPLATED--TO--ANY.sql
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5666>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.