RE: Psql meta-command conninfo+

>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:emailAddress=jim(at)uni-muenster(dot)de,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(at)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(at)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(at)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.