Add notBefore and notAfter to SSL cert info display

This adds the X509 attributes notBefore and notAfter to sslinfo
as well as pg_stat_ssl to allow verifying and identifying the
validity period of the current client certificate. OpenSSL has
APIs for extracting notAfter and notBefore, but they are only
supported in recent versions so we have to calculate the dates
by hand in order to make this work for the older versions of
OpenSSL that we still support.

Original patch by Cary Huang with additional hacking by Jacob
and myself.

Author: Cary Huang <[email protected]>
Co-author: Jacob Champion <[email protected]>
Co-author: Daniel Gustafsson <[email protected]>
Discussion: https://postgr.es/m/[email protected]

5 files changed, 110 insertions, 2 deletions

diff --git a/contrib/sslinfo/Makefile b/contrib/sslinfo/Makefile
index dd1ff83b16d..78a5a83d5c4 100644
--- a/contrib/sslinfo/Makefile
+++ b/contrib/sslinfo/Makefile
@@ -6,7 +6,7 @@ OBJS = \
 	sslinfo.o
 
 EXTENSION = sslinfo
-DATA = sslinfo--1.2.sql sslinfo--1.1--1.2.sql sslinfo--1.0--1.1.sql
+DATA = sslinfo--1.2--1.3.sql sslinfo--1.2.sql sslinfo--1.1--1.2.sql sslinfo--1.0--1.1.sql
 PGFILEDESC = "sslinfo - information about client SSL certificate"
 
 ifdef USE_PGXS
diff --git a/contrib/sslinfo/meson.build b/contrib/sslinfo/meson.build
index 39d49a1736c..a4bcd21ea69 100644
--- a/contrib/sslinfo/meson.build
+++ b/contrib/sslinfo/meson.build
@@ -26,6 +26,7 @@ install_data(
   'sslinfo--1.0--1.1.sql',
   'sslinfo--1.1--1.2.sql',
   'sslinfo--1.2.sql',
+  'sslinfo--1.2--1.3.sql',
   'sslinfo.control',
   kwargs: contrib_data_args,
 )
