Add system view pg_stat_ssl

This view shows information about all connections, such as if the
connection is using SSL, which cipher is used, and which client
certificate (if any) is used.

Reviews by Alex Shulgin, Heikki Linnakangas, Andres Freund & Michael Paquier

9 files changed, 243 insertions, 7 deletions

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 2800f73fb6e..a4fd88fa98d 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -646,6 +646,17 @@ CREATE VIEW pg_stat_replication AS
     WHERE S.usesysid = U.oid AND
             S.pid = W.pid;
 
+CREATE VIEW pg_stat_ssl AS
+    SELECT
+            S.pid,
+            S.ssl,
+            S.sslversion AS version,
+            S.sslcipher AS cipher,
+            S.sslbits AS bits,
+            S.sslcompression AS compression,
+            S.sslclientdn AS clientdn
+    FROM pg_stat_get_activity(NULL) AS S;
+
 CREATE VIEW pg_replication_slots AS
     SELECT
             L.slot_name,
diff --git a/src/backend/libpq/be-secure-openssl.c b/src/backend/libpq/be-secure-openssl.c
index b06f987b3fd..f7f6618bc21 100644
--- a/src/backend/libpq/be-secure-openssl.c
+++ b/src/backend/libpq/be-secure-openssl.c
@@ -90,6 +90,8 @@ static void info_cb(const SSL *ssl, int type, int args);
 static void initialize_ecdh(void);
 static const char *SSLerrmessage(void);
 
+static char *X509_NAME_to_cstring(X509_NAME *name);
+
 /* are we in the middle of a renegotiation? */
 static bool in_ssl_renegotiation = false;
 
@@ -1040,3 +1042,105 @@ SSLerrmessage(void)
 	snprintf(errbuf, sizeof(errbuf), _("SSL error code %lu"), errcode);
 	return errbuf;
 }
+
+/*
+ * Return information about the SSL connection
+ */
+int
+be_tls_get_cipher_bits(Port *port)
+{
+	int bits;
+
+	if (port->ssl)
+	{
+		SSL_get_cipher_bits(port->ssl, &bits);
+		return bits;
+	}
+	else
+		return 0;
+}
+
+bool
+be_tls_get_compression(Port *port)
+{
+	if (port->ssl)
+		return (SSL_get_current_compression(port->ssl) != NULL);
+	else
+		return false;
+}
+
+void
+be_tls_get_version(Port *port, char *ptr, size_t len)
+{
+	if (port->ssl)
+		strlcpy(ptr, SSL_get_version(port->ssl), len);
+	else
+		ptr[0] = '\0';
+}
+
+void
+be_tls_get_cipher(Port *port, char *ptr, size_t len)
+{
+	if (port->ssl)
+		strlcpy(ptr, SSL_get_cipher(port->ssl), len);
+	else
+		ptr[0] = '\0';
+}
+
+void
+be_tls_get_peerdn_name(Port *port, char *ptr, size_t len)
+{
+	if (port->peer)
+		strlcpy(ptr, X509_NAME_to_cstring(X509_get_subject_name(port->peer)), len);
+	else
+		ptr[0] = '\0';
+}
+
+/*
+ * Convert an X509 subject name to a cstring.
+ *
+ */
+static char *
+X509_NAME_to_cstring(X509_NAME *name)
+{
+	BIO		   *membuf = BIO_new(BIO_s_mem());
+	int			i,
+				nid,
+				count = X509_NAME_entry_count(name);
+	X509_NAME_ENTRY *e;
+	ASN1_STRING *v;
+	const char *field_name;
+	size_t		size;
+	char		nullterm;
+	char	   *sp;
+	char	   *dp;
+	char	   *result;
+
+	(void) BIO_set_close(membuf, BIO_CLOSE);
+	for (i = 0; i < count; i++)
+	{
+		e = X509_NAME_get_entry(name, i);
+		nid = OBJ_obj2nid(X509_NAME_ENTRY_get_object(e));
+		v = X509_NAME_ENTRY_get_data(e);
+		field_name = OBJ_nid2sn(nid);
+		if (!field_name)
+			field_name = OBJ_nid2ln(nid);
+		BIO_printf(membuf, "/%s=", field_name);
+		ASN1_STRING_print_ex(membuf, v,
+							 ((ASN1_STRFLGS_RFC2253 & ~ASN1_STRFLGS_ESC_MSB)
+							  | ASN1_STRFLGS_UTF8_CONVERT));
+	}
+
+	/* ensure null termination of the BIO's content */
+	nullterm = '\0';
+	BIO_write(membuf, &nullterm, 1);
+	size = BIO_get_mem_data(membuf, &sp);
+	dp = pg_any_to_server(sp, size - 1, PG_UTF8);
+
+	result = pstrdup(dp);
+	if (dp != sp)
+		pfree(dp);
+	BIO_free(membuf);
+
+	return result;
+}
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 0e7c1544ec0..1e6073abca4 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -2482,6 +2482,9 @@ static char *BackendClientHostnameBuffer = NULL;
 static char *BackendAppnameBuffer = NULL;
 static char *BackendActivityBuffer = NULL;
 static Size BackendActivityBufferSize = 0;
