public inbox for [email protected]
help / color / mirror / Atom feedFrom: Maiquel Grassi <[email protected]>
To: Nathan Bossart <[email protected]>
To: Jim Jones <[email protected]>
Cc: Pavel Luzanov <[email protected]>
Cc: Erik Wienhold <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: Psql meta-command conninfo+
Date: Sat, 17 Feb 2024 14:53:43 +0000
Message-ID: <CP8P284MB2496AAFB879ABC5B1CD01CF4EC532@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <20240216155449.GA1236054@nathanxps13>
References: <[email protected]>
<CP4P284MB24830F5BF17FFA7F3D733142EC452@CP4P284MB2483.BRAP284.PROD.OUTLOOK.COM>
<[email protected]>
<CP8P284MB249652F5F0BE828C20A38348EC442@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<[email protected]>
<CP8P284MB2496C7F14594722472C9FEBAEC482@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<[email protected]>
<CP8P284MB24969B39579E808F9F44CCF8EC4D2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<CP8P284MB249664C44A1793823C4546F8EC4D2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<[email protected]>
<20240216155449.GA1236054@nathanxps13>
Hi Nathan!
(v18)
>I might be alone on this, but I think this command should output the same
>columns regardless of the version, whether it's using SSL, etc. and just
>put NULL in any that do not apply. IMHO that would simplify the code and
>help prevent confusion. Plus, I'm not aware of any existing meta-commands
>that provide certain columns conditionally.
I implemented your suggestion. Now, whenever the \conninfo+ command is
invoked, all columns will appear. Contextually inappropriate cases will return
NULL. I also adjusted the names of the columns related to SSL to make this
information clearer for the user. I haven't focused on documenting the
columns yet. I will do that soon.
>Could we pull some of this information from pg_stat_ssl instead of from
>libpq? The reason I suggest this is because I think it would be nice if
>the query that \conninfo+ uses could be copy/pasted as needed and not rely
>on hard-coded values chosen by the client.
I've been considering using the views "pg_stat_ssl" and "pg_stat_gssapi"; however,
I realized that dealing with version-related cases using them is more complicated.
Let me explain the reasons:
The "pg_stat_ssl" view is available from >= PG 9.5, and the "pg_stat_gssapi" view is
available from >= PG 12. The "compression" column was removed from the
"pg_stat_ssl" from >= PG 14. All of these cases introduce greater complexity in
maintaining the SQL. The central idea from the beginning has always been to show
the user all the information from \conninfo and extend it in \conninfo+. The absence
of the "compression" column in version 14 and above makes dealing with this even
more complicated, and not showing it seems to contradict \conninfo.
SSL support has been available since version 7.1 (see documentation); if there was
support before that, I can't say. In this regard, it may seem strange, but there are still
many legacy systems running older versions of PostgreSQL. Just yesterday, I assisted
a client who is still using PG 8.2. In these cases, using the "pg_stat_ssl" and
"pg_stat_gssapi" views would not be possible because they don't exist on the server.
I believe that psql should cover as many cases as possible when it comes to compatibility
with older versions (even those no longer supported). In this case, concerning SSL and
GSS, I think libpq is better prepared to handle this.
I may be mistaken in my statement and I welcome any better suggestions. For now, I've
maintained the implementation using libpq as it seems to be working well and is not
contradicting \conninfo. If you have any suggestions on how to work around the absence
of the "compression" column, we can reconsider how to implement it without using libpq.
Tests:
[postgres@localhost bin]$ ./psql -x -h 127.0.0.1 -p 5432
Password for user postgres:
psql (17devel)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432".
postgres=# \conninfo+
Current Connection Information
-[ RECORD 1 ]------+-----------------------
Database | postgres
Authenticated User | postgres
System User | scram-sha-256:postgres
Current User | postgres
Session User | postgres
Backend PID | 22431
Server Address | 127.0.0.1
Server Port | 5432
Client Address | 127.0.0.1
Client Port | 51300
Socket Directory |
Host | 127.0.0.1
SSL Connection | f
SSL Protocol |
SSL Cipher |
SSL Compression |
GSSAPI | f
[postgres@localhost bin]$ ./psql -x -h 127.0.0.1 -p 5433
Password for user postgres:
psql (17devel, server 15.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5433".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
postgres=# \conninfo+
Current Connection Information
-[ RECORD 1 ]------+----------------------------
Database | postgres
Authenticated User | postgres
System User |
Current User | postgres
Session User | postgres
Backend PID | 22438
Server Address | 127.0.0.1
Server Port | 5433
Client Address | 127.0.0.1
Client Port | 36016
Socket Directory |
Host | 127.0.0.1
SSL Connection | t
SSL Protocol | TLSv1.2
SSL Cipher | ECDHE-RSA-AES256-GCM-SHA384
SSL Compression | off
GSSAPI | f
Thank you very much for your sugestions and help!
Maiquel Grassi.
Attachments:
[application/octet-stream] v18-0001-psql-meta-command-conninfo-plus.patch (11.6K, ../CP8P284MB2496AAFB879ABC5B1CD01CF4EC532@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM/3-v18-0001-psql-meta-command-conninfo-plus.patch)
download | inline diff:
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cc7d797..d6b92fb 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1029,11 +1029,84 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
</varlistentry>
<varlistentry id="app-psql-meta-command-conninfo">
- <term><literal>\conninfo</literal></term>
+ <term><literal>\conninfo[+]</literal></term>
<listitem>
<para>
Outputs information about the current database connection.
+ When no <literal>+</literal> is appended, it simply prints
+ a textual (string) description of a few connection options.
+ When <literal>+</literal> is given, more complete information
+ is displayed as a table containing the following columns:
</para>
+
+ <para>
+ <literal>Database:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>Authenticated User:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>System User:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>Current User:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>Session User:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>Backend PID:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>Server Address:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>Server Port:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>Client Address:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>Client Port:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>Socket Directory:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>Host:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>SSL Connection:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>SSL Protocol:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>SSL Cipher:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>SLL Compression:</literal> blah blah blah
+ </para>
+
+ <para>
+ <literal>GSSAPI:</literal> blah blah blah
+ </para>
+
</listitem>
</varlistentry>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 5c906e4..cb5429f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -68,7 +68,8 @@ static backslashResult exec_command_C(PsqlScanState scan_state, bool active_bran
static backslashResult exec_command_connect(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_cd(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);
@@ -317,8 +318,8 @@ exec_command(const char *cmd,
status = exec_command_connect(scan_state, active_branch);
else if (strcmp(cmd, "cd") == 0)
status = exec_command_cd(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)
@@ -643,47 +644,80 @@ exec_command_cd(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)
{
+ bool success = true;
+
if (active_branch)
{
char *db = PQdb(pset.db);
+ bool show_verbose;
+
+ show_verbose = strchr(cmd, '+') ? true : false;
- if (db == NULL)
- printf(_("You are currently not connected to a database.\n"));
+ /*
+ * \conninfo+
+ */
+ if (show_verbose)
+ success = listConnectionInformation();
+
+ /*
+ * \conninfo
+ */
else
{
- char *host = PQhost(pset.db);
- char *hostaddr = PQhostaddr(pset.db);
+ PGresult *res;
+ PQExpBufferData buf;
- 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));
- }
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " inet_server_addr();");
+
+ res = PSQLexec(buf.data);
+
+ termPQExpBuffer(&buf);
+
+ if (!res)
+ return PSQL_CMD_ERROR;
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 *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), PQgetvalue(res, 0, 0), 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\" on host \"%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, PQgetvalue(res, 0, 0), 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));
+ }
+ PQclear(res);
+ printSSLInfo();
+ printGSSInfo();
}
- printSSLInfo();
- printGSSInfo();
}
}
+ else
+ ignore_slash_options(scan_state);
- return PSQL_CMD_SKIP_LINE;
+ return success ? PSQL_CMD_SKIP_LINE : PSQL_CMD_ERROR;
}
/*
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index b6a4eb1..9a83592 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -26,6 +26,7 @@
#include "fe_utils/mbprint.h"
#include "fe_utils/print.h"
#include "fe_utils/string_utils.h"
+#include "libpq/pqcomm.h"
#include "settings.h"
#include "variables.h"
@@ -901,6 +902,105 @@ error_return:
return false;
}
+/*
+ * listConnectionInformation
+ *
+ * for \conninfo+
+ */
+bool
+listConnectionInformation(void)
+{
+ PGresult *res;
+ PQExpBufferData buf;
+ printQueryOpt myopt = pset.popt;
+
+ char *host = PQhost(pset.db);
+ char *hostaddr = PQhostaddr(pset.db);
+
+ const char *protocol;
+ const char *cipher;
+ const char *compression;
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " pg_catalog.current_database() AS \"Database\",\n"
+ " '%s' AS \"Authenticated User\",\n",
+ PQuser(pset.db));
+ if (pset.sversion >= 160000)
+ appendPQExpBuffer(&buf,
+ " pg_catalog.system_user() AS \"System User\",\n");
+ else
+ appendPQExpBuffer(&buf,
+ " NULL AS \"System User\",\n");
+ appendPQExpBuffer(&buf,
+ " pg_catalog.current_user() AS \"Current User\",\n"
+ " pg_catalog.session_user() AS \"Session User\",\n"
+ " pg_catalog.pg_backend_pid() AS \"Backend PID\",\n"
+ " pg_catalog.inet_server_addr() AS \"Server Address\",\n"
+ " pg_catalog.current_setting('port') AS \"Server Port\",\n"
+ " pg_catalog.inet_client_addr() AS \"Client Address\",\n"
+ " pg_catalog.inet_client_port() AS \"Client Port\",\n");
+ if (is_unixsock_path(host) && !(hostaddr && *hostaddr))
+ appendPQExpBuffer(&buf,
+ " '%s' AS \"Socket Directory\",\n",
+ host);
+ else
+ appendPQExpBuffer(&buf,
+ " NULL AS \"Socket Directory\",\n");
+ appendPQExpBuffer(&buf,
+ " CASE\n"
+ " WHEN\n"
+ " pg_catalog.inet_server_addr() IS NULL\n"
+ " AND pg_catalog.inet_client_addr() IS NULL\n"
+ " THEN NULL\n"
+ " ELSE '%s'\n"
+ " END AS \"Host\",\n",
+ host);
+ if (PQsslInUse(pset.db))
+ {
+ protocol = PQsslAttribute(pset.db, "protocol");
+ cipher = PQsslAttribute(pset.db, "cipher");
+ compression = PQsslAttribute(pset.db, "compression");
+
+ appendPQExpBuffer(&buf,
+ " true AS \"SSL Connection\",\n"
+ " '%s' AS \"SSL Protocol\",\n"
+ " '%s' AS \"SSL Cipher\",\n"
+ " '%s' AS \"SSL Compression\",\n",
+ protocol ? protocol : _("unknown"),
+ cipher ? cipher : _("unknown"),
+ (compression && strcmp(compression, "off") != 0) ? _("on") : _("off"));
+ }
+ else
+ appendPQExpBuffer(&buf,
+ " false AS \"SSL Connection\",\n"
+ " NULL AS \"SSL Protocol\",\n"
+ " NULL AS \"SSL Cipher\",\n"
+ " NULL AS \"SSL Compression\",\n");
+ if (PQgssEncInUse(pset.db))
+ appendPQExpBuffer(&buf,
+ " true AS \"GSSAPI\"\n");
+ else
+ appendPQExpBuffer(&buf,
+ " false AS \"GSSAPI\"\n");
+ appendPQExpBuffer(&buf,
+ ";");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.title = _("Current Connection Information");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
/*
* listAllDbs
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 273f974..8a4e83f 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -64,6 +64,9 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
/* \dFt */
extern bool listTSTemplates(const char *pattern, bool verbose);
+/* \conninfo */
+extern bool listConnectionInformation(void);
+
/* \l */
extern bool listAllDbs(const char *pattern, bool verbose);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 4e79a81..2c5426d 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -310,7 +310,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[+] display information about current connection\n");
HELP0(" \\encoding [ENCODING] show or set client encoding\n");
HELP0(" \\password [USERNAME] securely change the password for a user\n");
HELP0("\n");
view thread (43+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: RE: Psql meta-command conninfo+
In-Reply-To: <CP8P284MB2496AAFB879ABC5B1CD01CF4EC532@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox