Thread

[PATCH] v15rebase

Cary Huang <cary.huang@highgo.ca> — 2025-06-05T21:57:47Z

---
 contrib/sslinfo/Makefile                    |  2 +-
 contrib/sslinfo/meson.build                 |  1 +
 contrib/sslinfo/sslinfo--1.2--1.3.sql       | 12 ++++
 contrib/sslinfo/sslinfo.c                   | 71 +++++++++++++++++++++
 contrib/sslinfo/sslinfo.control             |  2 +-
 doc/src/sgml/monitoring.sgml                | 20 ++++++
 doc/src/sgml/sslinfo.sgml                   | 30 +++++++++
 src/backend/catalog/system_views.sql        |  4 +-
 src/backend/libpq/be-secure-openssl.c       | 52 +++++++++++++++
 src/backend/utils/activity/backend_status.c |  2 +
 src/backend/utils/adt/pgstatfuncs.c         | 46 ++++++++-----
 src/include/catalog/pg_proc.dat             |  6 +-
 src/include/libpq/libpq-be.h                |  2 +
 src/include/utils/backend_status.h          |  3 +
 src/test/regress/expected/rules.out         | 12 ++--
 src/test/ssl/t/001_ssltests.pl              |  8 +--
 src/test/ssl/t/003_sslinfo.pl               | 14 ++++
 17 files changed, 255 insertions(+), 32 deletions(-)
 create mode 100644 contrib/sslinfo/sslinfo--1.2--1.3.sql

diff --git a/contrib/sslinfo/Makefile b/contrib/sslinfo/Makefile
index 14305594e2..ef1475fffb 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 4c51375920..b560da0820 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 0000000000..424a11afe4
--- /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 da70201119..f6d009bb13 100644
--- a/contrib/sslinfo/sslinfo.c
+++ b/contrib/sslinfo/sslinfo.c
@@ -18,6 +18,7 @@
 #include "libpq/libpq-be.h"
 #include "miscadmin.h"
 #include "utils/builtins.h"