+#ifdef USE_SSL
+static PgBackendSSLStatus *BackendSslStatusBuffer = NULL;
+#endif
 
 
 /*
@@ -2563,6 +2566,26 @@ CreateSharedBackendStatus(void)
 		}
 	}
 
+#ifdef USE_SSL
+	/* Create or attach to the shared SSL status buffer */
+	size = mul_size(sizeof(PgBackendSSLStatus), MaxBackends);
+	BackendSslStatusBuffer = (PgBackendSSLStatus *)
+		ShmemInitStruct("Backend SSL Status Buffer", size, &found);
+
+	if (!found)
+	{
+		MemSet(BackendSslStatusBuffer, 0, size);
+
+		/* Initialize st_sslstatus pointers. */
+		buffer = (char *) BackendSslStatusBuffer;
+		for (i = 0; i < MaxBackends; i++)
+		{
+			BackendStatusArray[i].st_sslstatus = (PgBackendSSLStatus *)buffer;
+			buffer += sizeof(PgBackendSSLStatus);
+		}
+	}
+#endif
+
 	/* Create or attach to the shared activity buffer */
 	BackendActivityBufferSize = mul_size(pgstat_track_activity_query_size,
 										 MaxBackends);
@@ -2672,6 +2695,23 @@ pgstat_bestart(void)
 				NAMEDATALEN);
 	else
 		beentry->st_clienthostname[0] = '\0';
+#ifdef USE_SSL
+	if (MyProcPort && MyProcPort->ssl != NULL)
+	{
+		beentry->st_ssl = true;
+		beentry->st_sslstatus->ssl_bits = be_tls_get_cipher_bits(MyProcPort);
+		beentry->st_sslstatus->ssl_compression = be_tls_get_compression(MyProcPort);
+		be_tls_get_version(MyProcPort, beentry->st_sslstatus->ssl_version, NAMEDATALEN);
+		be_tls_get_cipher(MyProcPort, beentry->st_sslstatus->ssl_cipher, NAMEDATALEN);
+		be_tls_get_peerdn_name(MyProcPort, beentry->st_sslstatus->ssl_clientdn, NAMEDATALEN);
+	}
+	else
+	{
+		beentry->st_ssl = false;
+	}
+#else
+	beentry->st_ssl = false;
+#endif
 	beentry->st_waiting = false;
 	beentry->st_state = STATE_UNDEFINED;
 	beentry->st_appname[0] = '\0';
@@ -2892,6 +2932,9 @@ pgstat_read_current_status(void)
 	volatile PgBackendStatus *beentry;
 	LocalPgBackendStatus *localtable;
 	LocalPgBackendStatus *localentry;
+#ifdef USE_SSL
+	PgBackendSSLStatus *localsslstatus;
+#endif
 	char	   *localappname,
 			   *localactivity;
 	int			i;
@@ -2908,6 +2951,12 @@ pgstat_read_current_status(void)
 	localappname = (char *)
 		MemoryContextAlloc(pgStatLocalContext,
 						   NAMEDATALEN * MaxBackends);
