Thread: Psql meta-command conninfo+

Hi,

I'm seeking to improve the \conninfo meta-command in psql. Currently, it provides limited information about the current connection. I believe that expanding it using the concept of "plus" [+] could ease the work of DBAs, SysAdmins, DevOps, etc., who manage a large volume of databases and/or multiple PostgreSQL servers. The objective of this enhancement is to obtain quick information about the current connection (session). I believe that for a PostgreSQL administrator, it is not feasible to write a plpgsql function and apply it to all databases (for example, imagine managing over 200 databases). I have an example on GitHub https://github.com/maiquelgrassi/DBA-toolkit/blob/main/cluster/dba_whoami_function.sql of a plpgsql function demonstrating exactly what I believe is impractical for the daily routine of a PostgreSQL professional. I see psql's meta-commands as significant allies in daily work in productive environments.

Note: As this is a prototype, I will adjust the rest (documentation, tests, etc.) once an agreement is reached.

Use cases for both the current and improved command bellow.

Connection 1 ("remote server"):

[postgres@localhost bin]$ ./psql -h 192.168.0.5 -p 5433 -U postgres -d postgres

Connection 2 (socket):

[postgres@localhost bin]$ ./psql

On Tue, Feb 06, 2024 at 05:27:01PM +0000, Maiquel Grassi wrote:
> I'm seeking to improve the \conninfo meta-command in psql. Currently, it
> provides limited information about the current connection. I believe that
> expanding it using the concept of "plus" [+] could ease the work of DBAs,
> SysAdmins, DevOps, etc., who manage a large volume of databases and/or
> multiple PostgreSQL servers. The objective of this enhancement is to
> obtain quick information about the current connection (session). I
> believe that for a PostgreSQL administrator, it is not feasible to write
> a plpgsql function and apply it to all databases (for example, imagine
> managing over 200 databases). I have an example on GitHub
> https://github.com/maiquelgrassi/DBA-toolkit/blob/main/cluster/dba_whoami_function.sql
> of a plpgsql function demonstrating exactly what I believe is impractical
> for the daily routine of a PostgreSQL professional. I see psql's
> meta-commands as significant allies in daily work in productive
> environments.

This seems like a reasonable idea to me.

> postgres=# \conninfo+
>  Current Connection Information
>    Attribute    |     Value
> ----------------+----------------
>  Database       | postgres
>  User           | postgres
>  Server Version | 16.1
>  Server Address | 192.168.0.5/32
>  Server Port    | 5433
>  Client Address | 192.168.0.5/32
>  Client Port    | 52716
>  Session PID    | 21624
> (8 rows)

My first reaction is that this should instead return a single row with the
same set of columns for any connection type (the not-applicable ones would
just be set to NULL).  That would match the other meta-commands like \l and
\du, and you could still get an expanded display with \x if needed.  Also,
I think it would simplify the code a bit.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On 2024-02-06 19:19 +0100, Nathan Bossart wrote:
> On Tue, Feb 06, 2024 at 05:27:01PM +0000, Maiquel Grassi wrote:
> > postgres=# \conninfo+
> >  Current Connection Information
> >    Attribute    |     Value
> > ----------------+----------------
> >  Database       | postgres
> >  User           | postgres
> >  Server Version | 16.1
> >  Server Address | 192.168.0.5/32
> >  Server Port    | 5433
> >  Client Address | 192.168.0.5/32
> >  Client Port    | 52716
> >  Session PID    | 21624
> > (8 rows)
> 
> My first reaction is that this should instead return a single row with the
> same set of columns for any connection type (the not-applicable ones would
> just be set to NULL).  That would match the other meta-commands like \l and
> \du, and you could still get an expanded display with \x if needed.  Also,
> I think it would simplify the code a bit.

+1 for a single-row result and triggering expanded display with \x for
consistency with other commands.

-- 
Erik

On Tue, Feb 06, 2024 at 08:52:09PM +0000, Maiquel Grassi wrote:
> I made the adjustment in the code and updated the patch. I believe this
> is the format suggested by you all. Would this be it?

I was thinking something more like

  SELECT pg_catalog.current_database() AS "Database",
         current_user AS "User",
         pg_catalog.current_setting('server_version') AS "Server Version",
         pg_catalog.inet_server_addr() AS "Server Address",
         pg_catalog.current_setting('port') AS "Port",
         pg_catalog.inet_client_addr() AS "Client Address",
         pg_catalog.inet_client_port() AS "Client Port",
         pg_catalog.pg_backend_pid() AS "Session PID";

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Tue, Feb 06, 2024 at 03:06:05PM -0600, Nathan Bossart wrote:
> On Tue, Feb 06, 2024 at 08:52:09PM +0000, Maiquel Grassi wrote:
>> I made the adjustment in the code and updated the patch. I believe this
>> is the format suggested by you all. Would this be it?
> 
> I was thinking something more like
> 
>   SELECT pg_catalog.current_database() AS "Database",
>          current_user AS "User",
>          pg_catalog.current_setting('server_version') AS "Server Version",
>          pg_catalog.inet_server_addr() AS "Server Address",
>          pg_catalog.current_setting('port') AS "Port",
>          pg_catalog.inet_client_addr() AS "Client Address",
>          pg_catalog.inet_client_port() AS "Client Port",
>          pg_catalog.pg_backend_pid() AS "Session PID";

... although that seems to be missing items like the socket directory and
the host.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Tue, Feb 06, 2024 at 09:45:54PM +0000, Maiquel Grassi wrote:
> My initial idea has always been that they should continue to appear
> because \conninfo+ should show all the things that \conninfo shows and
> add more information. I think that's the purpose of the 'plus.' Now we're
> on a better path than the initial one. We can still add the socket
> directory and the host.

Agreed.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On 2024-02-07 05:13 +0100, Maiquel Grassi wrote:
>   On Tue, Feb 06, 2024 at 09:45:54PM +0000, Maiquel Grassi wrote:
>   > My initial idea has always been that they should continue to appear
>   > because \conninfo+ should show all the things that \conninfo shows and
>   > add more information. I think that's the purpose of the 'plus.' Now we're
>   > on a better path than the initial one. We can still add the socket
>   > directory and the host.
> 
>   Agreed.
> 
> --//--
> 
> I believe it's resolved reasonably well this way:
> 
> SELECT
>   pg_catalog.current_database() AS "Database",
>   current_user AS "User",
>   pg_catalog.current_setting('server_version') AS "Server Version",
>   CASE
>     WHEN pg_catalog.inet_server_addr() IS NULL
>     THEN 'NULL'
>     ELSE pg_catalog.inet_server_addr()::text
>   END AS "Server Address",

Should be NULL instead of string 'NULL'.  So the entire CASE expression
is redundant and you can just return pg_catalog.inet_server_addr().

>   pg_catalog.current_setting('port') AS "Port",
>   CASE
>     WHEN pg_catalog.inet_client_addr() IS NULL
>     THEN 'NULL'
>     ELSE pg_catalog.inet_client_addr()::text
>   END AS "Client Address",
>   CASE
>     WHEN pg_catalog.inet_client_port() IS NULL
>     THEN 'NULL'
>     ELSE pg_catalog.inet_client_port()::text
>   END AS "Client Port",

Same here.

>   pg_catalog.pg_backend_pid() AS "Session PID",
>   CASE
>     WHEN pg_catalog.current_setting('unix_socket_directories') = ''
>     THEN 'NULL'
>     ELSE pg_catalog.current_setting('unix_socket_directories')
>   END AS "Socket Directory",

The CASE expression can be simplified to:

    nullif(pg_catalog.current_setting('unix_socket_directories'), '')

>   CASE
>     WHEN
>       pg_catalog.inet_server_addr() IS NULL
>       AND pg_catalog.inet_client_addr() IS NULL
>     THEN 'NULL'
>     WHEN
>       pg_catalog.inet_server_addr() = pg_catalog.inet_client_addr()
>     THEN 'localhost'

Is it safe to assume localhost here?  \conninfo prints localhost only
when I connect with psql -hlocalhost:

    $ psql -hlocalhost postgres
    psql (16.1)
    postgres=# \conninfo
    You are connected to database "postgres" as user "ewie" on host "localhost" (address "::1") at port "5432".
    postgres=# \q

    $ psql -h127.0.0.1 postgres
    psql (16.1)
    postgres=# \conninfo
    You are connected to database "postgres" as user "ewie" on host "127.0.0.1" at port "5432".

>     ELSE pg_catalog.inet_server_addr()::text
>   END AS "Host";

-- 
Erik

This is a good idea about extended connection info.

P {margin-top:0;margin-bottom:0;}SELECT

...

  current_user AS "User",

This will be inconsistent with \conninfo.
\conninfo returns authenticated user (PQuser), not the current_user.
It might be worth showing current_user, session_user, and authenticated user,
but I can't find the appropriate sql function for PQuser.

What about to include system_user function? It shows useful authentication details.

Also, it seems that the verbose parameter in the listConnectionInformation
is unnecessary.

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com

SELECT