+#include "utils/timestamp.h"
 
 PG_MODULE_MAGIC_EXT(
 					.name = "sslinfo",
@@ -26,6 +27,7 @@ PG_MODULE_MAGIC_EXT(
 
 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(const ASN1_TIME *time);
 
 /*
  * Function context for data persisting over repeated calls.
@@ -217,6 +219,43 @@ X509_NAME_field_to_text(X509_NAME *name, text *fieldName)
 }
 
 
+/*
+ * Converts OpenSSL ASN1_TIME structure into timestamptz
+ *
+ * Parameter: ASN1_TIME timestamp from certificate
+ *
+ * Returns the ASN1_TIME timestamp as a timestamptz datum.
+ */
+static Datum
+ASN1_TIME_to_timestamptz(const ASN1_TIME *ASN1_cert_ts)
+{
+	struct pg_tm	pgtm;
+	struct tm	tm;
+	int			ret;
+	Timestamp	ts;
+
+	ret = ASN1_TIME_to_tm(ASN1_cert_ts, &tm);
+	if (!ret)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("failed to parse certificate validity")));
+
+	pgtm.tm_sec = tm.tm_sec;
+	pgtm.tm_min = tm.tm_min;
+	pgtm.tm_hour = tm.tm_hour;
+	pgtm.tm_mday = tm.tm_mday;
+	pgtm.tm_mon = tm.tm_mon + 1;
+	pgtm.tm_year = tm.tm_year + 1900;
+
+	if (unlikely(tm2timestamp(&pgtm, 0, NULL, &ts) != 0))
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("timestamp out of range"));
+
+	PG_RETURN_TIMESTAMP(ts);
+}
+
+
 /*
  * Returns specified field of client certificate distinguished name
  *
@@ -474,3 +513,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_get0_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_get0_notAfter(cert));
+}
diff --git a/contrib/sslinfo/sslinfo.control b/contrib/sslinfo/sslinfo.control
index c7754f924c..b53e95b7da 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
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 4265a22d4d..30b1ef1b4c 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2398,6 +2398,26 @@ description | Waiting for a newly initialized WAL file to reach durable storage
        This field is truncated like <structfield>client_dn</structfield>.
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>not_before</structfield> <type>text</type>
+      </para>
+      <para>
+       Not before timestamp of the client certificate, or NULL if no client
+       certificate was supplied.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>not_after</structfield> <type>text</type>
+      </para>
+      <para>
+       Not after timestamp of the client certificate, or NULL if no client
+       certificate was supplied.
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/doc/src/sgml/sslinfo.sgml b/doc/src/sgml/sslinfo.sgml
index 85d49f6653..2a6725cc1c 100644
--- a/doc/src/sgml/sslinfo.sgml
+++ b/doc/src/sgml/sslinfo.sgml
@@ -240,6 +240,36 @@ emailAddress
     </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term>
+     <function>ssl_client_get_notbefore() returns timestamptz</function>
+     <indexterm>
+      <primary>ssl_client_get_notbefore</primary>
+     </indexterm>
+    </term>
+    <listitem>
+    <para>
+     Return the <structfield>not before</structfield> timestamp of the client
+     certificate.
+    </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term>
+     <function>ssl_client_get_notafter() returns timestamptz</function>
+     <indexterm>
+      <primary>ssl_client_get_notafter</primary>
+     </indexterm>
+    </term>
+    <listitem>
+    <para>
+     Return the <structfield>not after</structfield> timestamp of the client
+     certificate.
+    </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
  </sect2>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 08f780a2e6..682498e365 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1002,7 +1002,9 @@ CREATE VIEW pg_stat_ssl AS
             S.sslbits AS bits,
             S.ssl_client_dn AS client_dn,
             S.ssl_client_serial AS client_serial,
-            S.ssl_issuer_dn AS issuer_dn
+            S.ssl_issuer_dn AS issuer_dn,
+            S.ssl_not_before AS not_before,
+            S.ssl_not_after AS not_after
     FROM pg_stat_get_activity(NULL) AS S
     WHERE S.client_port IS NOT NULL;
 
diff --git a/src/backend/libpq/be-secure-openssl.c b/src/backend/libpq/be-secure-openssl.c
index 64ff3ce3d6..1f79512b1a 100644
--- a/src/backend/libpq/be-secure-openssl.c
+++ b/src/backend/libpq/be-secure-openssl.c
@@ -35,6 +35,7 @@
 #include "storage/latch.h"
 #include "utils/guc.h"
 #include "utils/memutils.h"
+#include "utils/timestamp.h"
 
 /*
  * These SSL-related #includes must come after all system-provided headers.
@@ -79,6 +80,7 @@ static const char *SSLerrmessageExt(unsigned long ecode, const char *replacement
 static const char *SSLerrmessage(unsigned long ecode);
 
 static char *X509_NAME_to_cstring(X509_NAME *name);
+static TimestampTz ASN1_TIME_to_timestamptz(const ASN1_TIME *time);
 
 static SSL_CTX *SSL_context = NULL;
 static bool dummy_ssl_passwd_cb_called = false;
@@ -1557,6 +1559,24 @@ be_tls_get_peer_issuer_name(Port *port, char *ptr, size_t len)
 		ptr[0] = '\0';
 }
 
+void
+be_tls_get_peer_not_before(Port *port, TimestampTz *ptr)
+{
+	if (port->peer)
+		*ptr = ASN1_TIME_to_timestamptz(X509_get0_notBefore(port->peer));
+	else
+		*ptr = 0;
+}
+
+void
+be_tls_get_peer_not_after(Port *port, TimestampTz *ptr)
+{
+	if (port->peer)
+		*ptr = ASN1_TIME_to_timestamptz(X509_get0_notAfter(port->peer));
+	else
+		*ptr = 0;
+}
+
 void
 be_tls_get_peer_serial(Port *port, char *ptr, size_t len)
 {
@@ -1700,6 +1720,38 @@ X509_NAME_to_cstring(X509_NAME *name)
 	return result;
 }
 
+/*
+ * Convert an ASN1_TIME to a PostgreSQL Timestamp datum.
+ */
+static TimestampTz
+ASN1_TIME_to_timestamptz(const ASN1_TIME *ASN1_cert_ts)
+{
+	struct pg_tm	pgtm;
+	struct tm	tm;
+	int			ret;
+	TimestampTz	ts;
+
+	ret = ASN1_TIME_to_tm(ASN1_cert_ts, &tm);
+	if (!ret)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("failed to parse certificate validity")));
+
+	pgtm.tm_sec = tm.tm_sec;
+	pgtm.tm_min = tm.tm_min;
+	pgtm.tm_hour = tm.tm_hour;
+	pgtm.tm_mday = tm.tm_mday;
+	pgtm.tm_mon = tm.tm_mon + 1;
+	pgtm.tm_year = tm.tm_year + 1900;
+
+	if (unlikely(tm2timestamp(&pgtm, 0, NULL, &ts) != 0))
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("timestamp out of range"));
+
+	return ts;
+}
+
 /*
  * Convert TLS protocol version GUC enum to OpenSSL values
  *
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index a290cc4c97..a098f6ac56 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -413,6 +413,8 @@ pgstat_bestart_security(void)
 		be_tls_get_peer_subject_name(MyProcPort, lsslstatus.ssl_client_dn, NAMEDATALEN);
 		be_tls_get_peer_serial(MyProcPort, lsslstatus.ssl_client_serial, NAMEDATALEN);
 		be_tls_get_peer_issuer_name(MyProcPort, lsslstatus.ssl_issuer_dn, NAMEDATALEN);
+		be_tls_get_peer_not_before(MyProcPort, &lsslstatus.ssl_not_before);
+		be_tls_get_peer_not_after(MyProcPort, &lsslstatus.ssl_not_after);
 	}
 #endif
 
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 1c12ddbae4..baf11eadc0 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -330,7 +330,7 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
 Datum
 pg_stat_get_activity(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_ACTIVITY_COLS	31
+#define PG_STAT_GET_ACTIVITY_COLS	33
 	int			num_backends = pgstat_fetch_stat_numbackends();
 	int			curr_backend;
 	int			pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -424,7 +424,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 			pfree(clipped_activity);
 
 			/* leader_pid */
