Add more columns to pg_stat_ssl

Add columns client_serial and issuer_dn to pg_stat_ssl.  These allow
uniquely identifying the client certificate.

Rename the existing column clientdn to client_dn, to make the naming
more consistent and easier to read.

Discussion: https://www.postgresql.org/message-id/flat/398754d8-6bb5-c5cf-e7b8-22e5f0983caf@2ndquadrant.com/

4 files changed, 54 insertions, 7 deletions

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index f4d9e9daf71..3e229c693c4 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -782,7 +782,9 @@ CREATE VIEW pg_stat_ssl AS
             S.sslcipher AS cipher,
             S.sslbits AS bits,
             S.sslcompression AS compression,
-            S.sslclientdn AS clientdn
+            S.ssl_client_dn AS client_dn,
+            S.ssl_client_serial AS client_serial,
+            S.ssl_issuer_dn AS issuer_dn
     FROM pg_stat_get_activity(NULL) AS S;
 
 CREATE VIEW pg_replication_slots AS
diff --git a/src/backend/libpq/be-secure-openssl.c b/src/backend/libpq/be-secure-openssl.c
index 789a9754090..a2779543ec1 100644
--- a/src/backend/libpq/be-secure-openssl.c
+++ b/src/backend/libpq/be-secure-openssl.c
@@ -1109,7 +1109,7 @@ be_tls_get_cipher(Port *port)
 }
 
 void
-be_tls_get_peerdn_name(Port *port, char *ptr, size_t len)
+be_tls_get_peer_subject_name(Port *port, char *ptr, size_t len)
 {
 	if (port->peer)
 		strlcpy(ptr, X509_NAME_to_cstring(X509_get_subject_name(port->peer)), len);
@@ -1117,6 +1117,35 @@ be_tls_get_peerdn_name(Port *port, char *ptr, size_t len)
 		ptr[0] = '\0';
 }
 
+void
+be_tls_get_peer_issuer_name(Port *port, char *ptr, size_t len)
+{
+	if (port->peer)
+		strlcpy(ptr, X509_NAME_to_cstring(X509_get_issuer_name(port->peer)), len);
+	else
+		ptr[0] = '\0';
+}
+
+void
+be_tls_get_peer_serial(Port *port, char *ptr, size_t len)
+{
+	if (port->peer)
+	{
+		ASN1_INTEGER *serial;
+		BIGNUM	   *b;
+		char	   *decimal;
+
+		serial = X509_get_serialNumber(port->peer);
+		b = ASN1_INTEGER_to_BN(serial, NULL);
+		decimal = BN_bn2dec(b);
+		BN_free(b);
+		strlcpy(ptr, decimal, len);
+		OPENSSL_free(decimal);
+	}
+	else
+		ptr[0] = '\0';
+}
+
 #ifdef HAVE_X509_GET_SIGNATURE_NID
 char *
 be_tls_get_certificate_hash(Port *port, size_t *len)
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 3b9e86f7702..81c64992518 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -2906,7 +2906,9 @@ pgstat_bestart(void)
 		beentry->st_sslstatus->ssl_compression = be_tls_get_compression(MyProcPort);
 		strlcpy(beentry->st_sslstatus->ssl_version, be_tls_get_version(MyProcPort), NAMEDATALEN);
 		strlcpy(beentry->st_sslstatus->ssl_cipher, be_tls_get_cipher(MyProcPort), NAMEDATALEN);
-		be_tls_get_peerdn_name(MyProcPort, beentry->st_sslstatus->ssl_clientdn, NAMEDATALEN);
+		be_tls_get_peer_subject_name(MyProcPort, beentry->st_sslstatus->ssl_client_dn, NAMEDATALEN);
+		be_tls_get_peer_serial(MyProcPort, beentry->st_sslstatus->ssl_client_serial, NAMEDATALEN);
+		be_tls_get_peer_issuer_name(MyProcPort, beentry->st_sslstatus->ssl_issuer_dn, NAMEDATALEN);
 	}
 	else
 	{
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 20ebcfbcdf6..b6ba856ebe6 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -541,7 +541,7 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
 Datum
 pg_stat_get_activity(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_ACTIVITY_COLS	24
+#define PG_STAT_GET_ACTIVITY_COLS	26
 	int			num_backends = pgstat_fetch_stat_numbackends();
 	int			curr_backend;
 	int			pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -652,15 +652,29 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 			values[20] = CStringGetTextDatum(beentry->st_sslstatus->ssl_cipher);
 			values[21] = Int32GetDatum(beentry->st_sslstatus->ssl_bits);
 			values[22] = BoolGetDatum(beentry->st_sslstatus->ssl_compression);
-			if (beentry->st_sslstatus->ssl_clientdn[0])
-				values[23] = CStringGetTextDatum(beentry->st_sslstatus->ssl_clientdn);
+
+			if (beentry->st_sslstatus->ssl_client_dn[0])
+				values[23] = CStringGetTextDatum(beentry->st_sslstatus->ssl_client_dn);
 			else
 				nulls[23] = true;
+
+			if (beentry->st_sslstatus->ssl_client_serial[0])
+				values[24] = DirectFunctionCall3(numeric_in,
+												 CStringGetDatum(beentry->st_sslstatus->ssl_client_serial),
+												 ObjectIdGetDatum(InvalidOid),
+												 Int32GetDatum(-1));
+			else
+				nulls[24] = true;
+
+			if (beentry->st_sslstatus->ssl_issuer_dn[0])
+				values[25] = CStringGetTextDatum(beentry->st_sslstatus->ssl_issuer_dn);
+			else
+				nulls[25] = true;
 		}
 		else
 		{
 			values[18] = BoolGetDatum(false);	/* ssl */
-			nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = true;
+			nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = nulls[25] = true;
 		}
 
 		/* Values only available to role member or pg_read_all_stats */