...

  current_user AS "User",

  This will be inconsistent with \conninfo.  \conninfo returns authenticated user (PQuser), not the current_user.  It might be worth showing current_user, session_user, and authenticated user,  but I can't find the appropriate sql function for PQuser.    What about to include system_user function? It shows useful authentication details.
  Also, it seems that the verbose parameter in the listConnectionInformation  is unnecessary.

--//--

Hi,

Tks Pavel.

Analyzing the functions' code more thoroughly, it seems to make more sense.
I liked your suggestions and implemented them for validation.
Regarding "system_user," I believe it is valid and also added it to the row.

"Also, it seems that the verbose parameter in the listConnectionInformation is unnecessary."
Could you point out exactly the line or code snippet you are referring to?

To print the string from the "Authenticated User" column, I chose to use PQuser(pset.db) directly. I did the same for the "Host" column, opting for PQhost(pset.db). This does not contradict the result of \conninfo.

Here are the tests as usual, and v6 patch.

[postgres@localhost bin]$ ./psql
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=# \conninfo+
                                                                                  Current Connection Information
 Database | Authenticated User | Current User | Session User | System User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+--------------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+------
 postgres | postgres           | postgres     | postgres     |             | 17devel        |                | 5432        |                |             |       17240 | /tmp             |
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h ::1
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "::1" at port "5432".
postgres=# \conninfo+
                                                                                  Current Connection Information
 Database | Authenticated User | Current User | Session User | System User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+--------------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+------
 postgres | postgres           | postgres     | postgres     |             | 17devel        | ::1            | 5432        | ::1            |       47024 |       17242 | /tmp             | ::1
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h 127.0.0.1
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
 Database | Authenticated User | Current User | Session User | System User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory |   Host
----------+--------------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+-----------
 postgres | postgres           | postgres     | postgres     |             | 17devel        | 127.0.0.1      | 5432        | 127.0.0.1      |       35076 |       17245 | /tmp             | 127.0.0.1
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h localhost
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
postgres=# \conninfo+
                                                                                    Current Connection Information
 Database | Authenticated User | Current User | Session User | System User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory |   Host
----------+--------------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+-----------
 postgres | postgres           | postgres     | postgres     |             | 17devel        | ::1            | 5432        | ::1            |       47028 |       17248 | /tmp             | localhost
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h 192.168.0.5 -p 5433 -d postgres -U postgres
psql (17devel, server 16.1)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.0.5" at port "5433".
postgres=# \conninfo+
                                                                                     Current Connection Information
 Database | Authenticated User | Current User | Session User | System User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory |    Host
----------+--------------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+-------------
 postgres | postgres           | postgres     | postgres     |             | 16.1           | 192.168.0.5    | 5433        | 192.168.0.5    |       60115 |       28896 |                  | 192.168.0.5
(1 row)

Regards,
Maiquel O. Grassi.

Hi, Maiquel!