-			nulls[29] = true;
+			nulls[31] = true;
 
 			proc = BackendPidGetProc(beentry->st_procpid);
 
@@ -461,8 +461,8 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 				 */
 				if (leader && leader->pid != beentry->st_procpid)
 				{
-					values[29] = Int32GetDatum(leader->pid);
-					nulls[29] = false;
+					values[31] = Int32GetDatum(leader->pid);
+					nulls[31] = false;
 				}
 				else if (beentry->st_backendType == B_BG_WORKER)
 				{
@@ -470,8 +470,8 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 
 					if (leader_pid != InvalidPid)
 					{
-						values[29] = Int32GetDatum(leader_pid);
-						nulls[29] = false;
+						values[31] = Int32GetDatum(leader_pid);
+						nulls[31] = false;
 					}
 				}
 			}
@@ -615,35 +615,45 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 					values[24] = CStringGetTextDatum(beentry->st_sslstatus->ssl_issuer_dn);
 				else
 					nulls[24] = true;
+
+				if (beentry->st_sslstatus->ssl_not_before != 0)
+					values[25] = TimestampTzGetDatum(beentry->st_sslstatus->ssl_not_before);
+				else
+					nulls[25] = true;
+
+				if (beentry->st_sslstatus->ssl_not_after != 0)
+					values[26] = TimestampTzGetDatum(beentry->st_sslstatus->ssl_not_after);
+				else
+					nulls[26] = true;
 			}
 			else
 			{
 				values[18] = BoolGetDatum(false);	/* ssl */
-				nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = true;
+				nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = nulls[25] = nulls[26] = true;
 			}
 
 			/* GSSAPI information */
 			if (beentry->st_gss)
 			{
-				values[25] = BoolGetDatum(beentry->st_gssstatus->gss_auth); /* gss_auth */
-				values[26] = CStringGetTextDatum(beentry->st_gssstatus->gss_princ);
-				values[27] = BoolGetDatum(beentry->st_gssstatus->gss_enc);	/* GSS Encryption in use */
-				values[28] = BoolGetDatum(beentry->st_gssstatus->gss_delegation);	/* GSS credentials
+				values[27] = BoolGetDatum(beentry->st_gssstatus->gss_auth); /* gss_auth */
+				values[28] = CStringGetTextDatum(beentry->st_gssstatus->gss_princ);
+				values[29] = BoolGetDatum(beentry->st_gssstatus->gss_enc);	/* GSS Encryption in use */
+				values[30] = BoolGetDatum(beentry->st_gssstatus->gss_delegation);	/* GSS credentials
 																					 * delegated */
 			}
 			else
 			{
-				values[25] = BoolGetDatum(false);	/* gss_auth */
-				nulls[26] = true;	/* No GSS principal */
-				values[27] = BoolGetDatum(false);	/* GSS Encryption not in
+				values[27] = BoolGetDatum(false);	/* gss_auth */
+				nulls[28] = true;	/* No GSS principal */
+				values[29] = BoolGetDatum(false);	/* GSS Encryption not in
 													 * use */
-				values[28] = BoolGetDatum(false);	/* GSS credentials not
+				values[30] = BoolGetDatum(false);	/* GSS credentials not
 													 * delegated */
 			}
 			if (beentry->st_query_id == INT64CONST(0))
