Re: sslinfo extension - add notbefore and notafter timestamps

On Mon, Mar 4, 2024 at 6:23 AM Daniel Gustafsson <daniel@yesql.se> wrote:
> > On 12 Sep 2023, at 21:40, Jacob Champion <jchampion@timescale.com> wrote:

Sorry for the long delay!

> >> +     <function>ssl_client_get_notbefore() returns text</function>
> >> ...> +     <function>ssl_client_get_notafter() returns text</function>
> >
> > I think this should say timestamptz rather than text? Ditto for the
> > pg_stat_ssl documentation.
> >
> > Speaking of which: is the use of `timestamp` rather than `timestamptz`
> > in pg_proc.dat intentional? Will that cause problems with comparisons?
>
> It should be timestamptz, it was a tyop on my part. Fixed.

Looks like sslinfo--1.2--1.3.sql is also declaring the functions as
timestamp rather than timestamptz, which is breaking comparisons with
the not_before/after columns. It might also be nice to rename
ASN1_TIME_to_timestamp().

Squinting further at the server backend implementation, should that
also be using TimestampTz throughout, instead of Timestamp? It all
goes through float8_timestamptz at the end, so I guess it shouldn't
have a material impact, but it's a bit confusing.

> Thanks for reviewing, the attached v8 contains the fixes from this review along
> with a fresh rebase and some attempts at making tests more stable in the face
> of timezones by casting to date.

In my -08 timezone, the date doesn't match what's recorded either
(it's my "tomorrow"). I think those probably just need to be converted
to UTC explicitly? I've attached a sample diff on top of v8 that
passes tests on my machine.

--Jacob