P {margin-top:0;margin-bottom:0;

"Also, it seems that the verbose parameter in the listConnectionInformation is unnecessary."
Could you point out exactly the line or code snippet you are referring to?

+bool
+listConnectionInformation(const char *pattern, bool verbose)

I mean that parameter verbose is not used in the function body and listConnectionInformation called only in verbose mode.

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com

> "Also, it seems that the verbose parameter in the listConnectionInformation is unnecessary."
> Could you point out exactly the line or code snippet you are referring to?

> +bool
> +listConnectionInformation(const char *pattern, bool verbose)
> 
> I mean that parameter verbose is not used in the function body and listConnectionInformation called only in verbose mode.

--//--

There really was no need for the bool verbose. Therefore, it was removed.
Regarding the "system_user" function, as it is relatively new, I added
the necessary handling to avoid conflicts with versions lower than version 16.

I believe in v7 patch we have a quite substantial meta-command feature.

I validated the "System User" column for three versions. This way, we have a sample:

[postgres@localhost bin]$ ./psql -h 192.168.0.5 -p 5432 -U postgres -d postgres

Hi Maiquel

On 07.02.24 21:13, Maiquel Grassi wrote:
>
> I believe in v7 patch we have a quite substantial meta-command feature.
>
>
Thanks for implementing this. I find it very handy.

I was just wondering if a "permission denied" error for non-superusers
is the best choice for "\conninfo+":

postgres=> \conninfo+
ERROR:  permission denied to examine "unix_socket_directories"
DETAIL:  Only roles with privileges of the "pg_read_all_settings" role
may examine this parameter.

.. since it is not the case with "\conninfo":

postgres=> \conninfo
You are connected to database "postgres" as user "jim" via socket in
"/tmp" at port "5432".

Perhaps excluding the column from the result set or returning NULL in
the affected columns would be less confusing?

There are also some indentation issues in your patch:

/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:142:
indent with spaces.
    PGresult   *res;
/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:143:
indent with spaces.
    PQExpBufferData buf;
/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:144:
indent with spaces.
    printQueryOpt myopt = pset.popt;
/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:146:
indent with spaces.
    initPQExpBuffer(&buf);
/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:148:
indent with spaces.
    printfPQExpBuffer(&buf,
warning: squelched 34 whitespace errors
warning: 39 lines add whitespace errors.

Looking forward to see the documentation and tests!

-- 
Jim

Hi,

On 07.02.2024 23:13, Maiquel Grassi wrote:
P {margin-top:0;margin-bottom:0;}

Regarding the "system_user" function, as it is relatively new, I added the necessary handling to avoid conflicts with versions lower than version 16.

Yes, that's rights.

A couple of doubts about the implementation details.
But keep in mind that I'm not very good at programming in the C language.
I hope for the help of more experienced developers.


1.
+			if (db == NULL)
+				printf(_("You are currently not connected to a database.\n"));

This check is performed for \conninfo, but not for \conninfo+.


2.
Some values (address, socket) are evaluated separately for \conninfo
(via C functions) and for \conninfo+ (via sql functions).
It may be worth evaluating them in one place. But I'm not sure about that.

The final version of the patch will require changes to the documentation and tests.

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com

> On 07.02.2024 23:13, Maiquel Grassi wrote:

> Regarding the "system_user" function, as it is relatively new, I added > the necessary handling to avoid conflicts with versions lower than version 16.

> Yes, that's rights.
> 
> A couple of doubts about the implementation details.
> But keep in mind that I'm not very good at programming in the C language.
> I hope for the help of more experienced developers.
> 
> 
> 1.
> +			if (db == NULL)
> +				printf(_("You are currently not connected to a database.\n"));
> 
> This check is performed for \conninfo, but not for \conninfo+.
> 
> 
> 2.
> Some values (address, socket) are evaluated separately for \conninfo
> (via C functions) and for \conninfo+ (via sql functions).
> It may be worth evaluating them in one place. But I'm not sure about that.
> 
> The final version of the patch will require changes to the documentation and tests.

--//--

Hi Pavel,

First of all, thank you very much for your observations.

1. The connection check for the case of \conninfo+ is handled by "describe.c" itself since it deals with queries. I might be mistaken, but I believe that by using "printQuery()" via "describe.c", this is already ensured, and there is no need to evaluate the connection status.

2. I believe that by implementing the evaluations separately as they are, it becomes easier to perform future maintenance by minimizing the mixing of C code with SQL code as much as possible. However, certainly, the possibility of a better suggestion than mine remains open.

Regards,
Maiquel O. Grassi.

On 08.02.24 16:50, Maiquel Grassi wrote:
> Hi Jim,
> Thank you for your support on this patch!
> As I believe in its usability, I have been dedicating efforts to make
> it really interesting.
> I hadn't thought about the permissioning issue for
> "unix_socket_directories". I appreciate that.
> I thought about solving this situation using the same approach as
> \conninfo. I added the validation if (is_unixsock_path(host) &&
> !(hostaddr && *hostaddr)) in the SQL part along with an "append". In
> case of a negative result, another "append" adds NULL.
> Regarding the whitespace issue, before generating v8 patch file, I
> used pgindent to adjust each modified file. I believe it should be ok
> now. If you could verify, I'd be grateful.
> Below are the tests after adjusting for the permissioning issues:
>
> [postgres@localhost bin]$ ./psql
> psql (17devel)
> Type "help" for help.
>
> postgres=# \conninfo
> You are connected to database "postgres" as user "postgres" via socket
> in "/tmp" at port "5432".
> postgres=# \conninfo+
>                                                                      
>             Current Connection Information
>  Database | Authenticated User | System User | Current User | Session
> User | Session PID | Server Version | Server Address | Server Port |
> Client Address | Client Port | Socket Directory | Host
>
----------+--------------------+-------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+------------------+------
>  postgres | postgres           |             | postgres     | postgres
>     |       31479 | 17devel        |                | 5432        |  
>              |             | /tmp             |
> (1 row)
>
> postgres=# CREATE USER maiquel;
> CREATE ROLE
> postgres=# \q
> [postgres@localhost bin]$ ./psql -U maiquel -d postgres
> psql (17devel)
> Type "help" for help.
>
> postgres=> \conninfo
> You are connected to database "postgres" as user "maiquel" via socket
> in "/tmp" at port "5432".
> postgres=> \conninfo+
>                                                                      
>             Current Connection Information
>  Database | Authenticated User | System User | Current User | Session
> User | Session PID | Server Version | Server Address | Server Port |
> Client Address | Client Port | Socket Directory | Host
>
----------+--------------------+-------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+------------------+------
>  postgres | maiquel            |             | maiquel      | maiquel
>      |       31482 | 17devel        |                | 5432        |  
>              |             | /tmp             |
> (1 row)
>
> postgres=> \q
> [postgres@localhost bin]$ ./psql -h localhost -U maiquel -d postgres
> psql (17devel)
> Type "help" for help.
>
> postgres=> \conninfo
> You are connected to database "postgres" as user "maiquel" on host
> "localhost" (address "::1") at port "5432".
> postgres=> \conninfo+
>                                                                      
>               Current Connection Information
>  Database | Authenticated User | System User | Current User | Session
> User | Session PID | Server Version | Server Address | Server Port |
> Client Address | Client Port | Socket Directory |   Host
>
----------+--------------------+-------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+------------------+-----------
>  postgres | maiquel            |             | maiquel      | maiquel
>      |       31485 | 17devel        | ::1            | 5432        |
> ::1            |       47482 |                  | localhost
> (1 row)
>
> postgres=> \q
> [postgres@localhost bin]$ ./psql -h localhost
> psql (17devel)
> Type "help" for help.
>
> postgres=# \conninfo
> You are connected to database "postgres" as user "postgres" on host
> "localhost" (address "::1") at port "5432".
> postgres=# \conninfo+
>                                                                      
>               Current Connection Information
>  Database | Authenticated User | System User | Current User | Session
> User | Session PID | Server Version | Server Address | Server Port |
> Client Address | Client Port | Socket Directory |   Host
>
----------+--------------------+-------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+------------------+-----------
>  postgres | postgres           |             | postgres     | postgres
>     |       31488 | 17devel        | ::1            | 5432        |
> ::1            |       47484 |                  | localhost
> (1 row)
>
> Regards,
> Maiquel.

v8 no longer throws a permission denied error for non-superusers - it is
IMHO much nicer this way.

$ /usr/local/postgres-dev/bin/psql postgres -p 5432 -h 127.0.0.1 -U jim
psql (17devel)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# \conninfo+
Current Connection Information
-[ RECORD 1 ]------+----------
Database           | postgres
Authenticated User | jim
System User        |
Current User       | jim
Session User       | jim
Session PID        | 1321493
Server Version     | 17devel
Server Address     | 127.0.0.1
Server Port        | 5432
Client Address     | 127.0.0.1
Client Port        | 49366
Socket Directory   |
Host               | 127.0.0.1

postgres=# SET ROLE foo;
SET
postgres=> \conninfo+
Current Connection Information
-[ RECORD 1 ]------+----------
Database           | postgres
Authenticated User | jim
System User        |
Current User       | foo
Session User       | jim
Session PID        | 1321493
Server Version     | 17devel
Server Address     | 127.0.0.1
Server Port        | 5432
Client Address     | 127.0.0.1
Client Port        | 49366
Socket Directory   |
Host               | 127.0.0.1


The patch now applies cleanly.

One thing I just noticed. The psql autocomplete feature does not suggest
the new + option of \conninfo. For instance, when typing "\connin[TAB]"
it automatically autocompletes to "\conninfo ". I guess it should also
be included in this patch.

I can do a more thorough review of the code when you add the
documentation and tests to the patch.

Thanks!

-- 
Jim

On 2024-02-08 20:37 +0100, Jim Jones wrote:
> One thing I just noticed. The psql autocomplete feature does not suggest
> the new + option of \conninfo. For instance, when typing "\connin[TAB]"
> it automatically autocompletes to "\conninfo ". I guess it should also
> be included in this patch.

Modifiers such as + or S in \dS are not covered by autocompletion.
src/bin/psql/tab-complete.c only specifies backslash commands in their
basic form (without modifiers).

(\dS<TAB> actually autocompletes to \ds to my surprise)

> I can do a more thorough review of the code when you add the
> documentation and tests to the patch.

I noticed that the pattern parameter in listConnectionInformation is
unused.  exec_command_conninfo scans the pattern but \conninfo should
not accept any argument.  So the pattern can be removed entirely.

-- 
Erik

Hi Erik

On 08.02.24 21:37, Erik Wienhold wrote:
> Modifiers such as + or S in \dS are not covered by autocompletion.
> src/bin/psql/tab-complete.c only specifies backslash commands in their
> basic form (without modifiers).
>
> (\dS<TAB> actually autocompletes to \ds to my surprise)
>
Aha... I never noticed it. Well, with most commands having 1 - 3
characters it is not a surprised I never used it :)
That "\dS<TAB>" autocompletes to "\ds " surprises me even more.
Thanks for pointing out!

-- 
Jim

Sorry if this has been brought up, but I noticed that some of the
information is listed below the result set:

    postgres=# \conninfo+
    Current Connection Information
    -[ RECORD 1 ]------+---------
    Database           | postgres
    Authenticated User | nathan
    System User        | 
    Current User       | nathan
    Session User       | nathan
    Session PID        | 659410
    Server Version     | 17devel
    Server Address     | ::1
    Server Port        | 5432
    Client Address     | ::1
    Client Port        | 59886
    Socket Directory   | 
    Host               | ::1

    SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

Shouldn't we move this information into the result set?  Separately, does
the server version really belong here?  I'm not sure I would consider that
to be connection information.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Hi Nathan

On 09.02.24 03:41, Nathan Bossart wrote:

> Separately, does
> the server version really belong here?  I'm not sure I would consider that
> to be connection information.
>
I tend to agree with you. The info there wouldn't hurt, but perhaps the
client version would be a better fit.

-- 
Jim

Hi, Maiquel!

The patch v10 build ends with a warning:
$ make -j --silent
describe.c:911:1: warning: no previous prototype for ‘listConnectionInformation’ [-Wmissing-prototypes]  911 | listConnectionInformation()      | ^~~~~~~~~~~~~~~~~~~~~~~~~

About terms.

postgres@postgres(17.0)=# \x \conninfo+
Expanded display is on.
Current Connection Information
-[ RECORD 1 ]------+---------
Database           | postgres
Authenticated User | postgres
System User        | 
Current User       | postgres
Session User       | postgres
Session PID        | 951112
Server Version     | 17devel
Server Address     | 
Server Port        | 5401
Client Address     | 
Client Port        | 
Socket Directory   | /tmp
Host               | 

It looks like "Session PID" is a new term for the server process identifier.
How about changing the name to "Backend PID" (from pg_backend_pid) or even PID (from pg_stat_activity)?

> 1.
> +			if (db == NULL)
> +				printf(_("You are currently not connected to a database.\n"));
> 
> This check is performed for \conninfo, but not for \conninfo+.

P {margin-top:0;margin-bottom:0;}

1. The connection check for the case of \conninfo+ is handled by "describe.c" itself since it deals with queries. I might be mistaken, but I believe that by using "printQuery()" via "describe.c", this is already ensured, and there is no need to evaluate the connection status.

I found that \conninfo and \conninfo+ act differently when the connection is broken.
I used pg_terminate_backend function from another session to terminate an open psql session.
After that, \conninfo does not see the connection break (surprisingly!), and \conninfo+ returns an error:

postgres@postgres(17.0)=# \conninfo+
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly	This probably means the server terminated abnormally	before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

postgres@postgres(17.0)=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5401".

Another surprise is that this check: if (db == NULL) did not work in both cases.

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com

>information is listed below the result set:
>
  >      postgres=# \conninfo+
  >    Current Connection Information
  >      -[ RECORD 1 ]------+---------
  >     Database           | postgres
  >      Authenticated User | nathan
  >      System User        |
  >      Current User       | nathan
  >      Session User       | nathan
  >     Session PID        | 659410
  >      Server Version     | 17devel
  >      Server Address     | ::1
  >      Server Port        | 5432
  >      Client Address     | ::1
  >      Client Port        | 59886
  >      Socket Directory   |
  >      Host               | ::1

  >      SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

>Shouldn't we move this information into the result set?  Separately, does
>the server version really belong here?  I'm not sure I would consider that
>to be connection information.

--//--

Hi Nathan,

The "Server Version" information is closely related to the connection. However,
it does seem to be an element that does not belong to this set. I removed this
column and left only what is truly connection info.

Regarding the functions "printSSLInfo()" and "printGSSInfo()", I agree that we
should include them in the returned dataset (as two additional columns). A good
argument is that this will make more sense when using \x (Expanded display).
However, they are declared and defined within the "command.c" file. To make
calls to "printSSLInfo()" and "printGSSInfo()" within "describe.c", we would need
to move their declarations to a new header and create a new C file. I believe
something like "ssl_gss_info.h" and "ssl_gss_info.c". I'm not sure, but at first glance,
this is what occurs to me. Do you have any better or more concise suggestions
for resolving this?

Regards,
Maiquel Grassi.

>The patch v10 build ends with a warning:
>$ make -j --silent
>describe.c:911:1: warning: no previous prototype for ‘listConnectionInformation’ [-Wmissing-prototypes]
>  911 | listConnectionInformation()      | ^~~~~~~~~~~~~~~~~~~~~~~~~

>About terms.

I resolved this in v11. I had forgotten to put
the 'void' inside the parentheses (describe.h and describe.c).

>postgres@postgres(17.0)=# \x \conninfo+
>Expanded display is on.
>Current Connection Information
>-[ RECORD 1 ]------+---------
>Database           | postgres
>Authenticated User | postgres
>System User        | 
>Current User       | postgres
>Session User       | postgres
>Session PID        | 951112
>Server Version     | 17devel
>Server Address     | 
>Server Port        | 5401
>Client Address     | 
>Client Port        | 
>Socket Directory   | /tmp
>Host               | 

>It looks like "Session PID" is a new term for the server process identifier.
>How about changing the name to "Backend PID" (from pg_backend_pid) or even PID (from pg_stat_activity)?

You're right, it's better to stick with the proper terms already in use 
in PostgreSQL. This ensures that the user doesn't get confused. I've 
changed it to "Backend PID".

> 1.
> +			if (db == NULL)
> +				printf(_("You are currently not connected to a database.\n"));
> 
> This check is performed for \conninfo, but not for \conninfo+.

1. The connection check for the case of \conninfo+ is handled by "describe.c" itself since it deals with queries. I might be mistaken, but I believe that by using "printQuery()" via "describe.c", this is already ensured, and there is no need to evaluate the connection status.

>I found that \conninfo and \conninfo+ act differently when the connection is broken.
>I used pg_terminate_backend function from another session to terminate an open psql session.
>After that, \conninfo does not see the connection break (surprisingly!), and \conninfo+ returns an error:

>postgres@postgres(17.0)=# \conninfo+
>FATAL:  terminating connection due to administrator command
>server closed the connection unexpectedly	>This probably means the server terminated abnormally	>before or while processing the request.
>The connection to the server was lost. Attempting reset: Succeeded.

For this case, I believe it's already resolved, because if you get a 
return indicating that the connection was terminated, and indeed it was,
then "describe.c" is handling it correctly. At least that's what
it seems like.

>postgres@postgres(17.0)=# \conninfo
>You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5401".

Here it seems like we have an issue to be studied and subsequently resolved.

>Another surprise is that this check: if (db == NULL) did not work in both cases.

I will investigate further and, if necessary, remove it.

Here's v12, in the next version, I'll try to address the above situation.

Thanks a lot!
Maiquel Grassi.

Hmm, I noticed that this would call printSSLInfo() and printGSSInfo()
after listConnectionInformation.  It would be much better to show these
in tabular format as well and remove the calls to printSSL/GSSInfo.

I propose additional columns to the same \conninfo+ table; when SSL,
like this:

Database           | postgres
[...]
Host               | 127.0.0.1
Encryption         | SSL
Protocol           | PQsslAttribute(protocol)
Cipher             | PQsslAttribute(cipher)
Compression        | PQsslAttribute(compression)

When GSS, like this

Database           | postgres
[...]
Host               | 127.0.0.1
Encryption         | GSS

(why don't we print anything else in printGSSInfo()?  That's weird.)

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)