+#ifdef USE_SSL
+	localsslstatus = (PgBackendSSLStatus *)
+		MemoryContextAlloc(pgStatLocalContext,
+						   sizeof(PgBackendSSLStatus) * MaxBackends);
+#endif
+
 	localactivity = (char *)
 		MemoryContextAlloc(pgStatLocalContext,
 						   pgstat_track_activity_query_size * MaxBackends);
@@ -2944,6 +2993,14 @@ pgstat_read_current_status(void)
 				localentry->backendStatus.st_appname = localappname;
 				strcpy(localactivity, (char *) beentry->st_activity);
 				localentry->backendStatus.st_activity = localactivity;
+				localentry->backendStatus.st_ssl = beentry->st_ssl;
+#ifdef USE_SSL
+				if (beentry->st_ssl)
+				{
+					memcpy(localsslstatus, beentry->st_sslstatus, sizeof(PgBackendSSLStatus));
+					localentry->backendStatus.st_sslstatus = localsslstatus;
+				}
+#endif
 			}
 
 			pgstat_save_changecount_after(beentry, after_changecount);
@@ -2966,6 +3023,9 @@ pgstat_read_current_status(void)
 			localentry++;
 			localappname += NAMEDATALEN;
 			localactivity += pgstat_track_activity_query_size;
+#ifdef USE_SSL
+			localsslstatus += sizeof(PgBackendSSLStatus);
+#endif
 			localNumBackends++;
 		}
 	}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 78adb2d853c..bbe94c34a15 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -538,7 +538,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 
 		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
 
-		tupdesc = CreateTemplateTupleDesc(16, false);
+		tupdesc = CreateTemplateTupleDesc(22, false);
 		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "datid",
 						   OIDOID, -1, 0);
 		TupleDescInitEntry(tupdesc, (AttrNumber) 2, "pid",
@@ -571,6 +571,18 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 						   XIDOID, -1, 0);
 		TupleDescInitEntry(tupdesc, (AttrNumber) 16, "backend_xmin",
 						   XIDOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 17, "ssl",
+						   BOOLOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 18, "sslversion",
+						   TEXTOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 19, "sslcipher",
+						   TEXTOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 20, "sslbits",
+						   INT4OID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 21, "sslcompression",
+						   BOOLOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 22, "sslclientdn",
+						   TEXTOID, -1, 0);
 
 		funcctx->tuple_desc = BlessTupleDesc(tupdesc);
 
