Re: sslinfo extension - add notbefore and notafter timestamps

2025-06-05 Thread Cary Huang
On Mon, 10 Mar 2025 12:29:28 -0700 Cary Huang wrote --- > The attached v14 is the rebased patch that updated the OpenSSL API calls to > be compatible in > version 1.1.1 (and beyond), which is the new minimum OpenSSL version > supported in PostgreSQL. > > > Cary Huang > --

Re: sslinfo extension - add notbefore and notafter timestamps

2025-03-10 Thread Cary Huang
The attached v14 is the rebased patch that updated the OpenSSL API calls to be compatible in version 1.1.1 (and beyond), which is the new minimum OpenSSL version supported in PostgreSQL. Cary Huang - HighGo Software Inc. (Canada) cary.hu...@highgo.ca www.highgo.ca v14-0001-Add-no

Re: sslinfo extension - add notbefore and notafter timestamps

2024-12-19 Thread Cary Huang
> > The recent bump in minmum required versions of OpenSSL and LibreSSL made me > > remember to revisit this patch which was previously reverted due to library > > incompatibility (with *both* OpenSSL and LibreSSL on different APIs). > > > > The attached removes the timestamp conversion worka

Re: sslinfo extension - add notbefore and notafter timestamps

2024-12-09 Thread Michael Paquier
On Mon, Oct 28, 2024 at 09:59:35AM +0100, Daniel Gustafsson wrote: > The recent bump in minmum required versions of OpenSSL and LibreSSL made me > remember to revisit this patch which was previously reverted due to library > incompatibility (with *both* OpenSSL and LibreSSL on different APIs). > >

Re: sslinfo extension - add notbefore and notafter timestamps

2024-10-28 Thread Daniel Gustafsson
The recent bump in minmum required versions of OpenSSL and LibreSSL made me remember to revisit this patch which was previously reverted due to library incompatibility (with *both* OpenSSL and LibreSSL on different APIs). The attached removes the timestamp conversion workaround which is no longer

Re: sslinfo extension - add notbefore and notafter timestamps

2024-03-22 Thread Jacob Champion
On Fri, Mar 22, 2024 at 6:15 AM Daniel Gustafsson wrote: > While staging this to commit I realized one silly thing about it warranting > another round here. The ASN.1 timediff code can diff against *any* timestamp, > not just the UNIX epoch, so we could just pass in the postgres epoch and skip >

Re: sslinfo extension - add notbefore and notafter timestamps

2024-03-22 Thread Daniel Gustafsson
> On 20 Mar 2024, at 17:32, Jacob Champion > wrote: > I can't find anything else to note; patch LGTM. While staging this to commit I realized one silly thing about it warranting another round here. The ASN.1 timediff code can diff against *any* timestamp, not just the UNIX epoch, so we could j

Re: sslinfo extension - add notbefore and notafter timestamps

2024-03-20 Thread Jacob Champion
On Wed, Mar 20, 2024 at 7:50 AM Daniel Gustafsson wrote: > We are subtracting 30 years from a calculation that we know didnt overflow, so > I guess if the certificate notBefore (the notAfter cannot be that early since > we wouldn't be able to connect with it) was set to early enough? It didn't >

Re: sslinfo extension - add notbefore and notafter timestamps

2024-03-20 Thread Daniel Gustafsson
> On 20 Mar 2024, at 15:28, Jacob Champion > wrote: >> + result -= ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * USECS_PER_DAY); >> + return TimestampTzGetDatum(result); > > Is that final bare subtraction able to wrap around for dates far in the past? We are subtracting 30 years from a calc

Re: sslinfo extension - add notbefore and notafter timestamps

2024-03-20 Thread Jacob Champion
On Wed, Mar 20, 2024 at 7:03 AM Daniel Gustafsson wrote: > The issue here is that postgres use a different epoch from the unix epoch, so > any dates calcuated based on the unix epoch need to be adjusted. Ah, thank you! I had just reproduced Cary's problem and was really confused... > I've hacked

Re: sslinfo extension - add notbefore and notafter timestamps

2024-03-20 Thread Daniel Gustafsson
> On 20 Mar 2024, at 00:24, Cary Huang wrote: > but it seems to me that many of the timestamp related functions still consider > timestamp or timestampTz as "double values with units of seconds" though. The issue here is that postgres use a different epoch from the unix epoch, so any dates calc

Re: sslinfo extension - add notbefore and notafter timestamps

