Hi,
I have attached a new patch that incorporates the approach suggested by
David. The documentation has also been updated.
$ bin/psql "port=5430 sslmode=disable dbname=postgres" -x -h localhost
psql (18devel)
Type "help" for help.
postgres=# \conninfo+
Connection Information
-[ RECORD 1 ]+----------
Database | postgres
Client User | hunaid
Host | localhost
Host Address | 127.0.0.1
Port | 5430
Options |
Current Status
-[ RECORD 1 ]--------+------
Protocol Version | 3
Password Used | false
GSSAPI Authenticated | false
Backend PID | 26268
Server Parameter Settings
-[ RECORD 1 ]---------+-------
Superuser | true
Client Encoding | UTF8
Server Encoding | UTF8
Session Authorization | hunaid
Connection Encryption
-[ RECORD 1 ]--+------
SSL Connection | false
$ bin/psql "port=5430 sslmode=require dbname=postgres" -x -h localhost
psql (18devel)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off, ALPN: postgresql)
Type "help" for help.
postgres=# \conninfo+ E
Connection Encryption
-[ RECORD 1 ]--+-----------------------
SSL Connection | true
Library | OpenSSL
Protocol | TLSv1.3
Key Bits | 256
Cipher | TLS_AES_256_GCM_SHA384
Compression | off
ALPN | postgresql
I’m unsure if we need to expand the documentation further. I would
appreciate your suggestions on this.
Regards,
Hunaid Sohail
On Mon, Oct 7, 2024 at 9:31 PM David G. Johnston <david.g.johns...@gmail.com>
wrote:
> On Sun, Oct 6, 2024 at 11:17 PM Hunaid Sohail <hunaidp...@gmail.com>
> wrote:
>
>>
>> PQpass - no need
>>
>
> I would include this as presence/absence.
>
> I concur on all of the rest.
>
>
>>
>> For PQparameterStatus, some parameters are already used.
>> server_version and application_name were already discussed and removed in
>> v12 and v29 respectively. Do we need other parameters?
>>
>
> Ok, I'll need to go read the reasoning for why they are deemed unneeded
> and form an opinion one way or the other.
>
>
>>
>>> Within that framework having \conninfo[+[CSE][…]] be the command -
>>> printing out only the table specified would be the behavior (specifying no
>>> suffix letters prints all three) - would be an option.
>>>
>>
>> 3 separate tables without suffix?
>>
>
> Yes, the tables need headers specific to their categories.
>
> I do like the idea of having 4 though, placing settings into their own.
> Premised on having all or most of the available parameters being on the
> table. If it only ends up being a few of them then keeping those in
> the status table makes sense.
>
> David J.
>
>>
From 5fc7a9b2a80933641b67c30b5909a0aed810f0cd Mon Sep 17 00:00:00 2001
From: Hunaid Sohail <hunaid2000@gmail.com>
Date: Wed, 30 Oct 2024 10:44:21 +0500
Subject: [PATCH v36] Add psql meta command conninfo+
---
doc/src/sgml/ref/psql-ref.sgml | 23 ++-
src/bin/psql/command.c | 328 +++++++++++++++++++++++++++++++--
src/bin/psql/help.c | 2 +-
3 files changed, 328 insertions(+), 25 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index b825ca96a2..c620bcd94e 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1060,11 +1060,26 @@ INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
</varlistentry>
<varlistentry id="app-psql-meta-command-conninfo">
- <term><literal>\conninfo</literal></term>
+ <term><literal>\conninfo[+[C|S|P|E]]</literal></term>
<listitem>
- <para>
- Outputs information about the current database connection.
- </para>
+ <para>
+ Outputs information about the current database connection.
+ When <literal>+</literal> is appended, all details about the
+ connection are displayed in table format.
+ The modifiers can be:
+ <simplelist>
+ <member><literal>C:</literal> Displays connection information, including:
+ Database, Client User, Host, Host Address, Port, and Options.</member>
+ <member><literal>S:</literal> Displays the current connection status, including:
+ Protocol Version, Password Used, GSSAPI Authenticated, and Backend PID.</member>
+ <member><literal>P:</literal> Displays parameter settings of the server, including:
+ Superuser, Client Encoding, Server Encoding, and Session Authorization.</member>
+ <member><literal>E:</literal> Displays connection encryption details, including:
+ SSL Connection, Library, Protocol, Key Bits, Cipher, Compression, and ALPN.</member>
+ </simplelist>
+ If no modifier is specified, all available details are displayed in separate tables.
+ If connection is not using SSL, related encryption details are not displayed.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 328d78c73f..8a5b8d1ea9 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -72,7 +72,8 @@ static backslashResult exec_command_cd(PsqlScanState scan_state, bool active_bra
const char *cmd);
static backslashResult exec_command_close(PsqlScanState scan_state, bool active_branch,
const char *cmd);
-static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch);
+static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch,
+ const char *cmd);
static backslashResult exec_command_copy(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_copyright(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_crosstabview(PsqlScanState scan_state, bool active_branch);
@@ -179,6 +180,10 @@ static int count_lines_in_buf(PQExpBuffer buf);
static void print_with_linenumbers(FILE *output, char *lines, bool is_func);
static void minimal_error_message(PGresult *res);
+static void printConnInfo(char *db, char *host, char *hostaddr);
+static void printConnStatus(void);
+static void printConnParameterSettings(void);
+static void printConnEncryption(void);
static void printSSLInfo(void);
static void printGSSInfo(void);
static bool printPsetInfo(const char *param, printQueryOpt *popt);
@@ -328,8 +333,8 @@ exec_command(const char *cmd,
status = exec_command_cd(scan_state, active_branch, cmd);
else if (strcmp(cmd, "close") == 0)
status = exec_command_close(scan_state, active_branch, cmd);
- else if (strcmp(cmd, "conninfo") == 0)
- status = exec_command_conninfo(scan_state, active_branch);
+ else if (strcmp(cmd, "conninfo") == 0 || strcmp(cmd, "conninfo+") == 0)
+ status = exec_command_conninfo(scan_state, active_branch, cmd);
else if (pg_strcasecmp(cmd, "copy") == 0)
status = exec_command_copy(scan_state, active_branch);
else if (strcmp(cmd, "copyright") == 0)
@@ -738,11 +743,14 @@ exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd
}
/*
- * \conninfo -- display information about the current connection
+ * \conninfo, \conninfo+ -- display information about the current connection
*/
static backslashResult
-exec_command_conninfo(PsqlScanState scan_state, bool active_branch)
+exec_command_conninfo(PsqlScanState scan_state, bool active_branch, const char *cmd)
{
+ backslashResult status = PSQL_CMD_SKIP_LINE;
+ bool show_verbose = strchr(cmd, '+') ? true : false;
+
if (active_branch)
{
char *db = PQdb(pset.db);
@@ -754,31 +762,75 @@ exec_command_conninfo(PsqlScanState scan_state, bool active_branch)
char *host = PQhost(pset.db);
char *hostaddr = PQhostaddr(pset.db);
- if (is_unixsock_path(host))
+ if (!show_verbose)
{
- /* 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));
+ 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
- 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));
+ {
+ 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();
}
+ /* Print additional information about the connection in tabular format */
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));
+ char *opt = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, true);
+
+ /* opt can be C/S/E/P
+ * C - Connection information
+ * S - Connection status
+ * P - Parameter settings of the server
+ * E - Connection encryption
+ * If no modifier is provided, all the above information is displayed
+ */
+ if (opt)
+ {
+ if (strcmp(opt, "C") == 0)
+ printConnInfo(db, host, hostaddr);
+ else if (strcmp(opt, "S") == 0)
+ printConnStatus();
+ else if (strcmp(opt, "P") == 0)
+ printConnParameterSettings();
+ else if (strcmp(opt, "E") == 0)
+ printConnEncryption();
+ else
+ {
+ pg_log_error("invalid modifier \"%s\". Only C/S/P/E are allowed.", opt);
+ status = PSQL_CMD_ERROR;
+ }
+
+ free(opt);
+ }
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));
+ {
+ printConnInfo(db, host, hostaddr);
+ printConnStatus();
+ printConnParameterSettings();
+ printConnEncryption();
+ }
}
- printSSLInfo();
- printGSSInfo();
}
}
+ else
+ ignore_slash_whole_line(scan_state);
- return PSQL_CMD_SKIP_LINE;
+ return status;
}
/*
@@ -3998,6 +4050,242 @@ connection_warnings(bool in_startup)
}
}
+/*
+ * printConnInfo
+ *
+ * Prints information about the current connection in a tabular format.
+ * This function is called when \conninfo+ is used with modifier 'C'.
+ */
+static void
+printConnInfo(char *db, char *host, char *hostaddr)
+{
+ printQueryOpt popt = pset.popt;
+ printTableContent cont;
+ int cols,
+ rows;
+
+ /* Fixed number of columns */
+ cols = 5;
+ rows = 1;
+ /* +1 column if hostaddr is different from host */
+ if (!is_unixsock_path(host) && hostaddr && *hostaddr && strcmp(host, hostaddr) != 0)
+ cols++;
+
+ /* Print the information in a table */
+ printTableInit(&cont, &popt.topt, _("Connection Information"), cols, rows);
+
+ /* Database */
+ printTableAddHeader(&cont, _("Database"), true, 'l');
+ printTableAddCell(&cont, db, false, false);
+
+ /* Client User */
+ printTableAddHeader(&cont, _("Client User"), true, 'l');
+ printTableAddCell(&cont, PQuser(pset.db), false, false);
+
+ /* Host/Socket Information */
+ if (is_unixsock_path(host))
+ {
+ if (hostaddr && *hostaddr)
+ {
+ printTableAddHeader(&cont, _("Host Address"), true, 'l');
+ printTableAddCell(&cont, hostaddr, false, false);
+ }
+ else
+ {
+ printTableAddHeader(&cont, _("Socket Directory"), true, 'l');
+ printTableAddCell(&cont, host, false, false);
+ }
+ }
+ else
+ {
+ printTableAddHeader(&cont, _("Host"), true, 'l');
+ printTableAddCell(&cont, host, false, false);
+ if (hostaddr && *hostaddr && strcmp(host, hostaddr) != 0)
+ {
+ printTableAddHeader(&cont, _("Host Address"), true, 'l');
+ printTableAddCell(&cont, hostaddr, false, false);
+ }
+ }
+
+ /* Port */
+ printTableAddHeader(&cont, _("Port"), true, 'l');
+ printTableAddCell(&cont, PQport(pset.db), false, false);
+
+ /* Options */
+ printTableAddHeader(&cont, _("Options"), true, 'l');
+ printTableAddCell(&cont, PQoptions(pset.db), false, false);
+
+ printTable(&cont, pset.queryFout, false, pset.logfile);
+ printTableCleanup(&cont);
+}
+
+/*
+ * printConnStatus
+ *
+ * Prints status information about the current connection in a tabular format.
+ * This function is called when \conninfo+ is used with modifier 'S'.
+ */
+static void
+printConnStatus()
+{
+ printQueryOpt popt = pset.popt;
+ printTableContent cont;
+ char protocol_version[10],
+ backend_pid[10];
+ int cols,
+ rows;
+ int gssapi_used,
+ password_used;
+
+ /* Get values for the parameters */
+ sprintf(protocol_version, "%d", PQprotocolVersion(pset.db));
+ password_used = PQconnectionUsedPassword(pset.db);
+ gssapi_used = PQconnectionUsedGSSAPI(pset.db);
+ sprintf(backend_pid, "%d", PQbackendPID(pset.db));
+
+ /* Fixed number of columns */
+ cols = 4;
+ rows = 1;
+
+ /* Print the information in a table */
+ printTableInit(&cont, &popt.topt, _("Current Status"), cols, rows);
+
+ /* Protocol Version */
+ printTableAddHeader(&cont, _("Protocol Version"), true, 'l');
+ printTableAddCell(&cont, protocol_version, false, false);
+
+ /* Password Used */
+ printTableAddHeader(&cont, _("Password Used"), true, 'l');
+ printTableAddCell(&cont, password_used ? _("true") : _("false"), false, false);
+
+ /* GSSAPI Authenticated */
+ printTableAddHeader(&cont, _("GSSAPI Authenticated"), true, 'l');
+ printTableAddCell(&cont, gssapi_used ? _("true") : _("false"), false, false);
+
+ /* Backend PID */
+ printTableAddHeader(&cont, _("Backend PID"), true, 'l');
+ printTableAddCell(&cont, backend_pid, false, false);
+
+ printTable(&cont, pset.queryFout, false, pset.logfile);
+ printTableCleanup(&cont);
+}
+
+/*
+ * printConnParameterSettings
+ *
+ * Prints parameter settings of the current connection in a tabular format.
+ * This function is called when \conninfo+ is used with modifier 'P'.
+ */
+static void
+printConnParameterSettings()
+{
+ printQueryOpt popt = pset.popt;
+ printTableContent cont;
+ int cols,
+ rows;
+ char *is_superuser,
+ *client_encoding,
+ *server_encoding,
+ *session_authorization;
+
+ /* Get values for the parameters */
+ is_superuser = (char *) PQparameterStatus(pset.db, "is_superuser");
+ client_encoding = (char *) PQparameterStatus(pset.db, "client_encoding");
+ server_encoding = (char *) PQparameterStatus(pset.db, "server_encoding");
+ session_authorization = (char *) PQparameterStatus(pset.db, "session_authorization");
+
+ /* Fixed number of columns */
+ cols = 4;
+ rows = 1;
+
+ /* Print the information in a table */
+ printTableInit(&cont, &popt.topt, _("Server Parameter Settings"), cols, rows);
+
+ /* Is Superuser */
+ printTableAddHeader(&cont, _("Superuser"), true, 'l');
+ printTableAddCell(&cont, is_superuser ? strcmp(is_superuser, "on") == 0
+ ? _("true") : _("false") : _("unknown"), false, false);
+
+ /* Client Encoding */
+ printTableAddHeader(&cont, _("Client Encoding"), true, 'l');
+ printTableAddCell(&cont, client_encoding ? client_encoding : _("none"), false, false);
+
+ /* Server Encoding */
+ printTableAddHeader(&cont, _("Server Encoding"), true, 'l');
+ printTableAddCell(&cont, server_encoding ? server_encoding : _("none"), false, false);
+
+ /* Session Authorization */
+ printTableAddHeader(&cont, _("Session Authorization"), true, 'l');
+ printTableAddCell(&cont, session_authorization ? session_authorization : _("none"), false, false);
+
+ printTable(&cont, pset.queryFout, false, pset.logfile);
+ printTableCleanup(&cont);
+}
+
+/*
+ * printConnEncryption
+ *
+ * Prints information about the current connection encryption in a tabular format.
+ * This function is called when \conninfo+ is used with modifier 'E'.
+ */
+static void
+printConnEncryption()
+{
+ printQueryOpt popt = pset.popt;
+ printTableContent cont;
+ int cols,
+ rows;
+ int ssl_in_use;
+ char *library,
+ *protocol,
+ *key_bits,
+ *cipher,
+ *compression,
+ *alpn;
+
+ /* Get values for the parameters */
+ ssl_in_use = PQsslInUse(pset.db);
+ 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");
+
+ /* Fixed number of columns */
+ cols = 1;
+ rows = 1;
+ /* +6 columns if SSL is in use */
+ if (ssl_in_use)
+ cols += 6;
+
+ /* Print the information in a table */
+ printTableInit(&cont, &popt.topt, _("Connection Encryption"), cols, rows);
+
+ /* SSL Connection */
+ printTableAddHeader(&cont, _("SSL Connection"), true, 'l');
+ printTableAddCell(&cont, ssl_in_use ? _("true") : _("false"), false, false);
+
+ /* SSL Information */
+ if (ssl_in_use)
+ {
+ printTableAddHeader(&cont, _("Library"), true, 'l');
+ printTableAddCell(&cont, library ? library : _("unknown"), false, false);
+ printTableAddHeader(&cont, _("Protocol"), true, 'l');
+ printTableAddCell(&cont, protocol ? protocol : _("unknown"), false, false);
+ printTableAddHeader(&cont, _("Key Bits"), true, 'l');
+ printTableAddCell(&cont, key_bits ? key_bits : _("unknown"), false, false);
+ printTableAddHeader(&cont, _("Cipher"), true, 'l');
+ printTableAddCell(&cont, cipher ? cipher : _("unknown"), false, false);
+ printTableAddHeader(&cont, _("Compression"), true, 'l');
+ printTableAddCell(&cont, (compression && strcmp(compression, "off") != 0) ? _("on") : _("off"), false, false);
+ printTableAddHeader(&cont, _("ALPN"), true, 'l');
+ printTableAddCell(&cont, (alpn && alpn[0] != '\0') ? alpn : _("none"), false, false);
+ }
+
+ printTable(&cont, pset.queryFout, false, pset.logfile);
+ printTableCleanup(&cont);
+}
/*
* printSSLInfo
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 19d20c5878..35f792e09e 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -313,7 +313,7 @@ slashUsage(unsigned short int pager)
else
HELP0(" \\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}\n"
" connect to new database (currently no connection)\n");
- HELP0(" \\conninfo display information about current connection\n");
+ HELP0(" \\conninfo[+[C|S|P|E]] display information about current connection\n");
HELP0(" \\encoding [ENCODING] show or set client encoding\n");
HELP0(" \\parse STMT_NAME create a prepared statement\n");
HELP0(" \\password [USERNAME] securely change the password for a user\n");
--
2.34.1
