Message-ID: <8a93f6c0-795f-4260-a5fd-07ccbddbc88f@uni-muenster.de>
Date: Fri, 16 Feb 2024 12:18:45 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Psql meta-command conninfo+
Content-Language: en-US
To: Maiquel Grassi <grassi@hotmail.com.br>,
 Pavel Luzanov <p.luzanov@postgrespro.ru>,
 Nathan Bossart <nathandbossart@gmail.com>
Cc: Erik Wienhold <ewie@ewie.name>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
References: <CP8P284MB24965CB63DAC00FC0EA4A475EC462@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
 <20240206215022.GA3452@nathanxps13>
 <CP8P284MB2496A17A9852265A0F363A6AEC452@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
 <852cabef-f596-4826-9a54-d3f75d9a6cc8@postgrespro.ru>
 <CP8P284MB24961361A9C1D32C28A7EC0EEC452@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
 <e289dada-543d-495f-9cd3-5783a8412bc0@postgrespro.ru>
 <CP4P284MB24830F5BF17FFA7F3D733142EC452@CP4P284MB2483.BRAP284.PROD.OUTLOOK.COM>
 <f34b2134-85d6-4615-8f3f-e29a2f6f04ab@postgrespro.ru>
 <CP8P284MB249652F5F0BE828C20A38348EC442@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
 <49edac91-d47f-4cac-ae12-d51cabbd87c9@postgrespro.ru>
 <CP8P284MB2496C7F14594722472C9FEBAEC482@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
 <49635a5b-3d32-4f05-8ce4-c55c70181888@uni-muenster.de>
 <CP8P284MB24969B39579E808F9F44CCF8EC4D2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
 <CP8P284MB249664C44A1793823C4546F8EC4D2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
From: Jim Jones <jim.jones@uni-muenster.de>
In-Reply-To: <CP8P284MB249664C44A1793823C4546F8EC4D2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/8a93f6c0-795f-4260-a5fd-07ccbddbc88f%40uni-muenster.de>
Precedence: bulk


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@uni-muenster.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@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        | 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