2024-03-19 Thread Cary Huang
Thank you for your review again. > ...but I think Timestamp[Tz]s are stored as microseconds, so we're off > by a factor of a million. This still works because later we cast to > double and pass it back through float8_timestamptz, which converts it: In my test, if I made ASN1_TIME_to_timestamp

Re: sslinfo extension - add notbefore and notafter timestamps

2024-03-18 Thread Jacob Champion
On Mon, Mar 18, 2024 at 1:48 PM Cary Huang wrote: > Attached is the v10 patch with the above changes. Thanks again for the review. Awesome, looks good. On my final review pass I saw one last thing that bothered me (sorry for not seeing it before). The backend version of ASN1_TIME_to_timestamptz

Re: sslinfo extension - add notbefore and notafter timestamps

2024-03-18 Thread Cary Huang
Hi Jacob > Hi Cary, did you have any thoughts on the timestamptz notes from my last mail? > > > 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?

Re: sslinfo extension - add notbefore and notafter timestamps

2024-03-18 Thread Jacob Champion
On Fri, Mar 8, 2024 at 4:16 PM Cary Huang wrote: > Yes, I noticed this in the SSL test too. I am also in GTM-8, so for me the > tests would fail too due to the time zone differences from GMT. It shall be > okay to specifically set the outputs of pg_stat_ssl, > ssl_client_get_notbefore, and ssl_

Re: sslinfo extension - add notbefore and notafter timestamps