-				nulls[30] = true;
+				nulls[32] = true;
 			else
-				values[30] = Int64GetDatum(beentry->st_query_id);
+				values[32] = Int64GetDatum(beentry->st_query_id);
 		}
 		else
 		{
@@ -673,6 +683,8 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 			nulls[28] = true;
 			nulls[29] = true;
 			nulls[30] = true;
+			nulls[31] = true;
+			nulls[32] = true;
 		}
 
 		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d3d28a263f..24cb44b53d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5636,9 +5636,9 @@
   proname => 'pg_stat_get_activity', prorows => '100', proisstrict => 'f',
   proretset => 't', provolatile => 's', proparallel => 'r',
   prorettype => 'record', proargtypes => 'int4',
-  proallargtypes => '{int4,oid,int4,oid,text,text,text,text,text,timestamptz,timestamptz,timestamptz,timestamptz,inet,text,int4,xid,xid,text,bool,text,text,int4,text,numeric,text,bool,text,bool,bool,int4,int8}',
-  proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
-  proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id}',
+  proallargtypes => '{int4,oid,int4,oid,text,text,text,text,text,timestamptz,timestamptz,timestamptz,timestamptz,inet,text,int4,xid,xid,text,bool,text,text,int4,text,numeric,text,timestamptz,timestamptz,bool,text,bool,bool,int4,int8}',
+  proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
+  proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,ssl_not_before,ssl_not_after,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id}',
   prosrc => 'pg_stat_get_activity' },
 { oid => '6318', descr => 'describe wait events',
   proname => 'pg_get_wait_events', procost => '10', prorows => '250',
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index d6e671a638..c2eca60226 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -320,6 +320,8 @@ extern const char *be_tls_get_cipher(Port *port);
 extern void be_tls_get_peer_subject_name(Port *port, char *ptr, size_t len);
 extern void be_tls_get_peer_issuer_name(Port *port, char *ptr, size_t len);
 extern void be_tls_get_peer_serial(Port *port, char *ptr, size_t len);
+extern void be_tls_get_peer_not_before(Port *port, TimestampTz *ptr);
+extern void be_tls_get_peer_not_after(Port *port, TimestampTz *ptr);
 
 /*
  * Get the server certificate hash for SCRAM channel binding type
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 3016501ac0..4a867087d7 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -62,6 +62,9 @@ typedef struct PgBackendSSLStatus
 	char		ssl_client_serial[NAMEDATALEN];
 
 	char		ssl_issuer_dn[NAMEDATALEN];
+	/* Certificate validity in postgres epoch format */
+	TimestampTz	ssl_not_before;
+	TimestampTz	ssl_not_after;
 } PgBackendSSLStatus;
 
 /*
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 6cf828ca8d..80e5351bae 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1783,7 +1783,7 @@ pg_stat_activity| SELECT s.datid,
     s.query_id,
     s.query,
     s.backend_type
-   FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
+   FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
      LEFT JOIN pg_database d ON ((s.datid = d.oid)))
      LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
 pg_stat_all_indexes| SELECT c.oid AS relid,
@@ -1911,7 +1911,7 @@ pg_stat_gssapi| SELECT pid,
     gss_princ AS principal,
     gss_enc AS encrypted,
     gss_delegation AS credentials_delegated
-   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
+   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
   WHERE (client_port IS NOT NULL);
 pg_stat_io| SELECT backend_type,
     object,
@@ -2119,7 +2119,7 @@ pg_stat_replication| SELECT s.pid,
     w.sync_priority,
     w.sync_state,
     w.reply_time
-   FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
+   FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
      JOIN pg_stat_get_wal_senders() w(pid, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_priority, sync_state, reply_time) ON ((s.pid = w.pid)))
      LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
 pg_stat_replication_slots| SELECT s.slot_name,
@@ -2152,8 +2152,10 @@ pg_stat_ssl| SELECT pid,
     sslbits AS bits,
     ssl_client_dn AS client_dn,
     ssl_client_serial AS client_serial,
-    ssl_issuer_dn AS issuer_dn
-   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
+    ssl_issuer_dn AS issuer_dn,
+    ssl_not_before AS not_before,
+    ssl_not_after AS not_after
+   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
   WHERE (client_port IS NOT NULL);
 pg_stat_subscription| SELECT su.oid AS subid,
     su.subname,
diff --git a/src/test/ssl/t/001_ssltests.pl b/src/test/ssl/t/001_ssltests.pl
index 2cb4d0ffd4..484f58e66a 100644
--- a/src/test/ssl/t/001_ssltests.pl
+++ b/src/test/ssl/t/001_ssltests.pl
@@ -580,8 +580,8 @@ command_like(
 		'--command' =>
 		  "SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid()"
 	],
-	qr{^pid,ssl,version,cipher,bits,client_dn,client_serial,issuer_dn\r?\n
-				^\d+,t,TLSv[\d.]+,[\w-]+,\d+,_null_,_null_,_null_\r?$}mx,
+	qr{^pid,ssl,version,cipher,bits,client_dn,client_serial,issuer_dn,not_before,not_after\r?\n
+				^\d+,t,TLSv[\d.]+,[\w-]+,\d+,_null_,_null_,_null_,_null_,_null_\r?$}mx,
 	'pg_stat_ssl view without client certificate');
 
 # Test min/max SSL protocol versions.
@@ -782,8 +782,8 @@ command_like(
 		'--command' =>
 		  "SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid()"
 	],
-	qr{^pid,ssl,version,cipher,bits,client_dn,client_serial,issuer_dn\r?\n
-				^\d+,t,TLSv[\d.]+,[\w-]+,\d+,/?CN=ssltestuser,$serialno,/?\QCN=Test CA for PostgreSQL SSL regression test client certs\E\r?$}mx,
+	qr{^pid,ssl,version,cipher,bits,client_dn,client_serial,issuer_dn,not_before,not_after\r?\n
+				^\d+,t,TLSv[\d.]+,[\w-]+,\d+,/?CN=ssltestuser,$serialno,/?\QCN=Test CA for PostgreSQL SSL regression test client certs,2023-06-29 01:01:01+00,2050-01-01 01:01:01+00\E\r?$}mx,
 	'pg_stat_ssl with client certificate');
 
 # client key with wrong permissions
diff --git a/src/test/ssl/t/003_sslinfo.pl b/src/test/ssl/t/003_sslinfo.pl
index 3c756489cd..16f2dbda49 100644
--- a/src/test/ssl/t/003_sslinfo.pl
+++ b/src/test/ssl/t/003_sslinfo.pl
@@ -165,6 +165,20 @@ $result = $node->safe_psql(
 	connstr => $common_connstr);
 is($result, 't', "ssl_issuer_field() for commonName");
 
+$result = $node->safe_psql(
+	"certdb",
+	"SELECT ssl_client_get_notbefore() = not_before, "
+	  . "not_before AT TIME ZONE 'UTC' = '2023-06-29 01:01:01' FROM pg_stat_ssl WHERE pid = pg_backend_pid();",
+	connstr => $common_connstr);
+is($result, 't|t', "ssl_client_get_notbefore() for not_before timestamp");
+
+$result = $node->safe_psql(
+	"certdb",
+	"SELECT ssl_client_get_notafter() = not_after, "
+	  . "not_after AT TIME ZONE 'UTC' = '2050-01-01 01:01:01' FROM pg_stat_ssl WHERE pid = pg_backend_pid();",
+	connstr => $common_connstr);
+is($result, 't|t', "ssl_client_get_notafter() for not_after timestamp");
+
 $result = $node->safe_psql(
 	"certdb",
 	"SELECT value, critical FROM ssl_extension_info() WHERE name = 'basicConstraints';",
-- 
2.17.1


------=_Part_2411907_1679044284.1749162704953--