>Database           | postgres
>[...]
>Host               | 127.0.0.1
>Encryption         | SSL
>Protocol           | PQsslAttribute(protocol)
>Cipher             | PQsslAttribute(cipher)
>Compression        | PQsslAttribute(compression)
>
>When GSS, like this
>
>Database           | postgres
>[...]
>Host               | 127.0.0.1
>Encryption         | GSS

--//--

Hi PgHackers,

Columns were added for SSL and GSS.

For SSL, I conducted some tests as follows. For GSS, I will perform
them and intend to provide a sample here in the next interaction.

If anyone can and wants to test GSSAPI as well, I appreciate it.

[postgres@localhost bin]$ ./psql -h localhost -p 5432 -x

>I found that \conninfo and \conninfo+ act differently when the connection is broken.
>I used pg_terminate_backend function from another session to terminate an open psql session.
>After that, \conninfo does not see the connection break (surprisingly!), and \conninfo+ returns an error:
>
>postgres@postgres(17.0)=# \conninfo+
>FATAL:  terminating connection due to administrator command
>server closed the connection unexpectedly
>	This probably means the server terminated abnormally
>	before or while processing the request.
>The connection to the server was lost. Attempting reset: Succeeded.
>
>postgres@postgres(17.0)=# \conninfo
>You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5401".
>
>Another surprise is that this check: if (db == NULL) did not work in both cases.

--//--

Hi Pavel!

(v14)

The "if (db == NULL)" has been removed, as well
as the message inside it. To always maintain the
same error messages, I changed the validation of
"\conninfo" to match the validation used for "\conninfo+".
Therefore, now "\conninfo" and "\conninfo+" return
the same error when "pg_terminate_backend()" terminates
this session through another session. The result of
the adjustment is as follows:

Case 2 ("\conninfo"):

[postgres@localhost bin]$ ./psql -x
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" 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        | 24539
Server Address     |
Server Port        | 5432
Client Address     |
Client Port        |
Socket Directory   | /tmp
Host               |

postgres=# \conninfo
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

The connection to the server was lost. Attempting reset: Succeeded.

In both cases, the sessions were terminated by another session.

Regards,
Maiquel Grassi.

P {margin-top:0;margin-bottom:0;}

The "if (db == NULL)" has been removed, as well as the message inside it. To always maintain the same error messages, I changed the validation of "\conninfo" to match the validation used for "\conninfo+". Therefore, now "\conninfo" and "\conninfo+" return the same error when "pg_terminate_backend()" terminates this session through another session.

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com

On 12.02.24 15:16, Maiquel Grassi wrote:
>
> (v14)
>
>
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@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...

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 :).

Thanks for working on this

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

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

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.

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

Thanks for your work on this.  I haven't been keeping up with the
discussion, but I took a quick look at the latest patch.

+        <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.
         </para>

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.

+    if (PQsslInUse(pset.db))
+    {
+        protocol = PQsslAttribute(pset.db, "protocol");
+        cipher = PQsslAttribute(pset.db, "cipher");
+        compression = PQsslAttribute(pset.db, "compression");
+        appendPQExpBuffer(&buf,
+                          "  ,'SSL' AS \"Encryption\",\n"
+                          "  '%s' AS \"Protocol\",\n"
+                          "  '%s' AS \"Cipher\",\n"
+                          "  '%s' AS \"Compression\"\n",
+                          protocol ? protocol : _("unknown"),
+                          cipher ? cipher : _("unknown"),
+                          (compression && strcmp(compression, "off") != 0) ? _("on") : _("off"));
+    }

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.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Hi!

>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

I believe we don't actually have a problem here. To me, this makes sense.
I'm not a Networking expert, but from my standpoint, any string or IP
accepted by the "host" parameter of psql, pointing to "127.0.0.1" or to "::1", is correct.
I see it as a convention (would need to revisit Tanenbaum's books haha),
and I don't think there's a need to modify it. But as you mentioned, if someone
with more Networking knowledge could weigh in, a suggestion would be welcome.

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

This type of modification, in principle, does not generate errors as we
are only changing the display on screen of the strings already previously stored.

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

It seems to me that "appended" sounds good. I opted for it.
Following your suggestion, and merging this with the existing
previous ideas from the documentation, I've created a provisional
prototype of the column descriptions. At a later stage, I'll place the
descriptions correctly by removing placeholders (blah blah blah).
Along with this, I'll evaluate an iteration suggested by Nathan Bossart,

who also proposed changes.

Thank you for your work.

Regards,
Maiquel Grassi.

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@localhost bin]$ ./psql -x -h 127.0.0.1 -p 5432

On Sat, Feb 17, 2024 at 02:53:43PM +0000, Maiquel Grassi wrote:
> 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+.

IMHO we should use the views whenever possible (for the reason stated
above [0]).  I think it's okay if we need to fall back to a different
approach for older versions.  But presumably we'll discontinue psql support
for these old server versions at some point, at which point we can simply
delete the dead code that doesn't use the views.

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

I would be okay with using PQsslAttribute() for all versions for this one
since any remaining support for this feature is on its way out.  Once psql
no longer supports any versions that allow SSL compression, we could
probably remove it from \conninfo[+] completely or hard-code it to "off".

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

At the moment, the psql support cutoff appears to be v9.2 (see commit
cf0cab8), which has been out of support for over 6 years.  If \conninfo+
happens to work for older versions, then great, but I don't think we should
expend too much energy in this area.

[0] https://postgr.es/m/20240216155449.GA1236054%40nathanxps13

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Thu, Feb 29, 2024 at 10:02:21PM +0000, Maiquel Grassi wrote:
> Sorry for the delay. I will make the adjustments as requested soon.

Looking forward to it.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Hi Maiquel,

On Thu, Feb 29, 2024 at 10:02:21PM +0000, Maiquel Grassi wrote:
> Sorry for the delay. I will make the adjustments as requested soon.

We have only a few weeks left before feature-freeze for v17.  Do you think
you will be able to send an updated patch soon?

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

>
> Sorry for the delay, I was busy with other professional as well as personal activities.

