>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=j...@uni-muenster.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@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.
v15-0001-psql-meta-command-conninfo-plus.patch
Description: v15-0001-psql-meta-command-conninfo-plus.patch