2024-03-08 Thread Cary Huang
Hello Thank you for the review and your patch. I have tested with minimum OpenSSL version 1.0.2 support and incorporated your changes into the v9 patch as attached. > In my -08 timezone, the date doesn't match what's recorded either > (it's my "tomorrow"). I think those probably just need to

Re: sslinfo extension - add notbefore and notafter timestamps

2024-03-05 Thread Jacob Champion
On Mon, Mar 4, 2024 at 6:23 AM Daniel Gustafsson wrote: > > On 12 Sep 2023, at 21:40, Jacob Champion wrote: Sorry for the long delay! > >> + ssl_client_get_notbefore() returns text > >> ...> + ssl_client_get_notafter() returns text > > > > I think this should say timestamptz rather than

Re: sslinfo extension - add notbefore and notafter timestamps

2023-09-15 Thread Daniel Gustafsson
> On 12 Sep 2023, at 21:40, Jacob Champion wrote: > > Hello, > > On 7/25/23 07:21, Daniel Gustafsson wrote: >> The attached version passes ssl tests for me on 1.0.2 through OpenSSL Git >> HEAD. > > Tests pass for me too, including LibreSSL 3.8. Thanks for testing! >> + /* Calculate the dif

Re: sslinfo extension - add notbefore and notafter timestamps

2023-09-12 Thread Jacob Champion
Hello, On 7/25/23 07:21, Daniel Gustafsson wrote: > The attached version passes ssl tests for me on 1.0.2 through OpenSSL Git > HEAD. Tests pass for me too, including LibreSSL 3.8. > + /* Calculate the diff from the epoch to the certificat timestamp */ "certificate" > + ssl_client_get_n

Re: sslinfo extension - add notbefore and notafter timestamps

2023-07-25 Thread Daniel Gustafsson
> On 20 Jul 2023, at 17:24, Daniel Gustafsson wrote: > >> On 17 Jul 2023, at 20:26, Cary Huang wrote: > Perhaps calling "tm2timestamp(&pgtm_time, 0, NULL, &ts)" without checking the return code would be just fine. I see some other usages of tm2timstamp() in other code areas als

Re: sslinfo extension - add notbefore and notafter timestamps

2023-07-20 Thread Daniel Gustafsson
> On 17 Jul 2023, at 20:26, Cary Huang wrote: >>> Perhaps calling "tm2timestamp(&pgtm_time, 0, NULL, &ts)" without checking >>> the return code would be just fine. I see some other usages of >>> tm2timstamp() in other code areas also skip checking the return code. >> >> I think we want to know

Re: sslinfo extension - add notbefore and notafter timestamps

2023-07-17 Thread Cary Huang
Hello > > Perhaps calling "tm2timestamp(&pgtm_time, 0, NULL, &ts)" without checking > > the return code would be just fine. I see some other usages of > > tm2timstamp() in other code areas also skip checking the return code. > > I think we want to know about any failures, btu we can probab

Re: sslinfo extension - add notbefore and notafter timestamps

2023-07-14 Thread Daniel Gustafsson
> On 14 Jul 2023, at 20:41, Cary Huang wrote: > Perhaps calling "tm2timestamp(&pgtm_time, 0, NULL, &ts)" without checking the > return code would be just fine. I see some other usages of tm2timstamp() in > other code areas also skip checking the return code. I think we want to know about any f

Re: sslinfo extension - add notbefore and notafter timestamps

2023-07-14 Thread Cary Huang
Hello > The way we typically ship extensions in contrib/ is to not create a new base > version .sql file for smaller changes like adding a few functions. For this > patch we should keep --1.2.sql and instead supply a 1.2--1.3.sql with the new > functions. Thank you for pointing this out. It

Re: sslinfo extension - add notbefore and notafter timestamps

2023-07-13 Thread Daniel Gustafsson
I had another look at this today and I think this patch is in pretty good shape, below are a few comments on this revision: - 'sslinfo--1.2.sql', + 'sslinfo--1.2--1.3.sql', + 'sslinfo--1.3.sql', The way we typically ship extensions in contrib/ is to not create a new base version .sql file for

Re: sslinfo extension - add notbefore and notafter timestamps

2023-07-10 Thread Cary Huang
> Thanks for the new version! It doesn't fail the ssl tests, but the Kerberos > test now fails. You can see the test reports from the CFBot here: Yes, kerberos tests failed due to the addition of notbefore and notafter values. The values array within "pg_stat_get_activity" function related to

Re: sslinfo extension - add notbefore and notafter timestamps

2023-07-03 Thread Daniel Gustafsson
> On 30 Jun 2023, at 20:12, Cary Huang wrote: > >> This needs to adjust the tests in src/test/ssl which now fails due to SELECT >> * >> returning a row which doesn't match what the test was coded for. > > Thank you so much for pointing out. I have adjusted the extra ssl test to > account for t

Re: sslinfo extension - add notbefore and notafter timestamps

2023-06-30 Thread Cary Huang
> This needs to adjust the tests in src/test/ssl which now fails due to SELECT > * > returning a row which doesn't match what the test was coded for. Thank you so much for pointing out. I have adjusted the extra ssl test to account for the extra columns returned. It should not fail now. >

Re: sslinfo extension - add notbefore and notafter timestamps

2023-06-27 Thread Daniel Gustafsson
> On 23 Jun 2023, at 22:10, Cary Huang wrote: >> Off the cuff that doesn't seem like a bad idea, but I wonder if we should add >> them to pg_stat_ssl (or both) instead if we deem them valuable? > > I think the same information should be available to pg_stat_ssl as well. > pg_stat_ssl can show

Re: sslinfo extension - add notbefore and notafter timestamps

2023-06-23 Thread Cary Huang
> Yes, please add it to the July commitfest and feel free to set me as > Reviewer, > I intend to take a look at it. Thank you Daniel, I have added this patch to July commitfest under security category and added you as reviewer. best regards Cary Huang - HighGo Software Inc. (Ca

Re: sslinfo extension - add notbefore and notafter timestamps

2023-06-23 Thread Daniel Gustafsson
> On 23 Jun 2023, at 22:10, Cary Huang wrote: > would this feature be suitable to be added to commitfest? What do you think? Yes, please add it to the July commitfest and feel free to set me as Reviewer, I intend to take a look at it. -- Daniel Gustafsson

Re: sslinfo extension - add notbefore and notafter timestamps

2023-06-23 Thread Cary Huang
> Off the cuff that doesn't seem like a bad idea, but I wonder if we should add > them to pg_stat_ssl (or both) instead if we deem them valuable? I think the same information should be available to pg_stat_ssl as well. pg_stat_ssl can show the client certificate information for all connecting

Re: sslinfo extension - add notbefore and notafter timestamps

2022-08-20 Thread Daniel Gustafsson
> On 20 Aug 2022, at 01:00, Cary Huang wrote: > I noticed that sslinfo extension does not have functions to return current > client certificate's notbefore and notafter timestamps which are also quite > important attributes in a X509 certificate. The attached patch adds 2 > functions to get no

sslinfo extension - add notbefore and notafter timestamps

2022-08-19 Thread Cary Huang
Hello I noticed that sslinfo extension does not have functions to return current client certificate's notbefore and notafter timestamps which are also quite important attributes in a X509 certificate. The attached patch adds 2 functions to get notbefore and notafter timestamps from the curren