> I made all the changes you suggested. I removed the variables and started using
> views "pg_stat_ssl" and "pg_stat_gssapi". I handled the PostgreSQL versioning regarding the views used.

--//--

Olá Nathan!

I think we are very close to possibly finishing this patch, and
I would like your help to do it. Is there any correction you
deem necessary still?

Regards,
Maiquel Grassi.

> 1/ I looked through other psql-meta commands and the “+” adds details but

> does not change output format. In this patch, conninfo and conninfo+

> have completely different output. The former is a string with all the details

> and the latter is a table. Should conninfo just be a table with the minimal info

> and additional details can be displayed with "+" appended?

The initial and central idea was always to keep the metacommand
"\conninfo" in its original state, that is, to preserve the string as it is.
The idea of "\conninfo+" is to expand this to include more information.
If I change the "\conninfo" command and transform it into a table,
I would have to remove all the translation part (files) that has already
been implemented in the past. I believe that keeping the string and
its translations is a good idea and there is no great reason to change that.

> 2/ GSSAPI details should show the full details, such as "principal",

> "encrypted" and "credentials_delegated".

In this new patch, I added these columns. I handled the 'server versions'
for cases where the 'credentials_delegated' column is not in the view.
It turned out well. Thank you for the idea.

3/ A very nice to have is "Application Name", in the case one

sets the application_name within a connection or in the connection string.

I did the same here. I liked your idea and added this column in the SQL.

Look below to see how it turned out after it's finished.

Exemple 1:

[postgres@localhost ~]$ /home/pgsql-17devel/bin/psql -x -p 5432

>  description. However, I have some comments:

>  1/ For the description of the conninfo command, what about simplifying like below?

>  "Outputs a string displaying information about the current database connection. When + is appended, more details about the connection are displayed in table format:"

>  2/ I don't think the descriptions need to start with "Displays". It is very repetitive.

>  3/ For the "Socket Directory" description, this could be NULL if the host was not specified.

>  What about the below?

>  "The socket directory of the connection. NULL if the host or hostaddr are specified for the connection"

>  4/ For most of the fields, they are just the output of a function, such as "pg_catalog.system_user()". What about the docs simply

>  link to the documentation of the function. This way we are not copying descriptions and have to be concerned if the description

>  of the function changes in the future.
 

>  5/ "true" and "false", do not need double quotes. This is not the convention used in other places docs.

-----//-----

Hi Sami!

(v22)

I did everything you mentioned earlier, that is, I followed all your suggestions. However,
I didn't complete item 4. I'm not sure, but I believe that linking it to the documentation
could confuse the user a bit. I chose to keep the descriptions as they were. However, if
you have any ideas on how we could outline it, let me know and perhaps we can
implement it.

Thank you so much!

Exemples:

[postgres@localhost bin]$ ./psql -x -p 5000 -h 127.0.0.1

 
Regards,
Maiquel Grassi.

Hello,

Note that, in the patch as posted, the column names are not
translatable.  In order to be translatable, the code needs to do
something like

appendPQExpBuffer(&buf,
        "  NULL AS \"%s\",\n"
        "  NULL AS \"%s\",\n"
        "  NULL AS \"%s\",\n"
        "  NULL AS \"%s\",\n",
        _("SSL Connection"),
        _("SSL Protocol"),
        _("SSL Cipher"),
        _("SSL Compression"));

instead of 

       appendPQExpBuffer(&buf,
                         "  NULL AS \"SSL Connection\",\n"
                         "  NULL AS \"SSL Protocol\",\n"
                         "  NULL AS \"SSL Cipher\",\n"
                         "  NULL AS \"SSL Compression\",\n");


Please list me as reviewer for this patch, as I provided significant
guidance before it was even posted.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

On 2024-Mar-31, Maiquel Grassi wrote:

> Yes Álvaro, I have already appointed you as the patch reviewer.
> It's true, even before publishing it on Commifest, you have
> already provided good ideas and guidance.

Thanks.

> I adjusted the translation syntax for the SSL and GSSAPI columns.
> After your validation, that is, an okay confirmation that it's fine, I'll
> proceed with the others.

I meant those columns to be just examples, but this should be applied to
all the other columns in the output as well.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"No necesitamos banderas
 No reconocemos fronteras"                  (Jorge González)

Hello

Yeah, that's what I meant about the translations, thanks.


Two more comments,

- You don't need a two-level conditional here
    if (pset.sversion >= 90500)
    {
        if (pset.sversion < 140000)
            appendPQExpBuffer(&buf,
                              "  ssl.ssl AS \"%s\",\n"
                              "  ssl.version AS \"%s\",\n"
                              "  ssl.cipher AS \"%s\",\n"
                              "  ssl.compression AS \"%s\",\n",
                              _("SSL Connection"),
                              _("SSL Protocol"),
                              _("SSL Cipher"),
                              _("SSL Compression"));
        if (pset.sversion >= 140000)
            appendPQExpBuffer(&buf,
                              "  ssl.ssl AS \"%s\",\n"
                              "  ssl.version AS \"%s\",\n"
                              "  ssl.cipher AS \"%s\",\n"
                              "  NULL AS \"%s\",\n",
                              _("SSL Connection"),
                              _("SSL Protocol"),
                              _("SSL Cipher"),
                              _("SSL Compression"));
    }
    else
        appendPQExpBuffer(&buf,
                          "  NULL AS \"%s\",\n"
                          "  NULL AS \"%s\",\n"
                          "  NULL AS \"%s\",\n"
                          "  NULL AS \"%s\",\n",
                          _("SSL Connection"),
                          _("SSL Protocol"),
                          _("SSL Cipher"),
                          _("SSL Compression"));

  Instead you can just do something like

  if (pset.version >= 140000)
    one thing;
  else if (pset.version > 90500)
    second thing;
  else
    third thing;

  This also appears where you add the GSSAPI columns; and it's also in the
  final part where you append the FROM clause, though it looks a bit
  different there.

- You have three lines to output a semicolon at the end of the query
  based on version number.  Remove the first two, and just have a final
  one where the semicolon is added unconditionally.

- I don't think one <para> for each item in the docs is reasonable.
  There's too much vertical whitespace in the output.  Maybe do this
  instead:

        [...]
        database connection. When <literal>+</literal> is appended,
        more details about the connection are displayed in table
        format:

        <simplelist>
         <member>
          <term>Database:</term> The name of the current
          database on this connection.
         </member>

         <member>
          <term>Authenticated User:</term> The authenticated
          user at the time of psql connection with the server.
         </member>
     
         ...
        </simplelist>


- This text is wrong to start with "Returns the":

    System User: Returns the authentication method and the identity (if
    any) that the user presented during the authentication cycle before
    they were assigned a database role. It is represented as
    auth_method:identity or NULL if the user has not been authenticated.

  That minor point aside, I disagree with Sami about repeating the docs
  for system_user() here.  I would just say "The authentication data
  provided for this connection; see the function system_user() for more
  details." with a link to the appropriate section of the docs.  Making
  us edit this doc if we ever modify the behavior of the function is not
  great.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"We're here to devour each other alive"            (Hobbes)

> That minor point aside, I disagree with Sami about repeating the docs
> for system_user() here. I would just say "The authentication data
> provided for this connection; see the function system_user() for more
> details." 

+1. FWIW; Up the thread [1], I did mention we should link to the functions page,
but did not have a very strong opinion on that. 

I do like your suggestion and the same should be done for "Current User" 
and "Session User" as well. 

[1] https://www.postgresql.org/message-id/640B2586-EECF-44C0-B474-CA8510F8EAFC%40amazon.com

Regards,

Sami

The database name of the connection.

Authenticated User:

The authenticated database user of the connection.

Socket Directory:

The Unix socket directory of the connection. NULL if host or hostaddr are specified.

Host:

The host name or address of the connection. NULL if a Unix socket is used.

Server Port:

The IP address of the connection. NULL if a Unix socket is used.

Server Address:

The IP address of the host name. NULL if a Unix socket is used.

Client Address:

The IP address of the client connected to this backend. NULL if a Unix socket is used.

Client Port:

The port of the client connected to this backend. NULL if a Unix socket is used.

Backend PID:

The process id of the backend for the connection.

Application name:

<literal>psql<literal> is the default for a psql connection

unless otherwise specified in the <xref linkend="guc-application-name"/>

configuration parameter.

-----//-----

Hi Sami,
I considered your suggestions and Álvaro's suggestions
Regards,
Maiquel Grassi.

>  The public doc for this example is in [2].

>  Something similar can be done here. For example:

>  The session user's name; see the <function>session_user()</function> function in

>  <xref linkend="functions-info-session-table"/> for more details.

I updated the patch.

Thank you for this help.

Regards,
Maiquel Grassi.

ref/psql-ref.sgml:1042: element member: validity error : Element term is not declared in member list of possible children

         </member>

                  ^

I am able to build up to v24 before the <para> was replaced with <listitem><member>

I tested building with a modified structure as below; the <listitem> is a <para>

that has a <simplelist> within it.  Each field is a simple list member and

