Re: Psql meta-command conninfo+

On 15.02.24 23:16, Maiquel Grassi wrote:
>
> Hi!
>
> (v16)
>
> In this version, I made a small adjustment to the indentation
> of the \conninfo code and described the columns as returned
> by \conninfo+ as suggested by Jim Jones.
>
>

I've performed the following tests with v16:

1) hostaddr=172.19.42.1

$ /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" -c "\conninfo+" -c "\conninfo"

Current Connection Information
-[ RECORD 1
]------+---------------------------------------------------------------------------------------------------------------------------
Database           | postgres
Authenticated User | jim
System User        |
cert:emailAddress=wwwadmin(at)uni-muenster(dot)de,CN=jim,OU=WWU
IT,O=Universitaet Muenster,L=Muenster,ST=Nordrhein-Westfalen,C=DE
Current User       | jim
Session User       | jim
Backend PID        | 386839
Server Address     | 172.19.42.1
Server Port        | 5432
Client Address     | 192.168.178.27
Client Port        | 35602
Socket Directory   |
Host               | server.uni-muenster.de
Encryption         | SSL
Protocol           | TLSv1.3
Cipher             | TLS_AES_256_GCM_SHA384
Compression        | off

You are connected to database "postgres" as user "jim" on host
"server.uni-muenster.de" (address "172.19.42.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off)

The same with non-superusers

$ /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" -c "SET ROLE foo" -c "\conninfo+"
-c "\conninfo"
SET
Current Connection Information
-[ RECORD 1
]------+---------------------------------------------------------------------------------------------------------------------------
Database           | postgres
Authenticated User | jim
System User        |
cert:emailAddress=wwwadmin(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        | 547733
Server Address     | 172.19.42.1
Server Port        | 5432
Client Address     | 192.168.178.27
Client Port        | 58508
Socket Directory   |
Host               | server.uni-muenster.de
Encryption         | SSL
Protocol           | TLSv1.3
Cipher             | TLS_AES_256_GCM_SHA384
Compression        | off

You are connected to database "postgres" as user "jim" on host
"server.uni-muenster.de" (address "172.19.42.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off)

2) -h 192.168.178.27

$ /usr/local/postgres-dev/bin/psql -x -U postgres -h 192.168.178.27 -c
"\conninfo+" -c "\conninfo"
Current Connection Information
-[ RECORD 1 ]------+-----------------------
Database           | postgres
Authenticated User | postgres
System User        |
Current User       | postgres
Session User       | postgres
Backend PID        | 399670
Server Address     | 192.168.178.27
Server Port        | 5432
Client Address     | 192.168.178.27
Client Port        | 44174
Socket Directory   |
Host               | 192.168.178.27
Encryption         | SSL
Protocol           | TLSv1.3
Cipher             | TLS_AES_256_GCM_SHA384
Compression        | off

You are connected to database "postgres" as user "postgres" on host
"192.168.178.27" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off)

3) via socket

$ /usr/local/postgres-dev/bin/psql -x -U postgres -c "\conninfo+" -c
"\conninfo"
Current Connection Information
-[ RECORD 1 ]------+---------
Database           | postgres
Authenticated User | postgres
System User        |
Current User       | postgres
Session User       | postgres
Backend PID        | 394273
Server Address     |
Server Port        | 5432
Client Address     |
Client Port        |
Socket Directory   | /tmp
Host               |

You are connected to database "postgres" as user "postgres" via socket
in "/tmp" at port "5432".

4) -h 127.0.0.1

$ /usr/local/postgres-dev/bin/psql -x -U postgres -h 127.0.0.1 -c
"\conninfo+" -c "\conninfo"
Current Connection Information
-[ RECORD 1 ]------+-----------------------
Database           | postgres
Authenticated User | postgres
System User        |
Current User       | postgres
Session User       | postgres
Backend PID        | 396070
Server Address     | 127.0.0.1
Server Port        | 5432
Client Address     | 127.0.0.1
Client Port        | 52528
Socket Directory   |
Host               | 127.0.0.1
Encryption         | SSL
Protocol           | TLSv1.3
Cipher             | TLS_AES_256_GCM_SHA384
Compression        | off

You are connected to database "postgres" as user "postgres" on host
"127.0.0.1" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off)

5) -h localhost