diff --git a/contrib/sslinfo/sslinfo--1.2--1.3.sql b/contrib/sslinfo/sslinfo--1.2--1.3.sql
new file mode 100644
index 00000000000..424a11afe4f
--- /dev/null
+++ b/contrib/sslinfo/sslinfo--1.2--1.3.sql
@@ -0,0 +1,12 @@
+/* contrib/sslinfo/sslinfo--1.2--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION sslinfo" to load this file. \quit
+
+CREATE FUNCTION ssl_client_get_notbefore() RETURNS timestamptz
+AS 'MODULE_PATHNAME', 'ssl_client_get_notbefore'
+LANGUAGE C STRICT PARALLEL RESTRICTED;
+
+CREATE FUNCTION ssl_client_get_notafter() RETURNS timestamptz
+AS 'MODULE_PATHNAME', 'ssl_client_get_notafter'
+LANGUAGE C STRICT PARALLEL RESTRICTED;
diff --git a/contrib/sslinfo/sslinfo.c b/contrib/sslinfo/sslinfo.c
index 5fd46b98741..904b203a172 100644
--- a/contrib/sslinfo/sslinfo.c
+++ b/contrib/sslinfo/sslinfo.c
@@ -14,10 +14,12 @@
 #include <openssl/asn1.h>
 
 #include "access/htup_details.h"
+#include "common/int.h"
 #include "funcapi.h"
 #include "libpq/libpq-be.h"
 #include "miscadmin.h"
 #include "utils/builtins.h"
+#include "utils/timestamp.h"
 
 /*
  * On Windows, <wincrypt.h> includes a #define for X509_NAME, which breaks our
@@ -34,6 +36,7 @@ PG_MODULE_MAGIC;
 
 static Datum X509_NAME_field_to_text(X509_NAME *name, text *fieldName);
 static Datum ASN1_STRING_to_text(ASN1_STRING *str);
+static Datum ASN1_TIME_to_timestamptz(ASN1_TIME *time);
 
 /*
  * Function context for data persisting over repeated calls.
@@ -226,6 +229,66 @@ X509_NAME_field_to_text(X509_NAME *name, text *fieldName)
 
 
 /*
+ * Converts OpenSSL ASN1_TIME structure into timestamptz
+ *
+ * OpenSSL 1.0.2 doesn't expose a function to convert an ASN1_TIME to a tm
+ * struct, it's only available in 1.1.1 and onwards. Instead we can ask for the
+ * difference between the ASN1_TIME and a known timestamp and get the actual
+ * timestamp that way. Until support for OpenSSL 1.0.2 is retired we have to do
+ * it this way.
+ *
+ * Parameter: time - OpenSSL ASN1_TIME structure.
+ * Returns Datum, which can be directly returned from a C language SQL
+ * function.
+ */
+static Datum
+ASN1_TIME_to_timestamptz(ASN1_TIME *ASN1_cert_ts)
+{
+	int			days;
+	int			seconds;
+	const char	postgres_epoch[] = "20000101000000Z";
+	ASN1_TIME  *ASN1_epoch;
+	int64		result_days;
+	int64		result_secs;
+	int64		result;
+
+	/* Create an epoch to compare against */
+	ASN1_epoch = ASN1_TIME_new();
+	if (!ASN1_epoch)
+		ereport(ERROR,
+				(errcode(ERRCODE_OUT_OF_MEMORY),
+				 errmsg("could not allocate memory for ASN1 TIME structure")));
+
+	/* Calculate the diff from the epoch to the certificate timestamp */
+	if (!ASN1_TIME_set_string(ASN1_epoch, postgres_epoch) ||
+		!ASN1_TIME_diff(&days, &seconds, ASN1_epoch, ASN1_cert_ts))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("failed to read certificate validity")));
+
+	/*
+	 * Unlike when freeing other OpenSSL memory structures, there is no error
+	 * return on freeing ASN1 strings.
+	 */
+	ASN1_TIME_free(ASN1_epoch);
+
+	/*
+	 * Convert the reported date into usecs to be used as a TimestampTz. The
+	 * date should really not overflow an int64 but rather than trusting the
+	 * certificate we take overflow into consideration.
+	 */
+	if (pg_mul_s64_overflow(days, USECS_PER_DAY, &result_days) ||
+		pg_mul_s64_overflow(seconds, USECS_PER_SEC, &result_secs) ||
+		pg_add_s64_overflow(result_days, result_secs, &result))
+	{
+		return TimestampTzGetDatum(0);
+	}
+
+	return TimestampTzGetDatum(result);
+}
+
+
+/*
  * Returns specified field of client certificate distinguished name
  *
  * Receives field name (like 'commonName' and 'emailAddress') and
@@ -482,3 +545,35 @@ ssl_extension_info(PG_FUNCTION_ARGS)
 	/* All done */
 	SRF_RETURN_DONE(funcctx);
 }
+
+/*
+ * Returns current client certificate notBefore timestamp in
+ * timestamptz data type
+ */
+PG_FUNCTION_INFO_V1(ssl_client_get_notbefore);
+Datum
+ssl_client_get_notbefore(PG_FUNCTION_ARGS)
+{
+	X509	   *cert = MyProcPort->peer;
+
+	if (!MyProcPort->ssl_in_use || !MyProcPort->peer_cert_valid)
+		PG_RETURN_NULL();
+
+	return ASN1_TIME_to_timestamptz(X509_get_notBefore(cert));
+}
+
+/*
+ * Returns current client certificate notAfter timestamp in
+ * timestamptz data type
+ */
+PG_FUNCTION_INFO_V1(ssl_client_get_notafter);
+Datum
+ssl_client_get_notafter(PG_FUNCTION_ARGS)
+{
+	X509	   *cert = MyProcPort->peer;
+
+	if (!MyProcPort->ssl_in_use || !MyProcPort->peer_cert_valid)
+		PG_RETURN_NULL();
+
+	return ASN1_TIME_to_timestamptz(X509_get_notAfter(cert));
+}
diff --git a/contrib/sslinfo/sslinfo.control b/contrib/sslinfo/sslinfo.control
index c7754f924cf..b53e95b7da8 100644
--- a/contrib/sslinfo/sslinfo.control
+++ b/contrib/sslinfo/sslinfo.control
@@ -1,5 +1,5 @@
 # sslinfo extension
 comment = 'information about SSL certificates'
-default_version = '1.2'
+default_version = '1.3'
 module_pathname = '$libdir/sslinfo'
 relocatable = true