the the name of the fields should be <literal>.

      <varlistentry id="app-psql-meta-command-conninfo">

       <term><literal>\conninfo[+]</literal></term>

       <listitem>

       <para>

        Outputs a string displaying information about the current

        database connection. When <literal>+</literal> is appended,

        more details about the connection are displayed in table

        format:

       <simplelist>

       <member><literal>Database:</literal>The database name of the connection.</member>

       <member><literal>Authenticated User:</literal>The authenticated database user of the connection.</member>

       <member><literal>Current User:</literal>The user name of the current execution context;

          see the <function>current_user()</function> function in

          <xref linkend="functions-info-session-table"/> for more details.</member>

       </simplelist>

       </para>

       </listitem>

      </varlistentry>

---//---

Hi Sami!
(v27)
I made the adjustment in the documentation.
Thank you for the time dedicated to this feature.
Regards,
Maiquel Grassi.

The existing \conninfo command gets its values from libpq APIs.  You are 
changing all of this to make a server query, which is a totally 
different thing.  If we wanted to take a change like this, I don't think 
it should be reusing the \conninfo command.

But I don't really see the point of this.  The information you are 
querying is already available in various system views.  This proposal is 
just a shorthand for a collection of various random things some people 
like to see.  Like, by what reason is application name included as 
connection info?  Why not any other session settings?  What about 
long-term maintenance: By what logic should things be added to this?

On 2024-Apr-04, Peter Eisentraut wrote:

> But I don't really see the point of this.  The information you are querying
> is already available in various system views.  This proposal is just a
> shorthand for a collection of various random things some people like to see.
> Like, by what reason is application name included as connection info?  Why
> not any other session settings?  What about long-term maintenance: By what
> logic should things be added to this?

Usability is precisely the point.  You could also claim that we don't
need \dconfig, since you could get the same thing by querying
pg_settings for non-default settings.  But \dconfig is very handy.  I
expect \conninfo+ to be equally useful.  We don't give up command
history just on the grounds that you can obtain the same effect by
retyping the command.


I'm not sure to what extent is it useful to make a distinction between
the values that the client knows from those that the server knows.  If
it is, then we can redefine \conninfo+ to use the client values.

The point about application_name is a valid one.  I guess it's there
because it's commonly given from the client side rather than being set
server-side, even though it's still a GUC.  Arguably we could remove it
from \conninfo+, and claim that nothing that shows up in \dconfig should
also appear in \conninfo+.  Then users should get in the habit of using
both to obtain a full picture.  This sounds to me a very good compromise
actually.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
Tom: There seems to be something broken here.
Teodor: I'm in sackcloth and ashes...  Fixed.
                               http://postgr.es/m/482D1632.8010507@sigaev.ru

Maiquel Grassi <grassi@hotmail.com.br> writes:
>> The existing \conninfo command gets its values from libpq APIs.  You are
>> changing all of this to make a server query, which is a totally
>> different thing.  If we wanted to take a change like this, I don't think
>> it should be reusing the \conninfo command.

FWIW, I agree with Peter's concern here, for a couple of reasons:

* If \conninfo involves a server query, then it will fail outright
if the server is in an aborted transaction, or if there's something
wrong with the connection --- which seems like one of the primary
cases where you'd wish to use \conninfo.

* What if there's a discrepancy between what libpq thinks and what
the server thinks?  (This is hardly unlikely for, say, host names.)
In the current situation you can use \conninfo to investigate the
client-side parameters and then use a manual query to see what the
server thinks.  If we replace \conninfo with a server query then
there is no way at all to verify libpq parameters from the psql
command line.

So I concur that \conninfo should continue to report on libpq values
and nothing else.  We can certainly talk about expanding it within
that charter, if there's useful stuff it doesn't show now.  But a
command that retrieves settings from the server should be a distinct
thing.

            regards, tom lane

> The point about application_name is a valid one. I guess it's there
> because it's commonly given from the client side rather than being set
>server-side, even though it's still a GUC. Arguably we could remove it
> from \conninfo+, and claim that nothing that shows up in \dconfig should
> also appear in \conninfo+. Then users should get in the habit of using
> both to obtain a full picture. This sounds to me a very good compromise
> actually.

Perhaps another reason to remove "application_name" is because the
value can be modified after the connection is established. If that is also
another good reason, the same can be said about "Current User"
and "Session User" as those could be modified with SET commands.

This way conninfo could be only for attributes that will not change
during the lifetime of the connection.


Also, I just realized that pg_stat_ssl and pg_stat_gssapi will both return 0
rows if there is a set role/set session authorization, this means \conninfo+
will return empty.

postgres=> select current_user;
 current_user 
--------------
 user1
(1 row)

postgres=> select * from pg_stat_ssl ;
  pid  | ssl | version |           cipher            | bits | client_dn | client_serial | issuer_dn 
-------+-----+---------+-----------------------------+------+-----------+---------------+-----------
 27223 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 |           |               | 
(1 row)

postgres=> set role = user2;
SET
postgres=> select * from pg_stat_ssl ;
 pid | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn 
-----+-----+---------+--------+------+-----------+---------------+-----------
(0 rows)

postgres=> select current_user;
 current_user 
--------------
 user2
(1 row)

postgres=> reset role;
RESET
postgres=> select current_user;
 current_user 
--------------
 user1
(1 row)

postgres=> select * from pg_stat_ssl ;
  pid  | ssl | version |           cipher            | bits | client_dn | client_serial | issuer_dn 
-------+-----+---------+-----------------------------+------+-----------+---------------+-----------
 27223 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 |           |               | 
(1 row)


Regards, 

Sami 

On 04.04.24 18:15, Maiquel Grassi wrote:
> Well, I can revert \conninfo to its original state and keep \conninfo+
> as it is, perhaps removing the application name, as I believe everything
> else is important for a DBA/SysAdmin. Do you think we can proceed
> with the patch this way? I am open to ideas that make \conninfo not
> limited to just four or five basic pieces of information and easily bring
> everything else to the screen.

The original \conninfo was designed to report values from the libpq API 
about what libpq connected to.  And the convention in psql is that "+" 
provide more or less the same information but a bit more.  So I think it 
is wrong to make "\conninfo+" something fundamentally different than 
"\conninfo".

And even more so if it contains information that isn't really 
"connection information".  For example, current_user() doesn't make 
sense here, I think.

I mean, if you want to add a command \some_useful_status_information, we 
can talk about that, but let's leave \conninfo to what it does.

But I think there are better ways to implement this kind of server-side 
status, for example, with a system view.

One problem in this patch is that it has no tests.  Any change in any of 
the involved functions or views will silently break this.  (Note that 
plain \conninfo doesn't have this problem to this extent because it only 
relies on libpq function calls.  Also, a system view would not have this 
problem.)

> The original \conninfo was designed to report values from the libpq API
> about what libpq connected to. And the convention in psql is that "+"
> provide more or less the same information but a bit more. So I think it
> is wrong to make "\conninfo+" something fundamentally different than
> "\conninfo".

That is a fair argument. Looking at this a bit more, it looks like we can
enhance the GSSAPI output of conninfo to get the fields that GSSAPI fields that
conninfo+ was aiming for

Right now it just shows:

printf(_("GSSAPI-encrypted connection\n"));

but we can use libpq to get the other GSSAPI attributes in the output.

> And even more so if it contains information that isn't really
> "connection information". For example, current_user() doesn't make
> sense here, I think.

> I mean, if you want to add a command \some_useful_status_information, we
> can talk about that, but let's leave \conninfo to what it does.

> But I think there are better ways to implement this kind of server-side
> status, for example, with a system view.

What about a \sessioninfo command that shows all the
information for the current session, PID, app_name, current_user,
session_user, system_user, client_address, client_port, etc.

These will be the fields that we cannot gather if the connection is
broken for whatever reason.

The distinction here is \sessioninfo are the details after the connection
is established and could possibly be changed during the connection.

Regards,

Sami

On Wed, May 29, 2024 at 12:37:21PM +0000, Maiquel Grassi wrote:
> Is there someone willing to help me with this development and guide the
> patch so that it can be committed one day?

From a quick skim of the latest messages in this thread, it sounds like
there are a couple of folks who think \conninfo (and consequently
\conninfo+) should only report values from the libpq API.  I think they
would prefer server-side information to be provided via a system view or
maybe an entirely new psql meta-command.

IIUC a new system view would more-or-less just gather information from
other system views and functions.  A view would be nice because it could be
used outside psql, but I'm not sure to what extent we are comfortable
adding system views built on other system views.  Something like
\sessioninfo (as proposed by Sami) would look more similar to what you have
in your latest patch, i.e., we'd teach psql about a complicated query for
gathering all this disparate information.

IMHO a good way to help move this forward is to try implementing it as a
system view so that we can compare the two approaches.  I've had luck in
the past with implementing something a couple different ways to help drive
consensus.

-- 
nathan

Hi Hunaid

On 02.08.24 14:11, Hunaid Sohail wrote:
>
> I have also edited the documentation and added it to the patch. Please
> let me know if any changes are required.
>

I just wanted to review this patch again but v30 does not apply

