public inbox for [email protected]
help / color / mirror / Atom feedFrom: Maiquel Grassi <[email protected]>
To: Jim Jones <[email protected]>
To: Pavel Luzanov <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: Erik Wienhold <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: Psql meta-command conninfo+
Date: Thu, 15 Feb 2024 19:47:05 +0000
Message-ID: <CP8P284MB24969B39579E808F9F44CCF8EC4D2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <[email protected]>
References: <CP8P284MB24965CB63DAC00FC0EA4A475EC462@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<CP8P284MB2496B51FC5ED9BD6527D459DEC462@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<20240206210605.GA3903769@nathanxps13>
<20240206211205.GA3903996@nathanxps13>
<CP8P284MB24965479BA59DF5C2CC01CA0EC462@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<20240206215022.GA3452@nathanxps13>
<CP8P284MB2496A17A9852265A0F363A6AEC452@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<[email protected]>
<CP8P284MB24961361A9C1D32C28A7EC0EEC452@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<[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]>
>v14 applies cleanly and the SSL info is now shown as previously
>suggested. Here is a more comprehensive test:
>
>
>$ /usr/local/postgres-dev/bin/psql -x "\
> host=server.uni-muenster.de
> hostaddr=172.19.42.1
> user=jim dbname=postgres
> sslrootcert=server-certificates/server.crt
> sslcert=jim-certificates/jim.crt
> sslkey=jim-certificates/jim.key"
>
>psql (17devel)
>SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
>compression: off)
>Type "help" for help.
>
>postgres=# SET ROLE foo;
>SET
>postgres=> \conninfo+
>Current Connection Information
>-[ RECORD 1
>]------+---------------------------------------------------------------------------------------------------------------------------
>Database | postgres
>Authenticated User | jim
>System User | cert:[email protected],CN=jim,OU=WWU
>IT,O=Universitaet Muenster,L=Muenster,ST=Nordrhein-Westfalen,C=DE
>Current User | foo
>Session User | jim
>Backend PID | 278294
>Server Address | 172.19.42.1
>Server Port | 5432
>Client Address | 192.168.178.27
>Client Port | 54948
>Socket Directory |
>Host | server.uni-muenster.de
>Encryption | SSL
>Protocol | TLSv1.3
>Cipher | TLS_AES_256_GCM_SHA384
>Compression | off
>
>postgres=> \conninfo
>You are connected to database "postgres" as user "jim" on host
>"server.uni-muenster.de" (address "127.0.0.1") at port "5432".
>SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
>compression: off)
>A little odd is that "Server Address" of \conninfo+ differs from
>"address" of \conninfo...
----//----
Hi Jim!
Tests performed on CentOS Linux 7.
I made some further observations and concluded that there will
be more cases where the "address" from \conninfo will differ from
the "Server Address" from \conninfo+. Below is a more detailed example.
The input of "hostaddr" or "host" in the psql call can be any pointing to
"loopback local" and the connection will still be established. For example,
all of these are accepted:
Case (inet):
psql -x --host 0
psql -x --host 0.0.0.0
psql -x hostaddr=0
psql -x hostaddr=0.0.0.0
All these examples will have "Server Address" = 127.0.0.1
Case (inet6):
psql -x --host ::
psql -x --host * (this entry is not accepted)
psql -x --host \*
psql -x --host "*"
psql -x hostaddr=::
psql -x hostaddr=*
All these examples will have "Server Address" = ::1
Thus, the inet_server_addr() function will return 127.0.0.1 or ::1 which in some cases will differ from the "address" from \conninfo.
[postgres@localhost bin]$ ./psql -x hostaddr=0
Password for user postgres:
psql (17devel)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.
postgres=# SET ROLE maiquel;
SET
postgres=> \conninfo
You are connected to database "postgres" as user "postgres" on host "0" (address "0.0.0.0") at port "5432".
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 | scram-sha-256:postgres
Current User | maiquel
Session User | postgres
Backend PID | 15205
Server Address | 127.0.0.1
Server Port | 5432
Client Address | 127.0.0.1
Client Port | 57598
Socket Directory |
Host | 0
Encryption | SSL
Protocol | TLSv1.2
Cipher | ECDHE-RSA-AES256-GCM-SHA384
Compression | off
postgres=> \q
[postgres@localhost bin]$ ping 0.0.0.0
PING 0.0.0.0 (127.0.0.1) 56(84) bytes of data.
64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.061 ms
64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.069 ms
64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.071 ms
64 bytes from 127.0.0.1: icmp_seq=4 ttl=64 time=0.107 ms
^C
--- 0.0.0.0 ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3003ms
rtt min/avg/max/mdev = 0.061/0.077/0.107/0.017 ms
As demonstrated above, "address" = 0.0.0.0 and "Server Address" = 127.0.0.1 are divergent.
In practice, these IPs are the "same", and the ping from the example proves it.
However, we are concerned here with the psql user, and this may seem confusing to them at
first glance. I would like to propose a change in "address" so that it always returns the same as
"Server Address", that is, to use the inet_server_address() function in "address".
Result:
[postgres@localhost bin]$ ./psql -x hostaddr=0
psql (17devel)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "0" (address "127.0.0.1") at port "5432".
postgres=# \conninfo+
Current Connection Information
-[ RECORD 1 ]------+----------
Database | postgres
Authenticated User | postgres
System User |
Current User | postgres
Session User | postgres
Backend PID | 26859
Server Address | 127.0.0.1
Server Port | 5432
Client Address | 127.0.0.1
Client Port | 58254
Socket Directory |
Host | 0
----//----
>I think the documentation could add a little more info than just this:
>> When no + is specified, it simply prints a textual description of a
>>few connection options. When + is given, more complete information is
>>displayed as a table.
>Perhaps briefly mentioning the returned columns or simply listing them
>would be IMHO a nice addition. For some users the semantics of
>"Authenticated User", "System User", "Current User" and "Session User"
>can be a little confusing. And yes, I realize the current documentation
>of \conninfo is already a little vague :).
Your suggestion was well received, and I'will made the adjustment to make
the command description more comprehensive.
Here is version v15 where I sought to correct 'Adress' to make it the same
as 'Server Address'.
Could you perform the same test and validate?
Thank you so much!
Maiquel Grassi.
Attachments:
[application/octet-stream] v15-0001-psql-meta-command-conninfo-plus.patch (9.6K, ../CP8P284MB24969B39579E808F9F44CCF8EC4D2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM/3-v15-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..4e86ad9 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1029,10 +1029,14 @@ 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 specified, it simply prints
+ a textual description of a few connection options.
+ When <literal>+</literal> is given, more complete information
+ is displayed as a table.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 5c906e4..af69b74 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,75 @@ 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;
+
+ /*
+ * \conninfo+
+ */
+ if (show_verbose)
+ success = listConnectionInformation();
- if (db == NULL)
- printf(_("You are currently not connected to a database.\n"));
+ /*
+ * \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 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..2ef3db0 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,91 @@ 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");
+ 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,
+ " ,'SSL' AS \"Encryption\",\n"
+ " '%s' AS \"Protocol\",\n"
+ " '%s' AS \"Cipher\",\n"
+ " '%s' AS \"Compression\"\n",
+ protocol ? protocol : _("unknown"),
+ cipher ? cipher : _("unknown"),
+ (compression && strcmp(compression, "off") != 0) ? _("on") : _("off"));
+ }
+ if (PQgssEncInUse(pset.db))
+ appendPQExpBuffer(&buf,
+ " ,'GSSAPI' AS \"Encryption\"\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: <CP8P284MB24969B39579E808F9F44CCF8EC4D2@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