@@ -622,8 +634,8 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 	if (funcctx->call_cntr < funcctx->max_calls)
 	{
 		/* for each row */
-		Datum		values[16];
-		bool		nulls[16];
+		Datum		values[22];
+		bool		nulls[22];
 		HeapTuple	tuple;
 		LocalPgBackendStatus *local_beentry;
 		PgBackendStatus *beentry;
@@ -676,6 +688,21 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 		else
 			nulls[15] = true;
 
+		if (beentry->st_ssl)
+		{
+			values[16] = BoolGetDatum(true); /* ssl */
+			values[17] = CStringGetTextDatum(beentry->st_sslstatus->ssl_version);
+			values[18] = CStringGetTextDatum(beentry->st_sslstatus->ssl_cipher);
+			values[19] = Int32GetDatum(beentry->st_sslstatus->ssl_bits);
+			values[20] = BoolGetDatum(beentry->st_sslstatus->ssl_compression);
+			values[21] = CStringGetTextDatum(beentry->st_sslstatus->ssl_clientdn);
+		}
+		else
+		{
+			values[16] = BoolGetDatum(false); /* ssl */
+			nulls[17] = nulls[18] = nulls[19] = nulls[20] = nulls[21] = true;
+		}
+
 		/* Values only available to role member */
 		if (has_privs_of_role(GetUserId(), beentry->st_userid))
 		{
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 5191526bef7..8ecd5fd9af8 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201504061
+#define CATALOG_VERSION_NO	201504121
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 62187fb2741..9caa0960d50 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2756,7 +2756,7 @@ DATA(insert OID = 3057 ( pg_stat_get_autoanalyze_count PGNSP PGUID 12 1 0 0 0 f
 DESCR("statistics: number of auto analyzes for a table");
 DATA(insert OID = 1936 (  pg_stat_get_backend_idset		PGNSP PGUID 12 1 100 0 0 f f f f t t s 0 0 23 "" _null_ _null_ _null_ _null_ pg_stat_get_backend_idset _null_ _null_ _null_ ));
 DESCR("statistics: currently active backend IDs");
-DATA(insert OID = 2022 (  pg_stat_get_activity			PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23,28,28}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));
+DATA(insert OID = 2022 (  pg_stat_get_activity			PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23,28,28,16,25,25,23,16,25}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,ssl,sslversion,sslcipher,sslbits,sslcompression,sslclientdn}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));
 DESCR("statistics: information about currently active backends");
 DATA(insert OID = 3099 (  pg_stat_get_wal_senders	PGNSP PGUID 12 1 10 0 0 f f f f f t s 0 0 2249 "" "{23,25,3220,3220,3220,3220,23,25}" "{o,o,o,o,o,o,o,o}" "{pid,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state}" _null_ pg_stat_get_wal_senders _null_ _null_ _null_ ));
 DESCR("statistics: information about currently active replication");
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index cf520f545d9..f323ed87106 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -212,6 +212,11 @@ extern void be_tls_close(Port *port);
 extern ssize_t be_tls_read(Port *port, void *ptr, size_t len, int *waitfor);
 extern ssize_t be_tls_write(Port *port, void *ptr, size_t len, int *waitfor);
 
+extern int be_tls_get_cipher_bits(Port *port);
+extern bool be_tls_get_compression(Port *port);
+extern void be_tls_get_version(Port *port, char *ptr, size_t len);
+extern void be_tls_get_cipher(Port *port, char *ptr, size_t len);
+extern void be_tls_get_peerdn_name(Port *port, char *ptr, size_t len);
 #endif
 
 extern ProtocolVersion FrontendProtocol;
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index f2b2257a115..e3fe06e95be 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -701,6 +701,23 @@ typedef enum BackendState
  */
 
 
+/*
+ * PgBackendSSLStatus
+ *
+ * For each backend, we keep the SSL status in a separate struct, that
+ * is only filled in if SSL is enabled.
+ */
+typedef struct PgBackendSSLStatus
+{
+	/* Information about SSL connection */
+	int		ssl_bits;
+	bool	ssl_compression;
+	char	ssl_version[NAMEDATALEN];  /* MUST be null-terminated */
+	char	ssl_cipher[NAMEDATALEN];   /* MUST be null-terminated */
+	char	ssl_clientdn[NAMEDATALEN]; /* MUST be null-terminated */
+} PgBackendSSLStatus;
+
+
 /* ----------
  * PgBackendStatus
  *
@@ -744,6 +761,10 @@ typedef struct PgBackendStatus
 	SockAddr	st_clientaddr;
 	char	   *st_clienthostname;		/* MUST be null-terminated */
 
+	/* Information about SSL connection */
+	bool		st_ssl;
+	PgBackendSSLStatus *st_sslstatus;
+
 	/* Is backend currently waiting on an lmgr lock? */
 	bool		st_waiting;
 
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 17882704d48..71fa44a6bea 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1633,7 +1633,7 @@ pg_stat_activity| SELECT s.datid,
     s.backend_xmin,
     s.query
    FROM pg_database d,
-    pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin),
+    pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn),
     pg_authid u
   WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
 pg_stat_all_indexes| SELECT c.oid AS relid,
@@ -1739,10 +1739,18 @@ pg_stat_replication| SELECT s.pid,
     w.replay_location,
     w.sync_priority,
     w.sync_state
-   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin),
+   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn),
     pg_authid u,
     pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state)
   WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));
+pg_stat_ssl| SELECT s.pid,
+    s.ssl,
+    s.sslversion AS version,
+    s.sslcipher AS cipher,
+    s.sslbits AS bits,
+    s.sslcompression AS compression,
+    s.sslclientdn AS clientdn
+   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn);
 pg_stat_sys_indexes| SELECT pg_stat_all_indexes.relid,
     pg_stat_all_indexes.indexrelid,
     pg_stat_all_indexes.schemaname,