=== Applying patches on top of PostgreSQL commit ID d8df7ac5c04cd17bf13bd3123dcfcaf8007c6280 ===
/etc/rc.d/jail: WARNING: Per-jail configuration via jail_* variables  is obsolete.  Please consider migrating to
/etc/jail.conf.
=== applying patch ./v30-0001-psql-meta-command-conninfo-plus.patch
patch unexpectedly ends in middle of line
gpatch: **** Only garbage was found in the patch input.


I will set the status to "Waiting on Author".

-- 
Jim

On 10.09.24 06:32, Hunaid Sohail wrote:
>
> I have attached a rebased patch.

Thanks.

Is \conninfo+ no longer supposed to return the results in tabular form?
At least it wasn't the case till v28.

$ /usr/local/postgres-dev/bin/psql -d postgres -h 0 -c "\conninfo+"
You are connected to database "postgres" as user "jim" on host "0"
(address "0.0.0.0") at port "5432".
Protocol Version: 3
SSL Connection: no
GSSAPI Authenticated: no
Client Encoding: UTF8
Server Encoding: UTF8
Session User: jim
Backend PID: 579041

$ /usr/local/postgres-dev/bin/psql -d postgres -h 127.0.0.1 -c "\conninfo+"
You are connected to database "postgres" as user "jim" on host
"127.0.0.1" at port "5432".
Protocol Version: 3
SSL Connection: no
GSSAPI Authenticated: no
Client Encoding: UTF8
Server Encoding: UTF8
Session User: jim
Backend PID: 579087

Sorry if I missed that in the thread.

v31 has a couple of small indentation problems:

/home/jim/patches/conninfo/v31-0001-Add-psql-meta-command-conninfo-plus.patch:87:
indent with spaces.
    show_verbose = strchr(cmd, '+') ? true : false;
/home/jim/patches/conninfo/v31-0001-Add-psql-meta-command-conninfo-plus.patch:106:
trailing whitespace.

Checking patch doc/src/sgml/ref/psql-ref.sgml...
Checking patch src/bin/psql/command.c...
Checking patch src/bin/psql/help.c...
Applied patch doc/src/sgml/ref/psql-ref.sgml cleanly.
Applied patch src/bin/psql/command.c cleanly.
Applied patch src/bin/psql/help.c cleanly.
warning: 2 lines add whitespace errors.


-- 
Jim

On 2024-Sep-10, Jim Jones wrote:

> Is \conninfo+ no longer supposed to return the results in tabular form?
> At least it wasn't the case till v28.

I suspect the reason it's no longer a table is that it was previously a
query (which is easily printed as a table by calling printQuery) and now
it's just a client-side thing, and Hunaid didn't know how to handle that
as a table.  The good news is, it should be really easy to do
printTableInit(), then a bunch of printTableAddHeader() and
printTableAddCell(), end with printTable().  I think the tabular format
is better for sure.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)

On 11.09.24 10:16, Hunaid Sohail wrote:
> I have made the requested changes. Now output is returned in tabular 
> form. Indentation/whitespace issues are fixed.
>
> $bin/psql --port=5430 postgres
> postgres=# \conninfo+
> You are connected to database "postgres" as user "hunaid" via socket 
> in "/tmp" at port "5430".
>     Connection Information
>       Parameter       | Value
> ----------------------+--------
>  Protocol Version     | 3
>  SSL Connection       | no
>  GSSAPI Authenticated | no
>  Client Encoding      | UTF8
>  Server Encoding      | UTF8
>  Session User         | hunaid
>  Backend PID          | 121800
> (7 rows)

Thanks for working on this.

Any particular reason for the design change? In v28 it returned a table 
with a single row and multiple columns --- one column per attribute. But 
now it returns multiple rows. In this case, I was expecting 1 row with 7 
columns instead of 7 rows with 2 columns.

Jim

On 11.09.24 13:35, Hunaid Sohail wrote:
> Hi Jim,
>
> On Wed, Sep 11, 2024 at 3:03 PM Jim Jones <jim.jones@uni-muenster.de>
> wrote:
>
>     Thanks for working on this.
>
>     Any particular reason for the design change? In v28 it returned a
>     table
>     with a single row and multiple columns --- one column per
>     attribute. But
>     now it returns multiple rows. In this case, I was expecting 1 row
>     with 7
>     columns instead of 7 rows with 2 columns.
>
>
> I am not sure which design you are referring to.
> I haven't applied the v28 patch but the original author in thread [1]
> provided sample output. The output is in tabular form with 2 columns
> and multiple rows.
>
>
[1] https://www.postgresql.org/message-id/CP8P284MB249615AED23882E1E185C8ABEC3C2%40CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM 
>
It may look like this, but it is a single record --- mind the header "-[
RECORD 1 ]----------------+---------".
psql was called in expanded mode:

> $ /home/pgsql-17devel/bin/psql -x -p 5432

"-x" or "--expanded"

Example:

$ psql postgres -xc "SELECT 'foo' col1, 'bar' col2"
-[ RECORD 1 ]
col1 | foo
col2 | bar

-- 
Jim

On 13.09.24 06:49, Hunaid Sohail wrote:
>
> $ bin/psql --port=5430 postgres
> psql (18devel)
> Type "help" for help.
>
> postgres=# \conninfo+
> You are connected to database "postgres" as user "hunaid" via socket
> in "/tmp" at port "5430".
>                                                   Connection Information
>  Protocol Version | SSL Connection | GSSAPI Authenticated | Client
> Encoding | Server Encoding | Session User | Backend P
> ID
>
------------------+----------------+----------------------+-----------------+-----------------+--------------+----------
> ---
>  3                | no             | no                   | UTF8      
>      | UTF8            | hunaid       | 55598
> (1 row)

Nice.

I just noticed that messages' order has been slightly changed. The
message "You are connected to database "postgres" as user "hunaid" via
socket in "/tmp" at port "5430" used to be printed after the table, and
now it is printed before.

$ /usr/local/postgres-dev/bin/psql -x "\
    hostaddr=0
    user=jim dbname=postgres
    port=54322" -c "\conninfo+"

You are connected to database "postgres" as user "jim" on host "0"
(address "0.0.0.0") at port "54322".
Connection Information
-[ RECORD 1 ]--------+--------
Protocol Version     | 3
SSL Connection       | no
GSSAPI Authenticated | no
Client Encoding      | UTF8
Server Encoding      | UTF8
Session User         | jim
Backend PID          | 2419033

It is IMHO a little strange because the "SSL connection" info keeps
being printed in the end. I would personally prefer if they're printed
together --- preferably after the table. But I'm not sure if there's any
convention for that.

$ /usr/local/postgres-dev/bin/psql -x "\
    host=server.uni-muenster.de
    hostaddr=127.0.0.1
    user=jim dbname=postgres
    port=54322
    sslmode=verify-full
    sslrootcert=server-certificates/server.crt
    sslcert=jim-certificates/jim.crt
    sslkey=jim-certificates/jim.key" -c "\conninfo+"

You are connected to database "postgres" as user "jim" on host
"server.uni-muenster.de" (address "127.0.0.1") at port "54322".
Connection Information
-[ RECORD 1 ]--------+--------
Protocol Version     | 3
SSL Connection       | yes
GSSAPI Authenticated | no
Client Encoding      | UTF8
Server Encoding      | UTF8
Session User         | jim
Backend PID          | 2421556

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off, ALPN: postgresql)

Also, there are a few compilation warnings regarding const qualifiers:

command.c:810:49: warning: assignment discards ‘const’ qualifier from
pointer target type [-Wdiscarded-qualifiers]
  810 |                                 client_encoding =
PQparameterStatus(pset.db, "client_encoding");
      |                                                 ^
command.c:811:49: warning: assignment discards ‘const’ qualifier from
pointer target type [-Wdiscarded-qualifiers]
  811 |                                 server_encoding =
PQparameterStatus(pset.db, "server_encoding");
      |                                                 ^
command.c:812:46: warning: assignment discards ‘const’ qualifier from
pointer target type [-Wdiscarded-qualifiers]
  812 |                                 session_user =
PQparameterStatus(pset.db, "session_authorization");


-- 
Jim

On 2024-Sep-14, Hunaid Sohail wrote:

> I agree that both messages should be printed together. IMO the message
> "You are connected to database..." should be printed at the top, no?
> Because it shows important info that the user may be interested to see
> first. Then we can combine the ssl message.
> 
> postgres=# \x
> Expanded display is on.
> postgres=# \conninfo+
> You are connected to database "postgres" as user "hunaid" on host
> "localhost" (address "127.0.0.1") at port "5430".
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
> compression: off, ALPN: postgresql)
> Connection Information
> -[ RECORD 1 ]--------+-------
> Protocol Version     | 3
> SSL Connection       | yes
> GSSAPI Authenticated | no
> Client Encoding      | UTF8
> Server Encoding      | UTF8
> Session User         | hunaid
> Backend PID          | 109092

I don't understand why this is is printing half the information in
free-form plain text and the other half in tabular format.  All these
items that you have in the free-form text lines should be part of the
table, I think.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
#error "Operator lives in the wrong universe"
  ("Use of cookies in real-time system development", M. Gleixner, M. Mc Guire)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> I don't understand why this is is printing half the information in
