Feedback-ID: ia2694551:Fastmail
Date: Fri, 21 Feb 2025 19:19:10 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Hunaid Sohail <hunaidpgml@gmail.com>
Cc: Maiquel Grassi <grassi@hotmail.com.br>, 
	Sami Imseih <samimseih@gmail.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>, 
	"David G. Johnston" <david.g.johnston@gmail.com>, Jim Jones <jim.jones@uni-muenster.de>, 
	Tom Lane <tgl@sss.pgh.pa.us>, Nathan Bossart <nathandbossart@gmail.com>, 
	"Imseih (AWS), Sami" <simseih@amazon.com>, Peter Eisentraut <peter@eisentraut.org>, 
	Pavel Luzanov <p.luzanov@postgrespro.ru>, Erik Wienhold <ewie@ewie.name>, 
	Dean Rasheed <dean.a.rasheed@gmail.com>
Subject: Re: Psql meta-command conninfo+
Message-ID: <202502211819.uevfb34wedav@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="2nvccac7zpqpydf3"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CAMWA6yZ=X=11ccJH86SDOOP-QCQp7Z3bHSWjJNOzxfnNF=OXYg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/202502211819.uevfb34wedav%40alvherre.pgsql>
Precedence: bulk


--2nvccac7zpqpydf3
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

I suggest the attached, which gets 99% there with 10% of the
complexity, and has \conninfo (no plus sign) output this:

             Connection Information
       Parámetro       │         Valor          
───────────────────────┼────────────────────────
 Base de Datos         │ alvherre
 Client User           │ alvherre
 Host                  │ 192.168.178.37
 Port                  │ 55432
 Opciones              │ 
 Protocol Version      │ 3
 Password Used?        │ false
 GSSAPI Authenticated? │ false
 Backend PID           │ 1589499
 TLS Connection?       │ true
 TLS Library           │ OpenSSL
 TLS Protocol          │ TLSv1.3
 TLS Key Bits          │ 256
 TLS Cipher            │ TLS_AES_256_GCM_SHA384
 TLS Compression       │ false
 ALPN                  │ postgresql
 Superuser?            │ on
 Hot standby?          │ off
(18 filas)

I have added the parameters is_superuser and in_hot_standby only, and
stayed away from the libpq part of the patch to enumerate parameters.
ISTM a hardcoded list is fine.

Maybe keeping track of 'role' via ParameterStatus messages is a good
idea for reasons unrelated to this patch -- maybe it can be useful for
applications to be aware of role changes -- but I'm not 100% sure about
that, and in particular I'm not sure how heavy the protocol traffic is
going to be if such messages are emitted every time you run a security
invoker function or things like that.  So I'm leaving that part out for
now, and it's easy to do both the libpq patch and \conninfo.


Also, I don't see why we have to keep the current free-format \conninfo.
When you run a query, you get things like

# select 99 as "Luftballons";
 Luftballons 
─────────────
          99

You don't get "There were only 99 Luftballons" or any such nonsense, and I
don't see why \conninfo gets to play different rules.  So I got rid of
\conninfo+.

Do people hate the question marks?

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

--2nvccac7zpqpydf3
Content-Type: text/x-diff; charset=utf-8
Content-Disposition: attachment;
	filename="0001-Change-conninfo-to-use-tabular-format.patch"

From 6bb3628336d7a51d4d1afd92c03fd133c652a02c Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@alvh.no-ip.org>
Date: Fri, 21 Feb 2025 18:31:13 +0100
Subject: [PATCH] Change \conninfo to use tabular format

Also display more fields than before.

Author: Maiquel Grassi <grassi@hotmail.com.br>
Author: Hunaid Sohail <hunaidpgml@gmail.com>
Discussion: https://postgr.es/m/CP8P284MB24965CB63DAC00FC0EA4A475EC462@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM
---
 doc/src/sgml/ref/psql-ref.sgml |   7 +-
 src/bin/psql/command.c         | 186 ++++++++++++++++++++++++++++-----
 2 files changed, 161 insertions(+), 32 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index a8a0b694bbf..b6fe75efdd4 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1070,9 +1070,10 @@ INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
       <varlistentry id="app-psql-meta-command-conninfo">
         <term><literal>\conninfo</literal></term>
         <listitem>
