RE: Psql meta-command conninfo+

From: Maiquel Grassi <grassi(at)hotmail(dot)com(dot)br>
To: Nathan Bossart <nathandbossart(at)gmail(dot)com>, Jim Jones <jim(dot)jones(at)uni-muenster(dot)de>
Cc: Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru>, Erik Wienhold <ewie(at)ewie(dot)name>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: RE: Psql meta-command conninfo+
Date: 2024-02-17 14:53:43
Message-ID: CP8P284MB2496AAFB879ABC5B1CD01CF4EC532@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Nathan!

(v18)

>I might be alone on this, but I think this command should output the same
>columns regardless of the version, whether it's using SSL, etc. and just
>put NULL in any that do not apply. IMHO that would simplify the code and
>help prevent confusion. Plus, I'm not aware of any existing meta-commands
>that provide certain columns conditionally.

I implemented your suggestion. Now, whenever the \conninfo+ command is
invoked, all columns will appear. Contextually inappropriate cases will return
NULL. I also adjusted the names of the columns related to SSL to make this
information clearer for the user. I haven't focused on documenting the
columns yet. I will do that soon.

>Could we pull some of this information from pg_stat_ssl instead of from
>libpq? The reason I suggest this is because I think it would be nice if
>the query that \conninfo+ uses could be copy/pasted as needed and not rely
>on hard-coded values chosen by the client.

I've been considering using the views "pg_stat_ssl" and "pg_stat_gssapi"; however,
I realized that dealing with version-related cases using them is more complicated.

Let me explain the reasons:

The "pg_stat_ssl" view is available from >= PG 9.5, and the "pg_stat_gssapi" view is
available from >= PG 12. The "compression" column was removed from the
"pg_stat_ssl" from >= PG 14. All of these cases introduce greater complexity in
maintaining the SQL. The central idea from the beginning has always been to show
the user all the information from \conninfo and extend it in \conninfo+. The absence
of the "compression" column in version 14 and above makes dealing with this even
more complicated, and not showing it seems to contradict \conninfo.

SSL support has been available since version 7.1 (see documentation); if there was

support before that, I can't say. In this regard, it may seem strange, but there are still
many legacy systems running older versions of PostgreSQL. Just yesterday, I assisted
a client who is still using PG 8.2. In these cases, using the "pg_stat_ssl" and
"pg_stat_gssapi" views would not be possible because they don't exist on the server.
I believe that psql should cover as many cases as possible when it comes to compatibility
with older versions (even those no longer supported). In this case, concerning SSL and
GSS, I think libpq is better prepared to handle this.

I may be mistaken in my statement and I welcome any better suggestions. For now, I've
maintained the implementation using libpq as it seems to be working well and is not
contradicting \conninfo. If you have any suggestions on how to work around the absence
of the "compression" column, we can reconsider how to implement it without using libpq.

Tests:

[postgres(at)localhost bin]$ ./psql -x -h 127.0.0.1 -p 5432

Password for user postgres:
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432".
postgres=# \conninfo+
Current Connection Information
-[ RECORD 1 ]------+-----------------------
Database | postgres
Authenticated User | postgres
System User | scram-sha-256:postgres
Current User | postgres
Session User | postgres
Backend PID | 22431
Server Address | 127.0.0.1
Server Port | 5432
Client Address | 127.0.0.1
Client Port | 51300
Socket Directory |
Host | 127.0.0.1
SSL Connection | f
SSL Protocol |
SSL Cipher |
SSL Compression |
GSSAPI | f

[postgres(at)localhost bin]$ ./psql -x -h 127.0.0.1 -p 5433
Password for user postgres:
psql (17devel, server 15.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5433".
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 |
Current User | postgres
Session User | postgres
Backend PID | 22438
Server Address | 127.0.0.1
Server Port | 5433
Client Address | 127.0.0.1
Client Port | 36016
Socket Directory |
Host | 127.0.0.1
SSL Connection | t
SSL Protocol | TLSv1.2
SSL Cipher | ECDHE-RSA-AES256-GCM-SHA384
SSL Compression | off
GSSAPI | f

Thank you very much for your sugestions and help!
Maiquel Grassi.

Attachment Content-Type Size
v18-0001-psql-meta-command-conninfo-plus.patch application/octet-stream 11.6 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Francisco Olarte 2024-02-17 15:12:15 Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
Previous Message Mats Kindahl 2024-02-17 13:16:26 Re: glibc qsort() vulnerability