> free-form plain text and the other half in tabular format.  All these
> items that you have in the free-form text lines should be part of the
> table, I think.

+1, that was my reaction as well.  I can see the point of showing
those items the same way as plain \conninfo does, but I think
we're better off just making \conninfo+ produce a table and nothing
else.

            regards, tom lane

On 16.09.24 08:51, Hunaid Sohail wrote:
> I have attached a new patch that now prints all info in tabular format
> for \conninfo+. I have also made the table output dynamic, so if the
> connection uses SSL, the columns in the table will expand accordingly.
>
It looks much cleaner now.
> I have also updated the documentation.

The CF bot is still giving some warnings:

command.c:886:79: error: ‘alpn’ may be used uninitialized
[-Werror=maybe-uninitialized]
  886 |                                         printTableAddCell(&cont,
(alpn && alpn[0] != '\0') ? alpn : _("none"), false, false);
     
|                                                                          
~~~~^~~
command.c:803:50: note: ‘alpn’ was declared here
  803 |                                                 *alpn;
      |                                                  ^~~~
command.c:885:82: error: ‘compression’ may be used uninitialized
[-Werror=maybe-uninitialized]
  885 |                                         printTableAddCell(&cont,
(compression && strcmp(compression, "off") != 0) ? _("on") : _("off"),
false, false);
     
|                                                                                 
^~~~~~~~~~~~~~~~~~~~~~~~~~
command.c:802:50: note: ‘compression’ was declared here
  802 |                                                 *compression,
      |                                                  ^~~~~~~~~~~
command.c:884:41: error: ‘cipher’ may be used uninitialized
[-Werror=maybe-uninitialized]
  884 |                                         printTableAddCell(&cont,
cipher ? cipher : _("unknown"), false, false);
      |                                        
^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
command.c:801:50: note: ‘cipher’ was declared here
  801 |                                                 *cipher,
      |                                                  ^~~~~~
command.c:883:41: error: ‘protocol’ may be used uninitialized
[-Werror=maybe-uninitialized]
  883 |                                         printTableAddCell(&cont,
protocol ? protocol : _("unknown"), false, false);
      |                                        
^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
command.c:800:42: note: ‘protocol’ was declared here
  800 |                                 char    *protocol,
      |                                          ^~~~~~~~

I have a few questions regarding this example:

$ /usr/local/postgres-dev/bin/psql -x "\
    host=server.uni-muenster.de
    hostaddr=192.168.178.27
    user=jim dbname=db port=5432
    sslmode=verify-full
    sslrootcert=server-certificates/server.crt
    sslcert=jim-certificates/jim.crt
    sslkey=jim-certificates/jim.key"

psql (18devel)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off, ALPN: postgresql)
Type "help" for help.

db=# SET ROLE foo;
SET

db=> SELECT current_user, session_user;
-[ RECORD 1 ]+----
current_user | foo
session_user | jim

db=> \conninfo+
Connection Information
-[ RECORD 1 ]--------+-----------------------
Database             | db
Current User         | jim
Session User         | jim
Host                 | server.uni-muenster.de
Host Address         | 192.168.178.27
Port                 | 5432
Protocol Version     | 3
SSL Connection       | yes
SSL Protocol         | TLSv1.3
Cipher               | TLS_AES_256_GCM_SHA384
Compression          | off
ALPN                 | postgresql
GSSAPI Authenticated | no
Client Encoding      | UTF8
Server Encoding      | UTF8
Backend PID          | 315187


* The value of "Current User" does not match the function current_user()
--- as one might expcect. It is a little confusing, as there is no
mention of "Current User" in the docs. In case this is the intended
behaviour, could you please add it to the docs?

* "SSL Connection" says "yes", but the docs say: "True if the current
connection to the server uses SSL, and false otherwise.". Is it supposed
to be like this? I haven't checked other similar doc entries..


--
Jim

On 2024-Sep-16, Jim Jones wrote:

> * The value of "Current User" does not match the function current_user()
> --- as one might expcect. It is a little confusing, as there is no
> mention of "Current User" in the docs. In case this is the intended
> behaviour, could you please add it to the docs?

It is intended.  As Peter said[1], what we wanted was to display
client-side info, so PQuser() is the right thing to do.  Now maybe
"Current User" is not the perfect column header, but at least the
definition seems consistent with the desired end result.  Now, I think
the current docs saying to look at session_user() are wrong, they should
point to the libpq docs for the function instead; something like "The
name of the current user, as returned by PQuser()" and so on.
Otherwise, in the cases where these things differ, it is going to be
hard to explain.

[1] https://postgr.es/m/f4fc729d-7903-4d58-995d-6cd146049992@eisentraut.org

> * "SSL Connection" says "yes", but the docs say: "True if the current
> connection to the server uses SSL, and false otherwise.". Is it supposed
> to be like this? I haven't checked other similar doc entries..

Yeah, I think we should print what a boolean value would look like from
SQL, so "true" rather than "yes".


I think the code structure is hard to follow.  It would be simpler if it
was a bunch of

/* Database */
printTableAddHeader(&cont, _("Database"), true, 'l');
printTableAddCell(&cont, db, false, false);
...
/* Port */
printTableAddHeader(&cont, _("Por"), true, 'l');
printTableAddCell(&cont, PQport(pset.db), false, false);
/* ... */

And so on.  I don't think the printTable() API forces you to set all
headers first followed by all cells.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2024-Sep-16, Jim Jones wrote:
>> * The value of "Current User" does not match the function current_user()
>> --- as one might expcect. It is a little confusing, as there is no
>> mention of "Current User" in the docs. In case this is the intended
>> behaviour, could you please add it to the docs?

> It is intended.  As Peter said[1], what we wanted was to display
> client-side info, so PQuser() is the right thing to do.  Now maybe
> "Current User" is not the perfect column header, but at least the
> definition seems consistent with the desired end result.

Seems like "Session User" would be closer to being accurate, since
PQuser()'s result does not change when you do SET ROLE etc.

> Now, I think
> the current docs saying to look at session_user() are wrong, they should
> point to the libpq docs for the function instead; something like "The
> name of the current user, as returned by PQuser()" and so on.

Sure, but this does not excuse choosing a misleading column name
when there are better choices readily available.

            regards, tom lane

Hi

On 26.09.24 09:15, Hunaid Sohail wrote:
> This patch renames "Current User" to "Authenticated User" as suggested
> by me in my last email. I have also updated the documentation accordingly.

Could you perhaps in the documentation elaborate a bit more on the
difference between "Current User" and "Authenticated User"? IMHO a
couple of words on how exactly they differ would be very helpful, as
they show the same user in many cases.

> Authenticated User: The name of the user returned by PQuser()
> Session User: The session user's name.

Although a bit redundant, I'd argue that "SSL Compression" is better
than just "Compression".

Other than that, it looks much better now:

$ /usr/local/postgres-dev/bin/psql -x "\
    host=server.uni-muenster.de
    hostaddr=192.168.178.27
    user=jim dbname=db
    port=54322
    sslmode=verify-full
    sslrootcert=server-certificates/server.crt
    sslcert=jim-certificates/jim.crt
    sslkey=jim-certificates/jim.key"

psql (18devel)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off, ALPN: postgresql)
Type "help" for help.

db=# SET client_encoding TO 'LATIN1';
SET

db=# \conninfo+
Connection Information
-[ RECORD 1 ]--------+-----------------------
Database             | db
Authenticated User   | jim
Session User         | jim
Host                 | server.uni-muenster.de
Host Address         | 192.168.178.27
Port                 | 54322
Protocol Version     | 3
SSL Connection       | true
SSL Protocol         | TLSv1.3
SSL Cipher           | TLS_AES_256_GCM_SHA384
Compression          | off
ALPN                 | postgresql
GSSAPI Authenticated | false
Client Encoding      | LATIN1
Server Encoding      | UTF8
Backend PID          | 874890


Thanks!

On 01.10.24 06:27, Hunaid Sohail wrote:
> There are two users in the conninfo+: 'session' and 'authenticated'.
> Both are documented.

Right. I meant "Session User"

> Authenticated User: The name of the user returned by PQuser()
> Session User: The session user's name.

Thanks

-- 
Jim

On 02.10.24 06:48, Hunaid Sohail wrote:
> Should I revert to the v34 docs for Session User, or is it fine as is?

What I tried to say is that the current description is a bit vague ---
specially "Authenticated User".

> Authenticated User: The name of the user returned by PQuser()
> Session User: The session user's name.

I thought it would be nice to have a description that tells how both
Session and Authenticated users differ. IMHO *only* a reference to
PQuser() doesn't say much, but others might be ok with it. So let's see
what the other reviewers say.

-- 
Jim

Authenticated User: The name of the user returned by PQuser(), indicating the user who initiated or authenticated the current database connection.

Session User: The session user's name, which is initially the same as the authenticated user but can be changed with SET SESSION AUTHORIZATION. See the session_user() function in <xref linkend="functions-info-session-table"/> for more details.