Subject: Re: Psql meta-command conninfo+

From: Alvaro Herrera <[email-masked]>
Date: Fri, 21 Feb 2025 19:19:10 +0100

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/
>From 6bb3628336d7a51d4d1afd92c03fd133c652a02c Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <[email-masked]>
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 <[email-masked]>
Author: Hunaid Sohail <[email-masked]>
Discussion: https://postgr.es/m/[email-masked]
---
 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

---

From: Maiquel Grassi <[email-masked]>
Date: Fri, 21 Feb 2025 19:08:44 +0000

>From my perspective across this, I support this approach.

Regards,
Maiquel.

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 12 Jun 2025 16:25:22 +0200

> complexity, and has \conninfo (no plus sign) output this:

This new code uses the term "TLS" where the rest of PostgreSQL, 
including the rest of psql, uses the term "SSL".  Making this different 
seems uselessly confusing.  I suggest the attached patch to use "SSL" 
here as well.

From 6a45955233f5c6737552494c85076850d5f3fe69 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Thu, 12 Jun 2025 16:19:48 +0200
Subject: [PATCH] psql: Change new \conninfo to use SSL instead of TLS

Commit bba2fbc6238 introduced a new implementation of the \conninfo
command in psql.  That new code uses the term "TLS" while the rest of
PostgreSQL, including the rest of psql, consistently uses "SSL".  This
is uselessly confusing.  This changes the new code to use "SSL" as
well.
---
 doc/src/sgml/ref/psql-ref.sgml |  2 +-
 src/bin/psql/command.c         | 16 ++++++++--------
 2 files changed, 9 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 8f7d8758ca0..cb8b4b0c1f8 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1101,7 +1101,7 @@ <title>Meta-Commands</title>
         <listitem>
          <para>
           Outputs information about the current database connection,
-          including TLS-related information if TLS is in use.
+          including SSL-related information if SSL is in use.
          </para>
         </listitem>
       </varlistentry>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 81a5ba844ba..30b4d7eda45 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -874,11 +874,11 @@ exec_command_conninfo(PsqlScanState scan_state, bool active_branch)
 	printTableAddCell(&cont, _("Backend PID"), false, false);
 	printTableAddCell(&cont, backend_pid, false, false);
 
-	/* TLS Connection */
-	printTableAddCell(&cont, _("TLS Connection"), false, false);
+	/* SSL Connection */
+	printTableAddCell(&cont, _("SSL Connection"), false, false);
 	printTableAddCell(&cont, ssl_in_use ? _("true") : _("false"), false, false);
 
-	/* TLS Information */
+	/* SSL Information */
 	if (ssl_in_use)
 	{
 		char	   *library,
@@ -895,19 +895,19 @@ exec_command_conninfo(PsqlScanState scan_state, bool active_branch)
 		compression = (char *) PQsslAttribute(pset.db, "compression");
 		alpn = (char *) PQsslAttribute(pset.db, "alpn");
 
-		printTableAddCell(&cont, _("TLS Library"), false, false);
+		printTableAddCell(&cont, _("SSL Library"), false, false);
 		printTableAddCell(&cont, library ? library : _("unknown"), false, false);
 
-		printTableAddCell(&cont, _("TLS Protocol"), false, false);
+		printTableAddCell(&cont, _("SSL Protocol"), false, false);
 		printTableAddCell(&cont, protocol ? protocol : _("unknown"), false, false);
 
-		printTableAddCell(&cont, _("TLS Key Bits"), false, false);
+		printTableAddCell(&cont, _("SSL Key Bits"), false, false);
 		printTableAddCell(&cont, key_bits ? key_bits : _("unknown"), false, false);
 
-		printTableAddCell(&cont, _("TLS Cipher"), false, false);
+		printTableAddCell(&cont, _("SSL Cipher"), false, false);
 		printTableAddCell(&cont, cipher ? cipher : _("unknown"), false, false);
 
-		printTableAddCell(&cont, _("TLS Compression"), false, false);
+		printTableAddCell(&cont, _("SSL Compression"), false, false);
 		printTableAddCell(&cont, (compression && strcmp(compression, "off") != 0) ?
 						  _("true") : _("false"), false, false);
 
-- 
2.49.0

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 12 Jun 2025 17:28:39 +0200

On 2025-Jun-12, Peter Eisentraut wrote:

> This new code uses the term "TLS" where the rest of PostgreSQL, including
> the rest of psql, uses the term "SSL".  Making this different seems
> uselessly confusing.  I suggest the attached patch to use "SSL" here as
> well.

Sure, let's do that for now.

I think in the long term (19?) we should replace all uses of SSL with
TLS.

---

From: Peter Eisentraut <[email-masked]>
Date: Sun, 15 Jun 2025 11:13:32 +0200

> Sure, let's do that for now.

done