-        <para>
-        Outputs information about the current database connection.
-        </para>
+          <para>
+            Outputs information about the current database connection,
+            including TLS-related information if TLS is in use.
+          </para>
         </listitem>
       </varlistentry>
 
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 12d99eec5a3..1eef1983d83 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -763,40 +763,168 @@ exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd
 static backslashResult
 exec_command_conninfo(PsqlScanState scan_state, bool active_branch)
 {
-	if (active_branch)
-	{
-		char	   *db = PQdb(pset.db);
+	printTableContent cont;
+	int			rows,
+				cols;
+	char	   *db;
+	char	   *host;
+	bool		print_hostaddr;
+	char	   *hostaddr;
+	char	   *protocol_version,
+			   *backend_pid;
+	int			ssl_in_use,
+				password_used,
+				gssapi_used;
+	char	   *paramval;
 
-		if (db == NULL)
-			printf(_("You are currently not connected to a database.\n"));
+	if (!active_branch)
+		return PSQL_CMD_SKIP_LINE;
+
+	db = PQdb(pset.db);
+	if (db == NULL)
+	{
+		printf(_("You are currently not connected to a database.\n"));
+		return PSQL_CMD_SKIP_LINE;
+	}
+
+	/* Get values for the parameters */
+	host = PQhost(pset.db);
+	hostaddr = PQhostaddr(pset.db);
+	protocol_version = psprintf("%d", PQprotocolVersion(pset.db));
+	ssl_in_use = PQsslInUse(pset.db);
+	password_used = PQconnectionUsedPassword(pset.db);
+	gssapi_used = PQconnectionUsedGSSAPI(pset.db);
+	backend_pid = psprintf("%d", PQbackendPID(pset.db));
+
+	/* Only print hostaddr if it differs from host, and not if unixsock */
+	print_hostaddr = (!is_unixsock_path(host) &&
+					  hostaddr && *hostaddr && strcmp(host, hostaddr) != 0);
+
+	/* Determine the exact number of rows to print */
+	rows = 12;
+	cols = 2;
+	if (ssl_in_use)
+		rows += 6;
+	if (print_hostaddr)
+		rows++;
+
+	/* Set it all up */
+	printTableInit(&cont, &pset.popt.topt, _("Connection Information"), cols, rows);
+	printTableAddHeader(&cont, _("Parameter"), true, 'l');
+	printTableAddHeader(&cont, _("Value"), true, 'l');
+
+	/* Database */
+	printTableAddCell(&cont, _("Database"), false, false);
+	printTableAddCell(&cont, db, false, false);
+
+	/* Client User */
+	printTableAddCell(&cont, _("Client User"), false, false);
+	printTableAddCell(&cont, PQuser(pset.db), false, false);
+
+	/* Host/hostaddr/socket */
+	if (is_unixsock_path(host))
+	{
+		/* hostaddr if specified overrides socket, so suppress the latter */
+		if (hostaddr && *hostaddr)
+		{
+			printTableAddCell(&cont, _("Host Address"), false, false);
+			printTableAddCell(&cont, hostaddr, false, false);
+		}
 		else
 		{
-			char	   *host = PQhost(pset.db);
-			char	   *hostaddr = PQhostaddr(pset.db);
-
-			if (is_unixsock_path(host))
-			{
-				/* hostaddr overrides host */
-				if (hostaddr && *hostaddr)
-					printf(_("You are connected to database \"%s\" as user \"%s\" on address \"%s\" at port \"%s\".\n"),
-						   db, PQuser(pset.db), hostaddr, PQport(pset.db));
-				else
-					printf(_("You are connected to database \"%s\" as user \"%s\" via socket in \"%s\" at port \"%s\".\n"),
-						   db, PQuser(pset.db), host, PQport(pset.db));
-			}
-			else
-			{
-				if (hostaddr && *hostaddr && strcmp(host, hostaddr) != 0)
-					printf(_("You are connected to database \"%s\" as user \"%s\" on host \"%s\" (address \"%s\") at port \"%s\".\n"),
-						   db, PQuser(pset.db), host, hostaddr, PQport(pset.db));
-				else
-					printf(_("You are connected to database \"%s\" as user \"%s\" on host \"%s\" at port \"%s\".\n"),
-						   db, PQuser(pset.db), host, PQport(pset.db));
-			}
-			printSSLInfo();
-			printGSSInfo();
+			printTableAddCell(&cont, _("Socket Directory"), false, false);
+			printTableAddCell(&cont, host, false, false);
 		}
 	}
+	else
+	{
+		printTableAddCell(&cont, _("Host"), false, false);
+		printTableAddCell(&cont, host, false, false);
+		if (print_hostaddr)
+		{
+			printTableAddCell(&cont, _("Host Address"), false, false);
+			printTableAddCell(&cont, hostaddr, false, false);
+		}
+	}
+
+	/* Port */
+	printTableAddCell(&cont, _("Port"), false, false);
+	printTableAddCell(&cont, PQport(pset.db), false, false);
+
+	/* Options */
+	printTableAddCell(&cont, _("Options"), false, false);
+	printTableAddCell(&cont, PQoptions(pset.db), false, false);
+
+	/* Protocol Version */
+	printTableAddCell(&cont, _("Protocol Version"), false, false);
+	printTableAddCell(&cont, protocol_version, false, false);
+
+	/* Password Used */
+	printTableAddCell(&cont, _("Password Used?"), false, false);
+	printTableAddCell(&cont, password_used ? _("true") : _("false"), false, false);
+
+	/* GSSAPI Authenticated */
+	printTableAddCell(&cont, _("GSSAPI Authenticated?"), false, false);
+	printTableAddCell(&cont, gssapi_used ? _("true") : _("false"), false, false);
+
+	/* Backend PID */
+	printTableAddCell(&cont, _("Backend PID"), false, false);
+	printTableAddCell(&cont, backend_pid, false, false);
+
+	/* TLS Connection */
+	printTableAddCell(&cont, _("TLS Connection?"), false, false);
+	printTableAddCell(&cont, ssl_in_use ? _("true") : _("false"), false, false);
+
+	/* TLS Information */
+	if (ssl_in_use)
+	{
+		char	   *library,
+				   *protocol,
+				   *key_bits,
+				   *cipher,
+				   *compression,
+				   *alpn;
+
+		library = (char *) PQsslAttribute(pset.db, "library");
+		protocol = (char *) PQsslAttribute(pset.db, "protocol");
+		key_bits = (char *) PQsslAttribute(pset.db, "key_bits");
+		cipher = (char *) PQsslAttribute(pset.db, "cipher");
+		compression = (char *) PQsslAttribute(pset.db, "compression");
+		alpn = (char *) PQsslAttribute(pset.db, "alpn");
+
+		printTableAddCell(&cont, _("TLS Library"), false, false);
+		printTableAddCell(&cont, library ? library : _("unknown"), false, false);
+
+		printTableAddCell(&cont, _("TLS Protocol"), false, false);
+		printTableAddCell(&cont, protocol ? protocol : _("unknown"), false, false);
+
+		printTableAddCell(&cont, _("TLS Key Bits"), false, false);
+		printTableAddCell(&cont, key_bits ? key_bits : _("unknown"), false, false);
+
+		printTableAddCell(&cont, _("TLS Cipher"), false, false);
+		printTableAddCell(&cont, cipher ? cipher : _("unknown"), false, false);
+
+		printTableAddCell(&cont, _("TLS Compression"), false, false);
+		printTableAddCell(&cont, (compression && strcmp(compression, "off") != 0) ?
+						  _("true") : _("false"), false, false);
+
+		printTableAddCell(&cont, _("ALPN"), false, false);
+		printTableAddCell(&cont, (alpn && alpn[0] != '\0') ? alpn : _("none"), false, false);
+	}
+
+	paramval = (char *) PQparameterStatus(pset.db, "is_superuser");
+	printTableAddCell(&cont, "Superuser?", false, false);
+	printTableAddCell(&cont, paramval ? paramval : _("unknown"), false, false);
+
+	paramval = (char *) PQparameterStatus(pset.db, "in_hot_standby");
+	printTableAddCell(&cont, "Hot standby?", false, false);
+	printTableAddCell(&cont, paramval ? paramval : _("unknown"), false, false);
+
+	printTable(&cont, pset.queryFout, false, pset.logfile);
+	printTableCleanup(&cont);
+
+	pfree(protocol_version);
+	pfree(backend_pid);
 
 	return PSQL_CMD_SKIP_LINE;
 }
-- 
2.39.5


--2nvccac7zpqpydf3--