$ /usr/local/postgres-dev/bin/psql -x -U postgres -h localhost -c
"\conninfo+" -c "\conninfo"
Current Connection Information
-[ RECORD 1 ]------+-----------------------
Database           | postgres
Authenticated User | postgres
System User        |
Current User       | postgres
Session User       | postgres
Backend PID        | 397056
Server Address     | 127.0.0.1
Server Port        | 5432
Client Address     | 127.0.0.1
Client Port        | 53578
Socket Directory   |
Host               | localhost
Encryption         | SSL
Protocol           | TLSv1.3
Cipher             | TLS_AES_256_GCM_SHA384
Compression        | off

You are connected to database "postgres" as user "postgres" on host
"localhost" (address "127.0.0.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off)

6) -h 0

$ /usr/local/postgres-dev/bin/psql -x -U postgres -h 0 -c "\conninfo+"
-c "\conninfo"
Current Connection Information
-[ RECORD 1 ]------+-----------------------
Database           | postgres
Authenticated User | postgres
System User        |
Current User       | postgres
Session User       | postgres
Backend PID        | 406342
Server Address     | 127.0.0.1
Server Port        | 5432
Client Address     | 127.0.0.1
Client Port        | 38674
Socket Directory   |
Host               | 0
Encryption         | SSL
Protocol           | TLSv1.3
Cipher             | TLS_AES_256_GCM_SHA384
Compression        | off

You are connected to database "postgres" as user "postgres" on host "0"
(address "127.0.0.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off)

7) -h 0.0.0.0 - As you mentioned, this is one of the cases where host
and "server address" differ.
   I am not sure if it is an issue. Perhaps the other reviewers might
have an opinion on it

$ /usr/local/postgres-dev/bin/psql -x -U postgres -h 0.0.0.0 -c
"\conninfo+" -c "\conninfo"
Current Connection Information
-[ RECORD 1 ]------+-----------------------
Database           | postgres
Authenticated User | postgres
System User        |
Current User       | postgres
Session User       | postgres
Backend PID        | 404395
Server Address     | 127.0.0.1
Server Port        | 5432
Client Address     | 127.0.0.1
Client Port        | 54806
Socket Directory   |
Host               | 0.0.0.0
Encryption         | SSL
Protocol           | TLSv1.3
Cipher             | TLS_AES_256_GCM_SHA384
Compression        | off

You are connected to database "postgres" as user "postgres" on host
"0.0.0.0" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off)

> 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".

I'm not sure of the impact of this change in the existing \conninfo - at
least the cfbot and "make -j check-world" didn't complain.
I'll take a closer look at it as soon we have test cases.

Docs:

+        <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>

To keep it consistent with the other options, we might wanna use "+ is
appended" instead of "+ is specified" or "+ is given"

+        When <literal>+</literal> is given, more complete information
+        is displayed as a table.
+        </para>
+
+        <para>
+        "Database", "Authenticated User", "System User" (only for
PostgreSQL 16 or higher),
+        "Current User", "Session User", "Backend PID", "Server
Address", "Server Port",
+        "Client Address", "Client Port", "Socket Directory", and "Host"
columns are listed
+        by default when <literal>\conninfo+</literal> is invoked. The
columns "Encryption",
+        "Protocol", "Cipher", and "Compression" are added to this
output when TLS (SSL)
+        authentication is used. The same applies to GSS authentication
is used, where the
+        "GSSAPI" column is also added to the
<literal>\conninfo+</literal> output.

I think that a list with a brief description of all columns would be
more interesting in this case (it is just a suggestion based on personal
taste, so feel to ignore it)

I had something along these lines in mind:

Outputs a string containing information about the current database
connection.
When + is appended, it outputs a table containing the following columns:

* Database: lorem ipsum
* Authenticated User:lorem ipsum
* System User: lorem ipsum
* Current User: lorem ipsum
* Session User: lorem ipsum
* Backend PID: lorem ipsum
* Server Address: lorem ipsum
* Server Port: lorem ipsum
* Client Address:lorem ipsum
* Client Port: lorem ipsum
* Socket Directory: lorem ipsum
* Host: lorem ipsum

TLS (SSL) authentication

These columns are added to the table TLS (SSL) authentication is used

* Encryption:lorem ipsum
* Cipher:lorem ipsum
* Protocol:lorem ipsum

GSS authentication ...
...

Thanks

--
Jim